"WHERE" in SQL

[zhuanyi]

Hello,
I am just wondering that if I say SELECT something INTO

WHERE tableA. fieldA = tableB.fieldB, is that acceptable?

Thanks!

 

[rainman89]

no. you cant select into a table. it would be
SELECT blah FROM blah WHERE blah blah
or INSERT blah INTO blah WHERE blah blah

 

[zhuanyi]

oh, sorry, i should have added FROM anotherTable in there...but I am more concerned with the WHERE part...is that part looks fine to you?

Thanks!

 

[rainman89]

yes i think that would work

 

[zhuanyi]

Then how about the following statement:
SELECT [claimbase].[comp_code],[claimbase].[ced_comp_pol_num],[claimbase].[claim_liability],[claimbase].[date_of_death] INTO DeathRecon FROM [claimbase] WHERE [claimbase].[comp_code] = 'SUN' OR 'CLA' AND [claimbase].[ced_comp_pol_num] = [AppendAllFields].[POL] AND [claimbase].[date_of_death] >= #01-01-2003# AND [claimbase].[claim_liability] NOT BETWEEN ( [AppendAllFields].[NAR]/100 + 100 ) AND ([AppendAllFields].[NAR]/100 - 100 );
When I ran the query, it ask me for parameters for AppendAllFields.POL and AppendAllFields.NAR and the resulting table does not give the date_of_death after 01/01/2003, I tried to input the date as 01-01-2003, and that did not work as well
Please help, I've spent the whole morning on this line alr..
Thanks!

 

[WayneRyan]

z,

SELECT [claimbase].[comp_code],
       [claimbase].[ced_comp_pol_num],
       [claimbase].[claim_liability],
       [claimbase].[date_of_death] 
INTO DeathRecon 
FROM [B][claimbase] Inner Join [AppendAllFields] On
      [claimbase].[ced_comp_pol_num] = [AppendAllFields].[POL][/B]
WHERE [claimbase].[comp_code] [B]In ('SUN', 'CLA')[/B] AND 
      [claimbase].[date_of_death] >= #01-01-2003# AND 
      [claimbase].[claim_liability] NOT BETWEEN ([AppendAllFields].[NAR]/100 + 100) AND ([AppendAllFields].[NAR]/100 - 100);

hth,
Wayne

 

[zhuanyi]

Interesting,
so could you let me know why I need to use Inner Join and what difference does the In make?

Thanks!

 

[WayneRyan]

Z,

Literally, you're Where clause has --> Or 'CLA'

This isn't Cobol, so obviously 'CLA' evaluates to True, thereby including
ALL records. Use the In clause.

You could also change it to ([claimbase].[comp_code] = 'SUN' OR [claimbase].[comp_code] = 'CLA'), but
you MUST have the parentheses.

SELECT [claimbase].[comp_code],
       [claimbase].[ced_comp_pol_num],
       [claimbase].[claim_liability],
       [claimbase].[date_of_death],
       [claimbase].[claim_status] 
INTO DeathRecon 
FROM [claimbase] Inner Join [AppendAllFields] On [claimbase].[ced_comp_pol_num] = [AppendAllFields].[POL] 
WHERE [claimbase].[comp_code] = 'SUN' OR 'CLA' AND <-- Change to In ('SUN', 'CLA')
[claimbase].[claim_status] = 'PAID' AND 
[claimbase].[date_of_death] >= #01/01/2003# AND 
[claimbase].[date_of_death] <= #01/06/2006# AND 
[claimbase].[claim_liability]  NOT BETWEEN ([AppendAllFields].[NAR]/100 + 100 ) AND ([AppendAllFields].[NAR]/100 - 100);

hth,
Wayne