Nested SQL joins

[Rohit_R]

Hi

I am having some syntax trouble with this piece of code. It works fine when running in SQl but when running it using proc sql, it doesnt work except in the innermost select statement. I am not sure where the syntax error is :/.

The error is:

55 ) order by ORGN_ID)
_____ _
79 22
200
ERROR 79-322: Expecting a ).

ERROR 22-322: Syntax error, expecting one of the following: ;, !, !!, &, (, *, **, +, ',', -, '.', /, <, <=, <>, =, >, >=, ?, AND,
ASC, ASCENDING, BETWEEN, CONTAINS, DESC, DESCENDING, EQ, EQT, GE, GET, GT, GTT, IN, IS, LE, LET, LIKE, LT, LTT, NE,
NET, NOT, NOTIN, OR, ^, ^=, |, ||, ~, ~=.

The code is:
proc sql;
create table test1 as

select ORGN_ID, count(*) as V
from (
    select ORGN_ID, ID_PERSON,count(distinct ID_PERSON)
    from (
            select *
            from (
                      select AP.ORGN_ID,XX.ID_PERSON, count(AP.ORGN_ID) as VOL
                      from PC.AP_SEC AP
                      left join PC.KEY_PERSON XX
                      on AP.ID_APP=XX.ID_APP 
                      left join PC.BUSINESS BS
                      on AP.ID_BUSINESS=BS.ID_BUSINESS
                      where BS.BSNSS_TYP = 'Z'
                      and (XX.SRNM is not null or XX.TTL is not null or XX.DT_OF_BRTH is not null )
                      group by AP.ORGN_ID,XX.ID_PERSON
                      ) order by ORGN_ID
            ) group by ORGN_ID, ID_PERSON
      ) group by ORGN_ID;
quit;

[FreelanceReinh · Accepted solution]

Hi @Rohit_R,

The ORDER BY clause must be at the end of the outermost query to be syntactically correct. (Note that there's no need to sort anything else.) Due to the GROUP BY clause (with the same key variable) it should be redundant anyway, so you can simply delete it. Check section "Sort Information" of PROC CONTENTS output for table test1 to verify this.

For further simplification I'd suggest to replace "select * from (innermost query)" by "innermost query".

[Rohit_R]

Thank you FreelanceReinhard

Removing the Order BY clause worked like a charm. 

Cheers

Rohit