SQL - Select ALL rows

[whitsey]

I have an SQL statement which relies on a numeric value

e.g.

where id = 6

I am using a BI tool which let's me pass in the id to the report
dynamically however, I want to be able to pass "ALL" or similar to
return all rows.

Is there some type of wilcard expression I can use in SQL on the
fly???

e.g.

where id = *

Thanks

[Serge Rielau]

Remove the predicate....
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[whitsey]

On May 31, 9:18 pm, Serge Rielau <[e-mail]:

Remove the predicate....
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

If I was able to I would have and wouldn't have posted this thread!!!!

I CAN'T remove the predicate!!! Can I or can I not use a wildcard on
the fly?

[Philip Nelson]

whitsey wrote:

I have an SQL statement which relies on a numeric value

e.g.

where id = 6

I am using a BI tool which let's me pass in the id to the report
dynamically however, I want to be able to pass "ALL" or similar to
return all rows.

Is there some type of wilcard expression I can use in SQL on the
fly???

e.g.

where id = *

Thanks

What about

where id = id

Can you manage that ?

Phil

[Serge Rielau]

I CAN'T remove the predicate!!! Can I or can I not use a wildcard on

the fly?

You cannot. Forgive me, your questions are fairly basic. It is non
obvious what you do or do not know, or can and cannot do.

You BI tool should have an option to generate the predicate.
It's a pretty basic capability.
If you tell users what tool you are using and what you try to achieve
(on a higher level) they may be able to advise.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Konstantin Andreev]

Serge Rielau wrote:

>I CAN'T remove the predicate!!! Can I or can I not use a wildcard on the fly?

You BI tool should have an option to generate the predicate. It's a pretty basic capability.

Hmm... Not so simple. BI tool and automatic report generators are the real piece of junk :)

For example, you may be required to create and maintain two separate reports:

* with predicate 'id = ?'
* without this predicate

The reports, otherwise identical...
Sometimes, you can't even copy report and change sql statement without complete redesign of report.
In that case so crappy workaround may appear:

WHEN ((1 = ?) OR (id = ?))

to switch predicate 'id = ?' on and off on the fly...
--
Konstantin Andreev.

[Serge Rielau]

What we have seen tools do is this:
WHERE 1=1
Then any extra predicate gets glued on using 'AND ID = ? ' etc...
That is less harmful.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[whitsey]

On Jun 1, 1:19 am, Serge Rielau <[e-mail]:

What we have seen tools do is this:
WHERE 1=1
Then any extra predicate gets glued on using 'AND ID = ? ' etc...
That is less harmful.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Thanks Guys,

In the end I had to create two separate statements, one with and one
without.
I'm meeting with the BI tool company today (LogiXML) and will ask them
their suggestion.

Cheers