| Error: Prompting User for input (SQL Report Using Toad) [message #8621] |
Thu, 04 September 2003 11:48  |
Joyce
Messages: 13
Registered: March 2000
|
Junior Member |
|
|
Prompting for User input
Question- I want to add a prompt for the user to enter the accounting cycle, take that value in the where clause, and display the results. Time_Period_Name is defined as a Varchar(2) in the database. The input value for the Time_Period_name is 2002-12. It works fine if I hard code this value in the sql.
When I run the code below, it gives me error - ORA-01722: invalid number
I am using Toad to run this sql report
whenever sqlerror exit
accept acctcycle char FORMAT 'yyyy-mm' 'prompt Enter acctcycle:'
SELECT
Product.ProductCategory||''||
SUMMARY_LINE_FACTS.INTEREST_RATE ,
nvl( sum(summary_line_facts.REP_PER_AMOUNT), '0') Ordinary
FROM
Prodcut,
SUMMARY_LINE_FACTS,
TIME_PERIOD ACCOUNTING_CYCLE_DIM,
WHERE
( SUMMARY_LINE_FACTS.ProductCode=Product.MYCODE )
AND ( SUMMARY_KEYS.GUID=SUMMARY_LINE_FACTS.GUID )
AND (ACCOUNTING_CYCLE_DIM.TIME_KEY=SUMMARY_KEYS.ACCOUNTING_CYCLE_KEY )
AND (accounting_cycle_dim.time_period_name = (&acctcycle) )
GROUP BY
Product.ProductCategory,
SUMMARY_LINE_FACTS.INTEREST_RATE
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
