Help me please understand this SQL Stmt

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 please understand this SQL Stmt

Author

Topic

Aki
Starting Member

4 Posts

Posted - 2010-05-07 : 23:59:39

Please help me understand the HAVING part of the stmt below, highlighted in red below.
I am not sure where "x.bin_id" came from..
Thank you

select
z.name, x.date, y.brand,
sum(x.txn_amt) SALES, count(1) "COUNT"
from
(select
a.company, a.ident, a.date,
a.ref, a.linetype, b.bin_id, a.terms, trim(a.card_itmz),
sum(a.txn_sls_amt) txn_amt
from
stage.payment a,
stage.bins b
where
b.panlow <= a.bin_10 and a.bin_10 <= b.panhigh
group by a.company, a.ident, a.date,
a.ref, a.linetype, b.bin_id, a.terms, trim(a.card_itmz)
having
(a.linetype in (5, 6) and sum(txn_sls_amt) >= 2000) or
(a.linetype = 17 and a.terms = 3 and trim(a.card_itmz) like '0%'
and sum(txn_sls_amt) >= 5000)) x ,
stage.bins y,
stage.branch z
where
x.bin_id = y.bin_id
and x.ident = z.ident
group by
z.name,
x.date,
y.brand

sathiesh2005
Yak Posting Veteran

85 Posts

Posted - 2010-05-08 : 02:44:32

Hi Aki,

"x.bin_id" refers to "b.bin_id" in your inner select query. since you used x as alias name for the select query.
please see your code below highlighted in green.

select
z.name, x.date, y.brand,
sum(x.txn_amt) SALES, count(1) "COUNT"
from
(select
a.company, a.ident, a.date,
a.ref, a.linetype, b.bin_id , a.terms, trim(a.card_itmz),
sum(a.txn_sls_amt) txn_amt
from
stage.payment a,
stage.bins b
where
b.panlow <= a.bin_10 and a.bin_10 <= b.panhigh
group by a.company, a.ident, a.date,
a.ref, a.linetype, b.bin_id, a.terms, trim(a.card_itmz)
having
(a.linetype in (5, 6) and sum(txn_sls_amt) >= 2000) or
(a.linetype = 17 and a.terms = 3 and trim(a.card_itmz) like '0%'
and sum(txn_sls_amt) >= 5000)) x ,
stage.bins y,
stage.branch z
where
x.bin_id = y.bin_id
and x.ident = z.ident
group by
z.name,
x.date,
y.brand

Regards,
Sathieshkumar. R

Aki
Starting Member

4 Posts

Posted - 2010-05-08 : 03:12:21

Can the alias condition part be already included in the first WHERE clause? So that one select statement is retained?

Kristen
Test

22859 Posts

Posted - 2010-05-08 : 03:55:54

Formatting it might help


SELECT	z.name,
	x.date, 
	y.brand,
	sum(x.txn_amt) AS SALES,
	count(1) AS [COUNT]
FROM
(
	SELECT	a.company, 
		a.ident, 
		a.date, 
		a.ref, 
		a.linetype, 
		b.bin_id, 
		a.terms, 
		trim(a.card_itmz),
		sum(a.txn_sls_amt) AS txn_amt
	FROM	stage.payment AS a,
		stage.bins AS b
	WHERE	    b.panlow <= a.bin_10 
		AND a.bin_10 <= b.panhigh
	GROUP BY a.company, 
		a.ident, 
		a.date,
		a.ref,
		a.linetype,
		b.bin_id,
		a.terms,
		trim(a.card_itmz)
	HAVING (
			    a.linetype IN (5, 6) 
			AND SUM(txn_sls_amt) >= 2000
		) OR
		(
			    a.linetype = 17 
			AND a.terms = 3 
			AND trim(a.card_itmz) LIKE '0%'
			AND SUM(txn_sls_amt) >= 5000
		)
) AS x,
	stage.bins y,
	stage.branch z
WHERE	    x.bin_id = y.bin_id
	AND x.ident = z.ident
GROUP BY z.name,
	x.date,
	y.brand

Some observations:

TRIM() is not a SQL Server Function - so presumably this is not SQL Server?

"AND trim(a.card_itmz) LIKE '0%'" the function call is superfluous, unless it does a left-trim. If a left-trim is not required leave it out (so the query can make use of an index if available), if a Left Trim is required then use the LTrim() function explicitly. (although this is within the HAVING clause, so may be there in order that the GROUP BY on that field doesn't provide multiple hits on leading/trailing spaces - if so why not just return a FLAG in the inner select for whether the record matches the test, or not, and check that in the having?)

Use JOINs instead of the comma-separated-list of tables

Personally I would use more meaningful alias names than A, B and X, Y, Z.

Several of the columns in the inner SELECT are unused and should be removed from the SELECT. Those that are genuinely needed as tie-break definers in the GROUP BY can be left in.

Not sure why stage.bins.brand cannot be selected in the Inner Select (in order to avoid having to re-join the table in the Outer Select)

Aki
Starting Member

4 Posts

Posted - 2010-06-04 : 23:03:45

Thank you guys Ü

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources