SQL Query works in one database and not on similar other database.

[sreeramnavale]

Hello Forum,

I am using Crystal Reports 9.0 with Oracle.
In my database expert I have the SQL Query as shown at end of this Message.

This SQL Query works on my production database but fails on test database. I would like to know the reason why it fails on my test database.

I did a bit of investigation my self and found out that the SQL is failing because of the following part of SQL.
product.productnbr in ('000' + substr('{?UPC}',1,11))

The error I get when the report is run on the test database is
Failed to Open a rowset.
SQL Engine Error: 'ORA-01722:invalid number'
Not supported.
Details: Fail to fetch data. OCI Call: OCIStmtFetch

Thanks in Advance.

SPN

SQL Query in Database Expert.
With item_data as (
select item_parent item, item_desc
from item_master@RMSLINK im
where
im.item = {?UPC} and im.item_level = 3 and im.PRIMARY_REF_ITEM_IND = 'Y'
),

scs_sku_data as
(
SELECT
productnbr,
SKUNBR
from
product
where
product.productnbr in ('000' + substr('{?UPC}',1,11))
),

scs_data as (
SELECT
organization.orgnbr,
scs_sku_data.productnbr,
storeinventory.qtyonhand,
storeinventory.LASTSALESCALCDATE,
case when (store.phase2blivedate >sysdate)
then 'N'
else 'Y'
end Phase2B
FROM
scs_sku_data,
storeinventory storeinventory,
store store,
organization organization
WHERE
(scs_sku_data.skunbr=storeinventory.skunbr)
AND (storeinventory.storeprofileid=store.storeprofileid)
AND (store.orgid=organization.orgid)
)

SELECT
scs_data.orgnbr,
scs_data.productnbr,
scs_data.qtyonhand,
scs_data.LASTSALESCALCDATE,
scs_data.Phase2B,
item_data.item_desc,
ils.STOCK_ON_HAND
from
scs_data,
item_loc_soh@RMSLINK ils,
item_data
where
(ils.item = item_data.item)
and (ils.loc = scs_data.orgnbr)

 

[Turkbear]

Hi,
Iis the data type for product.productnbr
the same in both instances?

What is the Sql shown when you do a Database..Show Sql
from CR designer?

( + is not an Oracle concatenation operator, so it may be trying to ADD those 2 items - hence, invalid number)







To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[pelajhia]

you should test the query in sql plus against each db, rather than try to debug via crystal reports (if I am reading your post correctly, you have not yet tried this).

sounds like there is a difference in the database set up between test and prod, I would do the above test and if it still fails, take it up with your dba.

 

[lbass]

You could try using:

product.productnbr in ('000'||substr('{?UPC}',1,11))

-LB

 

[synapsevampire]

I agree with everything said here, whoever wrote the query didn't do so for Oracle, the pipe symbol is the prefered concatentation in Oracle, and you should be testing using anbother tool, I would suggest Toad or PL/SQL Devleoper over the standard SQL Plus that comes with Oracle.

Oracle has never been able to develop anything worthwhile beyond their excellent database, the rest of their products are always second rate, even when they buy first rate, they manage to destory them.

-k