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

[Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>]

Good catch - the extra closing parenthesis, no idea how that got in there.

Somehow I was sure I saw the fullselect version with parentheses - oh, 
well, could it have been at 5am?

Cheers
Vern

On 2/16/2013 3:08 PM, CRPence wrote:
> 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