Query AND OR question..

[Former Member]

My join in my query is fine, but I want the user to be able to enter search criteria:

the ItmsGroup and the WhsCode or either 1. Is this possible?

WHERE T1.ItmsGrpNam = [%0] AND T2.WhsCode = [%1] OR

T1.ItmsGrpNam = [%0] OR

T2.WhsCode = [%1]

^This is what I thought would work, but it will not work work when I try to enter both ItmsGrpNam AND WhsCode... It only works when I enter 1 of them.

Any help is appreciated, thanks

[former_member583013 · Accepted solution]

David,

Please paste your complete query..... just looking at the WHERE might not give a complete picture

But to get started, this is what you could try and base your logic using the CASE statement as demonstrated

SELECT T1.ItemCode AS 'Item No.', T1.Quantity AS 'Quantity', T1.WhsCode AS 'Warehouse Code', 
T0.ItmsGrpNam AS 'Group Name' FROM  [dbo].[OITB] T0 
INNER JOIN  [dbo].[OITM] T2 ON T2.ItmsGrpCod = T0.ItmsGrpCod 
INNER JOIN [dbo].[RDR1] T1 ON T1.ItemCode = T2.ItemCode
WHERE T0.ItmsGrpNam LIKE (CASE WHEN '[%0]' = '' THEN '%' ELSE '[%0]' END) AND 
T1.WhsCode LIKE (CASE WHEN '[%1]' = '' THEN '%' ELSE '[%1]' END)

Suda

[Former Member]

Thank you all for your help

Suda's suggestion gave me my solution for others who run into this issue.

Thanks again.

[Former Member]

Peter is right. You have to use bracket to make OR work since AND is alwasy take priorities. Please read more SQL tutorial to increase your power on the query you can produce.

Thanks,

Gordon

[Former Member]

Hi David

For complex and/or you can put them in brackets. For example:

WHERE (T1.ItmsGrpNam = %0 AND T2.WhsCode = %1) OR (T1.ItmsGrpNam = %2 AND T2.WhsCode = %3)

Kind regards

Peter Juby