Something like this, completely untested though: sql_stmt := 'SELECT foo_variable1 FROM foo_table WHERE foo_id = :1 AND fy = :2';
IF rectype < 3 THEN sql_stmt := sql_stmt || ' AND rec_type = :3'; ELSE sql_stmt := sql_stmt || ' AND rec_type <> 1'; END IF; execute immediate sql_stmt INTO foo_variable1 USING fooid, FY_IN, rectype I assumed fooid, FY_IN, and rectype are things being passed into the procedure but was not sure. Also was a little hazy on why you were selecting foo_variable1 into foo_variable1. On Wed, May 28, 2008 at 3:07 PM, Bill Mohr <[EMAIL PROTECTED]> wrote: > > 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 - > > > -- 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 -~----------~----~----~----~------~----~------~--~---
