Toad World® Forums

How can I get a predefined list for the bind name

I am using the bind function to input the date and state. What should I do to get a predefined list to input my state IDs. For example, I may want to create the report for below states. I may select one or some times more. How do I pick my states as I wish to run the code.

Tool : Toad Data point 3.2/Oracle

List of States : OR, WA, ID,CA, UT

Sample code


Select * from mytable

 WHERE Calendar_date BETWEEN :Start_date AND :End_date

AND State_ID in :Enter_State_ID

Start_date and End_Date will work, but good luck if you are trying to do a multi value entry for State_ID if you use automation. Might want to try version 3.7, I think they fixed something in this area.

Unfortunately Oracle does not support list values for the bind variable. A bind variable always contains one value.

You could use the following

WHERE State_ID IN (:ID1, :ID2, :ID3 )

or

WHERE State_ID = :ID1

OR State_ID = :ID2

OR State_ID = :ID3

Regards

Aleksey

If the state list is static we are currently adding a feature where you can load your IN values from a file (csv, text or xlxs). But if the values are dynamic that is a different story. In this case I would use a loop dataset in automation and run a query that generates the list of states. Then I would loop through and concatenate the values into a bind var --> 'CA, 'NY

This concatenated string can be used as one bind in another query.

Debbie, the static list feature that can be loaded in IN values would be incredible. We have an old software that is able to do this, so we’d be super excited to get something like this to work similarly in TDP. Would you please let us know if this is something added in a newer release? Thanks!

Try the TDP 3.8 Beta that was posted today. Go to Query Builder and bring up the where condition editor. Change the operator to IN. You will see an ellipse. clicking on this will load a txt, csv or excel file. It will pre-load the drop down with values.