Re: SQL select Problem

From: TurkBear <john.greco_at_dot.state.mn.us>
Date: Tue, 10 Sep 2002 08:53:00 -0500
Message-ID: <5qtrnuoabqbumiu09hjhab1o847da83gon@4ax.com>

"Simon Heinrich" <sheinrich_at_hightech-vola.de> wrote:

>Hi NG,
>
>I like to write an SQL select wich searches in an Collum for a list of
>Values and also inkludes that collums wich a 'NULL'
>
>SELECT
> "KUNDAT"."KNR", "KUNDAT"."KGR", "KUNDAT"."FNAME1", "KUNDAT"."FNAME2",
>"KUNDAT"."FNAME3","KUNDAT"."STR", "KUNDAT"."LAND", "KUNDAT"."PLZ",
>"KUNDAT"."ORT","KUNDAT"."POSNR", (SELECT sum ("KUNUMS"."UMS") from
>"DSPHTECH"."KUNUMS" "KUNUMS" where "KUNUMS"."JAHR" in (2001,2002) and
>"KUNDAT"."POSNR" = "KUNUMS"."PNKUNDAT" ) "Umsatz"
>FROM
> "HTECH"."KUNDAT" "KUNDAT",
> "HTECH"."KUNUMS" "KUNUMS"
>WHERE
> "KUNDAT"."POSNR" = "KUNUMS"."PNKUNDAT" And
> "KUNDAT"."NR" in ( select "KUNDAT"."KNR" from "HTECH"."KUNDAT" "KUNDAT"
>where "KUNDAT"."KGR" is in
>('AM','CG','CG,GI','CG,PM','COMI','COMI-RF+F','DR','ES','GSH','GT','HÃ„','HIL
>D','IGH','KL','ME','NW','NW-ME','RE','RK','SANIGRO','SED','SU','SU-AUS','SU,
>PI','TECHNO','THY','UH,PI','UH,BRICK','UH,H','UH,TAXIS','UH,VMG','VBH','VBH,
>B','WIEDEMANN','WUP') or "KUNDAT"."KGR" is null)
>
>....but this doesen`t work :(
>
>I hope you can help me.
>
>Simon
>
>

Some Ideas-

use aliases for the table names
do not use IS IN just IN
to make reading easier, get rid of the quotes around the parts of the qualified name - they are not needed try:
FROM
HTECH.KUNDAT KD,
HTECH.KUNUMS KN
WHERE
    KDAT.POSNR = KN.PNKUNDAT
    And
   KD.NR IN ( select KD1.KNR from HTECH.KUNDAT KD1 where
(KD1.KGR IN

 ('AM','CG','CG,GI','CG,PM','COMI','COMI-RF+F','DR',
'ES','GSH','GT','HÃ„','HILD','IGH','KL','ME','NW','NW-ME'
'RE','RK','SANIGRO','SED','SU','SU-AUS','SU
'PI','TECHNO','THY','UH,PI','UH,BRICK','UH,H','UH,TAXIS','UH,VMG','VBH','VBH,

'B','WIEDEMANN','WUP')
or (KD1.KGR is null) )

Please count the parens, I may have lost count with the 2 IN statements...

-----------== Posted via Newsfeed.Com - Uncensored Usenet News ==----------

http://www.newsfeed.com The #1 Newsgroup Service in the World! -----= Over 100,000 Newsgroups - Unlimited Fast Downloads - 19 Servers =----- Received on Tue Sep 10 2002 - 08:53:00 CDT