Help me to simplify this process

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

Help me to simplify this process

Author

Topic

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2007-11-06 : 15:34:14

SELECT WRREGN,WRCONO,WRDESC,WRWH01 as "WRWHSE", 1 as "WRORDER", WRDTMT
FROM PPTREASUSA.WHREGN
WHERE RTRIM(WRWH01)<>''
UNION
SELECT WRREGN,WRCONO,WRDESC,WRWH02 as "WRWHSE", 2 as "WRORDER", WRDTMT
FROM PPTREASUSA.WHREGN
WHERE RTRIM(WRWH02)<>''

.
.
.
.
.
UNION
SELECT WRREGN,WRCONO,WRDESC,WRW100 as "WRWHSE", 100 as "WRORDER", WRDTMT
FROM PPTREASUSA.WHREGN
WHERE RTRIM(WRW100)<>''

I have 100 queries pretty similar. Can I write one query for all instead?. How could I simplify this process? Using a cursor?

http://www.sqlserverstudy.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-06 : 15:37:42

That would mean you have 100 columns WRWH01--WRWH99, right?
If you are using SQL Server 2005, have a look at the UNPIVOT operator.

E 12°55'05.25"
N 56°04'39.16"

richard75013
Starting Member

19 Posts

Posted - 2007-11-06 : 15:41:05

if you were to try to fit all of them in 1 manageable procedure you would have to use dynamic SQL and that more often than not hurts you in the long run with recompiles and statement level comples.

to get rid of the union, and you typically want to, you can use temp tables but make sure tempdb IO can support it and that it is not already a bottle neck because far to often Server guys put tempdb on a mirror, (RAID 1) or a RAID 5 in which case RAID 5 ahs the over head of 4 additional IOs for every 1 write and the mirror down fall is that it can only be 2 drives therefore your spindle IOs are very limited, typical IO per SCSI drive is between 100-150 on a 10k drive and 150 to 180 on a 15k drive max for random IOs.

Thanks..Richard

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2007-11-06 : 16:01:45

quote:
Originally posted by Peso

That would mean you have 100 columns WRWH01--WRWH99, right?
If you are using SQL Server 2005, have a look at the UNPIVOT operator.

E 12°55'05.25"
N 56°04'39.16"

right 100 columns.
im using sql server 2k

http://www.sqlserverstudy.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-06 : 16:09:49

Use UNIÒN ALL instead of UNION. This will speed up your query.

E 12°55'05.25"
N 56°04'39.16"

gongxia649
So Suave

344 Posts

Posted - 2007-11-06 : 19:21:40

I will like to know if I could simplify the process.
I dont need UNION ALL as I don't need duplicates.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-07 : 01:09:31

quote:
Originally posted by gongxia649

I will like to know if I could simplify the process.
I dont need UNION ALL as I don't need duplicates.

Are you asking this on behalf of Original Poster?

Madhivanan

Failing to plan is Planning to fail

arorarahul.0688
Posting Yak Master

125 Posts

Posted - 2007-11-07 : 02:09:22

try this
just as a supplement

select desired_columns_list from tablename where col2 is not null or col4 is not null or col3 is not null

Rahul Arora
MCA 07 Batch
NCCE Israna, Panipat
HRY, INDIA

######################
IMPOSSIBLE = I+M+POSSIBLE

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-07 : 02:12:51

quote:
Originally posted by madhivanan

Are you asking this on behalf of Original Poster?

Madhivanan

Failing to plan is Planning to fail

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-07 : 03:19:55

Gonxia and Funketekun are the very same poster.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-07 : 03:20:59

How could there be duplicates?

WRREGN and WRCONO is the primary key, right?
And then you hardwire a "column number" in the query.

Where are the duplicates?

E 12°55'05.25"
N 56°04'39.16"

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2007-11-08 : 15:34:41

How could there be duplicates?
i dont know if there are duplicates.
WRREGN and WRCONO is the primary key, right?
yes
Where are the duplicates?
i dunt know.

http://www.sqlserverstudy.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-08 : 15:42:45

Since this is true, you can use UNION ALL instead of just UNION. It will speed up your query significantly.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources