Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2010
    Posts
    1

    Unanswered: Using a parameter table

    Friends

    Here's my scenario:

    Source data:

    Month_name | Account_id | Customer_tenure

    01-OCT-09 | 111 | 2
    01-OCT-09 | 112 | 15
    01-OCT-09 | 113 | 36

    Target Values expected

    Month_name | Account_id | tenure_description
    01-OCT-09 | 111 | 0 - 6 MOB
    01-OCT-09 | 112 | 12 - 24 MOB
    01-OCT-09 | 113 | 24 - 60 MOB

    To populate the tenure_description, I have used a parameter table which has values like this

    parameter_id | param_low_value | param_high_value | param_description

    tenure | 0 | 6 | 0 - 6 MOB
    tenure | 6 | 12 | 6 - 12 MOB
    tenure | 12 | 24 | 12 - 24 MOB
    tenure | 24 | 60 | 24 - 60 MOB

    Here's my insert statement:

    insert into target_table
    SELECT month_name, account_id,
    (SELECT DISTINCT param_description
    FROM parameter_table
    WHERE customer_tenure BETWEEN param_dec_low
    AND param_dec_high
    AND param_name = 'tenure') param_desc
    FROM table1
    I'm getting 'ORA-01427: single-row subquery returns more than one row' error. I tried using case statement but will require code change if a new tenure band is added.

    Then how can I get this result?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Then how can I get this result?
    It is poor design to store both CUSTOMER_TENURE & tenure_description in "detail" tables.
    These values MUST change over time!
    They are computed values that should be only presented when generating a report or displaying a form.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

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

    Wink Joining is the trick.

    Try joining the tables:
    Code:
    WITH
      Source_data AS
      (SELECT '01-OCT-09' Month_name, 111 Account_id, 2 Customer_tenure FROM dual UNION
       SELECT '01-OCT-09'           , 112           , 15  FROM dual UNION
       SELECT '01-OCT-09'           , 113           , 36  FROM dual)
    , parameter_table AS
      (SELECT 'tenure' parameter_id, 0 param_low_value, 6 param_high_value, '0 - 6 MOB' param_desc
         FROM dual UNION
       SELECT 'tenure',  6, 12, ' 6 - 12 MOB' FROM dual UNION
       SELECT 'tenure', 12, 24, '12 - 24 MOB' FROM dual UNION
       SELECT 'tenure', 24, 60, '24 - 60 MOB' FROM dual)
     SELECT  month_name, account_id, param_desc
       FROM  Source_data s, parameter_table p
      WHERE  s.customer_tenure BETWEEN p.param_low_value AND p.param_high_value
        AND  p.parameter_id = 'tenure'
      ORDER  BY 1,2,3;
    
    -- Result:
    SQL> /
    
    MONTH_NAM ACCOUNT_ID PARAM_DESC
    --------- ---------- -----------
    01-OCT-09        111 0 - 6 MOB
    01-OCT-09        112 12 - 24 MOB
    01-OCT-09        113 24 - 60 MOB
    
    3 rows selected.
    
    SQL>

    PS: Your parameter table has a flaw: What if the tenure value is 6 or 12 ar 24?
    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
  •