Feeding One Statement Into Another

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

Feeding One Statement Into Another

Author

Topic

Charles AWI
Starting Member

15 Posts

Posted - 2011-05-19 : 09:41:30

Hello,

I am new to SQL and i was wondering if anyone was kind enough to help! What i would like to know is how to count consecutive numbers. More specifically I want to be able to count consecutive numbers that show up in a column. To make it easy,
Lets say N = the *count* of the number of consecutive numbers going down a column in a run, and S = first number of the run of conecutive numbers and E = last number of the run of consecutive numbers.

EX.

6
7
8
9
10
11
12
13
14
15
16
17
18
20
21
22
23
24

I would like to have data appear in four columns based on the value of N

column 1 = 5 <= N <= 11
column 2 = 12 <= N <= 17
column 3 = 18 <= N <= 21
column 4 = 22 <= N

and under each of those 4 columns, i would like to have three "sub columns"

Column a = S
Column b = E
Column c = N

So the data should look like this

Column1_____Column2____Column3_____Column4
S__E__N_____S__E__N____S__E__N_____S__E__N
20_24_5_____6__18_13______________________

=================================================================

This Statement Gives me the UNUSEDITEMCODES:

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

================================================================

I need to use UNUSEDITEMCODES in a statement to give me the outputs as stated above

Column1_____Column2____Column3_____Column4
S__E__N_____S__E__N____S__E__N_____S__E__N
20_24_5_____6__18_13______________________

================================================================

I hope somene understands and is willing to help!! Thank you so Much!

WebFOCUS 7.6

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-19 : 09:52:17

Is this an add-on or enhancement to the issue that you had posted a few days ago here? http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=160596

If it is, it shouldn't be too hard, but not clear to me what if anything is different here.

Charles AWI
Starting Member

15 Posts

Posted - 2011-05-19 : 10:00:32

Yes It is, It Seemed like a lot of People were getting confused and I wanted to clarify and refine what I was asking for

WebFOCUS 7.6

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-19 : 10:10:04

So if I understood it correctly, you want to run the query from the earlier thread, but want to provide the input data to it using the "subtraction" that you described.

In the query below, I am assuming the following:
You have two tables TEST.ORDERLINEITEM and AWI.ITEMMASTER. You want to pick up every ROWNUM in TEST.ORDERLINEITEM that satisfies two conditions:

first: ROWNUM should be less than 100000, and

second: the value of ROWNUM should not exist in column ITEMCODE in table AWI.ITEMMASTER.

The collection of ROWNUMs you get should be fed as input to the query from the earlier thread.

Assuming all of that, would this work?


;WITH CTE1 AS -- This part is doing the "subtraction" that you described
(
   SELECT   ROWNUM AS ITEMCODE
   from     TEST.ORDERLINEITEM ol
   WHERE    ROWNUM < 100000
          AND NOT EXISTS 
          ( 
             SELECT * FROM AWI.ITEMMASTER im
             WHERE ol.ROWNUM = im.ITEMCODE
          )
),
-- from here on, it is the same as the query from earlier, except it is using the 
-- output from CTE1 above.
cte AS 
(
    SELECT
       ITEMCODE,
       ITEMCODE - ROW_NUMBER() OVER(ORDER BY ITEMCODE) AS grp
    FROM
       CTE1
)
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

Charles AWI
Starting Member

15 Posts

Posted - 2011-05-19 : 13:11:58

Thank you so much, I finally got it working!

This is How I got it to work

WITH CTE1 AS -- This part is doing the "subtraction" that you described
(
SELECT ROWNUM AS ITEMCODE2 FROM TEST.ORDERLINEITEM WHERE ROWNUM < 100000
MINUS
SELECT ITEMCODE FROM AWI.ITEMMASTER

),
-- from here on, it is the same as the query from earlier, except it is using the
-- output from CTE1 above.
cte AS
(
SELECT
ITEMCODE2,
ITEMCODE2 - ROW_NUMBER() OVER(ORDER BY ITEMCODE2) AS grp
FROM
CTE1
)
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

WebFOCUS 7.6

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-19 : 13:40:21

I am glad you got it working, but, it has me confused now!!


WITH CTE1 AS -- This part is doing the "subtraction" that you described
(
SELECT ROWNUM AS ITEMCODE2 FROM TEST.ORDERLINEITEM WHERE ROWNUM < 100000
MINUS
SELECT ITEMCODE FROM AWI.ITEMMASTER

),

MINUS just is not supposed to be a T-SQL operator. You didn't use EXCEPT keyword rather than MINUS, did you?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-05-19 : 14:20:30

MINUS (Oracle) = EXCEPT (SQL Server, ANSI/ISO, any sane RDBMS)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-19 : 22:41:35

quote:
Originally posted by robvolk

MINUS (Oracle) = EXCEPT (SQL Server, ANSI/ISO, any sane RDBMS)

Ah! That explains it, thanks Rob!

Except for that one change, it looks like the query I wrote worked on Oracle. I am going to add "Exceptional Oracle Programming Skills" as a line item on my resume.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources