Spool

[SHIVANAND BHARTI]

Hi folks ... I have a query with bind variable which accepts date.
How can I run thais query for 30 different dates and get it's output in a file.

I can write the same query 30 times with different date and run it as ascript.
Or write a PL/SQL block.

But it is an one time thing and I am looking for something quick .
Is there an option in Toad by which I can spool out the output of a query for different input for the same bind variable ?

For example how can I run this query for 30 different dates and spool it's output.

SELECT :RunDate ,
( SELECT COUNT(*)
FROM store
WHERE pstore_open_date =:RunDate ) store_count
,( SELECT COUNT(*)
FROM wh
WHERE wh_open_date =:RunDate ) ) wh_count
FROM dual;


Thanks,
Shiva



-------------------------------------------------------------------------------------

NOTE: This e-mail message contains PRIVILEGED and CONFIDENTIAL information and is intended only for the use of
the specific individual or individuals to which it is addressed. If you are not an intended recipient of this
e-mail, you are hereby notified that any unauthorized use, dissemination or copying of this e-mail or the
information contained herein or attached hereto is strictly prohibited. If you receive this e-mail in error,
notify the person named above by reply e-mail and please delete it. Thank you.

[gene.l.bradley]

How are you determining what the dates are.

You could put the dates in a temporary table
and then say where dateVariable in (select myDate from temp_table);

**
Hi folks … I have a query with bind variable which accepts date.****
How can I run thais query for 30 different dates and get it’s output in a
file.****
** **
I can write the same query 30 times with different date and run it as
ascript.****
Or write a PL/SQL block.****
** **
But it is an one time thing and I am looking for something quick .****
Is there an option in Toad by which I can spool out the output of a query
for different input for the same bind variable ?****
** **
For example how can I run this query for 30 different dates and spool
it’s output.****
** **
SELECT :RunDate ,****
( SELECT COUNT(*) ****
FROM store ****
WHERE pstore_open_date =:RunDate ) store_count ****
,( SELECT COUNT(*) ****
FROM wh ****
WHERE wh_open_date =:RunDate ) ) wh_count****
FROM dual;****
** **
** **
Thanks,****
Shiva****
** **
-------------------------------------------------------------------------------------
NOTE: This e-mail message contains PRIVILEGED and CONFIDENTIAL information
and is intended only for the use of the specific individual or individuals
to which it is addressed. If you are not an intended recipient of this
e-mail, you are hereby notified that any unauthorized use, dissemination or
copying of this e-mail or the information contained herein or attached
hereto is strictly prohibited. If you receive this e-mail in error, notify
the person named above by reply e-mail and please delete it. Thank you.

--
Gene L. Bradley Jr.
Software Developer I
Information Systems & Integration
Jackson State University
1400 J R Lynch Street
P.O. Box 17750
Jackson, MS 39217

ph 601.979.1042
fax [phone]
email ***@jsums.edu

[SHIVANAND BHARTI]

To keep it simple let's say last 30 days ... sysdate, sysdate - 1 , sysdate -2,...,sysdate-29

Thanks,
Shiva

From: ***@yahoogroups.com [mailto:***@yahoogroups.com] On Behalf Of gene.l.bradley
Sent: Monday, July 22, 2013 1:43 PM
To: ***@yahoogroups.com
Subject: Re: [toad] Spool


How are you determining what the dates are.

You could put the dates in a temporary table
and then say where dateVariable in (select myDate from temp_table);

On Mon, Jul 22, 2013 at 12:31 PM, SHIVANAND BHARTI <***@familydollar.com<mailto:***@familydollar.com>> wrote:

Hi folks ... I have a query with bind variable which accepts date.
How can I run thais query for 30 different dates and get it's output in a file.

I can write the same query 30 times with different date and run it as ascript.
Or write a PL/SQL block.

But it is an one time thing and I am looking for something quick .
Is there an option in Toad by which I can spool out the output of a query for different input for the same bind variable ?

For example how can I run this query for 30 different dates and spool it's output.

SELECT :RunDate ,
( SELECT COUNT(*)
FROM store
WHERE pstore_open_date =:RunDate ) store_count
,( SELECT COUNT(*)
FROM wh
WHERE wh_open_date =:RunDate ) ) wh_count
FROM dual;


Thanks,
Shiva


-------------------------------------------------------------------------------------

NOTE: This e-mail message contains PRIVILEGED and CONFIDENTIAL information and is intended only for the use of the specific individual or individuals to which it is addressed. If you are not an intended recipient of this e-mail, you are hereby notified that any unauthorized use, dissemination or copying of this e-mail or the information contained herein or attached hereto is strictly prohibited. If you receive this e-mail in error, notify the person named above by reply e-mail and please delete it. Thank you.



--
Gene L. Bradley Jr.
Software Developer I
Information Systems & Integration
Jackson State University
1400 J R Lynch Street
P.O. Box 17750
Jackson, MS 39217

ph 601.979.1042
fax [phone]
email ***@jsums.edu<mailto:***@jsums.edu>




-------------------------------------------------------------------------------------

NOTE: This e-mail message contains PRIVILEGED and CONFIDENTIAL information and is intended only for the use of
the specific individual or individuals to which it is addressed. If you are not an intended recipient of this
e-mail, you are hereby notified that any unauthorized use, dissemination or copying of this e-mail or the
information contained herein or attached hereto is strictly prohibited. If you receive this e-mail in error,
notify the person named above by reply e-mail and please delete it. Thank you.

[Simoneau, Roger]

In that case, since it's also expected to be a one-time script:

Add the date to your select statements and group by the date.

Select pstore_open_date, count(*)
From store
Where pstore_date between trunk(sysdate) - 30 and trunc(sysdate)
Group by pstore_open_date

Roger A. Simoneau
Systems Analyst
Alberta Blue Cross
[phone]

From: ***@yahoogroups.com [mailto:***@yahoogroups.com] On Behalf Of SHIVANAND BHARTI
Sent: Monday, July 22, 2013 12:08 PM
To: ***@yahoogroups.com
Subject: RE: [toad] Spool


To keep it simple let's say last 30 days ... sysdate, sysdate - 1 , sysdate -2,...,sysdate-29

Thanks,
Shiva

From: ***@yahoogroups.com<mailto:***@yahoogroups.com> [mailto:***@yahoogroups.com] On Behalf Of gene.l.bradley
Sent: Monday, July 22, 2013 1:43 PM
To: ***@yahoogroups.com<mailto:***@yahoogroups.com>
Subject: Re: [toad] Spool


How are you determining what the dates are.

You could put the dates in a temporary table
and then say where dateVariable in (select myDate from temp_table);

On Mon, Jul 22, 2013 at 12:31 PM, SHIVANAND BHARTI <***@familydollar.com<mailto:***@familydollar.com>> wrote:

Hi folks ... I have a query with bind variable which accepts date.
How can I run thais query for 30 different dates and get it's output in a file.

I can write the same query 30 times with different date and run it as ascript.
Or write a PL/SQL block.

But it is an one time thing and I am looking for something quick .
Is there an option in Toad by which I can spool out the output of a query for different input for the same bind variable ?

For example how can I run this query for 30 different dates and spool it's output.

SELECT :RunDate ,
( SELECT COUNT(*)
FROM store
WHERE pstore_open_date =:RunDate ) store_count
,( SELECT COUNT(*)
FROM wh
WHERE wh_open_date =:RunDate ) ) wh_count
FROM dual;


Thanks,
Shiva


-------------------------------------------------------------------------------------

NOTE: This e-mail message contains PRIVILEGED and CONFIDENTIAL information and is intended only for the use of the specific individual or individuals to which it is addressed. If you are not an intended recipient of this e-mail, you are hereby notified that any unauthorized use, dissemination or copying of this e-mail or the information contained herein or attached hereto is strictly prohibited. If you receive this e-mail in error, notify the person named above by reply e-mail and please delete it. Thank you.



--
Gene L. Bradley Jr.
Software Developer I
Information Systems & Integration
Jackson State University
1400 J R Lynch Street
P.O. Box 17750
Jackson, MS 39217

ph 601.979.1042
fax [phone]
email ***@jsums.edu<mailto:***@jsums.edu>


-------------------------------------------------------------------------------------

NOTE: This e-mail message contains PRIVILEGED and CONFIDENTIAL information and is intended only for the use of the specific individual or individuals to which it is addressed. If you are not an intended recipient of this e-mail, you are hereby notified that any unauthorized use, dissemination or copying of this e-mail or the information contained herein or attached hereto is strictly prohibited. If you receive this e-mail in error, notify the person named above by reply e-mail and please delete it. Thank you.


________________________________
This communication, including any attached documentation, is intended only for the person or entity to which it is addressed, and may contain confidential, personal and/or privileged information. Any unauthorized disclosure, copying, or taking action on the contents is strictly prohibited. If you have received this message in error, please contact us immediately so we may correct our records. Please then delete or destroy the original transmission and any subsequent reply.

[Gregory Liss]

Maybe something like this:


SET TIMING OFF
SET SERVEROUTPUT ON
SET TERMOUT OFF
SET VERIFY OFF
SET HEADING OFF
SET ECHO OFF
SET FEEDBACK OFF

-- build temp file to run all from
SPOOL c:\temp\run_all.sql

BEGIN
FOR counter IN 0 .. 29
LOOP
-- create spool file to output each item to
DBMS_OUTPUT.put_line ( 'spool c:\temp\file_' || TO_CHAR (SYSDATE - counter, 'yymmdd') || '.txt');
-- substitute your query here
DBMS_OUTPUT.put_line ('select TO_DATE(''' || TO_CHAR (SYSDATE - counter) || ''') from dual;');
-- close the spool
DBMS_OUTPUT.put_line ('Spool off');
END LOOP;
END;
-- close temp file spool
SPOOL OFF;
-- run temp file
@c:\temp\run_all.sql
/

Post by SHIVANAND BHARTI
To keep it simple let's say last 30 days ... sysdate, sysdate - 1 , sysdate -2,...,sysdate-29
Thanks,
Shiva
Sent: Monday, July 22, 2013 1:43 PM
Subject: Re: [toad] Spool
How are you determining what the dates are.
You could put the dates in a temporary table
and then say where dateVariable in (select myDate from temp_table);
Hi folks ... I have a query with bind variable which accepts date.
How can I run thais query for 30 different dates and get it's output in a file.
I can write the same query 30 times with different date and run it as ascript.
Or write a PL/SQL block.
But it is an one time thing and I am looking for something quick .
Is there an option in Toad by which I can spool out the output of a query for different input for the same bind variable ?
For example how can I run this query for 30 different dates and spool it's output.
SELECT :RunDate ,
( SELECT COUNT(*)
FROM store
WHERE pstore_open_date =:RunDate ) store_count
,( SELECT COUNT(*)
FROM wh
WHERE wh_open_date =:RunDate ) ) wh_count
FROM dual;
Thanks,
Shiva
-------------------------------------------------------------------------------------
NOTE: This e-mail message contains PRIVILEGED and CONFIDENTIAL information and is intended only for the use of the specific individual or individuals to which it is addressed. If you are not an intended recipient of this e-mail, you are hereby notified that any unauthorized use, dissemination or copying of this e-mail or the information contained herein or attached hereto is strictly prohibited. If you receive this e-mail in error, notify the person named above by reply e-mail and please delete it. Thank you.
--
Gene L. Bradley Jr.
Software Developer I
Information Systems & Integration
Jackson State University
1400 J R Lynch Street
P.O. Box 17750
Jackson, MS 39217
ph 601.979.1042
fax [phone]
-------------------------------------------------------------------------------------
NOTE: This e-mail message contains PRIVILEGED and CONFIDENTIAL information and is intended only for the use of
the specific individual or individuals to which it is addressed. If you are not an intended recipient of this
e-mail, you are hereby notified that any unauthorized use, dissemination or copying of this e-mail or the
information contained herein or attached hereto is strictly prohibited. If you receive this e-mail in error,
notify the person named above by reply e-mail and please delete it. Thank you.

------------------------------------

Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/toad/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/toad/join
(Yahoo! ID required)

<*> To change settings via email:
toad-***@yahoogroups.com
toad-***@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
toad-***@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/

[SHIVANAND BHARTI]

This is what I was looking for.

Thanks,
Shiva

From: ***@yahoogroups.com [mailto:***@yahoogroups.com] On Behalf Of Gregory Liss
Sent: Monday, July 22, 2013 3:00 PM
To: ***@yahoogroups.com
Subject: [toad] Re: Spool



Maybe something like this:

SET TIMING OFF
SET SERVEROUTPUT ON
SET TERMOUT OFF
SET VERIFY OFF
SET HEADING OFF
SET ECHO OFF
SET FEEDBACK OFF

-- build temp file to run all from
SPOOL c:\temp\run_all.sql

BEGIN
FOR counter IN 0 .. 29
LOOP
-- create spool file to output each item to
DBMS_OUTPUT.put_line ( 'spool c:\temp\file_' || TO_CHAR (SYSDATE - counter, 'yymmdd') || '.txt');
-- substitute your query here
DBMS_OUTPUT.put_line ('select TO_DATE(''' || TO_CHAR (SYSDATE - counter) || ''') from dual;');
-- close the spool
DBMS_OUTPUT.put_line ('Spool off');
END LOOP;
END;
-- close temp file spool
SPOOL OFF;
-- run temp file
@c:\temp\run_all.sql
/

Post by SHIVANAND BHARTI
To keep it simple let's say last 30 days ... sysdate, sysdate - 1 , sysdate -2,...,sysdate-29
Thanks,
Shiva
Sent: Monday, July 22, 2013 1:43 PM
Subject: Re: [toad] Spool
How are you determining what the dates are.
You could put the dates in a temporary table
and then say where dateVariable in (select myDate from temp_table);
Hi folks ... I have a query with bind variable which accepts date.
How can I run thais query for 30 different dates and get it's output in a file.
I can write the same query 30 times with different date and run it as ascript.
Or write a PL/SQL block.
But it is an one time thing and I am looking for something quick .
Is there an option in Toad by which I can spool out the output of a query for different input for the same bind variable ?
For example how can I run this query for 30 different dates and spool it's output.
SELECT :RunDate ,
( SELECT COUNT(*)
FROM store
WHERE pstore_open_date =:RunDate ) store_count
,( SELECT COUNT(*)
FROM wh
WHERE wh_open_date =:RunDate ) ) wh_count
FROM dual;
Thanks,
Shiva
----------------------------------------------------------
NOTE: This e-mail message contains PRIVILEGED and CONFIDENTIAL information and is intended only for the use of the specific individual or individuals to which it is addressed. If you are not an intended recipient of this e-mail, you are hereby notified that any unauthorized use, dissemination or copying of this e-mail or the information contained herein or attached hereto is strictly prohibited. If you receive this e-mail in error, notify the person named above by reply e-mail and please delete it. Thank you.
--
Gene L. Bradley Jr.
Software Developer I
Information Systems & Integration
Jackson State University
1400 J R Lynch Street
P.O. Box 17750
Jackson, MS 39217
ph 601.979.1042
fax [phone]
----------------------------------------------------------
NOTE: This e-mail message contains PRIVILEGED and CONFIDENTIAL information and is intended only for the use of
the specific individual or individuals to which it is addressed. If you are not an intended recipient of this
e-mail, you are hereby notified that any unauthorized use, dissemination or copying of this e-mail or the
information contained herein or attached hereto is strictly prohibited. If you receive this e-mail in error,
notify the person named above by reply e-mail and please delete it. Thank you.

-------------------------------------------------------------------------------------

NOTE: This e-mail message contains PRIVILEGED and CONFIDENTIAL information and is intended only for the use of
the specific individual or individuals to which it is addressed. If you are not an intended recipient of this
e-mail, you are hereby notified that any unauthorized use, dissemination or copying of this e-mail or the
information contained herein or attached hereto is strictly prohibited. If you receive this e-mail in error,
notify the person named above by reply e-mail and please delete it. Thank you.

[smlivesay]

Try this:

SELECT s1.RunDate, s1.store_count, s2.wh_count
FROM (SELECT TRUNC(pstore_open_date) RunDate, COUNT(*) store_count
FROM store
WHERE TRUNC(pstore_open_date) BETWEEN TRUNC(sysdate-30) AND TRUNC(sysdate)
GROUP BY TRUNC(pstore_open_date)
) s1,
(SELECT TRUNC(wh_open_date) RunDate, COUNT(*) wh_count
FROM wh
WHERE TRUNC(wh_open_date) BETWEEN TRUNC(sysdate-30) AND TRUNC(sysdate)
GROUP BY TRUNC(wh_open_date)
) s2
WHERE s1.RunDate = s2.RunDate
ORDER BY 1;


One issue - there will not be an entry for any date that does not have a row in one of the tables for that date.

Mike Livesay

Post by SHIVANAND BHARTI
To keep it simple let's say last 30 days ... sysdate, sysdate - 1 , sysdate -2,...,sysdate-29
Thanks,
Shiva
Sent: Monday, July 22, 2013 1:43 PM
Subject: Re: [toad] Spool
How are you determining what the dates are.
You could put the dates in a temporary table
and then say where dateVariable in (select myDate from temp_table);
Hi folks ... I have a query with bind variable which accepts date.
How can I run thais query for 30 different dates and get it's output in a file.
I can write the same query 30 times with different date and run it as ascript.
Or write a PL/SQL block.
But it is an one time thing and I am looking for something quick .
Is there an option in Toad by which I can spool out the output of a query for different input for the same bind variable ?
For example how can I run this query for 30 different dates and spool it's output.
SELECT :RunDate ,
( SELECT COUNT(*)
FROM store
WHERE pstore_open_date =:RunDate ) store_count
,( SELECT COUNT(*)
FROM wh
WHERE wh_open_date =:RunDate ) ) wh_count
FROM dual;
Thanks,
Shiva
-------------------------------------------------------------------------------------
NOTE: This e-mail message contains PRIVILEGED and CONFIDENTIAL information and is intended only for the use of the specific individual or individuals to which it is addressed. If you are not an intended recipient of this e-mail, you are hereby notified that any unauthorized use, dissemination or copying of this e-mail or the information contained herein or attached hereto is strictly prohibited. If you receive this e-mail in error, notify the person named above by reply e-mail and please delete it. Thank you.
--
Gene L. Bradley Jr.
Software Developer I
Information Systems & Integration
Jackson State University
1400 J R Lynch Street
P.O. Box 17750
Jackson, MS 39217
ph 601.979.1042
fax [phone]
-------------------------------------------------------------------------------------
NOTE: This e-mail message contains PRIVILEGED and CONFIDENTIAL information and is intended only for the use of
the specific individual or individuals to which it is addressed. If you are not an intended recipient of this
e-mail, you are hereby notified that any unauthorized use, dissemination or copying of this e-mail or the
information contained herein or attached hereto is strictly prohibited. If you receive this e-mail in error,
notify the person named above by reply e-mail and please delete it. Thank you.

------------------------------------

Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/toad/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/toad/join
(Yahoo! ID required)

<*> To change settings via email:
toad-***@yahoogroups.com
toad-***@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
toad-***@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/

[mlerch2002]

Hi Shiva-

Please create an account with ToadWorld.com and move future posts there. This Yahoo Group will soon be close permanently.

thanks
Mark

Post by SHIVANAND BHARTI
Hi folks ... I have a query with bind variable which accepts date.
How can I run thais query for 30 different dates and get it's output in a file.
I can write the same query 30 times with different date and run it as ascript.
Or write a PL/SQL block.
But it is an one time thing and I am looking for something quick .
Is there an option in Toad by which I can spool out the output of a query for different input for the same bind variable ?
For example how can I run this query for 30 different dates and spool it's output.
SELECT :RunDate ,
( SELECT COUNT(*)
FROM store
WHERE pstore_open_date =:RunDate ) store_count
,( SELECT COUNT(*)
FROM wh
WHERE wh_open_date =:RunDate ) ) wh_count
FROM dual;
Thanks,
Shiva
-------------------------------------------------------------------------------------
NOTE: This e-mail message contains PRIVILEGED and CONFIDENTIAL information and is intended only for the use of
the specific individual or individuals to which it is addressed. If you are not an intended recipient of this
e-mail, you are hereby notified that any unauthorized use, dissemination or copying of this e-mail or the
information contained herein or attached hereto is strictly prohibited. If you receive this e-mail in error,
notify the person named above by reply e-mail and please delete it. Thank you.

------------------------------------

Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/toad/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/toad/join
(Yahoo! ID required)

<*> To change settings via email:
toad-***@yahoogroups.com
toad-***@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
toad-***@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/