Home » SQL & PL/SQL » Client Tools » Error: Prompting User for input (SQL Report Using Toad)
Error: Prompting User for input (SQL Report Using Toad) [message #8621] Thu, 04 September 2003 11:48 Go to next message
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
Re: Error: Prompting User for input (SQL Report Using Toad) [message #8629 is a reply to message #8621] Tue, 16 September 2003 01:13 Go to previous message
Frank Naude
Messages: 4565
Registered: April 1998
Senior Member
Hi,

Use the TO_NUMBER() function to convert the variable
from char to number.

Best regards.

Frank
Previous Topic: No isqlplus!
Next Topic: isql login win xp
Goto Forum:
  


Current Time: Tue Aug 04 15:47:57 CDT 2020