parametr with left join - 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

SQL Server 2008 Forums

Analysis Server and Reporting Services (2008)

parametr with left join

Author

Topic

nord
Posting Yak Master

126 Posts

Posted - 2013-02-13 : 09:44:17

Hi,
I have sp:
USE [AS400]
GO
/****** Object: StoredProcedure [dbo].[Reports_ReshipRS_supplier] Script Date: 02/12/2013 12:21:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Chepeleutser , Evgeny
-- Create date: 12 fev. 2013

-- Description: Détails sur les Reship pour RS
-- =============================================
ALTER PROCEDURE [dbo].[Reports_ReshipRS_supplier]

@startDate DATETIME
,@EndDate DATETIME
,@Supplier nvarchar(50)
,@Network nvarchar(10)

AS
BEGIN
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET NOCOUNT ON;

SELECT DISTINCT
pgC.PGNFOU AS NoFournisseur
, foC.FODFOU AS NomFournisseur
, pgC.PGDTIT AS Title
, pbe.PENEDI AS ISSUE
, pbe.PECB AS UPC
, SUBSTRING(pbe.PECB,7,5) AS Bipad
, pbe.PEQFAC AS INVOICED
, pbe.pedven AS ONSALE
, [peqfac]*0.15 AS ReshipCharge
FROM
dbo.A250PPBG pgC
INNER JOIN dbo.A250PPBE pbe ON pgC.PGCODI = pbe.PECODI
INNER JOIN dbo.A250PFOU foC ON pgC.PGNFOU = foC.FONFOU
LEFT JOIN
(
select STNEDI, STCODI ,STNCLI
from A250PSTA
group by
STNEDI
,STCODI
,STNCLI
)A ON PBE.PENEDI=A.STNEDI AND PGC.PGCODI = A.STCODI
LEFT JOIN A250PCUS CUS ON a.STNCLI=CUS.CLNCLI
LEFT JOIN A250PRES RES ON CUS.CLCRES=RES.RSCRES
WHERE
--(pgC.PGNFOU = '08235'
--Or pgC.PGNFOU = '08307'
--Or pgC.PGNFOU = '08342'
--or pgC.PGNFOU = '08337')
--and
pbe.pedven BETWEEN dbo.fnct_ConvDateToNum(@startDate) AND dbo.fnct_ConvDateToNum(@EndDate)

END

because i need use left join this parametr not working in the report
and pgC.PGNFOU=ISNULL(@Supplier,pgC.PGNFOU)
and RES.RSDCA=ISNULL(@Network,RES.RSDCA)
Have any idea how i can do it?
Thanks

Mar
Starting Member

47 Posts

Posted - 2013-02-19 : 08:37:24

What are you trying to do?

Alan Schofield
Starting Member

23 Posts

Posted - 2013-02-26 : 22:18:49

I 'think' I understand what you problem is....

Are you saying that the RES.RSDCA=ISNULL(@Network,RES.RSDCA) does not work? If so then that is because, as you pointed out, you are LEFT joining to RES so where there are no matching records, RES.RSDCA will be NULL.
From your current code what you are saying is..
"If the @Network parameter = NULL then compare RES.RSDCA with itself, ELSE compare it to @Network which doesn't seem to make sense although I'm not sure exactly what you are trying to achieve.

If you want to compare RES.RSDCA with @Network unless RES.RSDCA is null then you should do this
and ISNULL(RES.RSDCA,@Network) = @Network
this would always include records where RES.RSDCA is NULL and also include records where RES.RSDCA = @Network.

This probably isn't what you want but it might point you in the right direction, if not please try to explain exactly what you want to achieve in your results.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-26 : 22:50:48

[code]
ALTER PROCEDURE [dbo].[Reports_ReshipRS_supplier]

@startDate DATETIME
,@EndDate DATETIME
,@Supplier nvarchar(50)
,@Network nvarchar(10)

AS
BEGIN
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET NOCOUNT ON;

SELECT DISTINCT
pgC.PGNFOU AS NoFournisseur
, foC.FODFOU AS NomFournisseur
, pgC.PGDTIT AS Title
, pbe.PENEDI AS ISSUE
, pbe.PECB AS UPC
, SUBSTRING(pbe.PECB,7,5) AS Bipad
, pbe.PEQFAC AS INVOICED
, pbe.pedven AS ONSALE
, [peqfac]*0.15 AS ReshipCharge
FROM
dbo.A250PPBG pgC
INNER JOIN dbo.A250PPBE pbe ON pgC.PGCODI = pbe.PECODI
INNER JOIN dbo.A250PFOU foC ON pgC.PGNFOU = foC.FONFOU
LEFT JOIN
(
select STNEDI, STCODI ,STNCLI
from A250PSTA
group by
STNEDI
,STCODI
,STNCLI
)A ON PBE.PENEDI=A.STNEDI AND PGC.PGCODI = A.STCODI
LEFT JOIN A250PCUS CUS ON a.STNCLI=CUS.CLNCLI
LEFT JOIN A250PRES RES ON CUS.CLCRES=RES.RSCRES and RES.RSDCA=ISNULL(@Network,RES.RSDCA)
WHERE
--(pgC.PGNFOU = '08235'
--Or pgC.PGNFOU = '08307'
--Or pgC.PGNFOU = '08342'
--or pgC.PGNFOU = '08337')
--and
pbe.pedven BETWEEN dbo.fnct_ConvDateToNum(@startDate) AND dbo.fnct_ConvDateToNum(@EndDate)

END
[/code]

If you're using LEFT JOIN I think what you may be looking at would be the above

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources