Combining SQL Statements Help!

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

Combining SQL Statements Help!

Author

Topic

Charles AWI
Starting Member

15 Posts

Posted - 2011-05-16 : 09:24:53

I am new to SQL and I had a simple question to ask.

I need to combine two SQL statements the first one is:

SELECT ROWNUM FROM TEST.ORDERLINEITEM WHERE ROWNUM < 100000
MINUS
SELECT ITEMCODE FROM AWI.ITEMMASTER

I would like the Result of this first statement, ^above^ , to be put into the second statement as ITEMCODE.

WITH cte AS
(
SELECT
ITEMCODE,
ITEMCODE - ROW_NUMBER() OVER(ORDER BY ITEMCODE) AS grp
FROM awi.ITEMMASTER
)
SELECT
MIN(ITEMCODE) AS startRange,
MAX(ITEMCODE) AS endRange,
MAX(ITEMCODE)-MIN(ITEMCODE)+1 AS ITEMcount,
CASE
WHEN MAX(ITEMCODE)-MIN(ITEMCODE)+1 > 22 THEN '> 22'
WHEN MAX(ITEMCODE)-MIN(ITEMCODE)+1 BETWEEN 18 AND 22 THEN '18-22'
WHEN MAX(ITEMCODE)-MIN(ITEMCODE)+1 BETWEEN 12 AND 17 THEN '12-17'
WHEN MAX(ITEMCODE)-MIN(ITEMCODE)+1 BETWEEN 5 AND 11 THEN ' 5-11'
ELSE ''
END AS Rnge

FROM
cte
GROUP BY
grp

The First Statement Gives me the missing ITEMCODES, I would like to Take the missing ITEM codes and put them into the second as ITEMCODE to give me the amount of consecutive missing ITEM codes and organize them by the count of consecutive missing ITEMCODES

WebFOCUS 7.6

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-16 : 09:37:54

SELECT ROWNUM FROM TEST.ORDERLINEITEM WHERE ROWNUM < 100000
MINUS
SELECT ITEMCODE FROM AWI.ITEMMASTER

The first select doesn't seem to produce an itemcode

maybe
SELECT distinct ITEMCODE FROM TEST.ORDERLINEITEM
where ITEMCODE not in (SELECT ITEMCODE FROM AWI.ITEMMASTER)

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

Charles AWI
Starting Member

15 Posts

Posted - 2011-05-16 : 09:49:32

Thanks for your reply!

All I am trying to do is get the result of the first statement to be feed into the second one as ITEMCODE.

EX.

(SELECT ROWNUM FROM TEST.ORDERLINEITEM WHERE ROWNUM < 100000
MINUS
SELECT ITEMCODE FROM AWI.ITEMMASTER) As ITEMCODE2

Then take ITEMCODE2 and put it into the next statement

WITH cte AS
(
SELECT
ITEMCODE2,
ITEMCODE2 - ROW_NUMBER() OVER(ORDER BY ITEMCODE2) AS grp
FROM awi.ITEMMASTER
)
SELECT
MIN(ITEMCODE2) AS startRange,
MAX(ITEMCODE2) AS endRange,
MAX(ITEMCODE2)-MIN(ITEMCODE2)+1 AS ITEMcount,
CASE
WHEN MAX(ITEMCODE2)-MIN(ITEMCODE2)+1 > 22 THEN '> 22'
WHEN MAX(ITEMCODE2)-MIN(ITEMCODE2)+1 BETWEEN 18 AND 22 THEN '18-22'
WHEN MAX(ITEMCODE2)-MIN(ITEMCODE2)+1 BETWEEN 12 AND 17 THEN '12-17'
WHEN MAX(ITEMCODE2)-MIN(ITEMCODE2)+1 BETWEEN 5 AND 11 THEN ' 5-11'
ELSE ''
END AS Rnge

FROM
cte
GROUP BY
grp

I hope this clears things up. Thanks for you help!

WebFOCUS 7.6

Charles AWI
Starting Member

15 Posts

Posted - 2011-05-16 : 10:07:05

Thanks for your reply!

All I am trying to do is get the result of the first statement to be feed into the second one as ITEMCODE2.

EX.

(SELECT ROWNUM FROM TEST.ORDERLINEITEM WHERE ROWNUM < 100000
MINUS
SELECT ITEMCODE FROM AWI.ITEMMASTER) As ITEMCODE2

Then take ITEMCODE2 and put it into the next statement

WITH cte AS
(
SELECT
ITEMCODE2,
ITEMCODE2 - ROW_NUMBER() OVER(ORDER BY ITEMCODE2) AS grp
FROM awi.ITEMMASTER
)
SELECT
MIN(ITEMCODE2) AS startRange,
MAX(ITEMCODE2) AS endRange,
MAX(ITEMCODE2)-MIN(ITEMCODE2)+1 AS ITEMcount,
CASE
WHEN MAX(ITEMCODE2)-MIN(ITEMCODE2)+1 > 22 THEN '> 22'
WHEN MAX(ITEMCODE2)-MIN(ITEMCODE2)+1 BETWEEN 18 AND 22 THEN '18-22'
WHEN MAX(ITEMCODE2)-MIN(ITEMCODE2)+1 BETWEEN 12 AND 17 THEN '12-17'
WHEN MAX(ITEMCODE2)-MIN(ITEMCODE2)+1 BETWEEN 5 AND 11 THEN ' 5-11'
ELSE ''
END AS Rnge

FROM
cte
GROUP BY
grp

I hope this clears things up. Thanks for you help!

WebFOCUS 7.6

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-16 : 10:41:55

>>
(SELECT ROWNUM FROM TEST.ORDERLINEITEM WHERE ROWNUM < 100000
MINUS
SELECT ITEMCODE FROM AWI.ITEMMASTER) As ITEMCODE2
>>

You seem to expect to get a resultset of itemcodes from this but is subtracting (except?) itemcodes from rownums which doesn't seem right. AT the best it would get a set of rownums whatever they are.

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

X002548
Not Just a Number

15586 Posts

Posted - 2011-05-16 : 10:46:03

is this sql server? I can find except but not minus

in any case...just a where not exist...right?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

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

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-16 : 11:13:33

quote:
Originally posted by X002548

is this sql server? I can find except but not minus

in any case...just a where not exist...right?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

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

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Already suggested - no idea how this rownum is connected to itemcode which seems to be the issue.

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

Charles AWI
Starting Member

15 Posts

Posted - 2011-05-18 : 13:16:17

quote:
Originally posted by nigelrivett

>>
(SELECT ROWNUM FROM TEST.ORDERLINEITEM WHERE ROWNUM < 100000
MINUS
SELECT ITEMCODE FROM AWI.ITEMMASTER) As ITEMCODE2
>>

You seem to expect to get a resultset of itemcodes from this but is subtracting (except?) itemcodes from rownums which doesn't seem right. AT the best it would get a set of rownums whatever they are.

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

ITEMCODE2 = missing ITEMCODES

how would i set the first statement, which results in the missing ITEMCODES equal to ITEMCODE2 to use in the second statement,

PLEASE HELP!

WebFOCUS 7.6

Charles AWI
Starting Member

15 Posts

Posted - 2011-05-19 : 09:09:11

.

WebFOCUS 7.6

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-19 : 09:19:48

SELECT ROWNUM FROM TEST.ORDERLINEITEM WHERE ROWNUM < 100000
MINUS
SELECT ITEMCODE FROM AWI.ITEMMASTER

rownum minus itemcode.

How is rownum related to itemcode?

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources