Using Outer Join & Left Join In One

hi
i have table cr_gnditem
dob date
unit varchar2
contain the sales data for each unit
the unit is equiivelent to STR_NO of cr_store (whcih is the master table for all stores)
i want to know the missing transactions per unit/day
i created also calendar_table which have all dates
SELECT d.DATE_TIME_START, S.STR_NO
FROM calendar_table d , cr_store s
LEFT JOIN (select unit,dob  
from 
cr_gnditem group by unit,dob) ds
  ON ds.dob = d.DATE_TIME_START
  and ds.unit = s.str_no
 where
 s.str_act = 1
and  TO_date(d.date_time_start, 'dd-mm-yyyy') >= '01-04-2015'
and d.date_time_start < (sysdate - 1)
and ds.dob is null;

Open in new window

this code not working , it says : sql not probabley ended
NiceMan331Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

johnsoneSenior Oracle DBACommented:
I believe this is functionally equivalent to what you have
SELECT d.date_time_start, 
       s.str_no 
FROM   (SELECT unit, 
               dob 
        FROM   cr_gnditem 
        GROUP  BY unit, 
                  dob) ds 
       right outer join calendar_table d 
                     ON ds.dob = d.date_time_start 
       right outer join cr_store 
                     ON ds.unit = s.str_no 
WHERE  s.str_act = 1 
       AND To_date(d.date_time_start, 'dd-mm-yyyy') >= '01-04-2015' 
       AND d.date_time_start < ( SYSDATE - 1 ) 
       AND ds.dob IS NULL; 

Open in new window

What datatype is CALENDAR_TABLE.DATE_TIME_START?  In one line of the where clause you treat it as a string and then in the next line you treat it as a date.  I suspect it is a date, so I would change:
       AND To_date(d.date_time_start, 'dd-mm-yyyy') >= '01-04-2015'
to
       AND d.date_time_start >= To_date('01-04-2015' , 'dd-mm-yyyy')
Converting a DATE to a DATE really doesn't make any sense.

If this doesn't work, please post create scripts for the tables, sample data, and expected results so we can figure out what is going on.
0
NiceMan331Author Commented:
it return error
s.str_no invalid identifier
like 11
What datatype is CALENDAR_TABLE.DATE_TIME_START?
date
0
NiceMan331Author Commented:
look , this works well for one unit

SELECT tc.DATE_TIME_START
FROM calendar_table tc
where 
TO_date(tc.date_time_start, 'dd-mm-yyyy') >= '01-04-2015'
and tc.date_time_start < (sysdate - 1)
and TO_date(tc.date_time_start, 'dd-mm-yyyy')
not IN(
select dob  
from 
cr_gnditem 
where 
TO_date(dob, 'dd-mm-yyyy') >= '01-04-2015'
and unit = '3007'
group by dob)
order by tc.DATE_TIME_START;

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

NiceMan331Author Commented:
it result missed dates considering one unit
now i need missed dates for all units
0
johnsoneSenior Oracle DBACommented:
I missed the table alias on line 10
SELECT d.date_time_start, 
       s.str_no 
FROM   (SELECT unit, 
               dob 
        FROM   cr_gnditem 
        GROUP  BY unit, 
                  dob) ds 
       right outer join calendar_table d 
                     ON ds.dob = d.date_time_start 
       right outer join cr_store s
                     ON ds.unit = s.str_no 
WHERE  s.str_act = 1 
       AND d.date_time_start >= To_date('01-04-2015', 'dd-mm-yyyy') 
       AND d.date_time_start < ( SYSDATE - 1 ) 
       AND ds.dob IS NULL; 

Open in new window

0
NiceMan331Author Commented:
it returns no row
let me simply fy it
this sql will return all possible  dates & units

SELECT d.DATE_TIME_START, S.STR_NO
FROM calendar_table d , cr_store s
where
 s.str_act = 1
  AND d.date_time_start >= To_date('01-04-2015', 'dd-mm-yyyy') 
       AND d.date_time_start < ( SYSDATE - 1 );

Open in new window


and return correct records

now , this statement , return all available records
select unit,dob  
from 
cr_gnditem group by unit,dob

Open in new window


just try to make the unmatched records between the 1st one and the 2nd one
0
NiceMan331Author Commented:
sorry i forget to tell you
str_no is varchar2
unit is number
0
johnsoneSenior Oracle DBACommented:
I think this is closer
SELECT          d.date_time_start, 
                s.str_no 
