Subquery w/in query of the same View/table

[lradon]

Apparentally, I am missing a close ")" in my code because I am receiving this SQL message:

Server: Msg 170, Level 15, State 1, Line 12
Line 12: Incorrect syntax near ')'.

But, now I am wondering if it is possible for a query to contain a subquery both of which select from the same View? I have try putting a close parentesis in various places and searched BOL for some help and I am stuck. Here is my code:

select APLNUM, * from underwriters
Where (SELECT APLNUM from underwriters WHERE (STATDATE >= '03/01/06' and STATDATE <='03/28/06' and STATUS = 'a' or status='d' or status='q' or status='c'))

It seems fairly simple, but, can someone see where I am going wrong? Please let me know what additional info. to provide.

Thanks, lradon

 

[gmmastros]

select APLNUM, * from underwriters
Where [!]APLNUM In[/!] (SELECT APLNUM from underwriters WHERE (STATDATE >= '03/01/06' and STATDATE <='03/28/06' and STATUS = 'a' or status='d' or status='q' or status='c'))

Or

[!]APLNUM Not In[/!] (depending on what you want).

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[lradon]

Thank you. I will give this a try and repost. Laura

 

[gmmastros]

For this query, I would probably have written it like this..

select APLNUM, 
       * 
From   underwriters
Where  STATDATE Between '2006-03-01' And '2006-03-28'
       And STATUS In ('a','d','q','c')

My guess is that you want records between a certain date range wher the status is a,d,q or c. If this is true, you will have problems with your query because of the OR's. As written, it will return records with STATUS = 'C' for ANY date. You should modify your where clause like so...

WHERE (STATDATE >= '03/01/06' and STATDATE <='03/28/06')
and (STATUS = 'a' or status='d' or status='q' or status='c')

Hope this helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[cmmrfrds]

Also, it is a good idea to put in parens in the select logic to make sure it is evaluating in the correct order. I think you want one of the 4 statues when it falls in the date range.

select APLNUM, * from underwriters
Where APLNUM In (SELECT APLNUM from underwriters WHERE (STATDATE >= '03/01/06' and STATDATE <='03/28/06') and STATUS IN ('a', 'd', 'q', 'c') )

 

[lradon]

Thanks guys!!!!!!!!!!!!! I have to clock out now. But, I will try your suggestions tomorrow am. PST.

Laura