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;
>>
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
