SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Analysis Server and Reporting Services (2008)
 parametr with left join
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nord
Posting Yak Master

126 Posts

Posted - 02/13/2013 :  09:44:17  Show Profile  Reply with Quote
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

45 Posts

Posted - 02/19/2013 :  08:37:24  Show Profile  Reply with Quote
What are you trying to do?
Go to Top of Page

Alan Schofield
Starting Member

United Kingdom
23 Posts

Posted - 02/26/2013 :  22:18:49  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/26/2013 :  22:50:48  Show Profile  Reply with Quote

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


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/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000