How to Use Parameters in SQL Statement?

SQL Navigator
1

HELLO,
I come from Taiwan,
My english is not good enough
I have question that i want to use a Paramenters In SQL Statement

I have a SQL Statement Use The same date value
who i change the date i must modify many places
how can i use Paramenters in my SQL Statement

Example:

SQL Statement Example

SELECT ‘POINT_CNT’ AS FILENAME,SUM(TOTAL_POINT) PNT
FROM RS.MEM_POINT
WHERE PNT_DATE BETWEEN ‘2010-01-01’ AND ‘2010-01-31’
UNION ALL
SELECT ‘TRANS_CNT’ AS FILENAME,COUNT(*) CNT
FROM RS.MEMBER_TRANS
WHERE TRANS_DATE BETWEEN ‘2010-01-01’ AND ‘2010-01-31’
…
…
…
…
…

I Union lots of table about member
When I want to change the date
I must modify lots of date to the new date
SO, how can i hse Parameters in SQL Statement Like below

begin_date = ‘2010-01-01’
end_date = ‘2010-01-31’

SELECT ‘POINT_CNT’ AS FILENAME,SUM(TOTAL_POINT) PNT
FROM RS.MEM_POINT
WHERE PNT_DATE BETWEEN begin_date AND end_date
UNION ALL
SELECT ‘TRANS_CNT’ AS FILENAME,COUNT(*) CNT
FROM RS.MEMBER_TRANS
WHERE TRANS_DATE BETWEEN begin_date AND end_date
…
…
…
…
…

thx a lot

2

Hi Sen,

You can use Bind Variable in your SQL by adding a colon “:” in front of a variable. SQL Navigator supports running SQL with bind variable in Code Editor. There is a button “Scan Defines/Substitutions” in Code Editor toolbar to be pressed when you want to execute a SQL with bind variable. Then when you execute (F9) the SQL, there will be a dialog which is shown for you to select data type of each variable and to input corresponding value. After you click OK button in the bind variable dialog, the SQL will be run as you expected. I attached an example screen shot for your reference.

Hope it helps.

Thanks,
Vincent
Bind_Var.jpeg

3

thx for your help

this problem confuse me a lots