Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2007
    Posts
    10

    Unanswered: SQL query help -- Urgent

    I have the following store procedure written.

    Here if i have values in the arguments....I will get the data selected accordingly.

    But my requirement is that..if the arguments are null....I have to retrieve the whole data from database without any conditions in where logic.

    Please suggest me asap.

    create or replace procedure get_tt_rule_1
    (
    v_t_cde char default null
    ,v_st_cde char default null
    ,v_carr char default null
    ,cur1 IN OUT gpkg.cur
    )
    as

    begin
    open cur1 for
    select
    A.t_cde
    ,A.st_cde
    ,A.carr
    from R_rule A
    where
    A.t_cde = v_t_cde
    and A.st_cde = v_st_cde
    and A.carr = v_carr;

    end get_tt_rule_1;

    Thanks,
    KNS.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    which database is this?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2007
    Posts
    10
    Oracle database.

  4. #4
    Join Date
    Feb 2007
    Posts
    10
    To make the requirement simple....If I have a proper value present in arguments... I have to select the data accordinlgy using the where condition. and if i have null value present in the arguments..I should get the whole data in table without checking any condition in where clause. Please do help me. THanks.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    moving thread to oracle forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    May 2006
    Posts
    18
    Hi,

    What about IF ... THEN ... ?

    Just write the corresponding SQLs for all cases (it's not clear from your post
    whether WHERE clause should be dropped when one of the IN parameters is NULL or all of them).

  7. #7
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Code:
     create or replace procedure get_tt_rule_1
    (
     v_t_cde       	char	default   null
    ,v_st_cde	char	default   null
    ,v_carr		char	default   null
    ,cur1       		IN OUT  gpkg.cur
    )
    as
    
    begin
    	open cur1 for
    	 select  
    		 A.t_cde
    		,A.st_cde
    		,A.carr	
    	from     R_rule A
    	 where 	
    		 (v_t_cde is null or A.t_cde = v_t_cde)
    		 and ( v_st_cde is null or A.st_cde = v_st_cde)
    		 and (v_carr is null or A.carr = v_carr);
    
    end get_tt_rule_1;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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