please help with SQL statement

[lindaklu]

I have a query that returns the order history of a customer based on their search catagory either by the item number or its description and customer number. There's an optional checkbox, if checked, will return the order history of that particular customer AND all the order history that belongs to his GROUP.

Below is my code:

'if box is checked
if group="yes" then
str = "SELECT DISTINCT item, description FROM history Where custgroup in (Select custgroup From history Where (custnum='" & CustNum & "') "
else
str = "SELECT DISTINCT item, description FROM history Where (custnum='" & CustNum & "') "
end if

However, it returns an error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[INTERSOLV][ODBC SequeLink driver][Progress]** Unable to understand after -- "custgroup". (247)

I think Progress doesn't allow nested if statements. Can someone please tell me if there's another way to do this???
My deadline is soon!!..

 

[franklin1232]

I am unfimaliar with your driver. What SQL standard does it follow. SQL92? If so I don't beleive your query is using a valid syntax. You Could create table aliases and inner join them on you the custnum field.

 

[Lukky]

You are missing a closing bracket after your IN subquery.