April 24, 2008 at 11:01 am
Came up with the following script to find differences between 2 tables & here it is.....
SELECT MIN(Table_Name) AS Table_Name, beenumber, nacdate, ConsultantPromDt, TeamLeadPromDt
FROM
(
SELECT 'Table A' AS Table_Name, beenumber, nacdate, ConsultantPromDt, TeamLeadPromDt
FROM
(select bb.beenumber, bbd.nacdate, bbd.ConsultantPromDt, bbd. TeamLeadPromDt from beebusiness bb
join beebusinessdate bbd on bbd.beebusinessguid = bb.beebusinessguid)A
WHERE NOT EXISTS
(SELECT 1 FROM [hqvm03\hqsqlinst05].sdk.dbo.ml B
WHERE A.beenumber= convert(char(13), ltrim(rtrim(stuff(B.sa_ss,1,patindex('%[^0]%', B.sa_ss)-1,''))))
AND isnull(A.nacdate,0) = isnull(B.CL1,0)
AND isnull(A.ConsultantPromDt,0) = isnull(B.cl2,0)
AND isnull(A.TeamLeadPromDt,0) = isnull(B.cl4,0)
)
UNION ALL
SELECT 'Table B' AS Table_Name, sa_ss as beenumber, cl1 as nacdate, cl2 as ConsultantPromDt, cl4 as TeamLeadPromDt
FROM [hqvm03\hqsqlinst05].sdk.dbo.ml B
WHERE NOT EXISTS
(SELECT 1 FROM (select bb.beenumber, bbd.nacdate, bbd.ConsultantPromDt, bbd. TeamLeadPromDt from beebusiness bb
join beebusinessdate bbd on bbd.beebusinessguid = bb.beebusinessguid)A
WHERE convert(char(13), ltrim(rtrim(stuff(B.sa_ss,1,patindex('%[^0]%', B.sa_ss)-1,''))))=A.beenumber
AND isnull(A.nacdate,0) = isnull(B.CL1,0)
AND isnull(A.ConsultantPromDt,0) = isnull(B.cl2,0)
AND isnull(A.TeamLeadPromDt,0) = isnull(B.cl4,0)
)
) tmp
group by beenumber, nacdate, ConsultantPromDt, TeamLeadPromDt
having count(*) = 1
order by beenumber
However, after 20 mins of serious thinking, it responds back saying "Error converting data type varchar to numeric. " Im not sure why it is throwing this error as I corrected the data type differences too.
Please correct my script !!!
April 24, 2008 at 11:38 am
April 24, 2008 at 12:01 pm
Bledu,
I followed your advice & threw in bunch of validations & here is where Im struck at now...
SELECT 'Table B' AS Table_Name, b.sa_ss as beenumber, cl1 as nacdate, cl2 as ConsultantPromDt,
cl4 as TeamLeadPromDt
FROM [hqvm03\hqsqlinst05].sdk.dbo.ml B where isnumeric(b.sa_ss) = 1 and b.sa_ss not like '99999%'
WHERE NOT EXISTS
(SELECT 1 FROM
(select bb.beenumber, bbd.nacdate, bbd.ConsultantPromDt, bbd. TeamLeadPromDt
from beebusiness bb
join beebusinessdate bbd on bbd.beebusinessguid = bb.beebusinessguid)A
WHERE
cast(b.sa_ss as INT) = A.beenumber and isnumeric(b.sa_ss) = 1 and b.sa_ss not like '99999%'
AND isnull(A.nacdate,0) = isnull(B.CL1,0)
AND isnull(A.ConsultantPromDt,0) = isnull(B.cl2,0)
AND isnull(A.TeamLeadPromDt,0) = isnull(B.cl4,0)
)
It is throwing a syntax error at line 6( point that says - WHERE NOT EXISTS) --
Incorrect syntax near the keyword 'WHERE'.
Thank you very much for your help !!!
April 24, 2008 at 12:17 pm
I think you are missing a closing paren.
April 24, 2008 at 12:20 pm
I think I finally got it... & here it is .... Thanks a lot 'Bledu' & 'Cbaker'
SELECT MIN(Table_Name) AS Table_Name, beenumber, nacdate, ConsultantPromDt, TeamLeadPromDt
FROM
(
SELECT 'Table A' AS Table_Name, beenumber, nacdate, ConsultantPromDt, TeamLeadPromDt
FROM
(select bb.beenumber, bbd.nacdate, bbd.ConsultantPromDt, bbd. TeamLeadPromDt from beebusiness bb
join beebusinessdate bbd on bbd.beebusinessguid = bb.beebusinessguid)A
WHERE NOT EXISTS
(SELECT 1 FROM [hqvm03\hqsqlinst05].sdk.dbo.ml B
WHERE A.beenumber= cast(b.sa_ss as INT)
and isnumeric(b.sa_ss) = 1 and b.sa_ss not like '999999%'
AND isnull(A.nacdate,0) = isnull(B.CL1,0)
AND isnull(A.ConsultantPromDt,0) = isnull(B.cl2,0)
AND isnull(A.TeamLeadPromDt,0) = isnull(B.cl4,0)
)
UNION ALL
SELECT 'Table B' AS Table_Name, bb.sa_ss as beenumber, bb.cl1 as nacdate, bb.cl2 as ConsultantPromDt,
bb.cl4 as TeamLeadPromDt
FROM
(select sa_ss, cl1, cl2, cl4 from
[hqvm03\hqsqlinst05].sdk.dbo.ml where isnumeric(sa_ss) = 1 and sa_ss not like '99999%')BB
WHERE NOT EXISTS
(SELECT 1 FROM
(select bb.beenumber, bbd.nacdate, bbd.ConsultantPromDt, bbd. TeamLeadPromDt
from beebusiness bb
join beebusinessdate bbd on bbd.beebusinessguid = bb.beebusinessguid)A
WHERE
cast(bb.sa_ss as INT) = A.beenumber and isnumeric(bb.sa_ss) = 1 and bb.sa_ss not like '99999%'
AND isnull(A.nacdate,0) = isnull(Bb.CL1,0)
AND isnull(A.ConsultantPromDt,0) = isnull(Bb.cl2,0)
AND isnull(A.TeamLeadPromDt,0) = isnull(Bb.cl4,0)
)
) tmp
group by beenumber, nacdate, ConsultantPromDt, TeamLeadPromDt
having count(*) = 1
order by beenumber
April 24, 2008 at 12:31 pm
upendramkumar (4/24/2008)
Bledu,I followed your advice & threw in bunch of validations & here is where Im struck at now...
FROM [hqvm03\hqsqlinst05].sdk.dbo.ml B where isnumeric(b.sa_ss) = 1 and b.sa_ss not like '99999%'
WHERE NOT EXISTS
(SELECT 1 FROM
you had put 2 where statements which I have put in bold
April 24, 2008 at 12:46 pm
Bledu,
You have noticed the correct thing but it is in its place & what I did to over come this problem is to create a derived table and then do my lil validation in there & then I just called the value without doing any more validation.
But for now, my issue is solved & I posted my complete sql statement that works now.
sincere thanks !
April 24, 2008 at 12:51 pm
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply