With Oracle you can not use the case in the where clause, you can use it in the select clause though for what it is worth unless maybe something has changed in one of the recent versions. What you can do is use a decode function in the where clause which gives you logic similar to a switch/case, but you can not use operators so no ability to do not equal to unless just wrote a simple function in Oracle for that but still unsure if it could be easily implemented in the where clause. So for the first part it would be WHERE rec_type = DECODE(rectype, 1, 1, 2, 2) but you still need to get that != 1 part in there when rectype is 3. Probably a way to stack some functions in there or apply decode in a different manner to get it but that way is not popping into my head right now for some reason. It is a shame it does not just allow a sub-query that will return multiple rows because then it could be something like WHERE rec_type IN (DECODE(rectype, 1, 1, 2, 2, (SELEC rec_type FROM TABLEWHATEVER WHERE rec_type != 1))
I am curious when you do a case within the where clause in MSSQL Server, does it apply that case to the entire table.column? I would think it would have to apply it to every value within that column before it would know how to apply the rules. Reason I ask is seems like that would be a performance hit if working with a table with a lot of rows in it. A project I inherited many years ago that used Oracle, did something like that but just with a function on one column, something like WHERE UPPER(VALUE) = 'BLAH' but the problem was the table had a few million rows and no function based index on that column so it was a really slow to run. Anyway, convert Ken's logic over to Oracle and use the execute immediate and it will run just fine. On Tue, May 20, 2008 at 5:47 PM, Aqil Mansuri <[EMAIL PROTECTED]> wrote: > Another way to do something like this in sql server which I find pretty > cool is to use case statements so you avoid dynamic querys. I am not sure > if this will work in oracle. It would be something like this: > > SELECT * INTO myNewTable > FROM myTable > WHERE rectype = CASE > WHEN @Rectype < 3 THEN @Rectype > WHEN @Rectype = 3 THEN 3 > END > > Aqil > > > On Tue, May 20, 2008 at 3:52 PM, Ken Auenson, II <[EMAIL PROTECTED]> > wrote: > >> Bill, >> Which flavor of SQL are you working with? >> For Microsoft SQL Server (TSQL), one method is to create your sql >> statement as a string, then you can use conditional logic to conditionally >> build the pieces you require... then use EXEC to run your dynamically built >> statement. >> so, for example... >> >> set @myStatement = 'select * into myNewTable from myTable where 1=1'; >> if @rectype < 3 >> begin >> set @myStatement = @myStatement + ' and rectype = ' + @rectype; >> end >> if @rectype = 3 >> begin >> set @myStatement = @myStatement + ' and rectype <> 1'; >> end >> exec ( @myStatement ); >> >> This could also be applied to other SQL Databases. >> >> Hope this helps! >> -- >> Ken >> On Tue, May 20, 2008 at 2:42 PM, Bill Mohr <[EMAIL PROTECTED]> wrote: >> >>> I'm messing with a stored procedure select query. Need a condition in >>> the where clause based on the variable 'rectype' - if rectype=1, then >>> select only records where rec_type = 1, if rectype=2, then select only >>> records where rec_type = 2, but if rectype=3 then select only records >>> where rec_type <> 1. >>> tried a switch case or if - then - else, but errors on syntax and I >>> need help: >>> something like this? >>> BEGIN >>> SELECT foo_variable1 >>> INTO foo_variable1 >>> FROM foo_table >>> WHERE >>> foo_id = fooid >>> and >>> fy = FY_IN >>> and >>> if >>> rectype < 3 then rec_type = rectype; >>> else rec_type <> 1; >>> end if; >>> END; >>> >> >> > > > > -- Aaron Rouse http://www.happyhacker.com/ --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the "Houston ColdFusion Users' Group" discussion list. To unsubscribe, send email to [EMAIL PROTECTED] For more options, visit http://groups.google.com/group/houcfug?hl=en -~----------~----~----~----~------~----~------~--~---