FROM            (calendar_table d 
join            cr_store s) 
left outer join cr_gnditem ds 
ON              ds.dob = d.date_time_start 
AND             ds.unit = s.str_no 
WHERE           s.str_act = 1 
AND             d.date_time_start >= to_date('01-04-2015', 'dd-mm-yyyy') 
AND             d.date_time_start < ( SYSDATE - 1 );

Open in new window

If that isn't giving you what you want, then please post sample information as requested.
0
NiceMan331Author Commented:
line 4 , missing keyword error
0
johnsoneSenior Oracle DBACommented:
Well, I learned something.  You cannot generate a cartesian product without an ON clause, so just add the ON clause.
SELECT d.date_time_start, 
       s.str_no 
FROM   (calendar_table d 
        join cr_store s 
          ON 1 = 1) 
       left outer join cr_gnditem ds 
                    ON ds.dob = d.date_time_start 
                       AND ds.unit = s.str_no 
WHERE  s.str_act = 1 
       AND d.date_time_start >= To_date('01-04-2015', 'dd-mm-yyyy') 
       AND d.date_time_start < ( SYSDATE - 1 ); 

Open in new window

0
NiceMan331Author Commented:
i'm sorry
it return too much  records which is not correct
i'll try tomorrow to send you what you asked me
thanx
0
NiceMan331Author Commented:
please look at the attached excell sheet
suppose i have 3 str_no (1,2,3)
and 10 days from : 01-01-2015 till 10-01-2015
the 1st group in the sheet display that i should have 30 records of transactions
each str_no should have 10 records , total = 30
the second group shows the actual data available in table of transactions cr_gnditem
where there are some missing records
the third group display the expected result
hopw it is clear now
and note agian
str_no is varchar2
unit is number
Lokkup.xlsx
0
johnsoneSenior Oracle DBACommented:
Given what was posted into the spreadsheet, this is what I came up with to generate the 3 tables.  Really, you should have posted this.
create table calendar_table (date_time_start date);
create table cr_store (str_no varchar2(10));
create table cr_gnditem (dob date, unit number);
insert into calendar_table values (to_date('01012015','mmddyyyy'));
insert into calendar_table values (to_date('01022015','mmddyyyy'));
insert into calendar_table values (to_date('01032015','mmddyyyy'));
insert into calendar_table values (to_date('01042015','mmddyyyy'));
insert into calendar_table values (to_date('01052015','mmddyyyy'));
insert into calendar_table values (to_date('01062015','mmddyyyy'));
insert into calendar_table values (to_date('01072015','mmddyyyy'));
insert into calendar_table values (to_date('01082015','mmddyyyy'));
insert into calendar_table values (to_date('01092015','mmddyyyy'));
insert into calendar_table values (to_date('01102015','mmddyyyy'));
insert into cr_store values ('1');
insert into cr_store values ('2');
insert into cr_store values ('3');
commit;
insert into cr_gnditem values (to_date('01012015','mmddyyyy'), 1);
insert into cr_gnditem values (to_date('01022015','mmddyyyy'), 1);
insert into cr_gnditem values (to_date('01032015','mmddyyyy'), 1);
insert into cr_gnditem values (to_date('01042015','mmddyyyy'), 1);
insert into cr_gnditem values (to_date('01052015','mmddyyyy'), 1);
insert into cr_gnditem values (to_date('01072015','mmddyyyy'), 1);
insert into cr_gnditem values (to_date('01082015','mmddyyyy'), 1);
insert into cr_gnditem values (to_date('01092015','mmddyyyy'), 1);
insert into cr_gnditem values (to_date('01012015','mmddyyyy'), 2);
insert into cr_gnditem values (to_date('01022015','mmddyyyy'), 2);
insert into cr_gnditem values (to_date('01032015','mmddyyyy'), 2);
insert into cr_gnditem values (to_date('01052015','mmddyyyy'), 2);
insert into cr_gnditem values (to_date('01062015','mmddyyyy'), 2);
insert into cr_gnditem values (to_date('01072015','mmddyyyy'), 2);
insert into cr_gnditem values (to_date('01082015','mmddyyyy'), 2);
insert into cr_gnditem values (to_date('01092015','mmddyyyy'), 2);
insert into cr_gnditem values (to_date('01102015','mmddyyyy'), 2);
insert into cr_gnditem values (to_date('01012015','mmddyyyy'), 3);
insert into cr_gnditem values (to_date('01022015','mmddyyyy'), 3);
insert into cr_gnditem values (to_date('01042015','mmddyyyy'), 3);
insert into cr_gnditem values (to_date('01052015','mmddyyyy'), 3);
insert into cr_gnditem values (to_date('01062015','mmddyyyy'), 3);
insert into cr_gnditem values (to_date('01072015','mmddyyyy'), 3);
insert into cr_gnditem values (to_date('01082015','mmddyyyy'), 3);
insert into cr_gnditem values (to_date('01092015','mmddyyyy'), 3);
insert into cr_gnditem values (to_date('01102015','mmddyyyy'), 3);
commit;

