Solved

Find missed day & store

Posted on 2014-11-10
43
166 Views
Last Modified: 2014-12-04
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
0
Comment
Question by:NiceMan331
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 19
  • 15
  • 4
  • +1
43 Comments
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40434390
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
 

Author Comment

by:NiceMan331
ID: 40434435
ok , but i still not get the  "i" in the query , which refere to store number
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40434629
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 35

Expert Comment

by:johnsone
ID: 40434774
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
 

Author Comment

by:NiceMan331
ID: 40434818
error at select , after loop
 an INTO clause is expected in this SELECT statement

Open in new window

0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40434983
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
 
LVL 35

Expert Comment

by:johnsone
ID: 40436373
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
 

Author Comment

by:NiceMan331
ID: 40436675
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40436686
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
 

Author Comment

by:NiceMan331
ID: 40436727
error message
date format not recognized
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40436808
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
 

Author Comment

by:NiceMan331
ID: 40436895
yes , it works
but it gives no rows selected , which means something adjustment needed
because actually there is some data missing
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40436912
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
 

Author Comment

by:NiceMan331
ID: 40437027
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40437040
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
 

Author Comment

by:NiceMan331
ID: 40437061
make that a left join

sorry , i don't know how to do it
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40437064
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
 

Author Comment

by:NiceMan331
ID: 40437217
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40439080
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40439089
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40439091
oh, and also think about how the query will sort by CG.dob if CG.dob IS NULL
0
 

Author Comment

by:NiceMan331
ID: 40445271
sorry for delay
i just attend the office
i try it , but it gives invalid error at the second line "AS"
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40446183
I misspelled WITH
line 1
0
 

Author Comment

by:NiceMan331
ID: 40447048
so , shall i remove "WITH" ?
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40449262
No. Coŕrect the spelling.
0
 

Author Comment

by:NiceMan331
ID: 40449560
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
 
LVL 35

Expert Comment

by:johnsone
ID: 40449631
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
 

Author Comment

by:NiceMan331
ID: 40449649
because it run (using Toad) giving now raw selected , labeling red colour underline word "with" , suggesting to use word "begin" instead
0
 
LVL 35

Expert Comment

by:johnsone
ID: 40449685
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
 

Author Comment

by:NiceMan331
ID: 40450507
i'm using the latest version of Toad , 12.5
0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 400 total points
ID: 40451252
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
 

Author Comment

by:NiceMan331
ID: 40475285
it also gives no result
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40475725
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
 

Author Comment

by:NiceMan331
ID: 40475738
ok , purtel , i appriciate
thanx alot
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40475771
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 40475785
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
 

Author Comment

by:NiceMan331
ID: 40476657
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 40476678
>>> 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
 

Author Comment

by:NiceMan331
ID: 40476725
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
 

Author Comment

by:NiceMan331
ID: 40477945
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
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 100 total points
ID: 40478404
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
 

Author Comment

by:NiceMan331
ID: 40480318
ok , thanx
in this regard , i would give substubar 100 point
thanx
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to recover a database from a user managed backup

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question