[Solved] Error with optional input parameter

[HildyD]

The following query works as long as a parameter value is entered for IncomeYear:
SELECT SUM( "IncomeAmount" )
FROM "Income"
WHERE YEAR("IncomeDate") =
CASE WHEN :IncomeYear <> '' THEN :IncomeYear ELSE YEAR("IncomeDate") END

Otherwise, the following error occurs:
SQL Status: 37000
Error code: -16
Wrong data type: java.lang.NumberFormatException: For input string: ""

Can anyone explain to me why I am receiving this error?

[UnklDonald418]

Looking at your query I noticed that the parameter dialog returns a Numeric type if numbers are typed into the dialog and your query functions normally.
When nothing or for that matter any non numeric character(s) is entered into the parameter dialog it returns a String. Which results in

Code: Select all

WHERE YEAR("IncomeDate") =  'Some String'

The error message you are seeing is the result of trying to equate a numeric value to a string.
Based on that I unsuccessfully tried to Cast the parameter dialog output into an Integer.
Going the other direction I arrived at

Code: Select all

WHERE CAST(YEAR("IncomeDate") AS VARCHAR(4)) = IFNULL( CAST(:IncomeYear AS VARCHAR(4)),  CAST(YEAR("IncomeDate") AS VARCHAR(4)))

This works for all numeric values and it doesn't result in an error message when nothing is typed into the dialog.
This may not work exactly as intended, but hopefully it will show you a way forward.

[UnklDonald418]

I just went back and modified you code once more to

Code: Select all

WHERE CAST( YEAR( "IncomeDate" ) AS VARCHAR ( 4 ) ) = 
CASE WHEN  CAST( :IncomeYear AS VARCHAR ( 4 ) ) <> '' THEN  CAST( :IncomeYear AS VARCHAR ( 4 ) ) ELSE CAST( YEAR( "IncomeDate" ) AS VARCHAR ( 4 ) ) END

and it seems to return the correct results.

[HildyD]

Thank you so much for your help! I modified my query as follows and it works:

SELECT SUM( "IncomeAmount" )
FROM "Income"
WHERE cast(YEAR("IncomeDate") as varchar(4)) =
CASE WHEN :IncomeYear <> '' THEN :IncomeYear ELSE cast(YEAR("IncomeDate") as varchar(4)) END

I was not required to cast the input parameter.