Home » Developer & Programmer » Reports & Discoverer » sql query in formula column
sql query in formula column [message #135585] |
Thu, 01 September 2005 05:49 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
sap7k
Messages: 11 Registered: August 2005
|
Junior Member |
![sap7k](/forum/theme/orafaq/images/yahoo.png)
|
|
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.
|
|
|
|
|
|
|
Re: sql query in formula column [message #355674 is a reply to message #135585] |
Mon, 27 October 2008 00:46 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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.
|
|
|
Re: sql query in formula column [message #355994 is a reply to message #355674] |
Tue, 28 October 2008 23:36 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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
|
|
|
|
|
Goto Forum:
Current Time: Sat Sep 07 12:22:46 CDT 2024
|