Home » Developer & Programmer » Reports & Discoverer » Cannot use inline query in formula columns (Forms 6i)
Cannot use inline query in formula columns [message #406116] Tue, 02 June 2009 05:39 Go to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

I am trying use one query as formula column in my report but i think it does not support.the query is like that

SELECT BSL_ITEM_CODE LBSP_ITEM_CODE,
BSL_BATCH_NO LBSP_BATCH_NO,
BSL_GRADE_CODE_1 LBSP_GRADE_CODE_1,
BSL_LOCN_CODE LOCN_CODE,
SUM(DECODE(BSL_RCVD_ISSD,'R',1,-1)*BSL_QTY_BU)/IU_MAX_LOOSE_1 QTY_BU, ITEM_NAME, LOCN_NAME,
0 BSL_COST_1,SUM(DECODE(BSL_RCVD_ISSD,'R',1,-1)*bSL_VAL_1) BSL_VAL_1,
TRUNC( sum( NVL((DECODE (BSL_RCVD_ISSD,'R',1,-1)*NVL(BSL_QTY_BU,0)/IU_MAX_LOOSE_1)/
(SELECT IU_CONV_FACTOR FROM OM_ITEM_UOM WHERE IU_ITEM_CODE=BSL_ITEM_CODE
AND IU_UOM_CODE = DECODE(ITEM_UOM_CODE,'KGS','PCS',ITEM_UOM_CODE)),0))) PCS_QTY,
(SELECT IU_UOM_CODE FROM OM_ITEM_UOM,OM_ITEM
WHERE IU_ITEM_CODE = ITEM_CODE
AND ITEM_CODE = BSL_ITEM_CODE
AND IU_UOM_CODE = DECODE(ITEM_UOM_CODE,'KGS','PCS',ITEM_UOM_CODE)) UOM,
(SELECT ITEM_UOM_CODE FROM OM_ITEM
WHERE ITEM_CODE = BSL_ITEM_CODE) BASE,
VSSV_FIELD_01,VSSV_FIELD_02,VSSV_CODE,ITEM_FLEX_03,ITEM_FLEX_07
FROM OM_ITEM, OM_ITEM_UOM,OS_BATCH_STK_LEDGER,IM_VS_STATIC_VALUE,OM_LOCATION
WHERE
BSL_LOCN_CODE BETWEEN :REP_VALUE_25 AND :REP_VALUE_26
AND BSL_BATCH_NO BETWEEN :REP_VALUE_27 AND :REP_VALUE_28
AND TO_CHAR(BSL_DT,'YYYYMM')<= :P_YYYYMM
AND BSL_ITEM_CODE = ITEM_CODE
AND ITEM_CODE = IU_ITEM_CODE
AND ITEM_UOM_CODE = IU_UOM_CODE
AND BSL_LOCN_CODE = LOCN_CODE
AND VSSV_CODE = ITEM_IG_CODE
AND VSSV_VS_CODE = 'ITEM_GROUP'
HAVING SUM(DECODE(BSL_RCVD_ISSD,'R',1,-1)*BSL_QTY_BU)/IU_MAX_LOOSE_1 >0 AND
SUM(DECODE(BSL_RCVD_ISSD,'R',1,-1)*bSL_VAL_1) >= 0
GROUP BY BSL_ITEM_CODE,BSL_BATCH_NO,BSL_gRADE_CODE_1,IU_MAX_LOOSE_1,ITEM_NAME,
VSSV_FIELD_01,VSSV_FIELD_02,VSSV_CODE,BSL_LOCN_CODE,LOCN_NAME,ITEM_FLEX_03,ITEM_FLEX_07
Re: Cannot use inline query in formula columns [message #406117 is a reply to message #406116] Tue, 02 June 2009 05:52 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Where exactly is this "inline query"? I'm not into losing my eyes trying to find it in this mess. Do format it and use [code] tags to preserve formatting.

Besides, if it is a "report" question, why did you put it into Forms forum?
Re: Cannot use inline query in formula columns [message #406120 is a reply to message #406116] Tue, 02 June 2009 06:07 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
And once you've formatted your query if you could tell us what error you're getting that'd help as well.
Re: Cannot use inline query in formula columns [message #406124 is a reply to message #406117] Tue, 02 June 2009 06:50 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

First i would like to apologize for pasting the code in wrong place without formatting properly.Second you can see the inline column with name PCS_qty . When i am pasting this into formula column i am getting error

Encountered the symbol "SELECT" when expecting the following:

} intersect minus union
Re: Cannot use inline query in formula columns [message #406127 is a reply to message #406116] Tue, 02 June 2009 07:12 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Quote:

Second you can see the inline column with name PCS_qty


I probably could but I'm really not going to try and read that query until you repost it with code tags in a formatted manner.
At a glance I can tell that part of the select alone is highly complicated.

If you run that query in sqlplus do you get the same error?
Re: Cannot use inline query in formula columns [message #406139 is a reply to message #406127] Tue, 02 June 2009 08:24 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

hi cookiemonster

its ok if i run the query from sqlplus but when i am adding that in report formula column its not working .Actually what i am doing is i am selecting one column qty and dividing this by another column from another table using nested select statement like you see below in pcs_qty

I m re writing the query in simple format for you

SELECT BSL_ITEM_CODE ,
BSL_BATCH_NO ,
BSL_LOCN_CODE ,
SUM(a/IU_MAX_LOOSE_1) QTY_BU,
sum((a/iu_max_loose_1)/
(SELECT IU_CONV_FACTOR FROM OM_ITEM_UOM WHERE IU_ITEM_CODE=BSL_ITEM_CODE ))) PCS_QTY
FROM OM_ITEM, OM_ITEM_UOM,OS_BATCH_STK_LEDGER,IM_VS_STATIC_VALUE,OM_LOCATION
WHERE
BSL_LOCN_CODE BETWEEN :REP_VALUE_25 AND :REP_VALUE_26
AND BSL_ITEM_CODE = ITEM_CODE
AND ITEM_CODE = IU_ITEM_CODE
AND ITEM_UOM_CODE = IU_UOM_CODE
AND BSL_LOCN_CODE = LOCN_CODE
AND VSSV_CODE = ITEM_IG_CODE
AND VSSV_VS_CODE = 'ITEM_GROUP'
GROUP BY BSL_ITEM_CODE,BSL_BATCH_NO,BSL_locn_code,IU_MAX_LOOSE_1
Re: Cannot use inline query in formula columns [message #406142 is a reply to message #406116] Tue, 02 June 2009 08:34 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
1) Please read the orafaq forum guide and learn how to use code tags. I'll do it for you this time but next time please do it yourself, it's not like it's difficult:
SELECT BSL_ITEM_CODE , 
BSL_BATCH_NO , 
BSL_LOCN_CODE ,
SUM(a/IU_MAX_LOOSE_1) QTY_BU, 
sum((a/iu_max_loose_1)/
(SELECT IU_CONV_FACTOR FROM OM_ITEM_UOM WHERE IU_ITEM_CODE=BSL_ITEM_CODE ))) PCS_QTY
FROM OM_ITEM, OM_ITEM_UOM,OS_BATCH_STK_LEDGER,IM_VS_STATIC_VALUE,OM_LOCATION
WHERE 
BSL_LOCN_CODE BETWEEN :REP_VALUE_25 AND :REP_VALUE_26 
AND BSL_ITEM_CODE = ITEM_CODE
AND ITEM_CODE = IU_ITEM_CODE
AND ITEM_UOM_CODE = IU_UOM_CODE
AND BSL_LOCN_CODE = LOCN_CODE
AND VSSV_CODE = ITEM_IG_CODE
AND VSSV_VS_CODE = 'ITEM_GROUP'
GROUP BY BSL_ITEM_CODE,BSL_BATCH_NO,BSL_locn_code,IU_MAX_LOOSE_1 


2) Your new query has one bracket too many and won't compile.

3) Your new query is overly simplified over the previous version. It's missing several functions and brackets. Oracle reports doesn't generally have an issue with sub-queries (which is the correct term for what you've got there, if you say inline queries I assume inline views which are something else). I suspect the problem with your original query is also misplaced brackets but it's difficult to tell.

If you repost the original query properly formatted in code tags like we asked you to we might be able to spot the problem.
Re: Cannot use inline query in formula columns [message #406143 is a reply to message #406142] Tue, 02 June 2009 08:50 Go to previous message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Hi cookiemonster i will make it one sample query like below

select a.item , a.name, sum(a.qty) ,
(sum(a.qty)/(select b.wt from table b where b.item = a.item ))
from table a
group by a.item,a.name
Previous Topic: REP-1212: Object 'Body' is not fully enclosed by its enclosing object
Next Topic: Discoverer Report - GL Date parameter
Goto Forum:
  


Current Time: Thu Dec 08 16:18:45 CST 2016

Total time taken to generate the page: 0.25231 seconds