Find missed day & store

i previously asked this question
http://www.experts-exchange.com/Database/Oracle/Q_28229246.html

it was works well to get the missed days for data of one store
now if i want to add store number to the result , like this


declare
cursor c_stores is 
select a.str_no
 from cr_store a
where a.opdate > '28-dec-13';

BEGIN
FOR i in c_stores
   LOOP
  
SELECT i,tc.DATE_TIME_START
FROM calendar_table tc
where tc.date_time_start between '01-jan-14' and sysdate
and tc.date_time_start
not IN
(select ds.dob from 
cr_gnditem ds
where ds.unit = i 
and ds.dob between '01-jan-14' and sysdate
group by ds.dob)
order by tc.DATE_TIME_START;
end loop;
end;

Open in new window


but it sent me error message that data wrong of type
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.

PortletPaulfreelancerCommented:
I see from the previous question that you changed the proposed NOT EXISTS() into NOT IN()
I would prefer to use NOT EXISTS, but here I would simply use a left outer join then locate those records that have no match. Also: I don't see why you are using a cursor now.

Requested:
All dates from '01-jan-14' until and including sysdate without a matching birthday in cr_gnditem

SELECT
      TC.DATE_TIME_START
FROM calendar_table TC
      LEFT JOIN (
            SELECT
                  dob
            FROM cr_gnditem
      ) CG ON TC.DATE_TIME_START = CG.dob
WHERE (TC.date_time_start BETWEEN '01-jan-14' AND sysdate)
AND CG.dob IS NULL
;

Open in new window


There MIGHT be an advantage in using DISTINCT for the subquery:
SELECT
      TC.DATE_TIME_START
FROM calendar_table TC
      LEFT JOIN (
            SELECT DISTINCT
                  dob
            FROM cr_gnditem
      ) CG ON TC.DATE_TIME_START = CG.dob
WHERE (TC.date_time_start BETWEEN '01-jan-14' AND sysdate)
AND CG.dob IS NULL
;

Open in new window

0
NiceMan331Author Commented:
ok , but i still not get the  "i" in the query , which refere to store number
0
PortletPaulfreelancerCommented:
Your cursor for store number is the equivalent of a cross join as far as I can see
SELECT
        S.str_no
      , TC.DATE_TIME_START
FROM calendar_table TC
      LEFT JOIN (
                  SELECT /* DISTINCT */ /* distinct MIGHT help */
                        dob
                  FROM cr_gnditem
                  ) CG ON TC.DATE_TIME_START = CG.dob
      CROSS JOIN (
                  SELECT
                        str_no
                  FROM cr_store
                  WHERE opdate > to_date('28-dec-13','DD-MMM-YY')
                  ) S
WHERE (TC.date_time_start BETWEEN to_date('01-jan-14','DD-MMM-YY') AND sysdate)
AND CG.dob IS NULL
;

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.

johnsoneSenior Oracle DBACommented:
The variable i that you are referencing is a record, not a single value.  You would need to reference it in your query as i.str_no.
0
NiceMan331Author Commented:
error at select , after loop
 an INTO clause is expected in this SELECT statement

Open in new window

0
PortletPaulfreelancerCommented:
NiceMan331 that is coming from elsewhere, not the query itself. I have no idea how you are using it.

I am not online at this time typically so I won't be able to debug the extra code.
0
johnsoneSenior Oracle DBACommented:
The message is coming from your original code.  You have a select statement in a PL/SQL block.  You need to select that into something.  Variables should be declared to hold the values you are selecting.
0
NiceMan331Author Commented:
johnson , may you please correct my sql
actually i want to print out the result of the missed days by stores , how i select it into something ?
0
PortletPaulfreelancerCommented:
You do not need PL/SQL for this, just run the SQL query I provided
SELECT
        S.str_no
      , TC.DATE_TIME_START
FROM calendar_table TC
      LEFT JOIN (
                  SELECT /* DISTINCT */ /* distinct MIGHT help */
                        dob
                  FROM cr_gnditem
                  ) CG ON TC.DATE_TIME_START = CG.dob
      CROSS JOIN (
                  SELECT
                        str_no
                  FROM cr_store
                  WHERE opdate > to_date('28-dec-13','DD-MMM-YY')
                  ) S
