SQL & PL/SQL

Hi,

I am a bit new to PL SQL (I've been mostly working with MS SQL so far) and therefore not really sure how can I get only the first row from a correlated subquery in one of the select columns that is also grouped by. The following query is for Oracle NetSuite ODBC.

In NetSuite, when an advanced intercompany journal is created for entity X against intercompany partner Y the transaction is recorded in both entities (SS.NAME = 'X' and SS.NAME = 'Y') with the same Transaction_ID. The purpose of the query is to list transactions for a selected entity. One of the columns returned is the intercompany partner, if the transaction is of intercompany type. Therefore in order to get a name of the intercompany partner for a given transaction ID you need to search for that transaction_id and check whether SS.NAME does not equal current SS.NAME and return that other SS.NAME (being the intercompany partner for the transaction). But since there are at least four rows created for every intercompany transaction we only need to return one row to get the intercompany partner for a transaction.

The following simplified query returns correct results (see table below)

SELECT 
TL.TRANSACTION_ID,
    SS.NAME AS "SUBSIDIARY NAME", 
    (SELECT DISTINCT SS2.NAME FROM TRANSACTION_LINES TL2
            LEFT OUTER JOIN SUBSIDIARIES SS2 ON TL2.SUBSIDIARY_ID = SS2.SUBSIDIARY_ID
            WHERE TL2.TRANSACTION_ID = TL.TRANSACTION_ID AND SS2.NAME <> SS.NAME 
        ) AS ICP_Name
    
FROM TRANSACTIONS TS
    LEFT OUTER JOIN TRANSACTION_LINES TL ON TS.TRANSACTION_ID = TL.TRANSACTION_ID
    LEFT OUTER JOIN ACCOUNTS AC ON TL.ACCOUNT_ID  = AC.ACCOUNT_ID
    LEFT OUTER JOIN ACCOUNTING_PERIODS AP ON TS.ACCOUNTING_PERIOD_ID = AP.ACCOUNTING_PERIOD_ID
    LEFT OUTER JOIN COMPANIES CN ON TL.COMPANY_ID = CN.COMPANY_ID
    LEFT OUTER JOIN ENTITY EY ON TS.ENTITY_ID = EY.ENTITY_ID
    LEFT OUTER JOIN SUBSIDIARIES SS ON TL.SUBSIDIARY_ID = SS.SUBSIDIARY_ID
WHERE 
 TL.TRANSACTION_ID = 247602

But if I try to use it in the following query I get the following error

DataSource.Error: ODBC: ERROR [42000] [NetSuite][ODBC 64bit driver][OpenAccess SDK SQL Engine]Invalid SQL Syntax. All columns of the select list & order by should have set functions or be part of GROUP BY clause.[10136]

Details:

    DataSourceKind=Odbc

    DataSourcePath=dsn=NetSuite

    OdbcErrors=Table

I am pretty sure this query would work fine in MS SQL but clearly in PL SQL the SELECT DISTINCT statement cannot be used in a subquery that is grouped. Any ideas?

SELECT 
    SS.NAME AS "SUBSIDIARY NAME", 
    N'NS' AS "SOURCE",
    AP.STARTING AS "POSTING PERIOD START",  
    (CASE WHEN AC.IS_BALANCESHEET = N'T' THEN 'BS' ELSE 'IS' END) AS "STATEMENT TYPE",
    AC.HFM_PARENT AS "HFM ACCOUNT",
    (CASE WHEN AC.HFM_IC_FLAG = 'T' THEN N'Yes' ELSE N'No' END) AS "HFM_IC_FLAG",
    (CASE WHEN TS.IS_ADVANCED_INTERCOMPANY = N'Yes' THEN 
        (SELECT DISTINCT SS2.NAME FROM TRANSACTION_LINES TL2
            LEFT OUTER JOIN SUBSIDIARIES SS2 ON TL2.SUBSIDIARY_ID = SS2.SUBSIDIARY_ID
            WHERE TL2.TRANSACTION_ID = TL.TRANSACTION_ID AND SS2.NAME <> SS.NAME 
        )
    ELSE CASE WHEN AC.HFM_IC_FLAG = 'T' THEN COALESCE(CASE WHEN TL.COMPANY_ID = 10 THEN NULL ELSE SUBSTR(CN.NAME, 1, INSTR(CN.NAME, N' ') -1) END, 
    SUBSTR(EY.NAME, 1, INSTR(EY.NAME, N' ') -1)) ELSE NULL END END) AS "HFM_IC",
    AC.ACCOUNTNUMBER AS "ACCOUNT NUMBER",
    AC.NAME AS "ACCOUNT NAME",
    (CASE WHEN AC.IS_BALANCESHEET = N'F' THEN  DS.HFM_SEGMENT ELSE NULL END) AS "HFM SEGMENT",
    (CASE WHEN AC.IS_BALANCESHEET = N'F' THEN  DS.NAME ELSE NULL END) AS "SEGMENT NAME",
    (CASE WHEN AC.IS_BALANCESHEET = N'F' THEN  COALESCE(CS2.NAME, CS.NAME) ELSE NULL END) AS "HARVEST YEAR",
    (CASE WHEN AC.IS_BALANCESHEET = N'F' THEN  LS.NAME ELSE NULL END) AS "LOCATION",
    (CASE WHEN AC.IS_BALANCESHEET = N'F' THEN  AY.ACTIVITY_NAME ELSE NULL END) AS "ACTIVITY",
    SUM(TL.AMOUNT) AS "AMOUNT (entity currency)"
FROM TRANSACTIONS TS
    LEFT OUTER JOIN TRANSACTION_LINES TL ON TS.TRANSACTION_ID = TL.TRANSACTION_ID
    LEFT OUTER JOIN ACCOUNTS AC ON TL.ACCOUNT_ID  = AC.ACCOUNT_ID
    LEFT OUTER JOIN ACCOUNTING_PERIODS AP ON TS.ACCOUNTING_PERIOD_ID = AP.ACCOUNTING_PERIOD_ID
    LEFT OUTER JOIN COMPANIES CN ON TL.COMPANY_ID = CN.COMPANY_ID
    LEFT OUTER JOIN ENTITY EY ON TS.ENTITY_ID = EY.ENTITY_ID
    LEFT OUTER JOIN SUBSIDIARIES SS ON TL.SUBSIDIARY_ID = SS.SUBSIDIARY_ID
    LEFT OUTER JOIN CLASSES CS ON TL.HARVEST_YEAR_DNU_ID = CS.CLASS_ID
    LEFT OUTER JOIN CLASSES CS2 ON TL.CLASS_ID = CS2.CLASS_ID
    LEFT OUTER JOIN DEPARTMENTS DS ON TL.DEPARTMENT_ID = DS.DEPARTMENT_ID
    LEFT OUTER JOIN LOCATIONS LS ON TL.LOCATION_ID = LS.LOCATION_ID
    LEFT OUTER JOIN ACTIVITY AY ON TL.ACTIVITY_ID = AY.ACTIVITY_ID
WHERE TL.AMOUNT IS NOT NULL AND TL.AMOUNT <> 0
    AND TS.IS_NON_POSTING = N'No'
    AND TL.ACCOUNT_ID IS NOT NULL
GROUP BY 
    SS.NAME, 
    AP.STARTING,
    (CASE WHEN AC.IS_BALANCESHEET = N'T' THEN 'BS' ELSE 'IS' END),
    AC.HFM_PARENT,
    (CASE WHEN AC.HFM_IC_FLAG = 'T' THEN N'Yes' ELSE N'No' END),
    (CASE WHEN TS.IS_ADVANCED_INTERCOMPANY = N'Yes' THEN 
        (SELECT DISTINCT SS2.NAME FROM TRANSACTION_LINES TL2
            LEFT OUTER JOIN SUBSIDIARIES SS2 ON TL2.SUBSIDIARY_ID = SS2.SUBSIDIARY_ID
            WHERE TL2.TRANSACTION_ID = TL.TRANSACTION_ID AND SS2.NAME <> SS.NAME 
        )
    ELSE CASE WHEN AC.HFM_IC_FLAG = 'T' THEN COALESCE(CASE WHEN TL.COMPANY_ID = 10 THEN NULL ELSE SUBSTR(CN.NAME, 1, INSTR(CN.NAME, N' ') -1) END, 
    SUBSTR(EY.NAME, 1, INSTR(EY.NAME, N' ') -1)) ELSE NULL END END),
    AC.ACCOUNTNUMBER,
    AC.NAME,
    (CASE WHEN AC.IS_BALANCESHEET = N'F' THEN  DS.HFM_SEGMENT ELSE NULL END),
    (CASE WHEN AC.IS_BALANCESHEET = N'F' THEN  DS.NAME ELSE NULL END),
    (CASE WHEN AC.IS_BALANCESHEET = N'F' THEN  COALESCE(CS2.NAME, CS.NAME) ELSE NULL END),
    (CASE WHEN AC.IS_BALANCESHEET = N'F' THEN  LS.NAME ELSE NULL END),
    (CASE WHEN AC.IS_BALANCESHEET = N'F' THEN  AY.ACTIVITY_NAME ELSE NULL END)