In Oracle you can instead use a FUNCTION to return a table set. Doing this depends on the version of Oracle you are using.
Example: http://blogs.ittoolbox.com/database/solutions/archives/returning-rows-through-a-table-function-in-oracle-7802 Since you define the cursor as a string, you could easily use conditional statements to build your query string before defining the cursor. If you would like me to walk you through the process explicitly, drop me a line. Kier Simmons Analyst/Programmer -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Bill Mohr Sent: Wednesday, May 28, 2008 3:08 PM To: Houston ColdFusion Users' Group Subject: [houcfug] Re: oracle sql condition Since this is a logic AND syntax issue - can you help me with how I would write this in an Oracle SP? It's awful nitpicky, but I think your idea will work! thanks! On May 20, 2: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;- Hide quoted text - > > - Show quoted text - --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
