Code not working in 2008/2005

Question

Code not working in 2008/2005

andrew 67979

Ten Centuries

Points: 1268
More actions
March 23, 2017 at 6:33 am

#336254

The following code works fine in SQL 2014/2016 but gives me zero's in the counts (c) on older versions 2005/8. Any clues as what I might need to change?
Thanks
select top 1 t.* from (select 4 as slot,UDCONT4,'Record' as RC,count(CONTNUMB.DONOR_NO) as c from CONTNUMB,SYSFILE where WHICH_CONTACT = 0 and (len(UDCONT4)>0) and 1=(select 1 from SYSFILE where (UDCONT4<>'') and CONTACT_ID=4) group by sysfile.UDCONT4,WHICH_CONTACT union all select 4, case when len(UDCONT4)=0 then '[Undefined]' else UDCONT4 end, 'Record',0 as rc from SYSFILE ) as t union all select top 1 t.* from (select 4 as slot,UDCONT4,'Contact' as RC,count(CONTNUMB.DONOR_NO) as c from CONTNUMB,SYSFILE where WHICH_CONTACT > 0 and (len(UDCONT4)>0) and 1=(select 1 from SYSFILE where (UDCONT4<>'') and CONTACT_ID=4) group by sysfile.UDCONT4,WHICH_CONTACT union all select 4, case when len(UDCONT4)=0 then '[Undefined]' else UDCONT4 end, 'Contact',0 as rc from SYSFILE) as t

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99126 More actions · Answer 1

Can you provide consumable sample data that shows the problem? We can't see access data, so we don't know what we should be expecting from your SQL.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

andrew 67979 Ten Centuries Points: 1268 More actions · Answer 2

Thanks for the reply
It's a bit tricky to show you the data but the result looks like this on 2005/8 but on 2014/16 the C column has numbers
SLOT UDCONT4 RC C 4 Mobile Record 0 4 Mobile Contact 0

Sysfile is a single record system table containing UDCONT4
ContNumb is a table with different types of phone numbers in

I've got a bit further since posting

If I run the pair of sub queries together on their own they produce a result so I think it is something to do with the outer

select top 1 t.* from ( ..... ) t that 2005/8 don't like ?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

andrew 67979 - Thursday, March 23, 2017 6:33 AM
The following code works fine in SQL 2014/2016 but gives me zero's in the counts (c) on older versions 2005/8. Any clues as what I might need to change?
Thanks
select top 1 t.* from (select 4 as slot,UDCONT4,'Record' as RC,count(CONTNUMB.DONOR_NO) as c from CONTNUMB,SYSFILE where WHICH_CONTACT = 0 and (len(UDCONT4)>0) and 1=(select 1 from SYSFILE where (UDCONT4<>'') and CONTACT_ID=4) group by sysfile.UDCONT4,WHICH_CONTACT union all select 4, case when len(UDCONT4)=0 then '[Undefined]' else UDCONT4 end, 'Record',0 as rc from SYSFILE ) as t union all select top 1 t.* from (select 4 as slot,UDCONT4,'Contact' as RC,count(CONTNUMB.DONOR_NO) as c from CONTNUMB,SYSFILE where WHICH_CONTACT > 0 and (len(UDCONT4)>0) and 1=(select 1 from SYSFILE where (UDCONT4<>'') and CONTACT_ID=4) group by sysfile.UDCONT4,WHICH_CONTACT union all select 4, case when len(UDCONT4)=0 then '[Undefined]' else UDCONT4 end, 'Contact',0 as rc from SYSFILE) as t

Have you tried running one of the blocks, such as this:

select 4 as slot,UDCONT4,'Contact' as RC,count(CONTNUMB.DONOR_NO) as c

from CONTNUMB,SYSFILE

where WHICH_CONTACT > 0

and (len(UDCONT4)>0)

and 1=(select 1 from SYSFILE where (UDCONT4<>'') and CONTACT_ID=4)

group by sysfile.UDCONT4,WHICH_CONTACT

Next, investigate CONTNUMB and SYSFILE. Shouldn't take more than a minute or two.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

andrew 67979 Ten Centuries Points: 1268 More actions · Answer 4

Yes perhaps our posts crossed I can run the whole query this is inside the outer select top 1 t.* from ( ..... )
As soon as it goes in here column c contains zeros

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Just a little OCD when it comes to formatting code:
select top 1 t.* from (select 4 as slot, UDCONT4, 'Record' as RC, count(CONTNUMB.DONOR_NO) as c from CONTNUMB,SYSFILE where WHICH_CONTACT = 0 and (len(UDCONT4)>0) and 1=(select 1 from SYSFILE where (UDCONT4<>'') and CONTACT_ID=4) group by sysfile. UDCONT4, WHICH_CONTACT union all select 4, case when len(UDCONT4)=0 then '[Undefined]' else UDCONT4 end, 'Record', 0 as rc from SYSFILE ) as t union all select top 1 t.* from (select 4 as slot, UDCONT4, 'Contact' as RC, count(CONTNUMB.DONOR_NO) as c from CONTNUMB,SYSFILE where WHICH_CONTACT > 0 and (len(UDCONT4)>0) and 1=(select 1 from SYSFILE where (UDCONT4<>'') and CONTACT_ID=4 ) group by sysfile. UDCONT4, WHICH_CONTACT union all select 4, case when len(UDCONT4)=0 then '[Undefined]' else UDCONT4 end, 'Contact', 0 as rc from SYSFILE ) as t;

Not a fan of SQL-89 style joins, even if it is a Cartesian product of a one row table, much prefer the use of SQL-92 style joins and using CROSS JOIN where a Cartesian join is being used. I think it makes the code cleaner and easier to understand.