ToNumber in Where clause

[mmemon]

Hi Everyone,

I have a query that I use in Sql*Plus:

SELECT A.PERS_ID,A.LAST_NAME,B.LAST_NAME FROM CNTRCTR A,CNTRCTR_EXT B WHERE TO_NUMBER(A.CNTRCTR
_NO,'9999')= B.CLONE_OF AND B.CLONE_OF IS NOT NULL;

I need to incorporate the tonumber function and format into
the crystal report query that is used for my report but everytime I add the tonumber code in the where clause, I keep getting error message from crystal stating that there is an invalid column name.

Can we use tonumber functions in the sql query? Or are they only available in formulas?

Thanks
Michele

 

[synapsevampire]

Depends on your version of Crystal. If you open the Database->Show SQL Query in CR you'll find the following help:

From, Where and Order by parts of the Query can be changed, etc...

Try to avoid editing the SQL for maintenance reasons.

I would create a View to handle the conversion and base the report on the View.

But wait until after you've fully enjoyed placing a flashlight full of ticks into the database architects <insert inappropriate orifice here>.

-k

 

[mmemon]

Hi,

Thanks K. You know writing a view didn't even cross my mind.

I think it is time for a vacation.

Michele