simple query

554271

I want the below query to pick up all the records when the user passes in 'All'.

SELECT ROWID, item_desc, item_id, item_val, item_type, sku_id
  FROM ss_item_master
 WHERE item_type in DECODE (:1, 'DM', 'D', 'Store', 'S','All',...)

How can the above decode be modified to do that?

Thanks,
Chiru

Frank Kulash

Hi, Chiru

Maybe

WHERE   item_type in DECODE (:1, 'DM', 'D', 'Store', 'S', ...)
OR      :1 = 'All'

SeánMacGC

Hello, try:

SELECT ROWID, item_desc, item_id, item_val, item_type, sku_id
  FROM ss_item_master
 WHERE item_type in DECODE (:1, 'DM', 'D', 'Store', 'S','All', item_type)

Ganesh Srivatsav

you have to use = instead of in. decode returns only one value.

See below make it equal to item_type itself then you will get all records.

SELECT ROWID, item_desc, item_id, item_val, item_type, sku_id
  FROM ss_item_master
 WHERE item_type in DECODE (:1,'DM', 'D', 'Store', 'S','All',item_type)

Regards,

G.

554271

Thanks for the answers. G, it looks like it does not matter where I use = or the 'in'. I get the same results.

Thanks,
Chiru

Frank Kulash

Megastar_Chiru wrote:
Thanks for the answers. G, it looks like it does not matter where I use = or the 'in'. I get the same results.

Thanks,
Chiru

That's right.
If there's only one expression in the list that comes after IN, then it doesn't matter if you use IN or =.

Ganesh Srivatsav

You're right. I need to correct my statement from "have to" to "better use"
Just said that to the OP to avoid.

Regards,

G.

Solomon Yakobson

SeánMacGC wrote:
Hello, try:

SELECT ROWID, item_desc, item_id, item_val, item_type, sku_id
FROM ss_item_master
WHERE item_type in DECODE (:1, 'DM', 'D', 'Store', 'S','All', item_type)

Do not forget about NULLs! The above will not, "pick up all the records when the user passes in 'All'" if item_type can have NULL values.

SY.

Frank Kulash

Hi,

Solomon Yakobson wrote:

SeánMacGC wrote:
Hello, try:

SELECT ROWID, item_desc, item_id, item_val, item_type, sku_id
FROM ss_item_master
WHERE item_type in DECODE (:1, 'DM', 'D', 'Store', 'S','All', item_type)

Do not forget about NULLs! The above will not, "pick up all the records when the user passes in 'All'" if item_type can have NULL values.

Actually, DECODE does match NULLs the same way it matches non-NULL values.
DECODE may be the only place where you don't have to do something special when comparing NULLs. You're absolutely right not to forget about them.

Do you know a situation where Seán's (and G's) solution won't work? Post some sample data.

Solomon Yakobson

Frank Kulash wrote:
Actually, DECODE does match NULLs the same way it matches non-NULL values.

Frank, it is not DECODE I have in mind:

item_type in DECODE (:1, 'DM', 'D', 'Store', 'S','All', item_type)

If user passes All, DECODE will result in item_type, which makes the above nothing more but:

WHERE item_type = item_type

And that is where NULL item_type comes to play.

SY.

Ganesh Srivatsav

You are rite,

Would like to add it to mine,

WITH t AS
     (SELECT     LEVEL item_type
            FROM DUAL
      CONNECT BY LEVEL <= 5
      UNION ALL
      SELECT NULL
        FROM DUAL)
SELECT *
  FROM t
 WHERE NVL (item_type, 10) = DECODE ('all', 'all', NVL (item_type, 10));

Regards.

G.