Conditional search criteria

[dsmith1000]

Hello folks,

Having a little trouble with a query I'm trying to build. I need to use a conditional criteria in my where statement based on the data in a field. Basically, if this piece of data is this, then search the table using this search criteria, otherwise if it's this value, use this other criteria, etc. Can't figure it out. Maybe a union instead? Now that I think about it that seems to be the way to go. Out of curiosity, is there a way to do what I'm trying without a union? I'm using an Oracle database.

Thanks,
David Smith

http://www.outerlimitcomics.com

David Smith

 

[PruSQLer]

That's a tad general. Could you give an example of what you're trying to do?

 

[rac2]

Pru, you could be right. Or maybe it is just a matter of logic.

DECLARE @piece_o_data INT
SET @piece_o_data = 7

DECLARE @criteria_A_1 DATETIME
DECLARE @criteria_B_1 MONEY
DECLARE @criteria_B_2 MONEY
SET @criteria_A_1 = '12/27/1945'
SET etc.

SELECT * 
FROM myTable
WHERE
     ( @piece_o_data = 3 AND (
                             col_A < @criteria_A_1
                             AND col_B = 0  
                             )
     )
     OR
     ( @piece_o_data = 7 AND (
                             AND col_B BETWEEN @criteria_B_1 AND @criteria_B_2  
                             )
     )

The piece of data is like a mask, it determines which part of the WHERE clause will be used.

But then maybe the piece of data is not the search criterion so much as an attribute of the things searched. As if to say we want the fat, black ones plus the skinny, old ones. When a row is color = black, then compare it to the weight criterion; when a row is lightweight(skinny) then compare it to the age criterion. Seems like the logic approach could be used in that case too.

But maybe the piece of data determines which table we take rows from. Now in that case we may need a UNION. Unless we can JOIN the tables, then we are back to the logic approach.

I think PruSQLer has a good point.

 

[r937]

rac2, nice examples

rudy

http://r937.com/

SQL Consulting