Parameter that can change

In the sql statement underneath, the parameter @erkenning should change wether the user gave in an option or not. If the user selects 'Geen' in the combobox then there should be:

erkenning IS NULL

If the user selects 'something' in the combobox else then 'Geen' the statement should be:

erkenning = 'Something'

I've tried to implement this in the statement underneath but it's not working. The syntax is wrong.

[CODE]
SELECT a.jaar, a.maand, a.naam, a.voornaam, a.type_afrek, a.tnr_afrek, a.erkenning, a.soort_kode, a.kode, a.bedrag, b.interne_code
FROM r00v_afvererk as a, ao1indnst as b
WHERE
tnr_opname_in = '521105' AND
jaar = @jaar AND
maand >= @maandvan AND
maand <= @maandtot AND
CASE WHEN @erkenning = 'Geen'
THEN erkenning IS NULL
ELSE erkenning = @erkenning
END
ORDER BY jaar, maand, naam, voornaam, type_afrek, tnr_afrek, erkenning, soort_kode, kode;
[/CODE]

Answer this question

Parameter that can change

Sonnenblume
thanks for your quick reply

Now this statement gives me all the records when the field a.datum_nonactief is null.

Another option for the parameter @ongeldigebew is "Inclusive".
When this is marked then all the records should be shown regardless if the field is null or is not null. I tried with an OR like this:

[CODE]
(
(isnull(a.datum_nonactief,'01.01.1970') =
CASE WHEN @ongeldigebew = 'Zonder'
THEN '01.01.1970'
END)
OR
(a.datum_nonactief= CASE WHEN @ongeldigebew='Inclusive'
THEN '%'
END)
)
[/CODE]

But then I get the same error as before about the conversion...
SergioFlorezM
you dit it again :)

thank you benni
Wayne Grincais
Thank you for your quick reply.

The SQL statement is coming from an old Informix Database. We have now upgraded to an SQL SERVER 2005 and the reports are following. We use the same queries as much as possible.

Microsoft SQL SERVER gives me the error that nvl is not a recognized built-in function name.
The function was ISNULL instead of nvl.And I forgot to make a JOIN between table a and table b. (for anyone who wants to use this code)
But you helped me creating the syntax a lot.

thanks
v1c
Looks like Oracle SQL..

try:

SELECT a.jaar, a.maand, a.naam, a.voornaam, a.type_afrek, a.tnr_afrek, a.erkenning, a.soort_kode, a.kode, a.bedrag, b.interne_code
FROM r00v_afvererk as a, ao1indnst as b
WHERE
tnr_opname_in = '521105' AND
jaar = @jaar AND
maand >= @maandvan AND
maand <= @maandtot AND
nvl(erkenning,'-1') =
CASE WHEN @erkenning = 'Geen'
THEN '-1'
ELSE @erkenning
END
ORDER BY jaar, maand, naam, voornaam, type_afrek, tnr_afrek, erkenning, soort_kode, kode;

This might work..
Azerila
So I managed to get that working
Now I need the same function but it has to be executed on a DateTime field.

my sql statement is:
[CODE]
isnull(a.datum_nonactief,'-1') =
CASE WHEN @ongeldigebew = 'Zonder'
THEN '-1'
END

[/CODE]

But that gives me the following error:
[ERROR]
An error occurred while reading data from the query result set.
Conversion failed when converting datetime from character string.
------------------------------------------------------------------------------------------
Conversion failed when converting datetime from character string. (Microsoft SQL Server, Error: 241)
[/ERROR]
I hope you can help me again
J. A.
Try just

[CODE]
(
(isnull(a.datum_nonactief,'01.01.1970') =
CASE WHEN @ongeldigebew = 'Zonder'
THEN '01.01.1970'
END)
OR
(@ongeldigebew='Inclusive')

[/CODE]

this will be evaluated as (someconditions) or (true) if @ongeldigebew='Inclusive' and will give you all rows..
Vivek Mayur Khan
[CODE]
isnull(a.datum_nonactief,'01.01.1970') =
CASE WHEN @ongeldigebew = 'Zonder'
THEN '01.01.1970'
END

[/CODE]
should work..

SQL-Server expects always the same datatype, so you cant return a varchar, when a datefield is null..
Parameter that can change

Answer this question