Adding Nested Join with Temp Table Result 1

[bustercoder]

Hello, this stored proc runs fine, but I'm not sure how to join it with another query/join I need in the result:

Here is what I have now:

ALTER PROCEDURE [dbo].[udsp_DR_Customer_Payment_History]
(
@customer varchar(32),
@checkNumber varchar(32)
)
AS
BEGIN

SELECT [Customer], [CheckNumber]
FROM (
SELECT [Customer] = RMTrxTemp.CUSTNMBR,
[CheckNumber] = RTRIM((CASE WHEN RMTrxTemp.CHEKNMBR = '' THEN 'None' ELSE RMTrxTemp.CHEKNMBR END))
FROM (
SELECT RM20101.CUSTNMBR,
RM20101.CHEKNMBR,
RM20101.RMDTYPAL,
RM20101.DOCDATE
FROM RM20101
WHERE RM20101.RMDTYPAL = 9 AND
RM20101.CUSTNMBR = @customer AND
@checkNumber IS NULL OR RM20101.CHEKNMBR = @checkNumber
UNION
SELECT RM30101.CUSTNMBR,
RM30101.CHEKNMBR,
RM30101.RMDTYPAL,
RM30101.DOCDATE
FROM RM30101
WHERE RM30101.RMDTYPAL = 9 AND
RM30101.CUSTNMBR = @customer AND
@checkNumber IS NULL OR RM30101.CHEKNMBR = @checkNumber
)
RMTrxTemp INNER JOIN
RM00101 ON RMTrxTemp.CUSTNMBR = RM00101.CUSTNMBR
GROUP BY
RMTrxTemp.CUSTNMBR,
RMTrxTemp.CHEKNMBR ,
RMTrxTemp.DOCDATE,
RMTrxTemp.RMDTYPAL

) some_logical_table_alias
GROUP BY
[Customer],
[CheckNumber]
END
GO

Here is what I'm trying add/join to get the RM20201.APPTOAMT in the resultset:

SELECT RM20201.CUSTNMBR, RM20201.APPTOAMT
FROM RM20201
WHERE RM20201.CUSTNMBR = @customer
UNION
SELECT RM30201.CUSTNMBR,
RM30201.APPTOAMT
FROM RM30201
WHERE RM30201.CUSTNMBR = @customer

If someone could show me the best approach I'd really appreciate it.

Thanks,
Buster

 

[GeertVerhoeven]

Hi,

It is not possible to join the result of a stored procedure with a table.

You can do 2 things:
- Create a function in case of a stored procedure. A function can be used as a normal table.
- Include the extra join in the stored procedure

Greetz,

Geert


Geert Verhoeven
Consultant @ Ausy Belgium

My Personal Blog

 

[Crowley16]

there is another option, you could dump the results of the stored procedure into a temp table, and then join on the temp table, however I'm suggesting this only to be complete and not suggesting you actually adopt this approach...

--------------------
Procrastinate Now!

 

[bustercoder]

Thanks. Actually, could someone just show me how to include this extra join in the stored procedure, and perhaps join it with the other join? In other words, just join these two joins on the customer number:

SELECT RM20101.CUSTNMBR,
RM20101.CHEKNMBR,
RM20101.RMDTYPAL,
RM20101.DOCDATE
FROM RM20101
WHERE RM20101.RMDTYPAL = 9 AND
RM20101.CUSTNMBR = @customer AND
@checkNumber IS NULL OR RM20101.CHEKNMBR = @checkNumber
UNION
SELECT RM30101.CUSTNMBR,
RM30101.CHEKNMBR,
RM30101.RMDTYPAL,
RM30101.DOCDATE
FROM RM30101
WHERE RM30101.RMDTYPAL = 9 AND
RM30101.CUSTNMBR = @customer AND
@checkNumber IS NULL OR RM30101.CHEKNMBR = @checkNumber

AND...

SELECT RM20201.CUSTNMBR, RM20201.APPTOAMT
FROM RM20201
WHERE RM20201.CUSTNMBR = @customer
UNION
SELECT RM30201.CUSTNMBR,
RM30201.APPTOAMT
FROM RM30201
WHERE RM30201.CUSTNMBR = @customer

Thanks,
Buster

 

[GeertVerhoeven]

It should be something like this: (not tested since no SQL at the moment)

SELECT
T1.CUSTNMBR,
T1.CHEKNMBR,
T1.RMDTYPAL,
T1.DOCDATE,
T2.APPTOAMT
FROM
(
SELECT
RM20101.CUSTNMBR,
RM20101.CHEKNMBR,
RM20101.RMDTYPAL,
RM20101.DOCDATE
FROM RM20101
WHERE RM20101.RMDTYPAL = 9 AND
RM20101.CUSTNMBR = @customer AND
@checkNumber IS NULL OR RM20101.CHEKNMBR = @checkNumber

UNION

SELECT
RM30101.CUSTNMBR,
RM30101.CHEKNMBR,
RM30101.RMDTYPAL,
RM30101.DOCDATE
FROM RM30101
WHERE RM30101.RMDTYPAL = 9 AND
RM30101.CUSTNMBR = @customer AND
@checkNumber IS NULL OR RM30101.CHEKNMBR = @checkNumber
) T1
INNER JOIN
(
SELECT
RM20201.CUSTNMBR,
RM20201.APPTOAMT
FROM RM20201
WHERE RM20201.CUSTNMBR = @customer

UNION

SELECT
RM30201.CUSTNMBR,
RM30201.APPTOAMT
FROM RM30201
WHERE RM30201.CUSTNMBR = @customer
) T2
ON T1.CUSTNMBR = T2.CUSTNMBR

Let me know if it works.



Geert Verhoeven
Consultant @ Ausy Belgium

My Personal Blog

 

[bustercoder]

Geert,
It works, and it's a lot cleaner than what I was trying to do with temp tables and so forth. Thank you so much for your help!
Buster