Bill,
I haven't tested this, but I believe the Oracle equivalent to what I wrote
below is:
DECLARE
myStatement varchar2(2000);
rectype number;
BEGIN
rectype = 1;
myStatement = 'create table myNewTable as select * from myTable where 1=1';
IF rectype < 3
THEN
myStatement := myStatement || ' and rectype = ' || rectype;
ELSIF rectype = 3
THEN
myStatement := myStatement || ' and rectype <> 1';
ELSE
/* do nothing in this case, but wanted to show that you could add
something here */
myStatement := myStatement || '';
END IF;
EXECUTE IMMEDIATE myStatement;
END;
As I said, I haven't tested this, but it seems correct...
For Oracle PL/SQL syntax, I recommend the O'Reilly book "Oracle PL/SQL
Programming".
http://www.oreilly.com/catalog/9780596009779/
It is a very good reference!
Hope this gets you started in the right direction!
--
Ken
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 -
> >
>
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