WHERE (TC.date_time_start BETWEEN to_date('01-jan-14','DD-MMM-YY') AND sysdate)
AND CG.dob IS NULL
;

Open in new window

0
NiceMan331Author Commented:
error message
date format not recognized
0
PortletPaulfreelancerCommented:
change the the date literals you use to a much safer and more sensible YYYY-MM-DD

e.g.
28-dec-13 becomes
2013-12-28

WHERE opdate > to_date('2013-12-28','YYYY-MM-DD')

& treat the other date literal the same way
0
NiceMan331Author Commented:
yes , it works
but it gives no rows selected , which means something adjustment needed
because actually there is some data missing
0
PortletPaulfreelancerCommented:
without the ability to work against your tables it is not feasible to suggest why there are no rows.

You should have by now the experience to troubleshoot this. basically you start "piece by piece" until a problem is identified then figure out why that is a problem

try this to start with, do those tables join at all? (note the inner join!)

SELECT
        TC.DATE_TIME_START
FROM calendar_table TC
      INNER JOIN (
                  SELECT /* DISTINCT */ /* distinct MIGHT help */
                        dob
                  FROM cr_gnditem
                  ) CG ON TC.DATE_TIME_START = CG.dob
WHERE (TC.date_time_start BETWEEN to_date('01-jan-14','DD-MMM-YY') AND sysdate) /*FIX THIS DATE ISSUE */

Open in new window

Please do not rely on me being online all night (it is evening for me)
0
NiceMan331Author Commented:
yes it gives a lot of rows as result  , but we have to consider that might be a couple of transactions per store per day
what we have to do is : to summarize the transactions per store / per day first , then to get the missing days by comparing it with calendar table
0
PortletPaulfreelancerCommented:
Very good now make that a left join and add this to the where clause:

AND CG.dob IS NULL

in other words, does it find the missing dates?
0
NiceMan331Author Commented:
make that a left join

sorry , i don't know how to do it
0
PortletPaulfreelancerCommented:
yes you do....

simply change INNER to LEFT

this you definitely should know how to do, you should learn by doing, getting answers fed to you does not make you learn
0
NiceMan331Author Commented:
ok , good , it gives correct result for one store number (unit)
now i added to get unit number ( equal store_no )
but it gives error that s.unit is invalid identifier
SELECT
 S.unit,
        TC.DATE_TIME_START
FROM calendar_table TC
      left JOIN (
                  SELECT /* DISTINCT */ /* distinct MIGHT help */
                        dob
                  FROM cr_gnditem s where s.unit in
                  (SELECT
                        str_no
                  FROM cr_store 
                  WHERE opdate > to_date('2013-12-28','YYYY-MM-DD'))
                  ) CG ON TC.DATE_TIME_START = CG.dob
WHERE (TC.date_time_start BETWEEN to_date('2014-03-06','YYYY-MM-DD') AND sysdate-1) /*FIX THIS DATE ISSUE */
AND CG.dob IS NULL
order by cg.dob;

Open in new window

0
PortletPaulfreelancerCommented:
Well the reason for the error should be obvious to you

If you are asked to go to the shops for bread and milk but come home with bread only
Then: When you are asked to pass the milk

Your error message is: "ooops I forgot the milk"

It is the same here, you have not put s.unit into the shopping bag so it cannot be provided

SELECT  S.unit,         TC.DATE_TIME_STARTFROM calendar_table TC       left JOIN (
SELECT           dob --<< here is the shopping bag
...
0
PortletPaulfreelancerCommented:
But there is a deeper problem. You are seeking dates where there is NO MATCH to dob. So if there is NO MATCH to dob there is also NO MATCH to s.unit

Look at the last line of the where clause again "AND CG.dob IS NULL" hence:
if CG.dob is NULL then CG.unit will also be ____________ ?

So, please try this:
WIH CTE 
AS (
                  SELECT
                        str_no
                  FROM cr_store
                  WHERE opdate > to_date('2013-12-28', 'YYYY-MM-DD')
   )
SELECT
      CJ.str_no
    , TC.DATE_TIME_START
FROM calendar_table TC
      LEFT JOIN (
            SELECT
                  dob
            FROM cr_gnditem S
            WHERE S.unit IN ( SELECT str_no FROM CTE )
      ) CG ON TC.DATE_TIME_START = CG.dob
CROSS JOIN ( SELECT str_no FROM CTE ) CJ
WHERE (TC.date_time_start BETWEEN to_date('2014-03-06', 'YYYY-MM-DD') AND sysdate - 1) 
AND CG.dob IS NULL
ORDER BY CG.dob;

Open in new window

0
PortletPaulfreelancerCommented:
oh, and also think about how the query will sort by CG.dob if CG.dob IS NULL
0
NiceMan331Author Commented:
sorry for delay
i just attend the office
i try it , but it gives invalid error at the second line "AS"
0
PortletPaulfreelancerCommented:
I misspelled WITH
line 1
0
NiceMan331Author Commented:
so , shall i remove "WITH" ?
0
PortletPaulfreelancerCommented:
No. CoĊ•rect the spelling.
0
NiceMan331Author Commented:
i correct it
then it ask to start with "BEGIN"
i added begin at the beggining of the statement and END to its end
then it sent this error
line 2 cloumn 1
 an INTO clause is expected in this SELECT statement
0
johnsoneSenior Oracle DBACommented:
Why do you need a BEGIN?  From what I understand of the query given by PortletPaul, I don't see a need for one.
0
NiceMan331Author Commented:
because it run (using Toad) giving now raw selected , labeling red colour underline word "with" , suggesting to use word "begin" instead
0
johnsoneSenior Oracle DBACommented:
Changing WITH to BEGIN is going to make it worse.  You need WITH for the query to run correctly.

Also, if it is returning no rows, you should be looking at the subqueries as already suggested to be sure they are returning what you expect.

Do you have a really old version of TOAD?  Support of WITH is a more or less recent thing in Oracle and a really old version of TOAD may not know what that is.  Have you tried running the query with SQL*Plus?
0
NiceMan331Author Commented:
i'm using the latest version of Toad , 12.5
0
PortletPaulfreelancerCommented:
WITH .... AS ( .... )

In Oracle documentation this is known as "subquery factoring"; to most of the SQL world however it is known as "Common Table Expression" (CTE)

It is a standard feature in Oracle SQL (it does NOT require PL/SQL)

The REASON for suggesting it was to avoid some repetition.

This will be my final attempt. Please try solving any issues this may produce as I am currently unwell and won't be monitoring.
SELECT
      CJ.str_no
    , TC.DATE_TIME_START
FROM calendar_table TC
      LEFT JOIN (
            SELECT
                  dob
            FROM cr_gnditem S
            WHERE S.unit IN ( 
                                  /* WITH WOULD SOLVE THIS REPETITION */
                                  SELECT
                                        str_no
                                  FROM cr_store
                                  WHERE opdate > to_date('2013-12-28', 'YYYY-MM-DD')
                            )
      ) CG ON TC.DATE_TIME_START = CG.dob
CROSS JOIN ( 
                                  /* WITH WOULD SOLVE THIS REPETITION */
                                  SELECT
                                        str_no
                                  FROM cr_store
                                  WHERE opdate > to_date('2013-12-28', 'YYYY-MM-DD')
           ) CJ
WHERE (TC.date_time_start BETWEEN to_date('2014-03-06', 'YYYY-MM-DD') AND sysdate - 1) 
AND CG.dob IS NULL
ORDER BY CG.dob
; 

Open in new window

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
NiceMan331Author Commented:
it also gives no result
0
PortletPaulfreelancerCommented:
Without any data I can access myself, I simply cannot resolve "no data" - only you can do that

I have been unwell for several weeks and truly: I am not monitoring - sorry. You can use the "Request Attention" to ask for more assistance - but my advice would be to provide some sample data and an expected result that relates to that data
0
NiceMan331Author Commented:
ok , purtel , i appriciate
thanx alot
0
sdstuberCommented:
I concur with this suggestion:

provide some sample data and an expected result that relates to that data

without a test case, we're all just shooting in the dark.

give us data to work with and a clear answer to target and the process will move a long MUCH faster
0
sdstuberCommented:
also, what is your database version?  I'm still curious as to why the WITH clause caused you troubles.

WITH clauses were introduced in 9.2  (2002) and Toad supported it immediately for execution although it's formatter took a little longer.

What is the exact error message you get?  ORA-xxxxx  something?
0
NiceMan331Author Commented:
i'm out of office now , i just see your post
actually it is so simple , i should solve it my self , but my mind is out of range
let simplefy it :
if i say :
SELECT
                  unit , dob 
            FROM cr_gnditem 
group by unit , dob

Open in new window


this should give me every day transactions per unit ( dob = trans_date)
in fact , every unit should have at least one transaction every day
ok , till now it is clear , right ?
now , i just want to know if any unit has no transaction in between range of dates
that is all
if we success , we will just add a small condition
where unit in(SELECT
                                        str_no
                                  FROM cr_store
                                  WHERE opdate > to_date('2013-12-28', 'YYYY-MM-DD'))

Open in new window

because the units is too much , we just want a small catigory of it
0
sdstuberCommented:
>>> ok , till now it is clear , right ?

no, sample data will help a lot though to clear things up.

I think this is what you "might" be looking for in your missing dates

this will return units that do not have DOB values between your min and max dates

  SELECT unit
    FROM cr_gnditem
GROUP BY unit
  HAVING COUNT(CASE WHEN dob >= :min_date AND dob <= :max_date THEN 1 END) = 0


again though, please provide sample data and expected output.
You only have 3 tables, just create a few rows for each along with the expected output.
It should take very little time on your part and will GREATLY expedite getting you a working answer
0
NiceMan331Author Commented:
ok , look at the attached file
i post some sample transactions
for units between 1 to 3
and trans_date between 01-03-2014 and 09-03-2014
the expected result is
day 07-03-2014 for unit 01
and day 09-03-2014 for unit 03
unit 02 has no missing data
sample.xlsx
0
NiceMan331Author Commented:
hi
actually i done
1st of all , the simple statement like this
SELECT st.str_no,tc.DATE_TIME_START
FROM calendar_table tc, cr_store st
  and st.str_no||tc.DATE_TIME_START
not IN
(select b.unit||b.dob from cr_gnditem b
group by b.unit,b.dob)
order by st.str_no,tc.DATE_TIME_START;

Open in new window

then , i added the specific conditions to the selected units & dates like this



SELECT st.str_no,tc.DATE_TIME_START
FROM calendar_table tc, cr_store st
 WHERE 
 st.str_no in(select de.str_no
 from cr_store de where de.opdate > to_date('2013-12-28', 'YYYY-MM-DD'))
 and  tc.date_time_start between ST.OPDATE and sysdate-1
 and st.str_no||tc.DATE_TIME_START
not IN
(select b.unit||b.dob from cr_gnditem b where
b.unit in(select de.str_no
 from cr_store de where de.opdate > to_date('2013-12-28', 'YYYY-MM-DD'))
 and b.dob between '01-jan-14' and sysdate-1
group by b.unit,b.dob
having sum(quantity * price) > 0
)
order by st.str_no,tc.DATE_TIME_START;

Open in new window


it gives correct result
now for closing the question , i really appreciate the effort of paurtuel
i can accept his solution as i benefit from his post
but is it ok for EE , and for other members who may search for this question , if i accept a solution which not really the correct one
0
sdstuberCommented:
don't do this...

 and st.str_no||tc.DATE_TIME_START
not IN
(select b.unit||b.dob from cr_gnditem b
group by b.unit,b.dob)

Open in new window


do this instead

 and (st.str_no, tc.DATE_TIME_START)
not IN
(select b.unit, b.dob from cr_gnditem b)

Open in new window



use sets instead of string concatenation - it's more effecient, cleaner and allows for index usage where it can
also, don't use GROUP BY when you only need a DISTINCT,  use GROUP BY when you have aggregates (like count, max,min,avg, etc)

In this case neither GROUP BY nor DISTINCT were correct though, because you had an IN clause which makes the DISTINCT superfluous,  GROUP BY on the other hand, might incur extra work for zero functionality.

In other words,  trying to make  a big IN-list with duplicates smaller by making it unique doesn't help.
0
NiceMan331Author Commented:
ok , thanx
in this regard , i would give substubar 100 point
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.