Join on a ?

[mikedaruke]

Can you Join on a date filed?

I am trying to and its returning nothing, but when i look at the tables side by side they match perfect.

 

[oradba101]

Hi, Mike

Can you provide the SQL SELECT statement you are using?

Regards,



William Chadbourne
Oracle DBA

 

[jimbopalmer]

You may need to Trunc() on your dates, otherwise the are accurate to the Millisecond and will only very rarely be equal.

I tried to remain child-like, all I acheived was childish.

 

[SantaMufasa]

Mike,

Jimbo's advice to TRUNC(<date expression>) is correct...that strips off the hours, minutes, and seconds components of the date and makes the resulting date appear to be midnight on the morning of the truncated date.

The only thing I would alter of Jimbo's posting is to say that DATE columns have granularity down to seconds; TIMESTAMP columns have granularity down to milliseconds.

Let us know if the TRUNC(<date expression>) in your WHERE clause gives you what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[mikedaruke]

Ok you asked for it!

What it is a job_table, that every time a job is ran, it either is success or fail. I need to know the max success and the max fail. Then I need other information as well. So later I try a join to gather other info, but the join fails when I try to join on the date,client,policy. If i join on client,policy it works but I get like a million results as every job comes up. All I care about is the job that is equal to the max failure and max success.

WITH temp7 AS
(
SELECT g.CLIENT, g.POLICY as policy_app_s, MAX(TRY_START_DATE) last_app_s
FROM job_detail g
WHERE summary_status IN (0,1)
and g.JOB_TYPE in (101,102,112)
GROUP BY g.CLIENT, g.POLICY
), temp8 AS
(
SELECT h.CLIENT, h.POLICY as policy_app_f, MAX(TRY_START_DATE) last_app_f
FROM job_detail h
WHERE summary_status = 2
and h.JOB_TYPE in (101,102,112)
GROUP BY h.CLIENT, h.POLICY
)
SELECT r.CLIENT,
temp7.policy_app_s,
temp7.last_app_s,
temp8.policy_app_f,
temp8.last_app_f,
r.TRY_START_DATE,
r.MASTER_NAME,
r.VENDOR_STATUS

FROM job_detail r, temp7, temp8
where temp7.client_host_name (+) = r.client_host_name and temp7.policy_app_s (+) = r.policy_name and temp7.last_app_s (+) = r.TRY_START_DATE
and temp8.client_host_name (+) = r.client_host_name and temp8.policy_app_f (+) = r.policy_name and temp8.last_app_f (+) = r.TRY_START_DATE
-- Lets report clients/policy with failtures in the last 7 days
and temp8.last_app_f >= sysdate -7
-- that don't have a successful full or incr in in less than 7 days
and temp7.last_app_s <= sysdate -7

 

[mikedaruke]

No, I need it by down to the seconds....

So I need these two columns to match...

10/6/2007 12:54:25.000 PM
&
10/6/2007 12:54:25.000 PM


I can see those two colums when I join by client,policy

Once I add to join by date also, I get no results. Yet they look identical.

 

[SantaMufasa]

Mike,

Make these two adjustments in your WHERE clauses:

...TRUNC(temp7.last_app_s) (+) = TRUNC(r.TRY_START_DATE)
...TRUNC(temp8.last_app_f) (+) = TRUNC(r.TRY_START_DATE)...

Let us know if that generates the results you want.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[mikedaruke]

I had to do it like this for it to run,

...TRUNC(temp7.last_app_s (+)) = TRUNC(r.TRY_START_DATE)
...TRUNC(temp8.last_app_f (+)) = TRUNC(r.TRY_START_DATE)...


Also but I can have like 30 jobs a day, so if you strip off the hour/min/sec, then I am going to get back more resutls than I am looking for. Is that what this is doing?

 

[SantaMufasa]

...if you strip off the hour/min/sec, then I am going to get back more resutls than I am looking for. Is that what this is doing?

Yes, and Yes. The challenge for us as responders to get the exact code that you need/want is our lack of familiarity with your data. If you have 30 readings per day and you do not want all 30 readings to return from your query, then you must adjust the query to act as you want.


If you say:

...TRUNC(temp7.last_app_s (+)) = TRUNC(r.TRY_START_DATE)
...TRUNC(temp8.last_app_f (+)) = TRUNC(r.TRY_START_DATE)...

...then this gives you matches that occurred on the same DATE, regardless of hour, minute, and second. If you want to match on hour, minute, and second, then you must remove the TRUNC() function.

Only you, who know your data, can determine what the WHERE clause should contain.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[mikedaruke]

lol I know but when I remove it, I get no results!

Thats the original question!

I dont get why this happens.

 

[mikedaruke]

Using the Trunc, I got no results. I dont get this. They are identical fields. Could the max function be changing the type of date>?

 

[hoinz]

Hello,

for me too there are a few things which I don't get here:

1) Column names of temp7 and temp8:
e.g in temp7 there is no temp7.client_host_name, and yet you use it the where clause.
How is this supposed to work?

2)

when I remove it, I get no results!

Using the Trunc, I got no results.

Now what?
When do and when don't you get results?

3) There are a lot of (+) in your query. They will give you a result row for every row in your job_detail table. This is how (+) is designed to work. But not sure if this is what you really want.
Using or not using trunc() shouldn't change much in this regard. (Also see point 2 above.)
I don't dare to say that I understand what you are doing. But omitting the (+) for the date part could be worth a try.

4) Joining dates should not be a problem per se.
But look at this:

where temp7.client_host_name (+) = r.client_host_name and temp7.policy_app_s (+) = r.policy_name and temp7.last_app_s (+) = r.TRY_START_DATE
and temp8.client_host_name (+) = r.client_host_name and temp8.policy_app_f (+) = r.policy_name and temp8.last_app_f (+) = r.TRY_START_DATE

Doesn't this mean you are looking for a run that was both successful and failing?

5)

-- that don't have a successful full or incr in in less than 7 days
and temp7.last_app_s <= sysdate -7

This is not the same. The condition will give you the clients where the last successful run was more than 7 days ago. It will not cover the cases when there never was a successful run. This might justify some kind of (+) for the date part though.

hope this helps