Dynamic SQL select stmt

[2005 IR Help]

By: hillary - wikkitt

Dynamic SQL select stmt

2005-11-22 09:31

Hi All!

 

How do you dynamically create a select stmt based on what/if info is passed in paramameters?

 

Example:

If $P{id}>0 then

select * from table where id = $P{id}

Else

select * from table

 

I've tried using variables based on the parameter info but it doesn't work.

 

Here's what I tried:

If $P{id}<0 then

$V{where} = new String(" where id = " + $P{id})

Else

$V{where} = new String("")

select * from table $V{where}

 

Any suggestions would help...I'm stuck at this point.

 

Thanks

Hillary

 

 

 

 

By: hillary - wikkitt

RE: Dynamic SQL select stmt

2005-11-22 09:54

Would it be better to just create the sql stmt in my program and pass that as a parameter?

 

So, for my Report SQL query I would use $P!{query} with the ! instead of $P{query} right?

 

 

 

 

 

 

 

By: harvestmoon1200 - harvestmoon1200

RE: Dynamic SQL select stmt

2005-12-15 23:10

 

select * from table

where If ($P{id}>0 , id = $P{id}, 0=0)

 

 

1st part, if statement, true/false

2nd part, action if true

3rd part, action if false.

0=0 is true so means u do nothing for the "where "