Results 1 to 3 of 3

Thread: SQL query

  1. #1
    Join Date
    Jul 2006
    Posts
    2

    Unanswered: SQL query

    Any one ever select multiple rows from a table and create a single record output?

    Example:
    Acct Date Action
    0001 7/31/06 Sign-IN
    0001 7/31/06 Work
    0001 7/31/06 Lunch
    0001 7/31/06 Nap
    0001 7/31/06 Sign-Out

    Result:
    0001 7/31/06 Sign-In Work Work Lunch Nap Sign-Out

  2. #2
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    a simple procedure using cursor will be like this:

    CREATE PROCEDURE abc.action
    (IN date1 date,
    IN account varchar(10),
    OUT action varchar(100)
    )

    P1: BEGIN

    DECLARE str varchar(100) default ' ';
    DECLARE string varchar(20);
    DECLARE count int;
    DECLARE counter int default 0;
    DECLARE C1 CURSOR FOR SELECT action FROM abc.work WHERE acct=account and todaydate=date1;
    set count= (select count(1) from abc.work WHERE acct=account and todaydate=date1);

    OPEN C1;

    while (counter< count)
    do
    FETCH C1 INTO string;
    SET str= str||' '||string;
    set counter=counter+1;
    end while;
    close c1;

    set action=str;
    END P1;

    call abc.action('2006-05-05','0001',?);
    completed successfully.

    Value of output parameters
    --------------------------
    Parameter Name : ACTION
    Parameter Value : abc def ghi jkl mno

    Return Status = 0

  3. #3
    Join Date
    Jul 2006
    Posts
    2
    Rahul S80,
    While your suggestion would work within a program, I forgot to say that I am creating a Query in SPUFI. I needed a Query that could stand alone. Below is what we actually came up with. You do need to be under DB2 version 8 (I think, maybe 7 or 8).

    SELECT Q3.ACCT_ID
    , Q3.Date
    , MAX(Q3.Action1)
    , MAX(Q3.Action2)
    , MAX(Q3.Action3)
    , MAX(Q3.Action4)
    , MAX(Q3.Action5)
    , MAX(Q3.Action6)
    , MAX(Q3.Action7)

    FROM (SELECT D.ACCT_ID
    , D.OFR_ID
    , D.OFR_ASOCN_TS

    , CASE WHEN D.Action = 'Sign-In '
    THEN D.Action
    END Action1

    , CASE WHEN D.Action = 'Work '
    THEN D.Action
    END Action2

    , CASE WHEN D.Action = 'Lunch '
    THEN D.Action
    END Action3

    , CASE WHEN D.Action = 'Nap '
    THEN D.Action
    END Action4

    , CASE WHEN D.Action = 'Sign-Out'
    THEN D.Action
    END Action5

    , CASE WHEN D.Action = 'Sick-Day'
    THEN D.Action
    END Action6

    , CASE WHEN D.Action = 'Other '
    THEN D.Action
    END Action7

    FROM Action_Table D
    WHERE 1=1 ) Q3

    GROUP BY Q3.ACCT_ID
    , Q3.OFR_ID
    , Q3.OFR_ASOCN_TS
    ;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •