Solved

SELECT inside the DECODE statement

Posted on 2006-10-20
11
1,118 Views
Last Modified: 2008-02-01
SELECT PC_PO_NBR , PC_ITM_SEQ_NBR , PC_PO_ID_MO_RNG , PC_SAP_PO_NBR,

       SY_STY_NBR , SY_COLR_CD_ID , XDM_DIM_CD ,PC_TOT_ON_ORD_QTY ,

       PC_PO_ID_SESN_CD , PC_DEST_XCT_CD

FROM DPRODC.VPOITM05@GOAL  GOALPO

WHERE PC_PO_ID_MO_RNG > 20000000

AND GOALPO.XGP_GPC_CD = '02'

AND XPL_PROD_LN_CD <> '11'

AND PC_TOT_ON_ORD_QTY > 0

AND PC_ITM_STAT_CD != 'CX'  --  GFP 'TN'

AND PC_PO_OT_CD = 'FT'

and SY_STY_NBR= '313952'

and SY_COLR_CD_ID ='001'

AND  decode(FY_FCTY_CD in (SELECT  FY_FCTY_CD

                           FROM DFCTY.VFCTY003@GOAL  GF

                           WHERE PC_XCT_ORIG_CD <> GOALPO.PC_DEST_XCT_CD

                           AND GF.XGP_GPC_CD = '02'

                           AND XXX_STAT = 'A'), 'L', 'N', FY_FCTY_CD));

 

How do I get the decode to work?

0
Comment
Question by:vish3210
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 11

Expert Comment

by:mohammadzahid
ID: 17778055

Here is a details on DECODE function. Please adjust your query accordingly.

http://www.techonthenet.com/oracle/functions/decode.php
0
 

Author Comment

by:vish3210
ID: 17778375
Hello all,

SELECT PC_PO_NBR , PC_ITM_SEQ_NBR , PC_PO_ID_MO_RNG , PC_SAP_PO_NBR,
       SY_STY_NBR , SY_COLR_CD_ID , XDM_DIM_CD ,PC_TOT_ON_ORD_QTY ,
       PC_PO_ID_SESN_CD , PC_DEST_XCT_CD
FROM DPRODC.VPOITM05@GOAL  GOALPO
WHERE PC_PO_ID_MO_RNG > 20000000
AND GOALPO.XGP_GPC_CD = '02'
AND XPL_PROD_LN_CD <> '11'
AND PC_TOT_ON_ORD_QTY > 0
AND PC_ITM_STAT_CD != 'CX'  --  GFP 'TN'
AND PC_PO_OT_CD = 'FT'
and SY_STY_NBR= '313952'
and SY_COLR_CD_ID ='001'
AND  FY_FCTY_CD in DECODE((SELECT  FY_FCTY_CD
                           FROM DFCTY.VFCTY003@GOAL  GF
                           WHERE PC_XCT_ORIG_CD <> GOALPO.PC_DEST_XCT_CD
                           AND GF.XGP_GPC_CD = '02'
                           AND XXX_STAT = 'A'), 'L', 'N', FY_FCTY_CD);


When I ran this query it threw me an error(ORA-01427: single-row subquery returns more than one row), saying it returns more one row, which means DECODE function's inside SELECT statement returns more than one row. So....

What is the better way to write this query then?????
0
 
LVL 11

Expert Comment

by:mohammadzahid
ID: 17778960
you can try using multiple row comparison operator or re-write your sql statement to handle return of multi rows from sub-query.

Some multiple row operators are:

> ALL
<ALL
<ANY
>ANY
=ANY (same as IN operator)
NOT EXISTS (row must match a value in the subquery)

Please goto Oracle web-site and download sql reference for details and examples.

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 14

Assisted Solution

by:GGuzdziol
GGuzdziol earned 250 total points
ID: 17779155
AND  FY_FCTY_CD in DECODE((SELECT  FY_FCTY_CD
                           FROM DFCTY.VFCTY003@GOAL  GF
                           WHERE PC_XCT_ORIG_CD <> GOALPO.PC_DEST_XCT_CD
                           AND GF.XGP_GPC_CD = '02'
                           AND XXX_STAT = 'A'), 'L', 'N', FY_FCTY_CD);

You probably want to change ,,in'' to ,,='' (in first line)

the problem is that for some reason this statement returns more then one row; if You know that those records are identical - add
GROUP BY FY_FCTY_CD

if because of some reason they are not identical, but You treat them in same way (i.e. they all are different then 'L' or all equal 'L') add
AND ROWNUM = 1

if You need to check if there is at least one record with 'L' You want probably something like
DECODE((SELECT  SUM(DECODE(FY_FCTY_CD, 'L', 1, 0))
                           FROM DFCTY.VFCTY003@GOAL  GF
                           WHERE PC_XCT_ORIG_CD <> GOALPO.PC_DEST_XCT_CD
                           AND GF.XGP_GPC_CD = '02'
                           AND XXX_STAT = 'A'), 0, FY_FCTY_CD, 'N');

and finally, if You need to check if there is at least one record different then 'L'

DECODE((SELECT  SUM(DECODE(FY_FCTY_CD, 'L', 0, 1))
                           FROM DFCTY.VFCTY003@GOAL  GF
                           WHERE PC_XCT_ORIG_CD <> GOALPO.PC_DEST_XCT_CD
                           AND GF.XGP_GPC_CD = '02'
                           AND XXX_STAT = 'A'), 0, 'N', FY_FCTY_CD);
0
 
LVL 6

Accepted Solution

by:
Ritesh_Garg earned 250 total points
ID: 17780542
Replace you last block:

