May 2, 2008 at 8:09 am
I am trying to replace this:
--------------------------
SELECT * FROM USFPIMS.dbo.PRODEXTEND
WHERE
DIN NOT IN ('02177579','02177595','02177722','02177706','02177587','02177692','02177714','02185830','02185857','02185873','02185849','02185865')
AND DIN + RTRIM(convert(varchar(10),PKGSIZE)) IN
(SELECT DIN + RTRIM(convert(varchar(10),PKGSIZE)) FROM PRODUCT WHERE CLIENT IN (SELECT CODE FROM CLIENT WHERE ACTIVE = 'Y'))
--AND DIN = '02245057'
AND DIN + RTRIM(convert(varchar(10),PKGSIZE)) NOT IN (SELECT DIN + RTRIM(convert(varchar(10),PKGSIZE)) FROM USFRET.dbo.PRODEXTEND)
-----------------------------
by this:
-----------------------------
SELECT mpe.din, mpe.pkgsize, mp.client, mc.active
FROM
USFPIMS..prodextend mpe
INNER JOIN USFPIMS..product mp ON mpe.din = mp.din AND mpe.pkgsize = mp.pkgsize
INNER JOIN USFPIMS..client mc ON mp.client = mc.code
LEFT OUTER JOIN USFRET..prodextend rpe ON mpe.din = rpe.din AND mpe.pkgsize = rpe.pkgsize
WHERE
mc.active = 'Y'
AND rpe.din IS NULL AND rpe.pkgsize IS NULL
AND mpe.din NOT IN ('02177579','02177595','02177722','02177706','02177587','02177692','02177714','02185830','02185857','02185873','02185849','02185865')
-----------------------------
but the first query returns 76 records
and the second query returns 0 records.
I just really don't like these WHERE IN...WHERE NOT IN....
Is my second query wrong?
Rob
May 2, 2008 at 8:32 am
Rob
I think your first query is actually equivalent to this:
SELECT mpe.din, mpe.pkgsize, mp.client, mc.active
FROM
USFPIMS..prodextend mpe
INNER JOIN USFPIMS..product mp ON mpe.din = mp.din AND mpe.pkgsize = mp.pkgsize
INNER JOIN USFPIMS..client mc ON mp.client = mc.code
LEFT OUTER JOIN USFRET..prodextend rpe ON mpe.din + mpe.pkgsize = rpe.pkgsize + rpe.din
WHERE
mc.active = 'Y'
AND rpe.din + rpe.pkgsize IS NULL
AND mpe.din NOT IN ('02177579','02177595','02177722','02177706','02177587','02177692','02177714','02185830','02185857','02185873','02185849','02185865')
John
May 2, 2008 at 10:31 am
Hi John,
You're probably right but
mpe.din is CHAR(3) and mpe.pkgsize is INT
so the join would look ugly again.
Something like
mpe.din + CONVERT(varchar(10),mpe.pkgsize)
I don't like aligning JOIN sides with CONVERT,RTRIM,concatination.
I prefer a more straight forward
...JOIN ON ISNULL(mpe.din,'') = ISNULL(rpe.din,'') AND ISNULL(mpe.pkgsize,0) = ISNULL(pre.pkgsize,0)...
Rob
May 2, 2008 at 10:34 am
I did a few tests
and finally decided to go with this version.
It now returns the same number of records as
the old "WHERE IN / NOT IN" code:
SELECT mpe.din, mpe.pkgsize
FROM
USFPIMS..prodextend mpe
INNER JOIN USFPIMS..product mp ON ISNULL(mpe.din,'') = ISNULL(mp.din,'') AND ISNULL(mpe.pkgsize,0) = ISNULL(mp.pkgsize,0)
INNER JOIN USFPIMS..client mc ON ISNULL(mp.client,'') = ISNULL(mc.code,'')
LEFT OUTER JOIN USFRET..prodextend rpe ON ISNULL(mpe.din,'') = ISNULL(rpe.din,'') AND ISNULL(mpe.pkgsize,0) = ISNULL(rpe.pkgsize,0)
WHERE
mc.active = 'Y'
AND rpe.din IS NULL AND rpe.pkgsize IS NULL
AND mpe.din NOT IN ('02177579','02177595','02177722','02177706','02177587','02177692','02177714','02185830','02185857','02185873','02185849','02185865')
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply