Hi Mark,

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

Thanks,

Manish Jawla

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