Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2003
    Location
    Karachi, Pakistan
    Posts
    45

    Unanswered: Optional Parameter values

    I try to make a query in which my parameter valus is optional, if user doesn't give any value then query return all list of values.

    Select char_val
    from ci_char_val
    where char_type_cd='ZONE'

    Select char_val
    from ci_char_val
    where char_type_cd='ZONE'
    and char_val ='01'

    above first query return all values while second query return specific value.

    what i try, I build below query but it gives me error "ORA-01427: single-row subquery returns more than one row".

    select case when trim(' ') is null then (Select char_val
    from ci_char_val
    where char_type_cd='ZONE')
    else
    (Select char_val
    from ci_char_val
    where char_type_cd='ZONE'
    and char_val =' ')
    end
    from dual

    Noor ali

  2. #2
    Join Date
    Oct 2006
    Posts
    4
    How about:

    Select char_val
    from ci_char_val
    where char_type_cd='ZONE'
    and char_val like case when :cv is null then '%' else :cv end

  3. #3
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    -- Or --
    Code:
    Select char_val 
      from ci_char_val 
     where char_type_cd='ZONE'
       and char_val = NVL(:cv,char_val);

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  4. #4
    Join Date
    Dec 2003
    Location
    Karachi, Pakistan
    Posts
    45

    I done it

    Thanks for replies. I done it by following query

    Select char_val
    from ci_char_val
    where char_type_cd='ZONE'
    and char_val = NVL(:cv,char_val);


    Thanks again

    Noor Ali

  5. #5
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    I would
    Code:
    select char_val
      from ci_char_val
     where char_type_cd = 'ZONE'
       and ( ( :cv is null and char_val is not null )
             or ( :cv is not null and b = :b ) )

  6. #6
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool



    Great! -- Good luck.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •