Inserting few Recordsets into Temp Table containing NULL Values

[Sascha]

Hi
I need some help on achieving the following:

I wrote a querie which collects Data out of three Tables, the Result
looks like this:

SET NOCOUNT ON

DECLARE @ROW INT

DECLARE @CURPTNO CURSOR

SET @CURPTNO = CURSOR
FOR
SELECT * FROM TEMP

OPEN @CURPTNO

FETCH NEXT FROM @CURPTNO
INTO @ROW

WHILE (@@FETCH_STATUS = 0)

BEGIN

SELECT ONE.CYNO, ONE.ROLE, ONE.ROL_BEZ, ONE.PTNO, ONE.NOCY, TWO.TEXT
AS NOCY_TEXT
FROM
(SELECT CY.CYNO,
RE.CYNO AS RECYNO, RTRIM(RE.ROLE) AS ROLE, RTRIM(V_ROLE.TE XT) AS
ROL_BEZ,
RE.PTNO AS PTNO, RTRIM(RE.NAME) AS SACHBEARBEITER, RE.NOCY
FROM CY
RIGHT OUTER JOIN
RE ON RE.CYNO = CY.CYNO
INNER JOIN
V_ROLE ON RE.ROLE = V_ROLE.CODE
WHERE (RE.PTNO IN
(SELECT PT.PTNO
FROM PT
WHERE PT.PTNO IN
(@ROW)
AND V_ROLE.LANGUAGE = PT.CLANG
))) AS ONE

LEFT JOIN

(SELECT V_NOCY.CODE, V_NOCY.TEXT
FROM V_NOCY
INNER JOIN
PT ON PT.CLANG = V_NOCY.LANGUAGE
AND PT.PTNO IN (SELECT PT.PTNO
FROM PT
WHERE PT.PTNO IN
(@ROW)
)) AS TWO
ON ONE.NOCY = TWO.CODE

FETCH NEXT FROM @CURPTNO
INTO @ROW

END

CLOSE @CURPTNO

DEALLOCATE @CURPTNO

The Result looks like this:
RS1:
6313,1300,Archi tekt,99737505,N ULL,NULL
2392762,100,Bau herr,99737505,N ULL,NULL

RS2:
2693265,100,Bau herr,99756900,N ULL,NULL
NULL,1,Planer,9 9756900,2,Bauhe rr macht Pläne selber

RS3:
2691919,100,Bau herr,99755058,N ULL,NULL
2691962,6000,Ko ntakt,99755058, NULL,NULL

My Problem is, that I need to have all the Resultsets in one Table at
the end.
So my further undertaking was to create a Temp Table with the
expectation to receive all the resultsets in one Step.

The TSQL for this looks like that:

SET NOCOUNT ON

CREATE TABLE #CYRE_TEMP
( CYNO INT NULL,

ROLE INT NULL,
ROL_BEZ VARCHAR (60) NULL,
PTNO INT NULL ,
NOCY INT NULL,
TEXT VARCHAR (60) NULL
)

GO

DECLARE @CYNO INT,

@ROLE INT,
@ROL_BEZ VARCHAR (60),
@PTNO INT,
@NOCY INT,
@TEXT VARCHAR (60),
@ROW INT,
@CURPTNO CURSOR

SET @CURPTNO = CURSOR FOR
SELECT PTNO FROM TEMP

OPEN @CURPTNO

FETCH NEXT FROM @CURPTNO
INTO @ROW

WHILE (@@FETCH_STATUS = 0)

BEGIN

INSERT INTO #CYRE_TEMP (CYNO, ROLE, ROL_BEZ, PTNO, NOCY, TEXT)

VALUES

(@CYNO,@ROLE ,@ROL_BEZ ,@PTNO ,@NOCY ,@TEXT)

SELECT @CYNO = ONE.CYNO,

@ROLE = ONE.ROLE,
@ROL_BEZ = ONE.ROL_BEZ,
@PTNO = ONE.PTNO,
@NOCY = ONE.NOCY,
@TEXT = TWO.TEXT
FROM
(SELECT CY.CYNO, RTRIM(RE.ROLE) AS ROLE, RTRIM(V_ROLE.TE XT) AS
ROL_BEZ,
RE.PTNO AS PTNO, RTRIM(RE.NAME) AS SACHBEARBEITER, RE.NOCY
FROM CY
RIGHT OUTER JOIN
RE ON RE.CYNO = CY.CYNO
INNER JOIN
V_ROLE ON RE.ROLE = V_ROLE.CODE
WHERE (RE.PTNO IN
(SELECT PT.PTNO
FROM PT
WHERE PT.PTNO =
@ROW
AND V_ROLE.LANGUAGE = PT.CLANG
))) AS ONE

LEFT JOIN

(SELECT V_NOCY.CODE, V_NOCY.TEXT
FROM V_NOCY
INNER JOIN
PT ON PT.CLANG = V_NOCY.LANGUAGE
AND PT.PTNO IN (SELECT PT.PTNO
FROM PT
WHERE PT.PTNO =
@ROW
)) AS TWO
ON ONE.NOCY = TWO.CODE

FETCH NEXT FROM @CURPTNO
INTO @ROW
END

CLOSE @CURPTNO
DEALLOCATE @CURPTNO

SELECT * FROM #CYRE_TEMP

DROP TABLE #CYRE_TEMP

GO

And the Output looks like this now:

Q1:
NULL,NULL,NULL, NULL,NULL,NULL
2392762,100,Bau herr,99737505,N ULL,NULL
NULL,1,Planer,9 9756900,2,Bauhe rr macht Pläne selber
Can someone help me getting all the 6 Rows into one Table as Output?

I appreciate any available Help on this..

Ssscha

[John Bell]

Hi

Posting DDL (CREATE TABLE statements etc.) Example Data (As Insert
Statements) helps when trying to answer questions like this. Without
testing(!) this may go some way to solving the problem.

SELECT CY.CYNO, RTRIM(RE.ROLE) AS ROLE, ONE.ROL_BEZ, RE.PTNO, RE.NOCY,
V_NOCY.TEXT AS NOCY_TEXT
FROM RE
INNER JOIN V_ROLE ON RE.ROLE = V_ROLE.CODE
INNER JOIN PT ON RE.PTNO = PT.PTNO AND V_ROLE.LANGUAGE = PT.CLANG
INNER JOIN #TEMP T ON PT.PTNO = T.PTNO
INNER JOIN V_NOCY ON V_NOCY.CODE = RE.NOCY
INNER JOIN PT ON PT.CLANG = V_NOCY.LANGUAGE
LEFT OUTER JOIN CY ON RE.CYNO = CY.CYNO

John

"Sascha" <sa************ ****@fachmediac om.ch> wrote in message
news:3d******** *************** ***@posting.goo gle.com...

Hi
I need some help on achieving the following:

I wrote a querie which collects Data out of three Tables, the Result
looks like this:

SET NOCOUNT ON

DECLARE @ROW INT

DECLARE @CURPTNO CURSOR

SET @CURPTNO = CURSOR
FOR
SELECT * FROM TEMP

OPEN @CURPTNO

FETCH NEXT FROM @CURPTNO
INTO @ROW

WHILE (@@FETCH_STATUS = 0)

BEGIN

SELECT ONE.CYNO, ONE.ROLE, ONE.ROL_BEZ, ONE.PTNO, ONE.NOCY, TWO.TEXT
AS NOCY_TEXT
FROM
(SELECT CY.CYNO,
RE.CYNO AS RECYNO, RTRIM(RE.ROLE) AS ROLE, RTRIM(V_ROLE.TE XT) AS
ROL_BEZ,
RE.PTNO AS PTNO, RTRIM(RE.NAME) AS SACHBEARBEITER, RE.NOCY
FROM CY
RIGHT OUTER JOIN
RE ON RE.CYNO = CY.CYNO
INNER JOIN
V_ROLE ON RE.ROLE = V_ROLE.CODE
WHERE (RE.PTNO IN
(SELECT PT.PTNO
FROM PT
WHERE PT.PTNO IN
(@ROW)
AND V_ROLE.LANGUAGE = PT.CLANG
))) AS ONE

LEFT JOIN