Open in new window

Given that data, this query gives the results that you asked for:
SELECT d.date_time_start, 
       s.str_no 
FROM   (calendar_table d 
        join cr_store s 
          ON 1 = 1) 
       left outer join cr_gnditem ds 
                    ON ds.dob = d.date_time_start 
                       AND ds.unit = s.str_no 
WHERE  ds.unit IS NULL; 

Open in new window

0
NiceMan331Author Commented:
could you please post the result of your query
0
johnsoneSenior Oracle DBACommented:
Output:
DATE_TIME_START     STR_NO
------------------- ----------
01/10/2015 00:00:00 1
01/03/2015 00:00:00 3
01/04/2015 00:00:00 2
01/06/2015 00:00:00 1

Open in new window

0
NiceMan331Author Commented:
yes may be your result is correct due to that we consider that table
cr_gnditem has one record per day/unit
but in fact , each unit may has many records per day
so , as per i post earlier , we have to use this query
select unit,dob  
from 
cr_gnditem 
group by unit,dob

Open in new window

if you can replace it in your last sql , i think will work well
0
johnsoneSenior Oracle DBACommented:
Then why does there need to be a join to the CR_GNDITEM table at all?  This produced the same result:
SELECT d.date_time_start, 
       s.str_no 
FROM   calendar_table d, 
       cr_store s 
WHERE  NOT EXISTS (SELECT 1 
                   FROM   cr_gnditem ds 
                   WHERE  ds.dob = d.date_time_start 
                          AND ds.unit = s.str_no); 

Open in new window

There shouldn't be any duplicate there.

You may complain that not exists is inefficient, but the cartesian product isn't that terribly efficient either.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
johnsoneSenior Oracle DBACommented:
Or, simply add a distinct:
SELECT distinct d.date_time_start, 
       s.str_no 
FROM   (calendar_table d 
        join cr_store s 
          ON 1 = 1) 
       left outer join cr_gnditem ds 
                    ON ds.dob = d.date_time_start 
                       AND ds.unit = s.str_no 
WHERE  ds.unit IS NULL; 

Open in new window

Again adding a sort to the query, which could be inefficient, but would remove duplication.
0
NiceMan331Author Commented:
yes , excellent , now it gives correct result
i just added the date range creteria like this

SELECT d.date_time_start, 
       s.str_no 
FROM   calendar_table d, 
       cr_store s 
WHERE  NOT EXISTS (SELECT 1 
                   FROM   cr_gnditem ds 
                   WHERE  ds.dob = d.date_time_start 
                          AND ds.unit = s.str_no)
                       and  s.str_act = 1 
       AND d.date_time_start >= To_date('01-04-2015', 'dd-mm-yyyy') 
       AND d.date_time_start < ( SYSDATE - 1 ) 
       order by s.str_no,D.DATE_TIME_START;   

Open in new window



but excuse me , may i ask what is the meaning of select 1
and in your previous sql you wrote
on 1 = 1 ?
just for knowledge
0
johnsoneSenior Oracle DBACommented:
SELECT 1 is because you have to select something.  As it is an exists, it doesn't matter what you select, you are just looking for whether or not there is a row.  The reason you put a constant in there (some people use NULL), is so that the full table row does not have to be read from the table in the subquery.  If there is an index on the 2 columns then the query can be satisfied with the index and never have to load a block from the table.

The ON 1=1 is because it appears that a JOIN clause requires an ON clause.  1=1 is always true, so we use that.
0
NiceMan331Author Commented:
ok thanx
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.