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_IDStart_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!
