| Author |
 Topic  |
|
|
sindhu sarah
Starting Member
United Arab Emirates
34 Posts |
 Posted - 11/21/2011 : 07:26:18
|
Hi,
Is it possible to to use a dynamic sql statement into an another statement.
For ex;
declare @param1 varchar(10)
declare @Line1E varchar(4000)
declare @SQF2 varchar(4000)
declare @est varchar(4000)
declare @EAC varchar(4000)
set @param1='0211%'
If @param1 = '0211%'
begin
set @Line1E = 'Select left(ProjCategoryGroup.CategoryGroupID,2) as Grp, ProjCategoryGroup.CategoryGroupID+'+'''-'''+'+ProjCategoryGroup.Name as CAT, '' as EName, '
set @SQF2 = 'ProjForecastEmpl, ProjCategoryGroup where
Left(ProjForecastEmpl.CategoryId,6) = ProjCategoryGroup.CategoryGroupID and
ProjForecastEmpl.DataAreaId = ProjCategoryGroup.DataAreaId and'
end
set @est = @Line1E +'ProjForecastEmpl.Qty as Est, 0 as EAC, 0 as PYr, 0 as M01,
0 as M02, 0 as M03, 0 as M04, 0 as M05, 0 as M06, ", 0 as YTD From
'''+@SQF2+'''
ProjForecastEmpl.DataAreaId = '+'''cae'''+' and
ProjForecastEmpl.ProjId = '+'''100304'''+' and ModelId = '+'''1-Order'''
print @est
select @EAC = @Line1E +
'0 as Est, ProjForecastEmpl.Qty as EAC, 0 as PYr, 0 as M01,
0 as M02, 0 as M03, 0 as M04, 0 as M05, 0 as M06, "
0 as M07, 0 as M08, 0 as M09, 0 as M10, 0 as M11, 0 as M12, 0 as YTD From
'''+@SQF2+'''
ProjForecastEmpl.DataAreaId = '+'''cae'''+' and
ProjForecastEmpl.ProjId = '+'''100304'''+' and ModelId = '+'''EAC'''
select* from(@est union all @EAC)
on the above statement,the variable @Line1E and @SQF2 needs to be used for many other statement and make union all between all the above statement.i dont want repeat the line for each statement..so i want it to declare to a variable and use it as many times in any other query.
Thanks in advance for your kind reply.
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 11/21/2011 : 08:48:58
|
looks like overcomplicating the issue. Anyways, since we dont have any more visibility on problem you can try this
exec('select * from('+ @est + ' union all ' + @EAC + ')t')
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
sindhu sarah
Starting Member
United Arab Emirates
34 Posts |
Posted - 11/21/2011 : 09:01:22
|
Thanks a lot visakh.I hope this works.The query seems to eb very complicated but what i wanted is to use the variable of the sql statement to another sql statement.rather than repeating all the times..but i think the solution which u gave should work.
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 11/21/2011 : 09:09:45
|
if you can explain the bigger picture, somebody will be able to suggest you an easy alternate solution.
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
johntech
Yak Posting Veteran
Egypt
51 Posts |
Posted - 11/24/2011 : 09:14:49
|
Executing DDL and SCL Statements in PL/SQL
Only dynamic SQL can execute the following types of statements within PL/SQL program units:
Data definition language (DDL) statements such as CREATE, DROP, GRANT, and REVOKE
Session control language (SCL) statements such as ALTER SESSION and SET ROLE
The TABLE clause in the SELECT statement
The following native dynamic SQL example uses a SELECT statement with the TABLE clause.
Example 8-1 Using SELECT . . . TABLE in Dynamic SQL
-- Create an object t_emp and a datatype t_emplist as a table of type t_emp
CREATE TYPE t_emp AS OBJECT (id NUMBER, name VARCHAR2(20))
/
CREATE TYPE t_emplist AS TABLE OF t_emp
/
-- Create a table with a nested table of type t_emplist
CREATE TABLE dept_new (id NUMBER, emps t_emplist)
NESTED TABLE emps STORE AS emp_table;
-- Populate the dept_new table with data
INSERT INTO dept_new VALUES
(
10,
t_emplist
(
t_emp(1, 'SCOTT'),
t_emp(2, 'BRUCE')
)
);
-- Write a PL/SQL block that queries table dept_new and nested table emps
-- SELECT ... FROM ... TABLE is not allowed in static SQL in PL/SQL
DECLARE
v_deptid NUMBER;
v_ename VARCHAR2(20);
BEGIN
EXECUTE IMMEDIATE 'SELECT d.id, e.name
FROM dept_new d, TABLE(d.emps) e
WHERE e.id = 1'
INTO v_deptid, v_ename;
END;
/
http://docs.oracle.com/cd/B19306_01/appdev.102/b14251/adfns_dynamic_sql.htm#BJEHDACE |
|
|
|
madhivanan
Premature Yak Congratulator
India
22460 Posts |
Posted - 11/25/2011 : 04:41:37
|
Note that this forum is for SQL Server. Your suggestion will not work in SQL Server
Madhivanan
Failing to plan is Planning to fail |
|
|
|
sindhu sarah
Starting Member
United Arab Emirates
34 Posts |
Posted - 11/28/2011 : 05:37:09
|
| Thanks Visakh your solution worked |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 11/28/2011 : 07:17:32
|
wc
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
|
|
Dynamic SQL
