How to use aliases on subquery in advanced SQL query

How to use aliases on subquery in advanced SQL query

  

I am trying to create a query with a subquery in the Advanced SQL tool. I am getting a SQL command not ended properly error. Could someone tell me what is wrong with this. It doesn't seem to like the closing parenthesis and the AS SUBLIST alias.

Hi Mark,

Try to execute the same query in sql/oracle editor, so that you can easily identify the issue.

Thanks,

Manish Jawla

Solution

Thanks for your reply Manish. I had initially written this query in TOAD editor and it worked perfectly. In trying to convert it to Outsystems syntax, I added the AS in front of the alias. I got that from another post, similar to this subject. Upon your suggestion, I copied the code from the Executed SQL tab, back into my TOAD editor, and saw it didn't like the AS, so I removed it and that resolved the problem. I also had to add an alias to the max statement, in the subquery, so I could reference it in the main query. Here is the final code, that is now working.


SELECT SUBLIST.LC_NO,
       SUBLIST.LC_NAME,
       SUBLIST.LC_NOTES,
       SUBLIST.MAX_LCV_NO,
       {LPMCaseVersion}.[LCV_TVE_NO],
       SUBLIST.VERSION_COUNT
  FROM (SELECT {LPMCase}.[LC_NO],
              {LPMCase}.[LC_NAME],
              {LPMCase}.[LC_NOTES],
              max({LPMCaseVersion}.[LCV_NO]) AS MAX_LCV_NO,
              count({LPMCaseVersion}.[LCV_NO]) AS VERSION_COUNT
         FROM {LPMCase}
        INNER JOIN {LPMCaseVersion}
           ON {LPMCaseVersion}.[LCV_LC_NO] = {LPMCase}.[LC_NO]
        WHERE {LPMCase}.[LC_PMCI_NO] = @ProdMgrContInfoId 
        GROUP BY {LPMCase}.[LC_NO],
                 {LPMCase}.[LC_NAME],
                 {LPMCase}.[LC_NOTES]) SUBLIST
 INNER JOIN {LPMCaseVersion}
    ON {LPMCaseVersion}.[LCV_NO] = SUBLIST.MAX_LCV_NO


Solution