(SELECT V_NOCY.CODE, V_NOCY.TEXT
FROM V_NOCY
INNER JOIN
PT ON PT.CLANG = V_NOCY.LANGUAGE
AND PT.PTNO IN (SELECT PT.PTNO
FROM PT
WHERE PT.PTNO IN
(@ROW)
)) AS TWO
ON ONE.NOCY = TWO.CODE

FETCH NEXT FROM @CURPTNO
INTO @ROW

END

CLOSE @CURPTNO

DEALLOCATE @CURPTNO

The Result looks like this:
RS1:
6313,1300,Archi tekt,99737505,N ULL,NULL
2392762,100,Bau herr,99737505,N ULL,NULL

RS2:
2693265,100,Bau herr,99756900,N ULL,NULL
NULL,1,Planer,9 9756900,2,Bauhe rr macht Pläne selber

RS3:
2691919,100,Bau herr,99755058,N ULL,NULL
2691962,6000,Ko ntakt,99755058, NULL,NULL

My Problem is, that I need to have all the Resultsets in one Table at
the end.
So my further undertaking was to create a Temp Table with the
expectation to receive all the resultsets in one Step.

The TSQL for this looks like that:

SET NOCOUNT ON

CREATE TABLE #CYRE_TEMP
( CYNO INT NULL,

ROLE INT NULL,
ROL_BEZ VARCHAR (60) NULL,
PTNO INT NULL ,
NOCY INT NULL,
TEXT VARCHAR (60) NULL
)

GO

DECLARE @CYNO INT,

@ROLE INT,
@ROL_BEZ VARCHAR (60),
@PTNO INT,
@NOCY INT,
@TEXT VARCHAR (60),
@ROW INT,
@CURPTNO CURSOR

SET @CURPTNO = CURSOR FOR
SELECT PTNO FROM TEMP

OPEN @CURPTNO

FETCH NEXT FROM @CURPTNO
INTO @ROW

WHILE (@@FETCH_STATUS = 0)

BEGIN

INSERT INTO #CYRE_TEMP (CYNO, ROLE, ROL_BEZ, PTNO, NOCY, TEXT)

VALUES

(@CYNO,@ROLE ,@ROL_BEZ ,@PTNO ,@NOCY ,@TEXT)

SELECT @CYNO = ONE.CYNO,

@ROLE = ONE.ROLE,
@ROL_BEZ = ONE.ROL_BEZ,
@PTNO = ONE.PTNO,
@NOCY = ONE.NOCY,
@TEXT = TWO.TEXT
FROM
(SELECT CY.CYNO, RTRIM(RE.ROLE) AS ROLE, RTRIM(V_ROLE.TE XT) AS
ROL_BEZ,
RE.PTNO AS PTNO, RTRIM(RE.NAME) AS SACHBEARBEITER, RE.NOCY
FROM CY
RIGHT OUTER JOIN
RE ON RE.CYNO = CY.CYNO
INNER JOIN
V_ROLE ON RE.ROLE = V_ROLE.CODE
WHERE (RE.PTNO IN
(SELECT PT.PTNO
FROM PT
WHERE PT.PTNO =
@ROW
AND V_ROLE.LANGUAGE = PT.CLANG
))) AS ONE

LEFT JOIN

(SELECT V_NOCY.CODE, V_NOCY.TEXT
FROM V_NOCY
INNER JOIN
PT ON PT.CLANG = V_NOCY.LANGUAGE
AND PT.PTNO IN (SELECT PT.PTNO
FROM PT
WHERE PT.PTNO =
@ROW
)) AS TWO
ON ONE.NOCY = TWO.CODE

FETCH NEXT FROM @CURPTNO
INTO @ROW
END

CLOSE @CURPTNO
DEALLOCATE @CURPTNO

SELECT * FROM #CYRE_TEMP

DROP TABLE #CYRE_TEMP

GO

And the Output looks like this now:

Q1:
NULL,NULL,NULL, NULL,NULL,NULL
2392762,100,Bau herr,99737505,N ULL,NULL
NULL,1,Planer,9 9756900,2,Bauhe rr macht Pläne selber
Can someone help me getting all the 6 Rows into one Table as Output?

I appreciate any available Help on this..

Ssscha