Re: SQL: how to combine a join with WHERE EXISTS

[CRPence <CRPbottle@xxxxxxxxx>]

On 15 Feb 2013 15:24, Vernon Hamberg wrote:
> Your WHERE EXISTS is ahead of the LEFT JOIN
> You also need parentheses around the SELECT that is populating the
> INSERT.

  Actually the INSERT using fullselect does not have parentheses, 
according to the syntax diagram.  Besides, as offered, there is a 
extraneous parenthesis at the end of the statement which would be 
unmatched, even if allowed.  And AFaIK the ORDER BY can not be specified 
on a subquery, which is what the fullselect becomes when inside 
paretheses.  Thus I believe what was offered, must have removed from the 
statement, those parentheses that are enclosing the fullselect; 
including the removal of the spurious right parenthesis at the end.

> Try this:
>
> insert into bzmiscchg OVERRIDING USER VALUE
> (select ' ','I',ASHPNBR,ABLDNGD,APLSQNB,AMSCHRG,
> CTLTEXT3 ChgDesc,sum(AMSCHRG2) chgamt,
> replace(char(curdate(),iso),'-','') || 'T'
> || replace(char(curTime()),':','')
> ,' ',' '
> from miscchg3 a
>
> left outer join ctltable
> on CTLTABLE = 'MISCCHG' and AMSCHRG = CTLELEMENT
>
> where exists
> (select * from APPLSHIP b
> where a.ASHPNBR = b.ASHPNBR
> and a.ABLDNGD = b.ABLDNGD
> and a.APLSQNB = b.APLSQNB
> )
> group by ASHPNBR,ABLDNGD,APLSQNB,AMSCHRG,CTLTEXT3
> order by ASHPNBR,ABLDNGD,APLSQNB,AMSCHRG))

   So the following [excuse any strange formatting, as I am trying to 
test what might defeat the loss of formatting by the archives; replace ' 
' (required space) with ' ' (blank) to correct]:

        insert into bzmiscchg OVERRIDING USER VALUE
        select ' ','I',ASHPNBR,ABLDNGD,APLSQNB,AMSCHRG
             ,CTLTEXT3 ChgDesc,sum(AMSCHRG2) chgamt
             ,replace(char(curdate(),iso),'-','') || 'T' ||
              replace(char(curTime()),':','')
             ,' ',' '
        from miscchg3 a
               left outer join
             ctltable
               on CTLTABLE = 'MISCCHG' and AMSCHRG = CTLELEMENT
        where exists ( select '*'
                       from APPLSHIP b
                       where a.ASHPNBR = b.ASHPNBR
                         and a.ABLDNGD = b.ABLDNGD
                         and a.APLSQNB = b.APLSQNB
                     )
        group by ASHPNBR,ABLDNGD,APLSQNB,AMSCHRG,CTLTEXT3
        order by ASHPNBR,ABLDNGD,APLSQNB,AMSCHRG