0

My SQL Query is,

SELECT  EAL_TYPE,
    EAL_ID,
    ROW_NUMBER ()OVER(PARTITION BY EAL_TYPE,EAL_ID ORDER BY EAL_TYPE,EAL_ID,EAL_ACTION_TIME Desc)SL#,
    EAL_STATUS,
    EAL_ACTION_TYPE,
    EAL_CMPCODE,
    ESSET_Description,
    EAL_SHOW_IN_ALERT,
    ESSETRS_WEIGHTAGE,
    Usr_UserRole,ESSET_HeaderTable_Prefix,ESSET_HeaderTable
FROM ESS_ACTION_LOG 
LEFT JOIN ESS_ENTRYTYPE_MASTER ON
       ESSET_ID=EAL_TYPE    
LEFT JOIN User_Profile ON
       Usr_LoginID='MK' 
LEFT JOIN ESS_ENTRYTYPE_ROLE_SETTINGS ON
       ESSETRS_CMPCODE = EAL_CMPCODE AND
       ESSETRS_ID = EAL_TYPE AND
       ESSETRS_ROLE_ID = Usr_UserRole
       INNER JOIN ESSET_HeaderTable ON
        ESSET_HeaderTable_Prefix+'_CMPCode'=EAL_CMPCODE AND
        ESSET_HeaderTable_Prefix+'_Type'=EAL_TYPE AND
        ESSET_HeaderTable_Prefix+'_ID'=EAL_ID
WHERE ESSETRS_WEIGHTAGE IS NOT NULL

In the above query, while executing it shows an error like 'ESSET_HeaderTable object name not found'. But that table name needs to come dynamically from another table. What's wrong with my query. Can anyone help me with this?

Arya
  • 504
  • 2
  • 8
  • 31
  • 2
    You can't use a column's value, or a variable to replace something that needs to be a literal; the error is correct you don't have a **`TABLE`** with the name `ESSET_HeaderTable`. You'd have to use dynamic SQL for this, however, the fact you want to do this infers you have a design flaw you should be correcting. A database's design should not require dynamic SQL to work correctly. – Thom A Dec 15 '20 at 11:00
  • 1
    Separate note, I strongly suggest using aliases and prefixing your columns; we have no idea where all of your columns are coming from, and neither will anyone else in your company that isn't very familiar with your database. I *assume* that `EAL_CMPCODE`, `EAL_TYPE` and `EAL_ID` are all from `ESS_ACTION_LOG`, however you have an `INNER JOIN` to your dynamic table `ESSET_HeaderTable` which I assume is coming from elsewhere; so that causes the prior `LEFT JOIN` to implicitly be an `INNER JOIN`. `ESSETRS_WEIGHTAGE` will have the same effect in the `WHERE` too. There's a lot that needs fixing. – Thom A Dec 15 '20 at 11:19
  • @Arya . . . Something is wrong with your data model if you are even considering constructing table names like this for a query. – Gordon Linoff Dec 15 '20 at 11:33

0 Answers0