Shortening query - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Shortening query

Author

Topic

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-05-27 : 02:11:53

SELECT h.trx_type, h.ref_no, h.trx_date
FROM ((SELECT h.trx_type, h.do_no as ref_no, h.trx_date
FROM (SELECT trx_type, do_no, do_date as trx_date FROM st_head WHERE in_date IS NULL UNION SELECT trx_type, do_no, in_date as trx_date FROM st_head WHERE in_date IS NOT NULL)h
WHERE (h.trx_type='DO'or h.trx_type='CDO'or h.trx_type='DOL'))
Union
(SELECT trx_Type, in_no as ref_no, in_date as trx_date
FROM st_head
WHERE (trx_type='INV'or trx_type='CS'or trx_type='CN'or trx_type='DN'or trx_type='POS'or trx_type='INC'or trx_type='CNC')))h

hi, can any1 help me to shorten the query...i tried and i failed T.T

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-05-27 : 02:34:51

[code]
SELECT
h.trx_type, h.do_no as ref_no, h.trx_date
FROM (
SELECT trx_type, do_no,
CASE WHEN in_Date IS NULL THEN do_date
WHEN in_date IS NOT NULL THEN in_date END AS trx_date
FROM st_head
)h
WHERE (h.trx_type='DO'or h.trx_type='CDO'or h.trx_type='DOL')
Union
SELECT
trx_Type, in_no as ref_no, in_date as trx_date
FROM st_head
WHERE (trx_type='INV'or
trx_type='CS'or trx_type='CN'or trx_type='DN'or trx_type='POS'or trx_type='INC'or trx_type='CNC')
[/code]

nr
SQLTeam MVY

12543 Posts

Posted - 2009-05-27 : 02:36:06

First thought
WHERE trx_type in ('INV','CS','CN','DN','POS','INC','CNC')

Can probably simplify the rest too

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-27 : 02:38:47

Try this


SELECT	trx_type,
	do_no AS ref_no,
	COALESCE(in_date, do_date) AS trx_date
FROM	st_head
WHERE	trx_type IN ('DO', 'CDO', 'DOL')

UNION

SELECT	trx_type,
	in_no AS ref_no,
	in_date AS trx_date
FROM	st_head
WHERE	trx_type IN ('INV', 'CS', 'CN', 'DN', 'POS', 'INC', 'CNC')

Your old query looked like this.

SELECT	h.trx_type,
	h.ref_no,
	h.trx_date
FROM	(
		SELECT	h.trx_type,
			h.do_no as ref_no,
			h.trx_date 
		FROM	(
				SELECT	trx_type,
					do_no,
					do_date as trx_date
				FROM	st_head
				WHERE	in_date IS NULL

				UNION

				SELECT	trx_type,
					do_no,
					in_date as trx_date
				FROM	st_head
				WHERE	in_date IS NOT NULL
			) AS h 
		WHERE	h.trx_type = 'DO'
			or h.trx_type = 'CDO'
			or h.trx_type = 'DOL'

		UNION

		SELECT	trx_Type,
			in_no as ref_no,
			in_date as trx_date
		FROM	st_head
		WHERE	trx_type = 'INV'
			or trx_type = 'CS'
			or trx_type = 'CN'
			or trx_type = 'DN'
			or trx_type = 'POS'
			or trx_type = 'INC'
			or trx_type = 'CNC'
	) AS h

See the UNION and using WHERE x IS NULL and then WHERE x IS NOT NULL?

E 12°55'05.63"
N 56°04'39.26"

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-05-27 : 02:39:08

arr thx alot!!! i was looking forwardto bklr answer...trying to slot in case but no ppl teaching me T.T

nr
SQLTeam MVY

12543 Posts

Posted - 2009-05-27 : 02:40:37

SELECT trx_type, ref_no = do_no, trx_date = coalesce(in_date,do_date)
FROM st_head
where trx_type in ('DO', 'CDO', 'DOL')
union
SELECT trx_Type, in_no, in_date
FROM st_head
WHERE trx_type in ('INV','CS','CN','DN','POS','INC','CNC')

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2009-05-27 : 02:46:13

And without the union
SELECT distinct trx_type, ref_no = do_no,
trx_date = case when trx_type in ('INV','CS','CN','DN','POS','INC','CNC') then in_date
else coalesce(in_date,do_date) end
FROM st_head
where trx_type in ('DO', 'CDO', 'DOL', 'INV','CS','CN','DN','POS','INC','CNC')

The distinct is because you had unions. If ythe rows are distint you could get rid of that and change union to union all in the others.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-05-27 : 02:46:19

thx thx alot!!! now i have to figure wat is COALESCE is about

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-27 : 03:01:14

http://msdn.microsoft.com/en-us/library/ms190349.aspx

E 12°55'05.63"
N 56°04'39.26"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-27 : 03:01:33