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  |
|
|
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 #406124 is a reply to message #406117] |
Tue, 02 June 2009 06:50  |
|
|
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 |
cookiemonster
Messages: 13959
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 |
|
|
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 |
cookiemonster
Messages: 13959
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.
|
|
|
|
|
|
Goto Forum:
Current Time: Sat Nov 30 06:27:16 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
