<div>
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 
 

<pre><code class="code-10 nolinks" id="code-20-40434390-1">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
;
</code></pre>
 
There MIGHT be an advantage in using DISTINCT for the subquery:
<pre><code class="code-10 nolinks" id="code-20-40434390-2">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
;
</code></pre>
</div>

<div>
ok , but i still not get the &nbsp;&quot;i&quot; in the query , which refere to store number
</div>

<div>
Your cursor for store number is the equivalent of a cross join as far as I can see 

<pre><code class="code-10 nolinks" id="code-20-40434629-1">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 &gt; 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
;
</code></pre>
</div>

<div>
The variable i that you are referencing is a record, not a single value. &nbsp;You would need to reference it in your query as i.str_no.
</div>

<div>
error at select , after loop 

<pre><code class="code-1 nolinks" id="code-20-40434818-1"> an INTO clause is expected in this SELECT statement
</code></pre>
</div>

<div>
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.
</div>

<div>
The message is coming from your original code. &nbsp;You have a select statement in a PL/SQL block. &nbsp;You need to select that into something. &nbsp;Variables should be declared to hold the values you are selecting.
</div>

<div>
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 ?
</div>

<div>
You do not need PL/SQL for this, just run the SQL query I provided
<pre><code class="code-10 nolinks" id="code-20-40436686-1">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 &gt; 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
;
</code></pre>
</div>

<div>
error message 
date format not recognized
</div>

<div>
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 &gt;&nbsp;to_date('2013-12-28','YYYY-MM-DD') 
 
&amp;&nbsp;treat the other date literal the same way
</div>

<div>
yes , it works 
but it gives no rows selected , which means something adjustment needed 
because actually there is some data missing
</div>

<div>
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 &quot;piece by piece&quot; 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!) 
 

<pre><code class="code-1 nolinks" id="code-20-40436912-1">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 */
</code></pre>
Please do not rely on me being online all night (it is evening for me)
</div>

<div>
yes it gives a lot of rows as result &nbsp;, 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
</div>

<div>
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?
</div>

<div>
<blockquote>
make that a left join 
</blockquote>
 
sorry , i don't know how to do it
</div>

<div>
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
</div>

<div>
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 

<pre><code class="code-10 nolinks" id="code-20-40437217-1">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 &gt; 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;
</code></pre>
</div>

<div>
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: &quot;ooops I forgot the milk&quot; 
 
It is the same here, you have not put s.unit into the shopping bag so it cannot be provided 
 
SELECT &nbsp;S.unit, &nbsp; &nbsp; &nbsp; &nbsp; TC.DATE_TIME_STARTFROM calendar_table TC &nbsp; &nbsp; &nbsp; left JOIN ( 
SELECT &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; dob --&lt;&lt; here is the shopping bag 
...
</div>

<div>
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 &quot;AND CG.dob IS NULL&quot; hence: 
if CG.dob is NULL then CG.unit will also be ____________ ? 
 
So, please try this:
<pre><code class="code-10 nolinks" id="code-20-40439089-1">WIH CTE 
AS (
 SELECT
 str_no
 FROM cr_store
 WHERE opdate &gt; 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;
</code></pre>
</div>

<div>
oh, and also think about how the query will sort by CG.dob if CG.dob IS NULL
</div>

<div>
sorry for delay 
i just attend the office 
i try it , but it gives invalid error at the second line &quot;AS&quot;
</div>

<div>
I misspelled WITH 
line 1
</div>

<div>
so , shall i remove &quot;WITH&quot; ?
</div>

<div>
i correct it 
then it ask to start with &quot;BEGIN&quot; 
i added begin at the beggining of the statement and END to its end 
then it sent this error 
line 2 cloumn 1 
&nbsp;an INTO clause is expected in this SELECT statement
</div>

<div>
Why do you need a BEGIN? &nbsp;From what I understand of the query given by PortletPaul, I don't see a need for one.
</div>

<div>
because it run (using Toad) giving now raw selected , labeling red colour underline word &quot;with&quot; , suggesting to use word &quot;begin&quot; instead
</div>

<div>
Changing WITH to BEGIN is going to make it worse. &nbsp;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? &nbsp;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. &nbsp;Have you tried running the query with SQL*Plus?
</div>

<div>
i'm using the latest version of Toad , 12.5
</div>

<div>
Without any data I can access myself, I simply cannot resolve &quot;no data&quot; - only you can do that 
 
I have been unwell for several weeks and truly: I am not monitoring - sorry. You can use the &quot;Request Attention&quot; to ask for more assistance - but my advice would be to provide some sample data and an expected result that relates to that data
</div>

<div>
ok , purtel , i appriciate 
thanx alot
</div>

<div>
I concur with this suggestion: 
 

<blockquote>
provide some sample data and an expected result that relates to that data
</blockquote>
 
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
</div>

<div>
also, what is your database version? &nbsp;I'm still curious as to why the WITH clause caused you troubles. 
 
WITH clauses were introduced in 9.2 &nbsp;(2002) and Toad supported it immediately for execution although it's formatter took a little longer. 
 
What is the exact error message you get? &nbsp;ORA-xxxxx &nbsp;something?
</div>

<div>
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 : 

<pre><code class="code-1 nolinks" id="code-20-40476657-1">SELECT
 unit , dob 
 FROM cr_gnditem 
group by unit , dob
</code></pre>
 
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 

<pre><code class="code-1 nolinks" id="code-20-40476657-2">where unit in(SELECT
 str_no
 FROM cr_store
 WHERE opdate &gt; to_date('2013-12-28', 'YYYY-MM-DD'))
</code></pre>
because the units is too much , we just want a small catigory of it
</div>

<div>
&gt;&gt;&gt; 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 &quot;might&quot; be looking for in your missing dates 
 
this will return units that do not have DOB values between your min and max dates 
 
&nbsp; SELECT unit 
&nbsp; &nbsp; FROM cr_gnditem 
GROUP BY unit 
&nbsp; HAVING COUNT(CASE WHEN dob &gt;= :min_date AND dob &lt;= :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
</div>

<div>
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 
<a href="https://filedb.experts-exchange.com/incoming/2014/12_w49/885895/sample.xlsx" target="_blank" class="file-inline" title="sample (9 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>sample.xlsx</a>
</div>

sample.xlsx

<div>
hi 
actually i done 
1st of all , the simple statement like this 

<pre><code class="code-1 nolinks" id="code-20-40477945-1">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;
</code></pre>
then , i added the specific conditions to the selected units &amp;&nbsp;dates like this 
 
 
 

<pre><code class="code-10 nolinks" id="code-20-40477945-2">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 &gt; 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 &gt; 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) &gt; 0
)
order by st.str_no,tc.DATE_TIME_START;
</code></pre>
 
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
</div>

<div>
ok , thanx 
in this regard , i would give substubar 100 point 
thanx
</div>

<div>
<div class="content wysiwyg-content">
i previously asked this question 
<a href="https://www.experts-exchange.com/questions/28229246/get-the-missed-day.html" rel="ugc">https://www.experts-exchange.com/questions/28229246/get-the-missed-day.html</a> 
 
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 
 
 

<pre><code class="code-10 nolinks" id="code-10-28554863-1">declare
cursor c_stores is 
select a.str_no
 from cr_store a
where a.opdate &gt; '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;
</code></pre>
 
but it sent me error message that data wrong of type
</div>
</div>

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

Find missed day &amp; store

Oracle is an object-relational database management system. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Oracle also has its own enterprise modules and application server software.

Find missed day &amp; store

Find missed day & store