Page 1 of 2 12 LastLast
Results 1 to 15 of 24

Thread: Decode question

  1. #1
    Join Date
    Sep 2005
    Posts
    220

    Unanswered: Decode question

    Can I use the DECODE function to do the following?

    If qty < 0 and qty > -1 then qty = -1


    I am using Oracle 8.1.7.4.0

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Yes, using DECODE and SIGN:

    DECODE(SIGN(qty),-1,DECODE(SIGN(qty+1),1,-1,qty),qty)

    Simple!?

  3. #3
    Join Date
    Sep 2005
    Posts
    220
    Thanks a lot

  4. #4
    Join Date
    Sep 2005
    Posts
    220
    I basically want to do this:

    If qty < 0 and qty > -1 then qty = -1
    If qty > 0 and qty < 1 then qty = 1
    else leave qty alone

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    DECODE can be used in SELECT statement only; you can not, for example, do something like

    quantity := DECODE(qty, 0, 100, 200)

    but, instead,

    SELECT DECODE(qty, 0, 100, 200) INTO quantity FROM ...

    The code you need would be this:
    Code:
    SELECT DECODE(SIGN(qty), -1, DECODE(SIGN(qty + 1), 1, -1, qty),
                              1, DECODE(SIGN(1 - qty), 1,  1, qty)
                 )
    INTO l_output
    FROM dual;
    Using the CASE expression (if your DB version allows it), this code becomes simpler and easier to understand:
    Code:
    SELECT CASE
             WHEN qty < 0 AND qty > -1 THEN -1
             WHEN qty > 0 AND qty <  1 THEN  1
             ELSE qty
           END
    INTO l_output
    FROM dual;

  6. #6
    Join Date
    Sep 2005
    Posts
    220
    This query will do the following: If the QTY > 0 and QTY < 1, then QTY is set to 1. Conversely, if QTY < 0 and QTY > -1, it is set to -1. I need to change the following query so that is does this same calculation for the SUM of the groupings, not for each individual value, as seen below:

    Code:
    PRODUCTGROUP	DMDUNIT	DMDGROUP	STARTDATE	QTY	     CURRENT	DESIRED
    HA080A	            R77-1162	INT	02/01/2006	0.069445	1	
    HA080A	            K197-3052R	INT	02/01/2006 	0.038400	1	
    			                         SUM	0.107845	2	   1
    Code:
    SELECT	sysdate rundate
          , d.p_productgroupcode prodgroup
          , f.dmdgroup
          , ROUND(SUM(DECODE(TO_CHAR(f.startdate,'YYYYMM'),TO_CHAR(ADD_MONTHS(SYSDATE-30,1),'YYYYMM'), DECODE(f.qty, 0, 0, DECODE(SIGN(f.qty), -1, DECODE(SIGN(f.qty + 1), 1, -1, f.qty), 1, DECODE(SIGN(1 - f.qty), 1,  1, f.qty))))))  month1qty
          , ROUND(SUM(DECODE(TO_CHAR(f.startdate,'YYYYMM'),TO_CHAR(ADD_MONTHS(SYSDATE-30,1),'YYYYMM'), DECODE(f.qty * d.p_systemavgcost, 0, 0, DECODE(SIGN(f.qty * d.p_systemavgcost), -1, DECODE(SIGN(f.qty * d.p_systemavgcost + 1), 1, -1, f.qty * d.p_systemavgcost), 1, DECODE(SIGN(1 - f.qty * d.p_systemavgcost), 1,  1, f.qty * d.p_systemavgcost))))))  month1$
     FROM manurpt.tempfcst f
        , stsc.dfu d 
    WHERE d.dmdunit = f.dmdunit
      AND d.dmdgroup = f.dmdgroup
      AND d.loc = f.loc
      AND d.p_dfulevel IN ( 211, 222 )
      AND d.scen = f.scen
      AND d.scen = 0
      and d.p_productgroupcode = 'HA080A'
      AND f.loc != 'PDC'  -- this will emilinate all the GLOBAL demand groups
      --AND NVL((f.qty * d.p_systemavgcost),0) != 0
      AND f.type = 1
    GROUP BY sysdate
           , d.p_productgroupcode
           , f.dmdgroup
    ORDER BY d.p_productgroupcode
           , f.dmdgroup;

  7. #7
    Join Date
    Jul 2003
    Posts
    2,296
    what is that first piece of code above?
    is that table data?

    why are you not grouping by month?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  8. #8
    Join Date
    Sep 2005
    Posts
    220
    The first part is just a sample of how the data is being computed now, and how I need it computed. As far as the grouping, this data is being put into a pivot table, and this is how I was able to get it into that format.

  9. #9
    Join Date
    Sep 2005
    Posts
    220
    This example of the data will illustrate the issue. If I do the check (qty > 0 and qty < 1...etc) before I sum them, I get a total = 281. If I sum them, then do the check, I get 267. There is a 14 unit difference, and that is a problem.

    The column to the right of QTY is the QTY column after I do the checking listed above.

    Code:
    GRPCODE	DMDUNIT	DMDGROUP STARTDATE	         QTY	
    AB010A	RL69100	CAN	02/01/2006 00:00:00	0.000000	0
    AB010A	RL69260	CAN	02/01/2006 00:00:00	5.359596	5.359596
    AB010A	RL69430	CAN	02/01/2006 00:00:00	0.477126	1
    AB010A	RL93730	CAN	02/01/2006 00:00:00	3.434927	3.434927
    AB010A	RL87090	CAN	02/01/2006 00:00:00	0.282328	1
    AB010A	RL91210	CAN	02/01/2006 00:00:00	0.000000	0
    AB010A	RL99210	CAN	02/01/2006 00:00:00	1.322724	1.322724
    AB010A	RL69410	CAN	02/01/2006 00:00:00	0.000000	0
    AB010A	RL74030	CAN	02/01/2006 00:00:00	2.828960	2.82896
    AB010A	RL69020	CAN	02/01/2006 00:00:00	0.000000	0
    AB010A	RL68970	CAN	02/01/2006 00:00:00	3.450994	3.450994
    AB010A	RL93700	CAN	02/01/2006 00:00:00	2.264162	2.264162
    AB010A	RL93670	CAN	02/01/2006 00:00:00	0.000000	0
    AB010A	RL71450	CAN	02/01/2006 00:00:00	5.787348	5.787348
    AB010A	RL93660	CAN	02/01/2006 00:00:00	15.769725	15.769725
    AB010A	RL74040	CAN	02/01/2006 00:00:00	2.889733	2.889733
    AB010A	RL99210	CAN	02/01/2006 00:00:00	0.000000	0
    AB010A	RL74720	CAN	02/01/2006 00:00:00	1.878544	1.878544
    AB010A	RL94660	CAN	02/01/2006 00:00:00	0.000000	0
    AB010A	RL94220	CAN	02/01/2006 00:00:00	2.136602	2.136602
    AB010A	RL92930	CAN	02/01/2006 00:00:00	0.000000	0
    AB010A	RL92870	CAN	02/01/2006 00:00:00	0.000000	0
    AB010A	RL92700	CAN	02/01/2006 00:00:00	13.847960	13.84796
    AB010A	RL92650	CAN	02/01/2006 00:00:00	29.582165	29.582165
    AB010A	RL93730	CAN	02/01/2006 00:00:00	5.023988	5.023988
    AB010A	RL69270	CAN	02/01/2006 00:00:00	0.000000	0
    AB010A	RL69260	CAN	02/01/2006 00:00:00	0.000000	0
    AB010A	RL93210	CAN	02/01/2006 00:00:00	4.403791	4.403791
    AB010A	RL88290	CAN	02/01/2006 00:00:00	0.000000	0
    AB010A	RL78180	CAN	02/01/2006 00:00:00	5.283085	5.283085
    AB010A	RL74240	CAN	02/01/2006 00:00:00	5.605946	5.605946
    AB010A	RL71400	CAN	02/01/2006 00:00:00	0.000000	0
    AB010A	RL71360	CAN	02/01/2006 00:00:00	2.315914	2.315914
    AB010A	RL71350	CAN	02/01/2006 00:00:00	7.553953	7.553953
    AB010A	RL87550	CAN	02/01/2006 00:00:00	0.000000	0
    AB010A	RL87490	CAN	02/01/2006 00:00:00	0.000000	0
    AB010A	RL91440	CAN	02/01/2006 00:00:00	12.094913	12.094913
    AB010A	RL92230	CAN	02/01/2006 00:00:00	0.000000	0
    AB010A	RL91920	CAN	02/01/2006 00:00:00	2.806944	2.806944
    AB010A	RL78000	CAN	02/01/2006 00:00:00	0.000000	0
    AB010A	RL90820	CAN	02/01/2006 00:00:00	9.785033	9.785033
    AB010A	RL90690	CAN	02/01/2006 00:00:00	10.356646	10.356646
    AB010A	RL80500	CAN	02/01/2006 00:00:00	11.827765	11.827765
    AB010A	RL71400	CAN	02/01/2006 00:00:00	0.430405	1
    AB010A	RL71360	CAN	02/01/2006 00:00:00	5.818333	5.818333
    AB010A	RL69050	CAN	02/01/2006 00:00:00	0.063069	1
    AB010A	RL68970	CAN	02/01/2006 00:00:00	0.345008	1
    AB010A	RL95800	CAN	02/01/2006 00:00:00	1.009298	1.009298
    AB010A	RL94710	CAN	02/01/2006 00:00:00	16.313841	16.313841
    AB010A	RL94660	CAN	02/01/2006 00:00:00	0.077771	1
    AB010A	RL92700	CAN	02/01/2006 00:00:00	12.097621	12.097621
    AB010A	RL92650	CAN	02/01/2006 00:00:00	6.467144	6.467144
    AB010A	RL90390	CAN	02/01/2006 00:00:00	0.061004	1
    AB010A	RL74000	CAN	02/01/2006 00:00:00	0.346242	1
    AB010A	RL73250	CAN	02/01/2006 00:00:00	0.000000	0
    AB010A	RL91580	CAN	02/01/2006 00:00:00	3.365800	3.3658
    AB010A	RL91490	CAN	02/01/2006 00:00:00	0.327713	1
    AB010A	RL71350	CAN	02/01/2006 00:00:00	3.646592	3.646592
    AB010A	RL69450	CAN	02/01/2006 00:00:00	0.260117	1
    AB010A	RL80330	CAN	02/01/2006 00:00:00	0.112918	1
    AB010A	RL80320	CAN	02/01/2006 00:00:00	0.077977	1
    AB010A	RL80080	CAN	02/01/2006 00:00:00	0.074758	1
    AB010A	RL77950	CAN	02/01/2006 00:00:00	0.087820	1
    AB010A	RL74720	CAN	02/01/2006 00:00:00	0.888521	1
    AB010A	RL74040	CAN	02/01/2006 00:00:00	0.061950	1
    AB010A	RL87480	CAN	02/01/2006 00:00:00	0.000000	0
    AB010A	RL87090	CAN	02/01/2006 00:00:00	0.000000	0
    AB010A	RL82040	CAN	02/01/2006 00:00:00	1.101064	1.101064
    AB010A	RL80910	CAN	02/01/2006 00:00:00	0.000000	0
    AB010A	RL80500	CAN	02/01/2006 00:00:00	10.783028	10.783028
    AB010A	RL91920	CAN	02/01/2006 00:00:00	0.000000	0
    AB010A	RL91580	CAN	02/01/2006 00:00:00	0.000000	0
    AB010A	RL91440	CAN	02/01/2006 00:00:00	3.240600	3.2406
    AB010A	RL91300	CAN	02/01/2006 00:00:00	0.000000	0
    AB010A	RL91210	CAN	02/01/2006 00:00:00	4.719620	4.71962
    AB010A	RL91010	CAN	02/01/2006 00:00:00	3.374384	3.374384
    AB010A	RL90820	CAN	02/01/2006 00:00:00	4.268341	4.268341
    AB010A	RL90690	CAN	02/01/2006 00:00:00	1.831366	1.831366
    AB010A	RL96400	CAN	02/01/2006 00:00:00	0.000000	0
    AB010A	RL95800	CAN	02/01/2006 00:00:00	8.798582	8.798582
    AB010A	RL94710	CAN	02/01/2006 00:00:00	2.163569	2.163569
    AB010A	RL69430	CAN	02/01/2006 00:00:00	0.000000	0
    AB010A	RL69350	CAN	02/01/2006 00:00:00	4.071358	4.071358
    AB010A	RL92870	CAN	02/01/2006 00:00:00	0.000000	0
    AB010A	RL91050	CAN	02/01/2006 00:00:00	0.361191	1
    AB010A	RL82040	CAN	02/01/2006 00:00:00	1.420692	1.420692
    AB010A	RL74100	CAN	02/01/2006 00:00:00	0.061058	1
    AB010A	RL94220	CAN	02/01/2006 00:00:00	0.148140	1
    			                   SUM	     267	     281

  10. #10
    Join Date
    Jul 2003
    Posts
    2,296
    just use a CASE statement (works in 8.1.7.2).
    What if the value is zero?? then just use zero?

    what is the correct value you are looking for?
    281?

    I'll work on this and come up with something.

    I have your formula as being:
    PHP Code:
    if qty and qty 1 then 1
    if qty and qty > -1 then 1
    if qty or qty 0 then qty 
    any nulls???
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  11. #11
    Join Date
    Sep 2005
    Posts
    220
    I'm using 8.1.7.4.0. Can I use CASE with this version?

    The correct value should be 267

    The corrected formula is:

    if sum(qty) > 0 and sum(qty) < 1 then 1
    if sum(qty) < 0 and sum(qty) > -1 then -1
    if sum(qty) > 1 or sum(qty) = 0 or sum(qty) < -1 then sum(qty)

    summed by productgroupcode, dmdgroup

  12. #12
    Join Date
    Jul 2003
    Posts
    2,296
    why is 267 correct?
    you are rounding up (ah, but it looks like you want to sum the group and then round up).

    also, wouldn't you be summing by dmdgroup AND date???
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  13. #13
    Join Date
    Jul 2003
    Posts
    2,296
    try this as a starting point (no join).
    I included startdate in the grouping since it looked like you wanted that.
    (fyi - this would be a lot easier with 9i and analytics but oh well)

    PHP Code:
    select
      dmdgroup
    ,
      
    startdate,
      case
               
    when sum_qty and sum_qty 1  then 1
               when sum_qty 
    and sum_qty > -1 then -1
               when sum_qty 
    or  sum_qty 1  then sum_qty
               
    else 0
      end new_sum_per_dmdgrp
    from 
    (
        
    select
          dmdgroup
    ,
          
    startdate,
          
    sum(f.qtysum_qty
        from manurpt
    .tempfcst
        group by dmdgroup
    startdatef
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  14. #14
    Join Date
    Sep 2005
    Posts
    220
    I think you got it now, sum 1st, then round. Right now I round then sum, by dmdgroup and date.

  15. #15
    Join Date
    Jul 2003
    Posts
    2,296
    sweet. post if you need further help but it sounds like you are now on the right track.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •