 Assign the SQL statement to a variable [message #633094] |
Tue, 10 February 2015 22:13  |
 |
mikelala
Messages: 6
Registered: February 2015
|
Junior Member |
|
|
Hello ,
I want the following query to be assigned to a variable so that I can use execute immedate in running the sql statement .
SELECT DMOG.hic_num AS HIC_NUM,
DMOG.mem_num AS MEM_NUM,
ELIG.bg_dt AS BG_DT,
ELIG.end_dt AS END_DT
FROM bi_ika_mem.ma_ika_mem_dmog DMOG,
bi_ika_mem.ma_ika_mem_elig ELIG,
bi_edw_hist.lab_pat_hsty PAT,
bi_edw_hist.lab_req_hsty LRH
WHERE LRH.serv_req_dt_key BETWEEN 20140101 AND 20140131
AND DMOG.src_sys_cd = 'MA'
AND ELIG.src_sys_cd = 'MA'
AND ELIG.mem_num = DMOG.mem_num
AND DMOG.mem_num = PAT.ctrc_num
AND LRH.pat_sk = PAT.pat_sk
AND PAT.edw_end_dt_key = 99991231
AND LRH.serv_req_dt_key >= To_number(To_char(DMOG.bg_dt, 'YYYYMMDD'))
AND LRH.serv_req_dt_key <= To_number(To_char(DMOG.end_dt, 'YYYYMMDD'));
*BlackSawn added {codetags}. Please do so yourself in the future.
[Updated on: Tue, 10 February 2015 22:23] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: Assign the SQL statement to a variable [message #633126 is a reply to message #633125] |
Wed, 11 February 2015 06:22  |
 |
sss111ind
Messages: 634
Registered: April 2012
Location: India
|
Senior Member |
|
|
You can declare a cursor of the select statment and use the parameter in where clause.
create or replace procedure p(p_from_dt in date,p_to_dt in date)is
CURSOR c1 IS
SELECT * FROM emp WHERE hiredate BETWEEN p_from_dt AND p_to_dt;
BEGIN
FOR i IN c1 loop
dbms_output.put_line(i.sal);
END loop;
end;
[Updated on: Wed, 11 February 2015 06:24] Report message to a moderator |
|
|
|
| Re: Assign the SQL statement to a variable [message #633128 is a reply to message #633125] |
Wed, 11 February 2015 06:33 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
mikelala wrote on Wed, 11 February 2015 17:30The where clause between parameters ..are dynamic - I just happened to give an example
WHERE LRH.serv_req_dt_key BETWEEN 20140101 AND 20140131
You need to explain in detail what exactly you are trying to do. Why not use bind variables?
And, why is LRH.serv_req_dt_key a NUMBER and not a DATE data type?
|
|
|
|
| Re: Assign the SQL statement to a variable [message #633131 is a reply to message #633125] |
Wed, 11 February 2015 07:23 |
 |
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
mikelala wrote on Wed, 11 February 2015 13:00The where clause between parameters ..are dynamic - I just happened to give an example
WHERE LRH.serv_req_dt_key BETWEEN 20140101 AND 20140131
What is dynamic? The values or the column?
If this is the values then it is not a dynamic query but a static one and you have to use variables in place of the values.
What is your programming language?
What is the application which will send the query to the database?
[Updated on: Wed, 11 February 2015 07:24] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
| Re: Assign the SQL statement to a variable [message #633139 is a reply to message #633135] |
Wed, 11 February 2015 08:19 |
|
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
mikelala wrote on Wed, 11 February 2015 14:57The values are dynamic which comes as parameters to a procedure that I am going to include in the said sql statement.
So the statement is static and you must NOT use execute immediate.
You didn't answer my questions:
Quote:What is your programming language?
What is the application which will send the query to the database?
[Updated on: Wed, 11 February 2015 08:19] Report message to a moderator |
|
|
|
|
|
| Re: Assign the SQL statement to a variable [message #633141 is a reply to message #633126] |
Wed, 11 February 2015 09:02 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
sss111ind wrote on Wed, 11 February 2015 17:52You can declare a cursor of the select statment and use the parameter in where clause.
create or replace procedure p(p_from_dt in date,p_to_dt in date)is
CURSOR c1 IS
SELECT * FROM emp WHERE hiredate BETWEEN p_from_dt AND p_to_dt;
BEGIN
FOR i IN c1 loop
dbms_output.put_line(i.sal);
END loop;
end;
You could do a task in many ways, and in programming language, such as PL/SQL in your post, it won't be efficient.
In my opinion, try to do it in plain SQL, else do it in PL/SQL, but keep in mind to do it efficiently. Please correct me if I am wrong.
Just as a comment(out of so many other options), why not use a CURSOR FOR loop, or why not do it in BULK? Ultimately, in OP's case, I would go with bind variables.
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
