OraFAQ Forum: Reports & Discoverer » sql query in formula column

Home » Developer & Programmer » Reports & Discoverer » sql query in formula column

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

sql query in formula column [message #135585]

Thu, 01 September 2005 05:49

sap7k
Messages: 11
Registered: August 2005

Junior Member

Hi all,
I am developing a report on Oracle 6i Reports builder. I am trying to put the following query into a formula column to retrieve a set of rows.

SELECT starts.serial_number AS "From" into :cp_from,
CASE
WHEN ends.sn2 - starts.sn2 >= 1
THEN ends.serial_number
ELSE NULL
END AS "To" into :cp_to,
starts.secondary_inventory_name,starts.segment1,starts.organization_id,starts.inventory_item_id
FROM (SELECT serial_number,secondary_inventory_name,segment1,organization_id,inventory_item_id, sn2, ROWNUM rn
FROM (SELECT msn.serial_number,
msi.secondary_inventory_name,
msib.SEGMENT1,msib.ORGANIZATION_ID,msn.inventory_item_id, sn1, sn2,
LAG (sn2) OVER (PARTITION BY sn1 ORDER BY sn2)
AS lag_sn2
FROM (SELECT mtl_serial_numbers.*,
RTRIM (serial_number, '1234567890') AS sn1,
--TO_NUMBER (LTRIM (serial_number, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ')
to_number(substr(serial_number,LENGTH(RTRIM (serial_number,'0123456789')) +1,LENGTH(serial_number))
) AS sn2
FROM mtl_serial_numbers) msn,
mtl_system_items_b msib,
mtl_secondary_inventories msi
WHERE msn.inventory_item_id = msib.inventory_item_id
AND msn.current_organization_id = msib.organization_id
AND msn.current_subinventory_code =
msi.secondary_inventory_name
AND msn.current_organization_id = msi.organization_id
AND msib.segment1 = NVL (:p_item, msib.segment1)
AND msib.organization_id = nvl(:ORGANIZATION_ID,msib.organization_id) --83
AND msn.current_status = 3
ORDER BY sn1, sn2)
WHERE sn2 <> lag_sn2 + 1 OR lag_sn2 IS NULL) starts,
(SELECT serial_number,secondary_inventory_name,segment1,organization_id, sn2, ROWNUM rn
FROM (SELECT msn.serial_number,
msi.secondary_inventory_name,
msib.SEGMENT1,
msib.ORGANIZATION_ID, msn.inventory_item_id,sn1, sn2,
LEAD (sn2) OVER (PARTITION BY sn1 ORDER BY sn2)
AS lead_sn2
FROM (SELECT mtl_serial_numbers.*,
RTRIM (serial_number, '1234567890') AS sn1,
--TO_NUMBER (LTRIM (serial_number, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ')
to_number(substr(serial_number,LENGTH(RTRIM (serial_number,'0123456789')) +1,LENGTH(serial_number))
) AS sn2
FROM mtl_serial_numbers) msn,
mtl_system_items_b msib,
mtl_secondary_inventories msi
WHERE msn.inventory_item_id = msib.inventory_item_id
AND msn.current_organization_id = msib.organization_id
AND msn.current_subinventory_code =
msi.secondary_inventory_name
AND msn.current_organization_id = msi.organization_id
AND msib.segment1 = NVL (:p_item, msib.segment1)
AND msib.organization_id =nvl(:ORGANIZATION_ID,msib.organization_id) -- 83
AND msn.current_status = 3
ORDER BY sn1, sn2)
WHERE sn2 <> lead_sn2 - 1 OR lead_sn2 IS NULL) ends
WHERE starts.rn = ends.rn
and starts.organization_id =:ORGANIZATION_ID
AND starts.inventory_item_id=:INVENTORY_ITEM_ID
AND starts.secondary_inventory_name=:Subinventory

-----------------------------------------------------------------

But when I try to compile the function it gives an error:
'Encountered the symbol "CASE" when expecting one of the following:

mod<an identfier> <a double quoted delimited identifier>
<a bind variable> current sql <a single quoted sql string>

Can someone please suggest a way out. Thanks for ur time.

Report message to a moderator

Re: sql query in formula column [message #135587 is a reply to message #135585]

Thu, 01 September 2005 05:58

kiran
Messages: 503
Registered: July 2000

Senior Member

Case statments looks correct.
It will be help ful , if you can paste the full error message.

--Kiran.

Report message to a moderator

Re: sql query in formula column [message #135601 is a reply to message #135587]

Thu, 01 September 2005 07:35

sap7k
Messages: 11
Registered: August 2005

Junior Member

Hi Kiran,
Thanks for looking into my problem. I have tried and tested the query using TOAD. It is working fine. I tried to put the code into a formula column and store the "from serial no" and "to_serial_no" into two place holder columns, :cp_from and :cp_to. But when I tried to compile the function the error came up.The error message was:

-----start of error msg------------------------------
'Encountered the symbol "CASE" when expecting one of the following:

mod<an identfier> <a double quoted delimited identifier>
<a bind variable> current sql <a single quoted sql string>
-----end of error msg----------------------------------

I have also tried keeping this sql statement as another query in Reports Builder 6i but when ever I tried to link it with the intended parent group a dialog box appears saying:

Cannot implement column link for this detail sql query. Converting to group link. (Pus there are 3 buttons "Yes" "Cancel" "Help"). Thanks once more.

Report message to a moderator

Re: sql query in formula column [message #135859 is a reply to message #135585]

Sat, 03 September 2005 04:39

venkatesham
Messages: 45
Registered: April 2005
Location: Hyd

Member

Hi there,

Can u check the version of oracle ur using ..since oracle 9i has this CASE feature..

Thanks
Venkat..

Report message to a moderator

Re: sql query in formula column [message #135882 is a reply to message #135859]

Sat, 03 September 2005 22:58

sap7k
Messages: 11
Registered: August 2005

Junior Member

HI Venkat,
the DB version is Oracle 9i. I have tried running the query separately in Toad and it is running perfectly. But I can't find a way to attach the statement in Oracle Reports Builder 6i.

Thanks

Report message to a moderator

Re: sql query in formula column [message #355674 is a reply to message #135585]

Mon, 27 October 2008 00:46

mnaeembaig
Messages: 8
Registered: November 2007
Location: Karachi

Junior Member

I am also facing the same problem. I am using the case query on button action trigger and getting the same error. While I have used the same query in the Main Query Pan of Report and it is working fine. Any help would be highly appreciated.

Report message to a moderator

Re: sql query in formula column [message #355994 is a reply to message #355674]

Tue, 28 October 2008 23:36

sispk6
Messages: 164
Registered: November 2006
Location: pakistan

Senior Member

Dear the poblem is due to SUBQUERY ,
reports builder does not recognize a link a link b/w two queries if one of them contains a subquery,or wny subquery returning data to report block ,
here the error is not due to CASE statement ,it is due to the usage of sub-query.
i faced the same problem and i had to change my query.

[Updated on: Tue, 28 October 2008 23:37]

Report message to a moderator

Re: sql query in formula column [message #355998 is a reply to message #355994]

Tue, 28 October 2008 23:40

sispk6
Messages: 164
Registered: November 2006
Location: pakistan

Senior Member

by the way if its just CASE that hurts you.
you can use DECODE a much better alternative

Report message to a moderator

Re: sql query in formula column [message #356585 is a reply to message #135585]

Fri, 31 October 2008 05:45

cookiemonster
Messages: 13959
Registered: September 2008
Location: Rainy Manchester

Senior Member

Forms and reports 6i does not recognize case.
One way round is to write your query as a view and then query the view from your formula column

Report message to a moderator

Previous Topic:	syntax error
Next Topic:	DDE command problem

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Dec 02 08:17:33 CST 2024