Link to home
Start Free TrialLog in
Avatar of Zack
ZackFlag for Australia

asked on

SQL - Invalid Object within a subquery

Hi EE,

For the following code, how would select data from the TAXTRANS TT table in the bold section of the SQL code below? Current the SQL engine is giving the error TT is an invalid object.


SELECT
      *
FROM (
      SELECT
           TT.TRANSDATE,
            TT.TAXAMOUNTCUR , TT.TAXORIGIN , TT.VOUCHER , TT.TAXITEMGROUP
          , TT.TAXCODE , TT.SOURCEBASEAMOUNTCUR , TT.SOURCETAXAMOUNTCUR
          , TTGJAERIVA.TAXTRANSRELATIONSHIP
          , TTGJAERIVA.GENERALJOURNALACCOUNTENTRY
          , TTGJAERIVA.LEDGERDIMENSION
          , GJAERIVA.TEXT
          , GJAERIVA.LEDGERDIMENSION AS LEDGERDIMENSIONGAE
          , GJAERIVA.POSTINGTYPE
      FROM TAXTRANS TT
      INNER MERGE JOIN TAXTRANSGENERALJOURNALACCOUNTENTRY TTGJAERIVA ON TTGJAERIVA.TAXTRANS = TT.RECID
      INNER MERGE JOIN GENERALJOURNALACCOUNTENTRY GJAERIVA ON TTGJAERIVA.GENERALJOURNALACCOUNTENTRY = GJAERIVA.RECID
            AND GJAERIVA.POSTINGTYPE IN (14, 236, 71, 41)
            AND TT.TRANSDATE >= '2016-10-10'
      WHERE (TT.TAXORIGIN <> 11
     AND TT.TRANSDATE BETWEEN '2017-10-10' AND '2017-12-15')
     OR (TT.TAXORIGIN = 11)
      -- AND TT.TRANSDATE BETWEEN '2017-10-20' AND '2017-10-25')
    ) AS T
LEFT MERGE JOIN (
      SELECT
            VOUCHER , SOURCERECID , TAXITEMGROUP , TAXCODE
          , GENERALJOURNALACCOUNTENTRY , TAXAMOUNT , TAXAMOUNTCUR
      FROM TT
    ) TTRISR ON T.VOUCHER = TTRISR.VOUCHER
          AND T.SOURCERECID = TTRISR.SOURCERECID
          AND T.TAXITEMGROUP = TTRISR.TAXITEMGROUP
          AND (TTRISR.TAXCODE LIKE 'R%')
          AND TTRISR.GENERALJOURNALACCOUNTENTRY = TTGJAE.GENERALJOURNALACCOUNTENTRY

Open in new window


Any assistance is appreciated.

Thank you. 
Avatar of PortletPaul
PortletPaul
Flag of Australia image

remove the word "MERGE" i.e. try it with INNER JOIN & LEFT JOIN

SELECT
      *
FROM (
      SELECT
           TT.TRANSDATE,
            TT.TAXAMOUNTCUR , TT.TAXORIGIN , TT.VOUCHER , TT.TAXITEMGROUP
          , TT.TAXCODE , TT.SOURCEBASEAMOUNTCUR , TT.SOURCETAXAMOUNTCUR
          , TTGJAERIVA.TAXTRANSRELATIONSHIP
          , TTGJAERIVA.GENERALJOURNALACCOUNTENTRY
          , TTGJAERIVA.LEDGERDIMENSION
          , GJAERIVA.TEXT
          , GJAERIVA.LEDGERDIMENSION AS LEDGERDIMENSIONGAE
          , GJAERIVA.POSTINGTYPE
      FROM TAXTRANS TT
      INNER JOIN TAXTRANSGENERALJOURNALACCOUNTENTRY TTGJAERIVA ON TTGJAERIVA.TAXTRANS = TT.RECID
      INNER JOIN GENERALJOURNALACCOUNTENTRY GJAERIVA ON TTGJAERIVA.GENERALJOURNALACCOUNTENTRY = GJAERIVA.RECID
            AND GJAERIVA.POSTINGTYPE IN (14, 236, 71, 41)
            AND TT.TRANSDATE >= '2016-10-10'
      WHERE (TT.TAXORIGIN <> 11
     AND TT.TRANSDATE BETWEEN '2017-10-10' AND '2017-12-15')
     OR (TT.TAXORIGIN = 11)
      -- AND TT.TRANSDATE BETWEEN '2017-10-20' AND '2017-10-25')
    ) AS T
