how to include other sql script? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

how to include other sql script?

Author

Topic

whitefreesia.bai
Starting Member

6 Posts

Posted - 2013-07-03 : 11:56:51

Hi,

I am new to SQL Server.
My script basically looks like this.

-- define a, b, c
with a as (
select ......
),
b as (
select ....
),
c as (
select ....
)
-- main sql1
select a.xxx, b.xxx
from a inner join b on .....

-- main sql2
select a.xxx, b.xxx, c.xxx
from a inner join b on .....
inner join c on ....

-- main sql3
select .....

The problem is I can't run the three main sql consecutively. It runs well for main sql1, but doesn't run for sql2 and sq3 because the definition of a, b, c is not saved. I wonder if I can save the first part of the code in some other file or function and just include it before each main sql. I came to this kind of code is because I don't have permission to write tables or procedures on the sql server. If there is another way to optimize my code, please feel free to let me know. Any advice is appreciated. Thanks a lot!!!

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-07-03 : 12:03:02

It seems like you're using CTE, which is usable for once just after its definition.

For sql2, you may wind up your logic like

SELECT ColumnsList
FROM (SELECT ..... )a ON ...
INNER JOIN (SELECT ....)b ON ...
INNER JOIN (SELECT ....)c ON ...

Cheers
MIK

whitefreesia.bai
Starting Member

6 Posts

Posted - 2013-07-03 : 12:44:33

Thanks MIK. Actually that is exactly what I try not to do. My CTEs are very long and I don't want to write them repeatedly for each main SQL. If I need to make little revision to the CTEs, I have to revise all of them which is not safe.

Thanks.

quote:
Originally posted by MIK_2008

It seems like you're using CTE, which is usable for once just after its definition.

For sql2, you may wind up your logic like

SELECT ColumnsList
FROM (SELECT ..... )a ON ...
INNER JOIN (SELECT ....)b ON ...
INNER JOIN (SELECT ....)c ON ...

Cheers
MIK

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-03 : 13:27:36

Depending on what the 3 queries are doing, it may be possible that you could combine all of them into one to get all the results you need. Emphasis on "may be", because it really really depends on what the queries are. If you post a sample then that would make it easier to say for sure.

I know you said you don't have permissions to create tables - but if you have permissions to create temp tables, put the data from each of the cte's into temp tables and then write your queries against those.

Barring those two possibilities, you just have to repeat the cte each time and live with it the additional maintainence efforts that come with it.

whitefreesia.bai
Starting Member

6 Posts

Posted - 2013-07-03 : 14:09:35

Thank you very much James.

My actually query looks like this.

with a as (
select a1, a2, a3
from xxxxx
),

b as (
select b1, b2, b3
from yyyy
)

-- main sql1 ---
select count(*) as cnt_by_a2
from a
group by a2

-- main sql2 ---
select count(*) as cnt_by_a3
from a
group by a3

-- main sql3 ---
select a2, sum(b2) as sum_b2_by_a2
from a inner join b on a.a1 = b.b1
group by a2

-- main sql4 ---
select a3, sum(b2) as sum_b2_by_a3
from a inner join b on a.a1 = b.b1
group by a3

Please let me know if there is a better way to combine these queries into one.

I am still in the process of requesting some personal space that I can create some temporary tables. Hope I can get it.

Thank you very much.

quote:
Originally posted by James K

Depending on what the 3 queries are doing, it may be possible that you could combine all of them into one to get all the results you need. Emphasis on "may be", because it really really depends on what the queries are. If you post a sample then that would make it easier to say for sure.

I know you said you don't have permissions to create tables - but if you have permissions to create temp tables, put the data from each of the cte's into temp tables and then write your queries against those.

Barring those two possibilities, you just have to repeat the cte each time and live with it the additional maintainence efforts that come with it.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-04 : 10:55:54

Looking at the queries you posted, unless there is a one to one relationship between a.a1 and b.b1, you cannot get the results in a single query. If that is so, you would have to use one of the other approaches that I had described earlier.

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-07-04 : 11:05:33

Some thing like this:

[CODE]

with a as (
select a1, a2, a3,
(SELECT COUNT(1) FROM xxxxx T2 WHERE T2.a2 = T.a2 GROUP BY T2.a2) as cnt_by_a2,
(SELECT COUNT(1) FROM xxxxx T2 WHERE T2.a3 = T.a3 GROUP BY T2.a3) as cnt_by_a3
from xxxxx
),

b as (
select b1, b2, b3
from yyyy
)

-- main sql1 ---
select *,
(select sum(b2)
from a A2 inner join b on A2.a1 = b.b1 WHERE A2.a2 = A1.a2 GROUP by A2.a2) as sum_b2_by_a2,
(select sum(b2)
from a A2 inner join b on A2.a1 = b.b1 WHERE A2.a2 = A1.a2 GROUP by A2.a3) as sum_b2_by_a3
from a A1;

[/CODE]

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-04 : 12:27:08

Good one, Mumu88! That thought (of using subqueries in the final query) didn't occur to me.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources