Solved

SELECT inside the DECODE statement

Posted on 2006-10-20
11
1,095 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
Comment Utility

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
Comment Utility
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
Comment Utility
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
 
LVL 14

Assisted Solution

by:GGuzdziol
GGuzdziol earned 250 total points
Comment Utility
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
Comment Utility
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

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

Vish
0
 

Author Comment

by:vish3210
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now