newbie help with subquery date thing

[ad602000]

Hi all please find a query I am trying to get working. The problem I am having is that I am trying to use a date field from the subquery as part of a 'date difference' thing in the main query and its got me stumped. Any help appreciated

cheers Pete

select count (*) FROM client cl WHERE 1 = (select count (*)
FROM client cl, cytology_result cr1, test_result tr1
WHERE cl.clientid = tr.clientid AND cr1.resultid = tr1.resultid
AND cr1.squ_cd = '9'
AND (tr1.test_date - cytt.test_date) < 730
GROUP by cl.clientid)
AND exists(SELECT count (distinct cl.clientid)
FROM client cl, cytology_result cyt, test_result cytt
WHERE cl.clientid=cytt.clientid
AND cytt.resultid = cyt.resultid
AND cytt.test_type='C'
AND cyt.squ_cd = '3'
AND cyt.hpv_cd != '3'
AND cyt.end_cd not between '3' and '8'
AND cyt.oth_cd not between '2' and '8'
AND cytt.test_date > '01-JAN-1999'
AND cytt.test_date < '31-DEC-1999');

 

[r937]

wow, no offence, but that's a pretty ugly query

leave aside the outer query for a sec, and look at the first subselect --

select count (*)
  FROM client cl
     , cytology_result cr1
     , test_result tr1
 WHERE cl.clientid = tr.clientid 
   AND cr1.resultid = tr1.resultid
   AND cr1.squ_cd = '9'
   AND (tr1.test_date - cytt.test_date) < 730
GROUP by cl.clientid

assuming that tr.clientid is a typo and should be tr1.clientid, this is a three-way join that provides a count of result records for each client (thus the GROUP BY)...

... but there can only be one client, because the subquery has to return only one value, or else WHERE 1=(...) will fail

... which doesn't make sense except that it might be a correlated subquery, because it has a c1 client table and so does the outer query!

... which is a most unusual way of writing a correlated subquery, if all you want is to test whether a particular client has exactly 1 test result

but wait a minute, that subselect also makes reference to another table outside of it, the cytt table in the other subselect

you know, i have a feeling this is never going to work with that structure

could you maybe say in english what the entire query is supposed to do?

the date calculation part is actually really simple in comparison to structuring the query properly

please don't feel like i'm trashing you personally, but that query is pretty bad...

rudy

http://rudy.ca/

 

[angiole]

Rudy, Rudy, put the wooden spoon away, I'll get the ruler... AA 8~)

 

[ad602000]

Well Rudy,
What can I say, In case you were in to much of a hurry for the scent of blood, I did mention I was new to this.

Sorry about all the joins and stuff, but that's the way the DB is structured. The subquery part of the whole query actually does work. All I was trying to find was further results for the people identified in the subquery within a 24 month period of the test identified in the subquery, but look what else I have found, a little buddy like you who is so helpful with handy suggestions about fixing my dilemma with the date problem I encountered. After reading your response I did a terrible thing I created an identical query in MSAccess and it worked (first the pictures then the words)
Again Rudy, I cant thank you enough for your deep and meaningful input. you have reconfirmed my opinion of the attitude of people who live between Canada and Mexico.

 

[PruSQLer]

Wait a minute. How do you know Rudy lives in the US of A? Did you see the way he spelled offense? With a 'c'. That's got UK written all over it.

 

[r937]

okay, ad602000, i'm sorry, i guess i must apologize a second time, and concede that there was no reason to criticize your query structure

if you say it works, that's good enough for me, but i've been working with sql for over fifteen years (starting in 1987 with db2 on a mainframe), and i've never seen anything quite like your subselects

in many cases where something is not working properly, it is important to understand which rows are expected in order to begin resolving problems

let's go back to your original question -- &quot;I am trying to use a date field from the subquery as part of a 'date difference' thing and its got me stumped. Any help appreciated.&quot;

the only date difference i can see is

(tr1.test_date - cytt.test_date) < 730

and as far as i can tell, this is just fine

what exactly is the problem with it?


rudy

http://rudy.ca/

p.s. in case you are not familiar with internet top level domains, &quot;.ca&quot; is canada

 

[carp]

I have to agree -
(tr1.test_date - cytt.test_date) < 730
should work just fine (assuming that test_date is a DATE datatype in both tables.

If the problem is definitely date-related, you might want to check the following lines:

AND cytt.test_date > '01-JAN-1999'
AND cytt.test_date < '31-DEC-1999'

How is your default date format set up? For instance, if you are using Oracle, the &quot;normal&quot; date format uses a two-digit year (e.g., '01-JAN-99'). Other than that, I don't see any date-specific problems.

But of course, that's one of those horrid observations from the United States. Sorry if it offends you.