cancel
Showing results for 
Search instead for 
Did you mean: 

Formatted search using IF ELSE

Former Member
0 Kudos

Hello.

Im trying to do a search with two parameters. If the first one is not empty, do the first search, else do the other search. I get an error. Something about having to select from a table... Anyone?

IF ('[%0]' != '')

BEGIN

SELECT T0.ShortName, T0.Debit, T0.Ref1, T0.Ref2

FROM JDT1 T0

WHERE T0.Debit > '0' AND (T0.Ref1 = '[%0]')

END

ELSE

BEGIN

SELECT T0.ShortName, T0.Debit, T0.Ref1, T0.Ref2

FROM JDT1 T0

WHERE T0.Debit > '0' AND (T0.Ref2 = '[%1]')

END

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Magnus,

I have had the same problem previously and the only way I could solve it was by declaring variables to save the values in. I have changed your query and tested it. I know this might look like an overkill, but please let me know if you find an easier solution!

DECLARE @Ref1 Varchar(11)
SELECT @Ref1 = T0.Ref1 FROM JDT1 T0 WHERE T0.Ref1 = '[%0]'
DECLARE @Ref2 Varchar(11)
SELECT @Ref2 = T0.Ref2 FROM JDT1 T0 WHERE T0.Ref2 = '[%1]'
IF (@Ref1 != '') 
BEGIN
  SELECT T0.ShortName, T0.Debit, T0.Ref1, T0.Ref2
  FROM JDT1 T0
  WHERE T0.Debit > '0' AND (T0.Ref1 = @Ref1) 
END 
ELSE
BEGIN
  SELECT T0.ShortName, T0.Debit, T0.Ref1, T0.Ref2
  FROM JDT1 T0
  WHERE T0.Debit > '0' AND (T0.Ref2 = @Ref2) 
END

Hope it helps,

Adele

Former Member
0 Kudos

Thanks alot... Worked !

Don't have the option to reward you points though....

Answers (1)

Answers (1)

Former Member
0 Kudos

Hi,

You need first store the data into a variable, you can use:

/*

SELECT count(T0.ShortName) FROM JDT1 T0 WHERE T0.Debit > '0' AND (T0.Ref1 = '[%0]')

*/

It's a comment, but capture the value of the variable.

Best regards.