SQL - Select ALL rows

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

whitsey - 31 May 2007 12:04 GMT

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

Reply to this Message

Serge Rielau - 31 May 2007 12:18 GMT

Remove the predicate....

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

whitsey - 31 May 2007 13:15 GMT

> 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?

Reply to this Message

Serge Rielau - 31 May 2007 13:27 GMT

> 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

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

Konstantin Andreev - 31 May 2007 14:37 GMT

>> 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.

Reply to this Message

Serge Rielau - 31 May 2007 16:19 GMT

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

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

whitsey - 31 May 2007 23:31 GMT

> What we have seen tools do is this:
> WHERE 1=1
[quoted text clipped - 8 lines]
> 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

Reply to this Message

Philip Nelson - 31 May 2007 13:24 GMT

> I have an SQL statement which relies on a numeric value
>
[quoted text clipped - 14 lines]
>
> Thanks

What about

where id = id

Can you manage that ?

Phil

Reply to this Message