Datatype Issue

[ekta22]

Hi Everyone,

I have a CODE field in my database which is of datatype NUMBER. I created a parameter of type VARCHAR2 as user can either enter number or text to get values within a range.

pCODE IN VARCHAR2;

I tried two things but I get error for both. I did the parameter comparison in my oracle stored procedure itself and it looks like this

(C.CODE = pCODE OR pCODE IS NULL) OR
 (pCODE = 'UN' AND (C.CODE) > 10) OR
 (pCODE = 'SC' AND (C.CODE) BETWEEN 1 AND 10)

When I run my report and enter number 5 I get the results. But if I enter UN or SC I get an error that says
"Failed to retrieve data from database - Database Connection Error: 'ORA-01722:invalid number"

Then I tried to do the same comparison in Record Selection. This is what I have for that

({C.CODE} = {?CODE} OR {?CODE} = '') OR
({?CODE} = "UN" AND {C.CODE} > "79") OR
({?CODE} = "SC" AND {C.CODE} IN "60" TO "69")

I get an error in this formula that says "A number is required here" and it highlights {?CODE}.
I am totally confused as to how to accept String and number for a parameter and compare it to a number field in the database. I am using Crystal XI and Oracle 10g.

Thanks!

 

[lbass]

Try:

(
(
isnull({?code}) or
{?code} = ''
) or
(
isnumeric(?code) and
{C.CODE} = {?code)
) or
(
{?code} = 'UN' AND
{C.CODE} > '79'
) OR
(
{?code} = 'SC' AND
{C.CODE} in '60' to '69'
)
)

I am not sure you can use nulls or empty parameters in CR X.

-LB

 

[ekta22]

Still getting an error. It highlights {?code} shown in bold
below

(
(
isnull({?code}) or
{?code} = ''
) or
(
isnumeric(?code) and
{C.CODE} = {?code}
) or
(
{?code} = 'UN' AND
{C.CODE} > '79'
) OR
(
{?code} = 'SC' AND
{C.CODE} in '60' to '69'
)
)

 

[PatriciaObreja]

For this in Oracle:
(C.CODE = pCODE OR pCODE IS NULL) OR
(pCODE = 'UN' AND (C.CODE) > 10) OR
(pCODE = 'SC' AND (C.CODE) BETWEEN 1 AND 10)

I think that you get an error for 'UN' or 'SC', because the pCode is not null and it gets to this condition "C.CODE = pCODE" which produces the error.

Try to revert the order:
(pCODE = 'UN' AND (C.CODE) > 10) OR
(pCODE = 'SC' AND (C.CODE) BETWEEN 1 AND 10) OR
(C.CODE = pCODE OR pCODE IS NULL)

The last condition will still produce an error if the user enter some character different then 'UN' or 'SC'.

 

[lbass]

Sorry, that should have been:

(
(
isnull({?code}) or
{?code} = ''
) or
(
isnumeric(?code) and
{C.CODE} = val({?code}) //convert {?code} to a number
) or
(
{?code} = 'UN' AND
{C.CODE} > 79 //these should be numbers since C.Code is a number
) OR
(
{?code} = 'SC' AND
{C.CODE} in 60 to 69//these should be numbers since C.Code is a number

)
)

-LB

 

[ekta22]

Patricia, even after reverting the order in Oracle I am still getting the same error.

LB, your solution works great. But I had a quick question. I have around 10 other parameters that I am comparing in my Oracle Stored Procedure. If I add just this CODE parameter selection in crystal and leave others in Oracle stored procedure, can it affect the performance?

Thanks!

 

[lbass]

I'm sorry, but I don't know the answer to that.

-LB

 

[musini]

Can you convert C.code to varchar in your stored procedure. I think if you do that, it will solve the problem.

Actually I am new to crystal but looking at the thread I thought of this. Please ignore this if it is inappropriate.

Thanks,
M