Forum Stats

  • 3,781,376 Users
  • 2,254,509 Discussions
  • 7,879,661 Comments

Discussions

simple query

554271
554271 Member Posts: 590
edited Jun 11, 2009 1:52PM in SQL & PL/SQL
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
«1

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,379 Red Diamond
    Hi, Chiru

    Maybe
    WHERE   item_type in DECODE (:1, 'DM', 'D', 'Store', 'S', ...)
    OR      :1 = 'All'
  • SeánMacGC
    SeánMacGC Member Posts: 2,914 Gold Trophy
    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)
  • 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
    554271 Member Posts: 590
    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
    Frank Kulash Member, Moderator Posts: 41,379 Red Diamond
    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 =.
  • 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
    Solomon Yakobson Member Posts: 19,009 Red Diamond
    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
    Frank Kulash Member, Moderator Posts: 41,379 Red Diamond
    edited Jun 11, 2009 1:37PM
    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
    Solomon Yakobson Member Posts: 19,009 Red Diamond
    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.
  • 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.
This discussion has been closed.