Leo Leo - 3 months ago 6
SQL Question

How can I declare a variable to read from a different table column?

I need to run a query and this query needs to run depends on the data in my excel file. So I supposed to create a ETL job to read this excel file to the temp table. User can change the Ques_P_id on different date, so I can read it to my temp table.

There are three column in my temp table:

Report_name | Ques_P_ID | Ques_R_ID

AAA 7(could be 1~12) 132
BBB 8(could be 1~12) 032


And I have another sql script to generate AAA report base on Ques_P_ID. Right now I want to do if the report_name =AAA and Ques_P_ID = 9 then run the query like this:

select a.contr_id, a.ques_r_id, a.ques_P_id,
max(case when ques_d_id = 320 then ques_d_id else NULL end) as "8-320",
max(case when ques_d_id = 321 then ques_d_id else NULL end) as "8-321"
from PODS.v_questionnaire_results_master A
left join PODS.v_questionnaire_results_detail B on A.ques_r_id = B.ques_r_id
where 1=1
and a.update_date = '20160824'
and a.ques_p_id = 8
and a.contr_id <> ''
group by 1,2,3;


This query will generate the report AAA report based on the ques_p_id = 8

contr_id ques_r_id ques_p_id 8-320 8-321
42315221 284806 8 320
44501488 282575 8 320
12817427 284460 8 320
11336635 284864 8 320
53040071 282691 8 321
50408289 284398 8 321


So I want to make the ques_p_id to variable can read the temp table if the ques_p_id = 1~9 then use 1~9 on my report query. So I need to help to declare this variable on my query.

If I need to identify the ques_p_id in my temp table I should do like this right?

Declare @P int
Set @P = select (ques_p_ID) from temp_table_excel
select * From generate_AAA_report(@P);


And also this script

max(CASE WHEN ques_d_id = 320 THEN 320 END),
max(CASE WHEN ques_d_id = 321 THEN 321 END)


ques_d_id will change the number by p_id, if P_id = 9,
it will change to WHEN ques_d_id = 520 THEN 520 END.....etc......

ques_p_id ques_m_id ques_d_id
8 1 320
8 1 321
8 1 322
8 1 323
8 2 324
9 1 445
9 1 446
9 1 447
9 2 448
9 2 449
9 2 450


Thanks

Answer

If I understand your problem correctly, then you best bet is a SQL function that takes ques_p_id as a parameter. With some tweaks to your query that looks like this:

CREATE FUNCTION generate_AAA_report(p_id integer)
RETURNS TABLE(contr_id text, ques_r_id int, ques_p_id int, "8-320" int, "8-321" int) AS $$
    SELECT a.contr_id, ques_r_id, p_id, 
           max(CASE WHEN ques_d_id = 320 THEN 320 END),
           max(CASE WHEN ques_d_id = 321 THEN 321 END)
    FROM PODS.v_questionnaire_results_master A
    LEFT JOIN PODS.v_questionnaire_results_detail B USING (ques_r_id)
    WHERE a.update_date = '20160824'
    AND a.ques_p_id = p_id
    AND a.contr_id <> ''
    GROUP BY 1,2,3;
$$ LANGUAGE sql STRICT STABLE;

You could add a second parameter to the function for the update_date field so that you can use the same function for other dates as well. You should then probably also add a field to the table that is being returned from the function to identify which date it is for.

You can then generate a report like this:

SELECT * FROM generate_AAA_report(9);

The report is of course just the output from a query. If you want to save it, you can use this query in a COPY command to create a CSV file that you can open in Excel again, or you can make a physical table like this:

CREATE TABLE AAA_report_3 AS
  SELECT * FROM generate_AAA_report(3);

Note that the function returns a table so you should use it as a row source (in the FROM clause of the query).

Comments