Toad World® Forums

Prompted Query and Bind Variables


#1

I have built a query with inline SELECTS where I want to prompt the user for 2 Dates. I will then take use variables in other queries.

Here’s the gist of my query:

–REGION Get End Date
SELECT a.BEG_DT, FACT.TIME_KEY AS END_DT
FROM INSIGHTADM.FACT FACT,
–REGION Get Begin Date
(SELECT FACT.TIME_KEY AS BEG_DT
FROM INSIGHTADM.FACT FACT
WHERE (FACT.TIME_KEY = ?)
GROUP BY FACT.TIME_KEY)a
–END REGION
WHERE (FACT.TIME_KEY = ?)
GROUP BY a.BEG_DT, FACT.TIME_KEY

–END REGION

When the user gets prompted, they get a pop-up box that shows a table view with 2 rows. The Name showing for the 2 variables are f0 and f1. Is there any way to pass a Name Alias to the user instead of the “f0” and “f1”?

Thanks!
image001.png


#2

If I use the (:TIME_KEY) approach, then I get an “ORA-00920: invalid relational operator” error


#3

This should do what you are asking for:

–REGION Get End Date
SELECT a.BEG_DT, FACT.TIME_KEY AS END_DT
FROM INSIGHTADM.FACT FACT,
–REGION Get Begin Date
(SELECT FACT.TIME_KEY AS BEG_DT
FROM INSIGHTADM.FACT FACT
WHERE (FACT.TIME_KEY = :name_one)
GROUP BY FACT.TIME_KEY)a
–END REGION
WHERE (FACT.TIME_KEY = :name_two)
GROUP BY a.BEG_DT, FACT.TIME_KEY
–END REGION

It will ask for name_one or name_two.

/Mauritz


#4

That is odd. They should be the same.

/Mauritz


#5

You, my friend…ROCK!!!

That worked PERFECTLY!

Thanks for such a quick response!

Mike