Solved

SQL Statement using With

Posted on 2013-02-01
2
658 Views
Last Modified: 2013-02-04
I am trying to rewrite a SQL statement that was previously written with a (with) at the beginning of it.  I am using PowerBuilder 12.5 and this behind a datawindow. The query is calling temp tables and I want to use a view instead. I am not sure what needs to be done. Can anyone give assistance?  I have attached the with statement.  I have never dealt with this kind of statement before.
With-Statement.txt
0
Comment
Question by:ShaePNC
2 Comments
 
LVL 32

Accepted Solution

by:
ewangoya earned 500 total points
ID: 38846560
The with statement implements what is called a CTE (Common Table Expression)
This is basically a temporary result set which can be queries by subsequent statements.

To better understand, separate them and study how they relate to each other.
Assume the first CTE was a temp table called fy with a field called fy as well and data was inserted to it
declare @fy table(fy Datatype)
insert into @fy
select fy
from fp.scenario 
where scenario_id = :fp_scenario_id

Open in new window

                 

The second table would be called t1
declare @t1 table(	row_id Datatype,  --all the fields have data typ
				metadata,
				fy0_amount,
				fy1_amount,
				fy2_amount,
				fy3_amount,
				fy4_amount,
				fy5_amount,
				fy6_amount,
				fy7_amount,
				fy8_amount,
				fy9_amount,
				fy10_amount,
				fund_code,
				fund_class,
				fiscal_year,
				child_req_id,
				child_req_title,
				child_req_desc,
				child_req_des,
				child_req_active,
				child_req_order,
				parent_req_id,
				parent_req_title,
				parent_req_desc,
				parent_req_des,
				parent_req_active,
				parent_req_order,
				change_record_id,
				change_record_title,
				change_record_desc,
				change_record_des,
				change_record_active,
				fund_record_id,
				rdu_number,
				rdu_title,
				rdu_sort_order,
				component_number,
				component_title,
				component_sort_order,
				formula,
				scenario_id,
				scenario_title,
				fp_scenario_id,
				department_id,
				scen_type_id,
				department_title,
				department_sort,
				active
			) --end of declare table

insert into @t1
select		row_id,
							metadata,
							(case when fiscal_year = (select fy from fy) - 1 then sum(amount) else 0 end) as fy0_amount,
							(case when fiscal_year = (select fy from fy) then sum(amount) else 0 end) as fy1_amount,
							(case when fiscal_year = (select fy from fy) + 1 then sum(amount) else 0 end) as fy2_amount,
							(case when fiscal_year = (select fy from fy) + 2 then sum(amount) else 0 end) as fy3_amount,
							(case when fiscal_year = (select fy from fy) + 3 then sum(amount) else 0 end) as fy4_amount,
							(case when fiscal_year = (select fy from fy) + 4 then sum(amount) else 0 end) as fy5_amount,
							(case when fiscal_year = (select fy from fy) + 5 then sum(amount) else 0 end) as fy6_amount,
							(case when fiscal_year = (select fy from fy) + 6 then sum(amount) else 0 end) as fy7_amount,
							(case when fiscal_year = (select fy from fy) + 7 then sum(amount) else 0 end) as fy8_amount,
							(case when fiscal_year = (select fy from fy) + 8 then sum(amount) else 0 end) as fy9_amount,
							(case when fiscal_year = (select fy from fy) + 9 then sum(amount) else 0 end) as fy10_amount,
							fund_code,
							fund_class,
							fiscal_year,
							child_req_id,
							child_req_title,
							child_req_desc,
							child_req_des,
							child_req_active,
							child_req_order,
							parent_req_id,
							parent_req_title,
							parent_req_desc,
							parent_req_des,
							parent_req_active,
							parent_req_order,
							change_record_id,
							(case when change_record_desc is null or length(change_record_desc)<1 then change_record_title else change_record_desc end),
							change_record_desc,
							change_record_des,
							change_record_active,
							fund_record_id,
							rdu_number,
							rdu_title,
							rdu_sort_order,
							component_number,
							component_title,
							component_sort_order,
							formula,
							scenario_id,
							scenario_title,
							fp_scenario_id,
							department_id,
							scen_type_id,
							(select ltitle from sh.department where dept = department_id) as department_title,
							(select sort_order from sh.department where dept = department_id) as department_sort,
							(case when parent_req_id <> 0 then parent_req_active  
									when child_req_id <> 0 then child_req_active
									when change_record_id <> 0 then change_record_active end) as active					
			from 		fp.v_funding_records
			where	fp_scenario_id = :fp_scenario_id and (parent_req_active = 1 or child_req_active = 1 or change_record_active = 1) and scen_type_id in (1,2,3,4)
			group by	row_id,
						metadata,
						amount,
						fund_code,
						fund_class,
						fiscal_year,
						child_req_id,
						child_req_title,
						child_req_desc,
						child_req_des,
						child_req_active,
						child_req_order,
						parent_req_id,
						parent_req_title,
						parent_req_desc,
						parent_req_des,
						parent_req_active,
						parent_req_order,
						change_record_id,
						change_record_title,
						change_record_desc,
						change_record_des,
						change_record_active,
						fund_record_id,
						rdu_number,
						rdu_title,
						rdu_sort_order,
						component_number,
						component_title,
						component_sort_order,
						formula,
						scenario_id,
						scenario_title,
						fp_scenario_id,
						department_id,
						scen_type_id
		

Open in new window


.....And so on and so forth

You can clearly see what kind of advantage a CTE offers you in this case.
Instead of declaring all these as temp tables, we simple use the CTE and let SQL Server worry about how its storing the data.
0

Featured Post

ScreenConnect 6.0 Free Trial

Explore all the enhancements in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI, app configurations and chat acknowledgement to improve customer engagement!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question