LEFT JOIN (
      SELECT
            VOUCHER , SOURCERECID , TAXITEMGROUP , TAXCODE
          , GENERALJOURNALACCOUNTENTRY , TAXAMOUNT , TAXAMOUNTCUR
      FROM TT
    ) TTRISR ON T.VOUCHER = TTRISR.VOUCHER
          AND T.SOURCERECID = TTRISR.SOURCERECID
          AND T.TAXITEMGROUP = TTRISR.TAXITEMGROUP
          AND (TTRISR.TAXCODE LIKE 'R%')
          AND TTRISR.GENERALJOURNALACCOUNTENTRY = TTGJAE.GENERALJOURNALACCOUNTENTRY

Open in new window

Just a note on the last subquery.

In your original query there really is no need to make that a subquery, instead just join the table directly
LEFT JOIN TT AS TTRISR ON T.VOUCHER = TTRISR.VOUCHER
          AND T.SOURCERECID = TTRISR.SOURCERECID
          AND T.TAXITEMGROUP = TTRISR.TAXITEMGROUP
          AND (TTRISR.TAXCODE LIKE 'R%')
          AND TTRISR.GENERALJOURNALACCOUNTENTRY = TTGJAE.GENERALJOURNALACCOUNTENT

Open in new window

OR, if you want to separate out the filtering logic from the join logic, then move one condition into the subquery:
LEFT JOIN (
   SELECT VOUCHER
      ,SOURCERECID
      ,TAXITEMGROUP
      ,TAXCODE
      ,GENERALJOURNALACCOUNTENTRY
      ,TAXAMOUNT
      ,TAXAMOUNTCUR
   FROM TT
   WHERE TAXCODE LIKE 'R%'
   ) AS TTRISR ON T.VOUCHER = TTRISR.VOUCHER
   AND T.SOURCERECID = TTRISR.SOURCERECID
   AND T.TAXITEMGROUP = TTRISR.TAXITEMGROUP
   AND TTRISR.GENERALJOURNALACCOUNTENTRY = TTGJAE.GENERALJOURNALACCOUNTENTRY

Open in new window


OH, hold on. Are you trying to "re-use" the alias "TT"? that's not valid

just use the correct table name instead: TAXTRANS  
LEFT JOIN (
   SELECT VOUCHER
      ,SOURCERECID
      ,TAXITEMGROUP
      ,TAXCODE
      ,GENERALJOURNALACCOUNTENTRY
      ,TAXAMOUNT
      ,TAXAMOUNTCUR
   FROM TAXTRANS  
   WHERE TAXCODE LIKE 'R%'
   ) AS TTRISR ON T.VOUCHER = TTRISR.VOUCHER
   AND T.SOURCERECID = TTRISR.SOURCERECID
   AND T.TAXITEMGROUP = TTRISR.TAXITEMGROUP
   AND TTRISR.GENERALJOURNALACCOUNTENTRY = TTGJAE.GENERALJOURNALACCOUNTENTRY

Open in new window




Avatar of Zack

ASKER

HI PortletPaul,

That's the correct last comment, the code was mostly right but the last line :

