OraFAQ Forum: Server Administration » Try this "Query problem"

Home » RDBMS Server » Server Administration » Try this "Query problem"

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Try this "Query problem" [message #374321]

Tue, 05 June 2001 12:13

bosou
Messages: 3
Registered: June 2001

Junior Member

I am a newbie sql script writer. Have this problem.

I have a table with the following redundant records:
-------------------------------------------------------
ID Posn Eff_Date Job_Type
---------------------------------------------------
123456 posn001 01-JAN-2001 Primary
123456 posn002 02-MAR-2001 Secondary
123456 posn003 24-MAR-2001 Secondary
123456 posn007 03-MAY-2001 Secondary
231234 posn007 01-APR-2001 Primary
909090 posn004 19-JAN-2001 Primary
909090 posn007 22-APR-2001 Secondary
-------------------------------------------------------
Question: How do I write an SQL script so that it pulls data to make a one line record per employee? Example rpt format:

ID Posn1 Posn2 Posn3 Posn4
-------------------------------------------------------
123456 posn001 posn002 posn003 posn007
231234 posn007 NULL NULL NULL
909090 posn004 posn007 NULL NULL
-------------------------------------------------------

You can email me the script.

Thanks.

bosou

Report message to a moderator

Re: Try this "Query problem" [message #374322 is a reply to message #374321]

Tue, 05 June 2001 12:53

Jon
Messages: 483
Registered: May 2001

Senior Member

I would solve this problem by writing a PL/SQL stored procedure. Assume your TABLE_NAME is jontest:

CREATE OR REPLACE PROCEDURE sp_jonTest IS
--
l_id varchar2(6);
--
TYPE PositionRecord IS RECORD (posn varchar2(7));
TYPE PositionTable IS TABLE of PositionRecord
INDEXED BY BINARY_INTEGER;

--
l_positionTable PositionTable;
--
CURSOR MyID IS
SELECT distinct id
FROM jontest;
CURSOR MyPosn IS
SELECT posn
FROM jontest
WHERE id = l_id;
--
BEGIN
--
FOR curIDrec in MyID
LOOP;
l_id := curIDrec.id;
DBMS_OUTPUT.PUT(l_id||' ');
FOR curPosnRec in MyPosn
LOOP
DBMS_OUTPUT.PUT(curPosnRec.posn||' '||);
END LOOP;
DBMS_OUTPUT.PUT_LINE(' ');
END LOOP;
END
/
Be sure you remember to SET SERVEROUTPUT ON before you EXEC sp_jontest in SQL*PLUS.

Report message to a moderator

Re: Try this "Query problem"..PS [message #374323 is a reply to message #374321]

Tue, 05 June 2001 12:55

Jon
Messages: 483
Registered: May 2001

Senior Member

Ignore the following lines. I started down one path and ended up at another...

TYPE PositionRecord IS RECORD (posn varchar2(7));
TYPE PositionTable IS TABLE of PositionRecord
INDEXED BY BINARY_INTEGER;

--
l_positionTable PositionTable;
--

Report message to a moderator

Re: Try this "Query problem"..PS [message #374324 is a reply to message #374321]

Tue, 05 June 2001 12:55

Jon
Messages: 483
Registered: May 2001

Senior Member

Report message to a moderator

Previous Topic:	Try this "Query problem"
Next Topic:	Oracle "insertion problem"

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jul 04 21:46:43 CDT 2024