Creating and Using Variables in SQL (TOAD)

[Papa_Don]

Group,

I'm using TOAD to query the Oracle database. My better expertise is in VB.net. I have very little SQL experience.

My question: Can I create a variable in TOAD/SQL to prompt for a date(s)? Here is what I want to do:

Question 1: Enter Beginning Date (user to enter date value that looks like this: 01/01/2015)
Question 2: Enter Ending Date (user to enter ending date value, i.e. 04/30/2015)

I then will need to create two further variables based on these first two to represent the prior years dates (i.e 01/01/2014 and 04/30/2014). I would know how to do this in VB.net. Unfortunately I'm not sure about PL/SQL (or whatever it is that TOAD uses). Can you teach me how to do this?

Thanks for your assistance.

Don

[pritaeas]

Is this what you mean:

http://www.toadworld.com/products/toad-for-oracle/f/10/t/10764.aspx

[Papa_Don]

@pritaeas, I saw this but didn't understand what any of that meant. So I'm back to square one.

Some of my lack of understanding stems from the codes selected in the link select every field from the table. I'm not doing this as I'm selecting specific ones. Further, I'm unsure if SQL scripting allows for run time questions. Lastly, I'm so inexperienced in SQL, I'm not sure if I'm even asking the right kind of question.

Thanks for your interest and your teaching.

Don

[pritaeas]

Can I create a variable in TOAD/SQL to prompt for a date

I saw this but didn't understand what any of that meant. So I'm back to square one.

The way I read it is that this:

select * from atable
where adatecolumn = :thedate 

when executed in toad should popup a dialog to input a date value for :thedate. I assumed that's what you're looking for.

[Papa_Don]

I think this is what I want. However based on how I've written the code so far, I'm not sure where to insert this new line (or should I modify an existing line?). Here's how it is currently written:

where                  
          a11.BKNG_LOC_KEY = a14.LOC_KEY and             
          a11.PROP_KEY = a15.PROP_KEY and             
          a11.RATE_PLAN_KEY = a16.RATE_PLAN_KEY and             
          a11.BKNG_DATE_KEY = a17.DATE_KEY and             
          a11.ARV_DATE_KEY = a18.DATE_KEY and            
          a11.STAY_DATE_KEY = a19.DATE_KEY and            
          a15.prop_master_id = a20.prop_id and
          a11.DPRT_DATE_KEY = a22.DATE_KEY and 
          a15.PROP_MASTER_ID = A21.PROP_MASTER_ID and
          a19.FULL_DATE between To_Date('01/01/15','MM/DD/YY') and To_Date('05/26/15','MM/DD/YY'

In trying to use your suggestion, I've rewritten it this way:

          a19.FULL_DATE between To_Date(:begdate,'MM/DD/YY') and To_Date(:enddate,'MM/DD/YY')  

This does bring up a box to enter something in to both of these new variables. Unfortunately when trying to run the script, I get an error message that says, "Parameter begdate not found". Is this because I'm not entering it in the correct format (I used 01/01/2015)?

Thanks for your help.

Don

[pritaeas]

Sorry, that might be a toad issue, which I am unfamiliar with. The thread said something about the query type, but I don't know if that would fix it.

[Papa_Don]

Thanks for the updates. At least you've got me thinking in the right direction. I'll try finding a TOAD site and ask them.

Thank you very much for the help.