LEFT JOIN (
   SELECT VOUCHER
      ,SOURCERECID
      ,TAXITEMGROUP
      ,TAXCODE
      --,GENERALJOURNALACCOUNTENTRY
      ,TAXAMOUNT
      ,TAXAMOUNTCUR
   FROM TAXTRANS  
   WHERE TAXCODE LIKE 'R%'
   ) AS TTRISR ON T.VOUCHER = TTRISR.VOUCHER
   AND SOURCERECID = TTRISR.SOURCERECID
   AND T.TAXITEMGROUP = TTRISR.TAXITEMGROUP
   AND TTRISR.GENERALJOURNALACCOUNTENTRY = TTGJAERIVA.GENERALJOURNALACCOUNTENTRY

Msg 207, Level 16, State 1, Line 37
Invalid column name 'GENERALJOURNALACCOUNTENTRY'.
Msg 4104, Level 16, State 1, Line 37
The multi-part identifier "TTGJAERIVA.GENERALJOURNALACCOUNTENTRY" could not be bound.

This is because I need to select from alias T to make the last line of the above query work, but the alias T isn't recognised. I think this what i am supposed to do. 

:
LEFT JOIN (
   SELECT VOUCHER
      ,SOURCERECID
      ,TAXITEMGROUP
      ,TAXCODE
      --,GENERALJOURNALACCOUNTENTRY
      ,TAXAMOUNT
      ,TAXAMOUNTCUR
   FROM T
   WHERE TAXCODE LIKE 'R%'
   ) AS TTRISR ON T.VOUCHER = TTRISR.VOUCHER
   AND T.SOURCERECID = TTRISR.SOURCERECID
   AND T.TAXITEMGROUP = TTRISR.TAXITEMGROUP
   AND TTRISR.GENERALJOURNALACCOUNTENTRY = TTGJAERIVA.GENERALJOURNALACCOUNTENTRY

Open in new window


How would I go about fixing this.?

Thank you.

ASKER CERTIFIED SOLUTION
Avatar of PortletPaul
PortletPaul
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I would write such a query using CTE's. Then you see easier what happens:

WITH T AS
  (SELECT TT.TRANSDATE,
          TT.TAXAMOUNTCUR,
          TT.TAXORIGIN,
          TT.VOUCHER,
          TT.TAXITEMGROUP,
          TT.TAXCODE,
          TT.SOURCEBASEAMOUNTCUR,
          TT.SOURCETAXAMOUNTCUR,
          TTGJAERIVA.TAXTRANSRELATIONSHIP,
          TTGJAERIVA.GENERALJOURNALACCOUNTENTRY,
          TTGJAERIVA.LEDGERDIMENSION,
          GJAERIVA.TEXT,
          GJAERIVA.LEDGERDIMENSION AS LEDGERDIMENSIONGAE,
          GJAERIVA.POSTINGTYPE
   FROM TAXTRANS TT
      INNER MERGE JOIN TAXTRANSGENERALJOURNALACCOUNTENTRY TTGJAERIVA ON TTGJAERIVA.TAXTRANS = TT.RECID
      INNER MERGE JOIN GENERALJOURNALACCOUNTENTRY GJAERIVA ON TTGJAERIVA.GENERALJOURNALACCOUNTENTRY = GJAERIVA.RECID
                                                               AND GJAERIVA.POSTINGTYPE IN (14, 236, 71, 41)
                                                               AND TT.TRANSDATE >= '2016-10-10'
   WHERE (TT.TAXORIGIN <> 11 AND TT.TRANSDATE BETWEEN '2017-10-10' AND '2017-12-15')
     OR (TT.TAXORIGIN = 11) ),
     TTRISR AS
  (SELECT VOUCHER,
          SOURCERECID,
          TAXITEMGROUP,
          TAXCODE,
          GENERALJOURNALACCOUNTENTRY,
          TAXAMOUNT,
          TAXAMOUNTCUR
   FROM TT)
