Query Help - 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

General SQL Server Forums

New to SQL Server Programming

Query Help

Author

Topic

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2012-05-28 : 03:29:03

Hi ,

here is the details

CREATE TABLE SAMPLE
(
SOURCE_FILE_ID INT,
CYCLE_ID INT,
TASK_ID INT ,
TASK_STATE_DESC VARCHAR(100)
)

INSERT INTO SAMPLE
SELECT 22,1,1,'Valid' UNION ALL
SELECT 22,1,2,'Error' UNION ALL
SELECT 22,1,3,'Error' UNION ALL
SELECT 22,1,4,'Setup Fail' UNION ALL
SELECT 22,1,5,'Setup Fail' UNION ALL
SELECT 22,1,6,'Error' UNION ALL
SELECT 22,1,7,'Setup Fail' UNION ALL
SELECT 22,1,8,'Error' UNION ALL
SELECT 22,1,9,'Error' UNION ALL
SELECT 22,1,10,'Setup Fail' UNION ALL
SELECT 22,2,1,'Valid' UNION ALL
SELECT 22,2,2,null UNION ALL
SELECT 22,2,3,'Error' UNION ALL
SELECT 22,2,4,null UNION ALL
SELECT 22,2,5,'Setup Fail' UNION ALL
SELECT 22,2,6,'Error' UNION ALL
SELECT 22,2,7,'Setup Fail' UNION ALL
SELECT 22,2,8,'Error' UNION ALL
SELECT 22,2,9,'Error' UNION ALL
SELECT 22,2,10,'Setup Fail' UNION ALL
SELECT 22,3,1,'Valid' UNION ALL
SELECT 22,3,2,null UNION ALL
SELECT 22,3,3,'Error' UNION ALL
SELECT 22,3,4,null UNION ALL
SELECT 22,3,5,'Setup Fail' UNION ALL
SELECT 22,3,6,'Error' UNION ALL
SELECT 22,3,7,'Setup Fail' UNION ALL
SELECT 22,3,8,'Error' UNION ALL
SELECT 22,3,9,'Error' UNION ALL
SELECT 22,3,10,'Setup Fail' UNION ALL

select * from SAMPLE

I need to write a qurey to select
all the records having TASK_STATE_DESC='Error'
and Next subsequent tasks if it is satisfied the condition TASK_STATE_DESC='Setup fail'

here Every cycle will have 10 tasks (i.e., 1,2 ... 10)

-- Expected output.
--- from cycle1
22 1 2 Error
22 1 3 Error
22 1 4 Setup Fail
22 1 5 Setup Fail
22 1 6 Error
22 1 7 Setup Fail
22 1 8 Error
22 1 9 Error
22 1 10 Setup Fail

-- from cycle2
13 22 2 3 Error
16 22 2 6 Error
17 22 2 7 Setup Fail
18 22 2 8 Error
19 22 2 9 Error
20 22 2 10 Setup Fail

in the above list the task_id 5 have to miss in select list becasue the previous task not having Error as TASK_STATE_DESC
and task_id 4 also should miss because it is having Null as TASK_STATE_DESC

-- from cycle 3
22 3 3 Error
22 3 6 Error
22 3 7 Setup Fail
22 3 8 Error
22 3 9 Error
22 3 10 Setup Fail

-- the above select

in the above list the task_id 5 have to miss in select list becasue the previous task not having Error as TASK_STATE_DESC
and task_id 2,4 also should miss because it is having Null as TASK_STATE_DESC

Please help me
Thanks,

--Irk

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2012-05-28 : 04:39:17

Try something like this:

SELECT S.SOURCE_FILE_ID, S.CYCLE_ID, S.TASK_ID, S.TASK_STATE_DESC
  FROM SAMPLE AS S
 WHERE S.TASK_STATE_DESC = 'Error'
 UNION ALL
SELECT S.SOURCE_FILE_ID, S.CYCLE_ID, S.TASK_ID, S.TASK_STATE_DESC
  FROM SAMPLE AS S
 WHERE S.TASK_STATE_DESC = 'Setup Fail'
   AND EXISTS(SELECT *
                FROM SAMPLE AS S2
               WHERE S2.SOURCE_FILE_ID = S.SOURCE_FILE_ID
                 AND S2.CYCLE_ID = S.CYCLE_ID
                 AND S2.TASK_ID = S.TASK_ID - 1
                 AND S2.TASK_STATE_DESC IN ('Error', 'Setup Fail'))

For us, there is only the trying. The rest is not our business. ~T.S. Eliot

Muhammad Al Pasha

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2012-05-28 : 05:31:57

quote:

AND S2.TASK_ID = S.TASK_ID - 1
AND S2.TASK_STATE_DESC IN ('Error', 'Setup Fail'))[/code]

In some cases your query will fail:

-- sample data for cycle 4
SOURCE_FILE_ID CYCLE_ID TASK_ID TASK_STATE_DESC
22 4 1 Valid
22 4 2 Setup Fail
22 4 3 Error
22 4 4 Setup Fail
22 4 5 Setup Fail
22 4 6 NULL
22 4 7 Setup Fail
22 4 8 Setup Fail
22 4 9 Error
22 4 10 Setup Fail

-- My Expected Output
22 4 3 Error
22 4 4 Setup Fail
22 4 5 Setup Fail
22 4 9 Error
22 4 10 Setup Fail

-- But your query will select
22 4 3 Error
22 4 4 Setup Fail
22 4 5 Setup Fail
22 4 8 Setup Fail ( In Valid Record)
22 4 9 Error
22 4 10 Setup Fail

Even though the task_id 6 does not contains TASK_STATE_DESC='Error '

I need to select the records [TASK_STATE_DESC] containing a string ='Error ' and
the subsequent Failed tasks with STG_xxx_TASK_STATE_DATA [TASK_STATE_DESC] containing a string 'Setup fail'

--Irk

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2012-05-28 : 05:46:57

Then try something like this:


WITH CTE AS
(
   SELECT S.SOURCE_FILE_ID, S.CYCLE_ID, S.TASK_ID, S.TASK_STATE_DESC
     FROM SAMPLE AS S
    WHERE S.TASK_STATE_DESC = 'Error'

    UNION ALL

   SELECT Curr.SOURCE_FILE_ID, Curr.CYCLE_ID, Curr.TASK_ID, Curr.TASK_STATE_DESC
     FROM CTE AS Prev
          INNER JOIN
          SAMPLE AS Curr
          ON Curr.SOURCE_FILE_ID = Prev.SOURCE_FILE_ID
             AND Curr.CYCLE_ID = Prev.CYCLE_ID
             AND Curr.TASK_ID = Prev.TASK_ID + 1
    WHERE Curr.TASK_STATE_DESC = 'Setup Fail'
)
SELECT SOURCE_FILE_ID, CYCLE_ID, TASK_ID, TASK_STATE_DESC
  FROM CTE
OPTION(MAXRECURSION 0);

For us, there is only the trying. The rest is not our business. ~T.S. Eliot

Muhammad Al Pasha

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2012-05-28 : 06:50:04

Thanks working fine ,

Now I need to Convert above code into NETEZZA Sql ..

--Irk

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-28 : 14:53:50

quote:
Originally posted by Ranjit.ileni

Thanks working fine ,

Now I need to Convert above code into NETEZZA Sql ..

--Irk

you can try in some NETEZZA forums for that

http://www.topix.com/forum/com/netezza

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources