Re: Spool

"gene.l.bradley" <[email protected]> Mon, 22 Jul 2013 12:42:38 -0500
Newsgroups	gmane.comp.db.oracle.toad.free
Message-ID	<CAJEeFSKgAvwh9KxCNZrhZYmPzwkJCbf4D508EJ+Vyq=ya33=sw@mail.gmail.com>
--001a11c38dc0d35dd404e21d33c0
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: quoted-printable

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 <[email protected]=
m
> wrote:

> **
>
>
>  Hi folks =85 I have a query with bind variable which accepts date.****
>
> How can I run thais query for 30 different dates and get it=92s 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=92s output.****
>
> ** **
>
> SELECT  :RunDate   ,****
>
>       ( SELECT COUNT(*) ****
>
>            FROM store ****
>
>           WHERE pstore_open_date =3D:RunDate  ) store_count      ****
>
>       ,( SELECT COUNT(*) ****
>
>            FROM wh ****
>
>           WHERE wh_open_date =3D:RunDate  ) ) wh_count****
>
>   FROM dual;****
>
> ** **
>
> ** **
>
> Thanks,****
>
> Shiva****
>
> ** **
>
> -------------------------------------------------------------------------=
------------
>
>
> NOTE: This e-mail message contains PRIVILEGED and CONFIDENTIAL informatio=
n
> and is intended only for the use of the specific individual or individual=
s
> 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, notif=
y
> the person named above by reply e-mail and please delete it. Thank you.
>
>=20
>



--=20
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 601.371.9146
email [email protected]

--001a11c38dc0d35dd404e21d33c0
Content-Type: text/html; charset=windows-1252
Content-Transfer-Encoding: quoted-printable





<head>

<style type=3D"text/css">
<!--

/* start of attachment style */
       .ygrp-photo-title{
         clear: both;
         font-size: smaller;
         height: 15px;
         overflow: hidden;
         text-align: center;
         width: 75px;
       }
       div.ygrp-photo{
         background-position: center;
         background-repeat: no-repeat;
         background-color: white;
         border: 1px solid black;
         height: 62px;
         width: 62px;
       }

       div.photo-title=20
         a,
         div.photo-title a:active,
         div.photo-title a:hover,
         div.photo-title a:visited {
           text-decoration: none;=20
       }

       div.attach-table div.attach-row {
         clear: both;
       }

       div.attach-table div.attach-row div {
         float: left;
         /* margin: 2px;*/
       }

       p {
         clear: both;
         padding: 15px 0 3px 0;
	 overflow: hidden;
       }

       div.ygrp-file {
         width: 30px;
         valign: middle;
       }
       div.attach-table div.attach-row div div a {
         text-decoration: none;
       }

       div.attach-table div.attach-row div div span {
         font-weight: normal;
       }

       div.ygrp-file-title {
         font-weight: bold;
       }
 /* end of attachment style */
        -->
        </style>
        </head>
<html>
<head>
<style type=3D"text/css">
<!--
#ygrp-mkp {
  border: 1px solid #d8d8d8;
  font-family: Arial;
  margin: 10px 0;
  padding: 0 10px;
}

#ygrp-mkp hr {
  border: 1px solid #d8d8d8;
}

#ygrp-mkp #hd {
  color: #628c2a;
  font-size: 85%;
  font-weight: 700;
  line-height: 122%;
  margin: 10px 0;
}

#ygrp-mkp #ads {
  margin-bottom: 10px;
}

#ygrp-mkp .ad {
  padding: 0 0;
}

#ygrp-mkp .ad p {
  margin: 0;
}

#ygrp-mkp .ad a {
  color: #0000ff;
  text-decoration: none;
}
-->
</style>
</head>
<body>





<div dir=3D"ltr">How are you determining what the dates are.<div><br></div>=
<div>You could put the dates in a temporary table</div><div>and then say wh=
ere dateVariable in (select myDate from temp_table);</div></div><div class=
=3D"gmail_extra">
<br><br><div class=3D"gmail_quote">On Mon, Jul 22, 2013 at 12:31 PM, SHIVAN=
AND BHARTI <span dir=3D"ltr">&lt;<a href=3D"mailto:[email protected]=
" target=3D"_blank">[email protected]</a>&gt;</span> wrote:<br><bloc=
kquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #cc=
c solid;padding-left:1ex">



<u></u>










<div style>
<span>=A0</span>


<div>
  <div>


    <div>
=20=20=20=20=20=20
=20=20=20=20=20=20
      <p>


</p><div>
<p class=3D"MsoNormal">Hi folks =85 I have a query with bind variable which=
 accepts date.<u></u><u></u></p>
<p class=3D"MsoNormal">How can I run thais query for 30 different dates and=
 get it=92s output in a file.<u></u><u></u></p>