SELECT *
FROM T
   LEFT MERGE JOIN TTRISR ON T.VOUCHER = TTRISR.VOUCHER
                                AND T.SOURCERECID = TTRISR.SOURCERECID
                                AND T.TAXITEMGROUP = TTRISR.TAXITEMGROUP
                                AND (TTRISR.TAXCODE LIKE 'R%')
                                AND TTRISR.GENERALJOURNALACCOUNTENTRY = TTGJAE.GENERALJOURNALACCOUNTENTRY;

Open in new window

Now you see, that you use an table alias from a different scope.

Thus can line 31 to

FROM TAXTRANS TT)

Open in new window

Then only the error in the last line from the undeclared table or alias TTGJAE remains to be solved.

When the reuse of TT implies that you want to reuse the filtered set from T, then it should be

WITH TT AS
  (SELECT TT.TRANSDATE,
          TT.TAXAMOUNTCUR,
          TT.TAXORIGIN,
          TT.VOUCHER,
          TT.TAXITEMGROUP,
          TT.TAXCODE,
          TT.SOURCEBASEAMOUNTCUR,
          TT.SOURCETAXAMOUNTCUR,
          TTGJAERIVA.TAXTRANSRELATIONSHIP,
          TTGJAERIVA.GENERALJOURNALACCOUNTENTRY,
          TTGJAERIVA.LEDGERDIMENSION
   FROM TAXTRANS TT
   WHERE TT.TRANSDATE >= '2016-10-10'
      AND (TT.TAXORIGIN <> 11 AND TT.TRANSDATE BETWEEN '2017-10-10' AND '2017-12-15')
        OR (TT.TAXORIGIN = 11) ),
T AS
  (SELECT TT.TRANSDATE,
          TT.TAXAMOUNTCUR,
          TT.TAXORIGIN,
          TT.VOUCHER,
          TT.TAXITEMGROUP,
          TT.TAXCODE,
          TT.SOURCEBASEAMOUNTCUR,
          TT.SOURCETAXAMOUNTCUR,
          TTGJAERIVA.TAXTRANSRELATIONSHIP,
          TTGJAERIVA.GENERALJOURNALACCOUNTENTRY,
          TTGJAERIVA.LEDGERDIMENSION,
          GJAERIVA.TEXT,
          GJAERIVA.LEDGERDIMENSION AS LEDGERDIMENSIONGAE,
          GJAERIVA.POSTINGTYPE
   FROM TT
      INNER MERGE JOIN TAXTRANSGENERALJOURNALACCOUNTENTRY TTGJAERIVA ON TTGJAERIVA.TAXTRANS = TT.RECID
      INNER MERGE JOIN GENERALJOURNALACCOUNTENTRY GJAERIVA ON TTGJAERIVA.GENERALJOURNALACCOUNTENTRY = GJAERIVA.RECID
                                                               AND GJAERIVA.POSTINGTYPE IN (14, 236, 71, 41)
   ),
     TTRISR AS
  (SELECT VOUCHER,
          SOURCERECID,
          TAXITEMGROUP,
          TAXCODE,
          GENERALJOURNALACCOUNTENTRY,
          TAXAMOUNT,
          TAXAMOUNTCUR
   FROM TT)
SELECT *
FROM T
   LEFT MERGE JOIN TTRISR ON T.VOUCHER = TTRISR.VOUCHER
                                AND T.SOURCERECID = TTRISR.SOURCERECID
                                AND T.TAXITEMGROUP = TTRISR.TAXITEMGROUP
                                AND (TTRISR.TAXCODE LIKE 'R%')
                                AND TTRISR.GENERALJOURNALACCOUNTENTRY = TTGJAE.GENERALJOURNALACCOUNTENTRY;

Open in new window


btw, CTE's and sub-queries perform the same as they normally get the same execution plan.
Avatar of Zack

ASKER

Thank you kindly gents your input is always appreciated hectic day here in the office several fires to put out my apologies for the delay.