Home » SQL & PL/SQL » SQL & PL/SQL » One result missing in a product (Orcale, 10.2.0, Windows)
One result missing in a product [message #280590] Wed, 14 November 2007 03:06 Go to next message
lorco
Messages: 10
Registered: October 2005
Junior Member
I have been looking for a solution to this problem, joint, left joints, but I really can found it.
I let you the scripts



CREATE TABLE AVAILABILITY_TEMP
(
  AV_AVAILABILITY_ID_PK    NUMBER(19)           NOT NULL,
  AV_AVAILABLE_ON          NUMBER(10)           NOT NULL,
  AV_AVAILABLE_OFF         NUMBER(10)           NOT NULL,
  AV_DATE_APPOINTMENTS     TIMESTAMP(6)			NOT NULL,
  AV_LOCATIONTIME_SLOT_FK  NUMBER(19)
);

CREATE TABLE LOCATIONS_TIME_SLOT_TEMP
(
  LTS_LOCATIONTIMESLOT_ID_PK  NUMBER(19)        NOT NULL,
  LTS_OFF_LINE_PLACES         NUMBER(10),
  LTS_ON_LINE_PLACES          NUMBER(10),
  LTS_TIMEFROM                VARCHAR2(255)     NOT NULL,
  LTS_TIMETO                  VARCHAR2(255)     NOT NULL
);

INSERT INTO AVAILABILITY_TEMP ( AV_AVAILABILITY_ID_PK, AV_AVAILABLE_ON, AV_AVAILABLE_OFF,
AV_DATE_APPOINTMENTS, AV_LOCATIONTIME_SLOT_FK ) VALUES ( 
22, 0, 10, to_date( '14/11/07', 'dd/MM/yy' ), 1); 
INSERT INTO AVAILABILITY_TEMP ( AV_AVAILABILITY_ID_PK, AV_AVAILABLE_ON, AV_AVAILABLE_OFF,
AV_DATE_APPOINTMENTS, AV_LOCATIONTIME_SLOT_FK ) VALUES ( 
21, 6, 10, to_date( '14/11/07', 'dd/MM/yy' ),  4); 
INSERT INTO AVAILABILITY_TEMP ( AV_AVAILABILITY_ID_PK, AV_AVAILABLE_ON, AV_AVAILABLE_OFF,
AV_DATE_APPOINTMENTS, AV_LOCATIONTIME_SLOT_FK ) VALUES ( 
23, 0, 5, to_date( '14/11/07', 'dd/MM/yy' ), 2); 
commit;

 
INSERT INTO LOCATIONS_TIME_SLOT_TEMP ( LTS_LOCATIONTIMESLOT_ID_PK, 
LTS_OFF_LINE_PLACES, LTS_ON_LINE_PLACES, LTS_TIMEFROM, LTS_TIMETO ) VALUES ( 
16, 666, 666, '11:01', '12:00'); 
INSERT INTO LOCATIONS_TIME_SLOT_TEMP ( LTS_LOCATIONTIMESLOT_ID_PK, 
LTS_OFF_LINE_PLACES, LTS_ON_LINE_PLACES, LTS_TIMEFROM, LTS_TIMETO ) VALUES ( 
1, 10, 1, '08:00', '10:00'); 
INSERT INTO LOCATIONS_TIME_SLOT_TEMP ( LTS_LOCATIONTIMESLOT_ID_PK, 
LTS_OFF_LINE_PLACES, LTS_ON_LINE_PLACES, LTS_TIMEFROM, LTS_TIMETO ) VALUES ( 
2, 5, 1, '10:01', '11:00');  
commit;
 
 
 
 
select *
from (select to_date( '11', 'mm' )+rownum-1 dt
           from (select 1 from dual group by cube(1,2,3,4,5) ) 
            where rownum <= to_char(last_day(to_date( '11', 'mm' )),'dd') ) x 
		   left outer join (select * from availability_temp ) t 
		   on ((x.dt = t.av_date_appointments)),
		   locations_time_slot_temp lts 
		   where  ((lts.LTS_LOCATIONTIMESLOT_ID_PK = t.AV_LOCATIONTIME_SLOT_FK) 
		           or (t.AV_LOCATIONTIME_SLOT_FK is null))
order by dt




When launching the request I only have 2 result for the 14th, and I want to have 3 (the location time slot with id=16 is not in the result)
As long as I have a result in the AVAILABILITY_TEMP table it is not doing the product as I wanted to Sad


Can you help me there???
Re: One result missing in a product [message #280599 is a reply to message #280590] Wed, 14 November 2007 03:31 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Now THIS is how a question should be asked. Thankyou for supplying the appropriate test case.
Try this
LEFT OUTER JOIN t 
ON dys.dt = t.av_date_appointments
left JOIN locations_time_slot_temp lts 
ON  ((lts.LTS_LOCATIONTIMESLOT_ID_PK = t.AV_LOCATIONTIME_SLOT_FK) 
		           OR (t.AV_LOCATIONTIME_SLOT_FK IS NULL))

I tweaked your date generator a bit too:
SELECT TO_DATE('11','MM')+LEVEL-1 dt
            FROM DUAL
            CONNECT BY LEVEL <= EXTRACT(DAY FROM LAST_DAY(TO_DATE( '11', 'mm' )))

Removes the need for the subquery
Re: One result missing in a product [message #280663 is a reply to message #280590] Wed, 14 November 2007 07:22 Go to previous messageGo to next message
lorco
Messages: 10
Registered: October 2005
Junior Member
THANKS!!!

Can you please publish the hole request, I'm trying to do it, but each time I have the same result, only 2 lines for the 14th

Re: One result missing in a product [message #280679 is a reply to message #280663] Wed, 14 November 2007 08:50 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
No probs
with dys as (SELECT TO_DATE('11','MM')+LEVEL-1 dt
            FROM DUAL
            CONNECT BY LEVEL <= EXTRACT(DAY FROM LAST_DAY(TO_DATE( '11', 'mm' )))
            ),
    t as (SELECT * 
           FROM availability_temp
           )
SELECT *
FROM dys
LEFT OUTER JOIN t 
ON dys.dt = t.av_date_appointments
left JOIN locations_time_slot_temp lts 
ON  ((lts.LTS_LOCATIONTIMESLOT_ID_PK = t.AV_LOCATIONTIME_SLOT_FK) 
		           OR (t.AV_LOCATIONTIME_SLOT_FK IS NULL))
ORDER BY dt;

I think that this'll get you your results
Re: One result missing in a product [message #280811 is a reply to message #280590] Wed, 14 November 2007 18:52 Go to previous messageGo to next message
lorco
Messages: 10
Registered: October 2005
Junior Member
Hi,
Still not working, only 2 results for the 14th.
I manage to have the THREE result with this request, but I have only one result for all the other days

Sad

with dys as (SELECT TO_DATE('11','MM')+LEVEL-1 dt
            FROM DUAL
            CONNECT BY LEVEL <= EXTRACT(DAY FROM LAST_DAY(TO_DATE( '11', 'mm' )))
            ),
    lts as (SELECT *
FROM  locations_time_slot_temp t
left OUTER JOIN availability_temp lts 
ON  (t.LTS_LOCATIONTIMESLOT_ID_PK = lts.AV_LOCATIONTIME_SLOT_FK)
)	   
SELECT *
FROM  dys
LEFT JOIN lts 
ON  (dys.dt = lts.av_date_appointments or lts.av_date_appointments is null)
ORDER BY dt;
Re: One result missing in a product [message #280820 is a reply to message #280590] Wed, 14 November 2007 21:26 Go to previous messageGo to next message
lorco
Messages: 10
Registered: October 2005
Junior Member
A BIG help from someone at work give me the solution

with dys as (SELECT TO_DATE('11','MM')+LEVEL-1 dt
            FROM DUAL
            CONNECT BY LEVEL <= EXTRACT(DAY FROM LAST_DAY(TO_DATE( '11', 'mm' )))),
    t as (SELECT * FROM availability_temp)
select * from dys join  locations_time_slot_temp lts on (not dys.dt is null)
left outer join t ON (dys.dt = t.av_date_appointments 
  and (lts.LTS_LOCATIONTIMESLOT_ID_PK = t.AV_LOCATIONTIME_SLOT_FK))
order by dys.dt


Mas maraming samalat Michelle [thank you very much]
Re: One result missing in a product [message #280891 is a reply to message #280820] Thu, 15 November 2007 02:18 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Ignoramus as I am ... which language is it? Mas marmalade salami Michel? I thought it should have gone to the Food topic in the Community Hangout, but I was wrong, obviously.

P.S. I am NOT making fun of your language; please, excuse me for not knowing it, but - as you didn't mention where you are from, I'd really like to know which language you used here.
Re: One result missing in a product [message #280894 is a reply to message #280891] Thu, 15 November 2007 02:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
He comes from Philippines (between Hinoba and Bayawan).
I don't think the message is for me but for his colleague.

Regards
Michel
Re: One result missing in a product [message #280900 is a reply to message #280811] Thu, 15 November 2007 02:52 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Great that you've got the result that you need, however, based on your test case, my solution returns 3 rows for each date:
SQL> SELECT dt, count(*) FROM(
  2  with dys as (SELECT TO_DATE('11','MM')+LEVEL-1 dt
  3              FROM DUAL
  4              CONNECT BY LEVEL <= EXTRACT(DAY FROM LAST_DAY(TO_DATE( '11', 'mm' )))
  5              ),
  6      t as (SELECT *
  7             FROM availability_temp
  8             )
  9  SELECT *
 10  FROM dys
 11  LEFT OUTER JOIN t
 12  ON dys.dt = t.av_date_appointments
 13  left JOIN locations_time_slot_temp lts
 14  ON  ((lts.LTS_LOCATIONTIMESLOT_ID_PK = t.AV_LOCATIONTIME_SLOT_FK)
 15                        OR (t.AV_LOCATIONTIME_SLOT_FK IS NULL)))
 16  GROUP BY dt
 17  ORDER BY dt;

DT          COUNT(*)
--------- ----------
01-NOV-07          3
02-NOV-07          3
03-NOV-07          3
04-NOV-07          3
05-NOV-07          3
06-NOV-07          3
07-NOV-07          3
08-NOV-07          3
09-NOV-07          3
10-NOV-07          3
11-NOV-07          3
12-NOV-07          3
13-NOV-07          3
14-NOV-07          3
15-NOV-07          3
16-NOV-07          3
17-NOV-07          3
18-NOV-07          3
19-NOV-07          3
20-NOV-07          3
21-NOV-07          3
22-NOV-07          3
23-NOV-07          3
24-NOV-07          3
25-NOV-07          3
26-NOV-07          3
27-NOV-07          3
28-NOV-07          3
29-NOV-07          3
30-NOV-07          3
Re: One result missing in a product [message #280902 is a reply to message #280894] Thu, 15 November 2007 02:55 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
He does? How do you know?!? Wow!

Michel <> Michelle, obviously. It seems that I should adjust my sniper's focus. So, I won't have my marmalade + salami sandwich ... too sad.
Re: One result missing in a product [message #280904 is a reply to message #280902] Thu, 15 November 2007 02:57 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Quote:

It seems that I should adjust my sniper's focus
Laughing
Thanks for the early morning guffaw Smile
Re: One result missing in a product [message #280911 is a reply to message #280902] Thu, 15 November 2007 03:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

So, I won't have my marmalade + salami sandwich ... too sad.

No, fortunately!

Regards
Michel
Re: One result missing in a product [message #281147 is a reply to message #280911] Thu, 15 November 2007 18:52 Go to previous message
lorco
Messages: 10
Registered: October 2005
Junior Member
Right, it is Filipino (Talalog)

and thanks all of you
Previous Topic: How to trim trailing spaces in DBMS_OUTPUT
Next Topic: Trigger - can I access :NEW AND :OLD dynamically???
Goto Forum:
  


Current Time: Sat Dec 10 08:49:24 CST 2016

Total time taken to generate the page: 0.08024 seconds