Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SELECT inside the DECODE statement

Posted on 2006-10-20
11
Medium Priority
?
1,139 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 1000 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 1000 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

926 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