Standard Oracle Query Format in Crystal Report

[Former Member]

Hi,

PFA oracle query which i insert into crystal report xi version it give me error like Ora-00904 ITEM.PAYOR_ACCOUNT_NBR invalid identifier but same query is run on oracle client and ITEM.PAYOR_ACCOUNT_NBR this column is in database with same name but it give error in crystal report pls tell me what are the modification should do in query which crystal report will accept

Kind Regards,

Ganesh

Oracle Query.txt.zip

[former_member11696]

Hi Ganesh,

What Oracle client are you using in CR connection?

Try one of the other ones:

Oracle Server in the DB Connection wizard ( native Oracle CR driver )

Oracles ODBC driver

Oracle OLE DB Provider

Or the Data Direct Oracle Clients.

Do not use Microsoft's Oracle driver, it only supports Oracle 7

Don

[Former Member]

Hi Don,

I use Oracle server Connection for oracle client 11g

[abhilash_kumar]

Hi Ganesh,

1) Try adding a table alias for each of your inline views and also use that alias for referencing the columns.

2) Try running each of your inline views separately to find out which one is causing the issue.

3) Upgrade CR XI to CR XI R2 SP6.

4) Make sure you're using the right database driver.

-Abhilash

[Former Member]

Hi Abhilash,

I try this in Crystal Report 2013 but it give the error like ORA-00923 From keyword is not found where expected

[abhilash_kumar]

Did you try running the inline views separately?

-Abhilash

[Former Member]

Hi Abhilash,

Yes i try the running the inline views separately but there is problem in

item2

WHERE item2.item_inward_status_code = item1.item_inward_status_code

--{?WHERE_CLAUSE}

GROUP BY item1.session_nbr, item1.business_date, item1.p_final_dest_routing_nbr, item1.transaction_code, item1.p_init_orig_routing_nbr,

       item1.serial_nbr, item1.sequence_nbr, item1.payor_account_nbr, item1.amount, item1.p_business_date, item1.p_cycle_nbr, item1.clearing_type_code

       ,item1.currency_code

ORDER BY p_final_dest_routing_nbr, serial_nbr, transaction_code

this part of query this is the last part of query the above 3 parts are successfully show the result

but this last part give the error like sql command not properly ended

[Former Member]

Hi Abhilash,

i think u have of query that i posted here

[abhilash_kumar]

Does this part work in SQL Developer?

-Abhilash

[Former Member]

Hi Abhilash,

No it give the error like sql command not properly ended

[Former Member]

Hi Abhilash,

Can u tell me the right structure of that query

[abhilash_kumar]

Did you use table aliases for the columns in the second inline view?

P.S: If it does't work in the SQL Developer, please consider posting the question to the Oracle forum.

-Abhilash

[Former Member]

Hi Abhilash,

No i didnt use alias for the columns in the second inline view

[abhilash_kumar]

What's stopping you .

-Abhilash

[Former Member]

Hi Abhilash,

The Whole Query is work in oracle client but not in cr

[former_member477249]

From a SQL perspective there doesn't seem to be any issue. Can you remove all references to this column from the query and post the result that is observed from CR XI designer.

btw: what is the CR version, can you upgrade to CR XI Release 2 highest patch level.

-Prathamesh

[Former Member]

Hi Prathamesh,

That same query i use in latest Crystal Report 2013 but it give me same error

Kind Regards,

Ganesh

[former_member477249]

Does it work after removing this column

[Former Member]

Hi Prathamesh,

I cant remove this column it is used in report

[former_member477249]

Can you create a new report for testing. Hope, you are using the Add Command feature to put the SQL as a command object.

[Former Member]

Hi Prathamesh,

Why Should i create a new report?

[Former Member]

Hi Prathamesh,

I Create Test Report by using sql command but it give same error