| Author |
 Topic  |
|
|
nord
Yak Posting Veteran
66 Posts |
 Posted - 02/13/2013 : 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
23 Posts |
Posted - 02/19/2013 : 08:37:24
|
| What are you trying to do? |
 |
|
|
Alan Schofield
Starting Member
United Kingdom
23 Posts |
Posted - 02/26/2013 : 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
India
47173 Posts |
Posted - 02/26/2013 : 22:50:48
|
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/
|
|
|
| |
Topic |
|
|
|
parametr with left join
