If statements in SQL

[Former Member]

hi everyone, is it possible to use the equivalent of IF statements in a query?

for example i might want one column in a query to be like:

=IF (t1.creditline < (t1.balance + t1.ordersbal)), "EXCEEDED LIMIT",)

which is saying 'show "EXCEEDED LIMIT" if BP balance plus doctotal is more than creditlimit'

can i do this?

[Former Member · Accepted solution]

The syntax would be easy. Add this complete query in between your main query for one column like:

, (SELECT "EXCEEDED LIMIT" FROM OCRD T1 WHERE t1.creditline < (t1.balance + t1.ordersbal)) AND XXXX,

XXXX here would the condition to link this column to main query. If you still have doubt, post your complete query for us to test.

Thanks,

Gordon

[Former Member · Accepted solution]

thanks for your help Gordon. my query is below

SELECT T0.[DocNum], T0.[CardCode], T0.[CardName], (select name from oidc where code = t0.indicator) as 'Status', (xxxxxxxxxxxx) as 'credit line' FROM ORDR T0 INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode where (t0.groupnum = 12 or t1.creditline < (t1.balance + t1.ordersbal)) and (t0.indicator is null or t0.indicator = 'pending') and wddstatus = '-' and t0.docstatus ='o'

i would like the clause to be where the (xxxxxxx) is above. When i tried it, the message said 'invalid column name EXCEEDED LIMIT'

i hope you can help

Edited by: Groovy UK on Oct 19, 2009 6:28 PM

[Former Member · Accepted solution]

Try this one:

SELECT T0.DocNum, T0.CardCode, T0.CardName, (select name from oidc where code = t0.indicator) as 'Status', (SELECT t1.balance + t1.ordersbal - t1.creditline FROM OCRD T1 WHERE t1.creditline < (t1.balance + t1.ordersbal) AND T0.CardCode = T1.CardCode) as 'credit line' FROM ORDR T0 INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode where (t0.groupnum = 12 or t1.creditline < (t1.balance + t1.ordersbal)) and (t0.indicator is null or t0.indicator = 'pending') and wddstatus = '-' and t0.docstatus ='o'

[JesperB1 · Accepted solution]

Hi,

In SQL you can use case for conditions within the select.

Suggestion below.

 
SELECT T0.DocNum, T0.CardCode, T0.CardName, t1.balance, t1.ordersbal,
(select name from oidc where code = t0.indicator) as 'Status', 
	(select case 
			when t1.creditline < (t1.balance + t1.ordersbal) then 'Eceeds Limit'
			else 'Within Limit' end
			) as 'Credit Line'

 
FROM	
	ORDR T0 INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode 

where	
		(t0.groupnum = 12 or t1.creditline < (t1.balance + t1.ordersbal)) 
		and (t0.indicator is null or t0.indicator = 'pending') 
		and wddstatus = '-' and t0.docstatus ='o'

Hope it helps,

Jesper

[Former Member · Accepted solution]

thanks to you both for your suggestions, i have amended yours Jesper and it seems to be working fine

thanks again