AND  decode(FY_FCTY_CD in (SELECT  FY_FCTY_CD
                           FROM DFCTY.VFCTY003@GOAL  GF
                           WHERE PC_XCT_ORIG_CD <> GOALPO.PC_DEST_XCT_CD
                           AND GF.XGP_GPC_CD = '02'
                           AND XXX_STAT = 'A'), 'L', 'N', FY_FCTY_CD));

with this:

AND  exist           (SELECT  1
                           FROM    DFCTY.VFCTY003@GOAL  GF
                           WHERE  PC_XCT_ORIG_CD <> GOALPO.PC_DEST_XCT_CD
                           AND      GF.XGP_GPC_CD = '02'
                           AND      XXX_STAT = 'A'
                           AND      decode( FY_FCTY_CD, 'L', 'N', GOALPO.FY_FCTY_CD) = GOALPO.FY_FCTY_CD    --> Check this line
                          );

Please see the highlighted line.  I am using decode in the subquery instead of using it in the outer query.

Thanks,
Ritesh
0
 

Author Comment

by:vish3210
ID: 17791936
Thank you Ritesh. I accepted your answer and you are the star.!!!

Vish
0
 

Author Comment

by:vish3210
ID: 17797327
Hello,

What I need is a decode that will return all this:

SELECT pc_po_nbr, pc_itm_seq_nbr, pc_po_id_mo_rng, pc_sap_po_nbr, sy_sty_nbr,
       sy_colr_cd_id, xdm_dim_cd, pc_tot_on_ord_qty, pc_po_id_sesn_cd,
       pc_dest_xct_cd
  FROM dprodc.vpoitm05@goal goalpo
 WHERE pc_po_id_mo_rng > 20000000
   AND xgp_gpc_cd = '02'
   AND xpl_prod_ln_cd <> '11'
   AND pc_tot_on_ord_qty > 0
   AND pc_itm_stat_cd != 'CX'  --  GFP 'TN'
   AND pc_po_ot_cd = 'FT'
   AND sy_sty_nbr = '312673'
   AND sy_colr_cd_id = '101'

with a new row being the decode.  I need to know if the row have a L, or an N next to it.

Help me please...

Thanks
VI--
0
 
LVL 6

Expert Comment

by:Ritesh_Garg
ID: 17797494
Vish,
Is it that you want to access a pair of rows and then check the values.  Or is there an internal join in the table?
Not clear what is it you need here.  Please give some examples.
0
 

Author Comment

by:vish3210
ID: 17799527
Ritesh,

Please bear with me Ritesh. Anyway thanks once again for coming online. Appreciate that.

SELECT pc_po_nbr, pc_itm_seq_nbr, pc_po_id_mo_rng, pc_sap_po_nbr, sy_sty_nbr,
       sy_colr_cd_id, xdm_dim_cd, pc_tot_on_ord_qty, pc_po_id_sesn_cd,
       pc_dest_xct_cd
  FROM dprodc.vpoitm05@goal goalpo
 WHERE pc_po_id_mo_rng > 20000000
   AND xgp_gpc_cd = '02'
   AND xpl_prod_ln_cd <> '11'
   AND pc_tot_on_ord_qty > 0
   AND pc_itm_stat_cd != 'CX'  --  GFP 'TN'
   AND pc_po_ot_cd = 'FT'
   AND sy_sty_nbr = '312673'
   AND sy_colr_cd_id = '101'

I need to display another column which shows if it is CHINA, then "Y", else "N". Can I use NVL or DECODE for this.

Here is the sample:

PC_PO_NBR      PC_ITM_SEQ_NBR      PC_PO_ID_MO_RNG      PC_SAP_PO_NBR      SY_STY_NBR      SY_COLR_CD_ID      XDM_DIM_CD      PC_TOT_ON_ORD_QTY      PC_PO_ID_SESN_CD      PC_DEST_XCT_CD RESULT
317551      10      20051012      4500551143      312673      101      00      864        PANA     N
317551      20      20051012      4500551143      312673      101      00      576        PANA     N
317551      40      20051012      4500551143      312673      101      00      1008        ECUA     N
317551      100      20051012      4500551143      312673      101      00      72        PANA     N
317793      20      20051012      3500145333      312673      101      00      528        INDI       N
335157      10      20060103      3500179142      312673      101      00      396        CHIN      Y

Thank you,
--Vish
0
 
LVL 6

Expert Comment

by:Ritesh_Garg
ID: 17799565
This is simple:

SELECT pc_po_nbr, pc_itm_seq_nbr, pc_po_id_mo_rng, pc_sap_po_nbr, sy_sty_nbr,
       sy_colr_cd_id, xdm_dim_cd, pc_tot_on_ord_qty, pc_po_id_sesn_cd,
       pc_dest_xct_cd
      ,decode(pc_dest_xct_cd, 'CHIN', 'Y', 'N')  RESULT    -- this is where is shows
  FROM dprodc.vpoitm05@goal goalpo
 WHERE pc_po_id_mo_rng > 20000000
   AND xgp_gpc_cd = '02'
   AND xpl_prod_ln_cd <> '11'
   AND pc_tot_on_ord_qty > 0
   AND pc_itm_stat_cd != 'CX'  --  GFP 'TN'
   AND pc_po_ot_cd = 'FT'
   AND sy_sty_nbr = '312673'
   AND sy_colr_cd_id = '101'

Does this give you desired results?
0
 

Author Comment

by:vish3210
ID: 17799620
Ritesh,

My mind is not working today. May be my Manager is confusing me on this. This is so simple. Anyway thanks for your help. If anything I needed in this topic, I will post you in this link.

You are double star(**).

--Vish
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

680 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question