Odd Query Message

Getting a trouble call for yet another program someone else did...

A DTS package runs every time a Coldfusion page is called. The call is now making IE crawl to a halt and creating an unending memory leak. I'm told that calling a DTS package from a page itself is a bad idea, and I'm not one to disagree, but the main problem here seems to be that the number of rows being returned is far greater than what it should be (a few thousand should be returned at most, I'm getting over 130,000 records).

Looking at the data that's being compared (corporate vs. local) it appears that the problem stems from the local data having an extra 0 on the front of numbers in one column. This means that the IDs created from the various columns won't match up. (I should note that all columns discussed here are of type varchar.) Here's the part of the DTS package giving me trouble:


sql Code:






Original
- sql Code




SELECT
LTRIM(RTRIM(REG_NR)) + LTRIM(RTRIM(COY_NA)) + LTRIM(RTRIM(DIS_NR)) + LTRIM(RTRIM(PKG_CTR_NR)) +
LTRIM(RTRIM(BDG_NR)) + LTRIM(RTRIM(PSL_CD)) + LTRIM(RTRIM(CTY_NA)) +
LTRIM(RTRIM(IN_BDG_LOC_NR)) + LTRIM(RTRIM(PFR_CTY_NA)) AS ID,

MIN(TABLE1) as Version,

REG_NR, DIS_NR, PKG_CTR_NR, BDG_NR, CTY_NA, COY_NA, PSL_CD, ST_CD, ST_NA, CNY_CD, PFR_CTY_NA, IN_BDG_LOC_NR

FROM
(
SELECT 'Corporate' as TABLE1,

A.REG_NR, A.DIS_NR, A.PKG_CTR_NR, A.BDG_NR, A.CTY_NA, A.COY_NA,
A.PSL_CD, A.ST_CD, A.ST_NA, A.CNY_CD, A.PFR_CTY_NA, A.IN_BDG_LOC_NR
FROM TABLE1 A
UNION ALL
SELECT 'LOCAL' as TABLE2,

B.REG_NR, B.DIS_NR, B.PKG_CTR_NR, B.BDG_NR, B.CTY_NA, B.COY_NA,
B.PSL_CD, B.ST_CD, B.ST_NA, B.CNY_CD, B.PFR_CTY_NA, B.IN_BDG_LOC_NR
FROM TABLE2 B
) tmp
GROUP BY
REG_NR, DIS_NR, PKG_CTR_NR, BDG_NR, CTY_NA, COY_NA, PSL_CD, ST_CD, ST_NA, CNY_CD, PFR_CTY_NA, IN_BDG_LOC_NR
HAVING COUNT(*) = 1
ORDER BY PSL_CD






SELECT      LTRIM(RTRIM(REG_NR)) + LTRIM(RTRIM(COY_NA)) + LTRIM(RTRIM(DIS_NR)) + LTRIM(RTRIM(PKG_CTR_NR)) +         LTRIM(RTRIM(BDG_NR)) + LTRIM(RTRIM(PSL_CD)) + LTRIM(RTRIM(CTY_NA)) +         LTRIM(RTRIM(IN_BDG_LOC_NR)) + LTRIM(RTRIM(PFR_CTY_NA)) AS ID,     MIN(TABLE1) AS Version,       REG_NR, DIS_NR, PKG_CTR_NR, BDG_NR, CTY_NA, COY_NA, PSL_CD, ST_CD, ST_NA, CNY_CD, PFR_CTY_NA, IN_BDG_LOC_NR FROM(  SELECT 'Corporate' AS TABLE1,      A.REG_NR, A.DIS_NR, A.PKG_CTR_NR, A.BDG_NR, A.CTY_NA, A.COY_NA,     A.PSL_CD, A.ST_CD, A.ST_NA, A.CNY_CD, A.PFR_CTY_NA, A.IN_BDG_LOC_NR  FROM TABLE1 A  UNION ALL  SELECT 'LOCAL' AS TABLE2,     B.REG_NR, B.DIS_NR, B.PKG_CTR_NR, B.BDG_NR, B.CTY_NA, B.COY_NA,     B.PSL_CD, B.ST_CD, B.ST_NA, B.CNY_CD, B.PFR_CTY_NA, B.IN_BDG_LOC_NR  FROM TABLE2 B) tmpGROUP BY     REG_NR, DIS_NR, PKG_CTR_NR, BDG_NR, CTY_NA, COY_NA, PSL_CD, ST_CD, ST_NA, CNY_CD, PFR_CTY_NA, IN_BDG_LOC_NRHAVING COUNT(*) = 1ORDER BY PSL_CD


Since it was just one extra 0 (in the local table), I thought I could add a 0 to the beginning of the string in the first table:

sql Code:






Original
- sql Code




SELECT 'Corporate' as TABLE1,

A.REG_NR, A.DIS_NR, A.PKG_CTR_NR, '0'+A.BDG_NR, A.CTY_NA, A.COY_NA,
A.PSL_CD, A.ST_CD, A.ST_NA, A.CNY_CD, A.PFR_CTY_NA, A.IN_BDG_LOC_NR
FROM TABLE1 A






  SELECT 'Corporate' AS TABLE1,      A.REG_NR, A.DIS_NR, A.PKG_CTR_NR, '0'+A.BDG_NR, A.CTY_NA, A.COY_NA,     A.PSL_CD, A.ST_CD, A.ST_NA, A.CNY_CD, A.PFR_CTY_NA, A.IN_BDG_LOC_NR  FROM TABLE1 A


However, this keeps giving me the error 'no column specified for column 5 of tmp'. Unsure how to fix that, I try to instead trim the local database to four numbers:

sql Code:






Original
- sql Code




SELECT 'LOCAL' as TABLE2,

B.REG_NR, B.DIS_NR, B.PKG_CTR_NR, RIGHT(B.BDG_NR,4), B.CTY_NA, B.COY_NA,
B.PSL_CD, B.ST_CD, B.ST_NA, B.CNY_CD, B.PFR_CTY_NA, B.IN_BDG_LOC_NR
FROM TABLE2 B






  SELECT 'LOCAL' AS TABLE2,     B.REG_NR, B.DIS_NR, B.PKG_CTR_NR, RIGHT(B.BDG_NR,4), B.CTY_NA, B.COY_NA,     B.PSL_CD, B.ST_CD, B.ST_NA, B.CNY_CD, B.PFR_CTY_NA, B.IN_BDG_LOC_NR  FROM TABLE2 B


This works, and I am only getting about 10,000 rows (perhaps not quite right, but better than before). The two columns now line up. However, this has an unintended (and really crazy) sideffect: The IN_BDG_LOC_NR column (and thus part of the ID) is now completely empty for the corporate data, and 00 for the local data (both were originally a two digit number that was not 00).

I have absolutely no idea what is causing this, and google isn't helping. Is there a way to make the first idea I had work? If so, I'd prefer to use that. If not, I would appreciate thoughts on making the second idea work.