IF statement within CFQUERY/SQL

[Unamailer]

Does anyone know if SQL has an IF statement? I can't seem to find an example
that works.

I know it is possible to perform a CFIF within a CFQUERY. However, I can't
compare one of the DB fields to a variable or constant while within that
CFQUERY.

Here is some pseudo-code of what I am trying to do.

SELECT type, category, description, datein
FROM someTable
WHERE type = 'K'
IF (category = 5) THEN
AND dateIn = 042105
END IF

None of the values in the WHERE statement are CF variables. Basically, I only
want to check the dateIn value if the category field from the table equals 5.

Is this possible?

Thanks in advance for the help!!

[mattis_72]

I dont really know what you mean, but this may work?

SELECT type, category, description, datein
FROM someTable
WHERE type = 'K'
<CFIF (category eq 5) >
AND dateIn = 042105
</CFIF>

[The ScareCrow]

As far as I know you can't do this in pure sql. But you could do it in a
stored procedure if your db allows them.

But I think the following should return what you want.

<cfquery>
SELECT type, category, description, datein
FROM someTable
WHERE type = 'K'
AND category <> = 5
UNION
SELECT type, category, description, datein
FROM someTable
WHERE type = 'K'
AND category = 5
AND dateIn = 042105
</cfquery>


Ken

[Jochem van Dieten - TMM]

Unamailer wrote:

> Does anyone know if SQL has an IF statement?

CASE WHEN x THEN y ELSE z END

> I know it is possible to perform a CFIF within a CFQUERY. However, I can't
> compare one of the DB fields to a variable or constant while within that
> CFQUERY.
>
> Here is some pseudo-code of what I am trying to do.
>
> SELECT type, category, description, datein
> FROM someTable
> WHERE type = 'K'
> IF (category = 5) THEN
> AND dateIn = 042105
> END IF

SELECT type, category, description, datein
FROM someTable
WHERE type = 'K' AND
CASE WHEN category = 5 AND dateIn = 042105 THEN TRUE
WHEN category = 5 THEN FALSE ELSE TRUE END

Ugly, but should work.

Jochem

--
Jochem van Dieten
Team Macromedia Volunteer for ColdFusion, beer and fun.