<p class=3D"MsoNormal"><u></u>=A0<u></u></p>
<p class=3D"MsoNormal">I can write the same query 30 times with different d=
ate and run it as ascript.<u></u><u></u></p>
<p class=3D"MsoNormal">Or write a PL/SQL block.<u></u><u></u></p>
<p class=3D"MsoNormal"><u></u>=A0<u></u></p>
<p class=3D"MsoNormal">But it is an one time thing and I am looking for som=
ething quick .<u></u><u></u></p>
<p class=3D"MsoNormal">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 ?<u></=
u><u></u></p>
<p class=3D"MsoNormal"><u></u>=A0<u></u></p>
<p class=3D"MsoNormal">For example how can I run this query for 30 differen=
t dates and spool =A0it=92s output.<u></u><u></u></p>
<p class=3D"MsoNormal"><u></u>=A0<u></u></p>
<p class=3D"MsoNormal"><span style=3D"color:#1f497d">SELECT=A0 :RunDate=A0=
=A0 ,<u></u><u></u></span></p>
<p class=3D"MsoNormal"><span style=3D"color:#1f497d">=A0=A0=A0=A0=A0 ( SELE=
CT COUNT(*) <u></u><u></u></span></p>
<p class=3D"MsoNormal"><span style=3D"color:#1f497d">=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0FROM store <u></u><u></u></span></p>
<p class=3D"MsoNormal"><span style=3D"color:#1f497d">=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0WHERE pstore_open_date =3D:RunDate=A0=A0) store_count=A0=A0=A0=A0=
=A0
<u></u><u></u></span></p>
<p class=3D"MsoNormal"><span style=3D"color:#1f497d">=A0=A0=A0=A0=A0=A0,( S=
ELECT COUNT(*) <u></u><u></u></span></p>
<p class=3D"MsoNormal"><span style=3D"color:#1f497d">=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0FROM wh <u></u><u></u></span></p>
<p class=3D"MsoNormal"><span style=3D"color:#1f497d">=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0WHERE wh_open_date =3D:RunDate=A0=A0) ) wh_count<u></u><u></u></sp=
an></p>
<p class=3D"MsoNormal"><span style=3D"color:#1f497d">=A0=A0FROM dual;<u></u=
><u></u></span></p>
<p class=3D"MsoNormal"><u></u>=A0<u></u></p>
<p class=3D"MsoNormal"><u></u>=A0<u></u></p>
<p class=3D"MsoNormal">Thanks,<u></u><u></u></p>
<p class=3D"MsoNormal">Shiva<u></u><u></u></p>
<p class=3D"MsoNormal"><u></u>=A0<u></u></p>
</div>
<br clear=3D"all"> --------------------------------------------------------=
-----------------------------
<p>
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 y=
ou.




</p>

    <p></p></div>
=20=20=20=20=20

=20=20=20=20
    <div style=3D"color:#fff;min-height:0"></div>


</div>



=20=20






</blockquote></div><br><br clear=3D"all"><div><br></div>-- <br><div>Gene L.=
 Bradley Jr.</div>
<div>Software Developer I</div>
<div>Information Systems &amp; Integration</div>
<div>Jackson State University</div>
<div>1400 J R Lynch Street</div>
<div>P.O. Box 17750</div>
<div>Jackson, MS 39217</div>
<div>=A0</div>
<div>ph 601.979.1042</div>
<div>fax 601.371.9146</div>
<div>email <a href=3D"mailto:[email protected]" target=3D"_blank">gbradley=
@jsums.edu</a></div>
<div>=A0</div>
</div>




<!-- |**|begin egp html banner|**| -->

<br>

=20=20=20=20
=20=20=20=20
<br>

<!-- |**|end egp html banner|**| -->


<div width=3D"1" style=3D"color: white; clear: both;"/>__._,_.___</div>

<!-- Start Recommendations -->
<!-- End Recommendations -->



<!-- |**|begin egp html banner|**| -->

  <img src=3D"http://geo.yahoo.com/serv?s=3D97476590/grpId=3D1103546/grpspI=
d=3D1705115368/msgId=3D58540/stime=3D1374514959" width=3D"1" height=3D"1"> =
<br>

<!-- |**|end egp html banner|**| -->

=20=20
<!-- |**|begin egp html banner|**| -->

<br>
      <div style=3D"font-family: verdana; font-size: 77%; border-top: 1px s=
olid #666; padding: 5px 0;" >
      Your email settings: Individual Email|Traditional <br>
      <a href=3D"http://groups.yahoo.com/group/toad/join;_ylc=3DX3oDMTJma28=
5dmk2BF9TAzk3NDc2NTkwBGdycElkAzExMDM1NDYEZ3Jwc3BJZAMxNzA1MTE1MzY4BHNlYwNmdH=
IEc2xrA3N0bmdzBHN0aW1lAzEzNzQ1MTQ5NTk-">Change settings via the Web</a> (Ya=
hoo! ID required) <br>
      Change settings via email: <a href=3D"mailto:toad-digest@yahoogroups.=
com?subject=3DEmail Delivery: Digest">Switch delivery to Daily Digest</a> |=
 <a href =3D "mailto:[email protected]?subject=3DChange Del=
ivery Format: Fully Featured">Switch to Fully Featured</a> <br>
           <a href=3D"http://groups.yahoo.com/group/toad;_ylc=3DX3oDMTJkM2l=
sNTExBF9TAzk3NDc2NTkwBGdycElkAzExMDM1NDYEZ3Jwc3BJZAMxNzA1MTE1MzY4BHNlYwNmdH=
IEc2xrA2hwZgRzdGltZQMxMzc0NTE0OTU5">
        Visit Your Group=20
      </a> |
      <a href=3D"http://docs.yahoo.com/info/terms/">
        Yahoo! Groups Terms of Use
      </a> |
      <a href=3D"mailto:[email protected]?subject=3DUnsubscr=
ibe">
       Unsubscribe=20
      </a>=20
 <br>
    </div>
  <br>

<!-- |**|end egp html banner|**| -->


<div style=3D"color: white; clear: both;"/>__,_._,___</div>
</body>

</html>

--001a11c38dc0d35dd404e21d33c0--