Select Query help

[vidhya]

Hello,

My table looks similar to this, I have removed a few columns:
table1:
forecast_id forecast_name freeze enabled
100 Q1 2009 N Y
101 Q2 2009 N Y

table2:
forecast_id parameter_name parameter_value
100 Start Date 01/01/2009
100 End Date 03/31/2009
100 Growth % 20
100 Retailer Walmart
101 Start Date 04/01/2009
101 End Date 06/30/2009
101 Growth % 20
101 Retailer Walmart

What i need to do is
select from table 1, forecast name & freeze
where in table2 parameter = Retailer, Parameter value = Walmart and
Start Date = 01/01/2009 and End Date = 03/31/2009

here is my query is there a easy way this can be done. I have used decode function in the past for similar situation for counting and grouping.

SELECT
i.FORECAST_ID,
i.FORECAST_NAME,
i.FREEZE_FLAG
FROM
(SELECT
x.FORECAST_ID,
x.FORECAST_NAME,
x.FREEZE_FLAG
FROM
(SELECT
A.FORECAST_ID,
A.FORECAST_NAME,
A.FREEZE_FLAG
FROM GC_FORECAST A, GC_FORECAST_PARAMETERS B
WHERE
A.FORECAST_ID = B.FORECAST_ID AND
B.PARAMETER_NAME = 'RETAILER' AND
B.PARAMETER_VALUE = 'WALMART') x, GC_FORECAST_PARAMETERS y
WHERE
x.FORECAST_ID = y.FORECAST_ID AND
y.PARAMETER_NAME = 'START DATE' AND
y.PARAMETER_VALUE = '01/01/2009') i, GC_FORECAST_PARAMETERS j
WHERE
i.FORECAST_ID = j.FORECAST_ID AND
j.PARAMETER_NAME = 'END DATE' AND
j.PARAMETER_VALUE = '03/31/2009'

Thank you for your time and help.

Vidhya

[PAVB]

Does your query returns what you want? I do not think so.

If I got your specs right you query is resolved with a simple join; you do not need inline views

[vidhya]

Thank you for your response. Yes, My query returns the result I needed. The problem with the simple join is that i cannot write a single query to pull all three parameter values as they are in three different records. I found a solution using case statements.

SELECT gc.forecast_id, gc.forecast_name, gc.freeze_flag
  FROM gc_forecast gc,
       (SELECT   forecast_id,
                 MAX (CASE
                         WHEN parameter_name = 'START DATE'
                         AND parameter_value = '01/01/2009'
                            THEN 1
                         ELSE 0
                      END
                     ) cond1,
                 MAX (CASE
                         WHEN parameter_name = 'END DATE'
                         AND parameter_value = '03/31/2009'
                            THEN 1
                         ELSE 0
                      END
                     ) cond2,
                 MAX (CASE
                         WHEN parameter_name = 'RETAILER'
                         AND parameter_DESC = 'WALMART'
                            THEN 1
                         ELSE 0
                      END
                     ) cond3
            FROM gc_forecast_parameters
        GROUP BY forecast_id) gf
 WHERE gc.forecast_id = gf.forecast_id
   AND gf.cond1 = 1
   AND gf.cond2 = 1
   AND gf.cond3 = 1

[PAVB]

The problem with the simple join is that i cannot write a single query to pull all three parameter values as they are in three different records.

Yes you can.

In your WHERE clause you can have three entries for your gc_forecast_parameters table each one of them with a different alias then you pull one parameter for each alias.

[vidhya]

Thank you for your suggestion. I tried using 3 different alias for this table but i still get the result i need. if you wouldnt mind would you please give me an example of the code.

Thank you again for your help.