1. Registered User
Join Date
Sep 2005
Posts
220

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. Moderator.
Join Date
Sep 2002
Location
UK
Posts
5,171
Yes, using DECODE and SIGN:

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

Simple!?

3. Registered User
Join Date
Sep 2005
Posts
220
Thanks a lot

4. Registered User
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. Lost Boy
Join Date
Jan 2004
Location
Croatia, Europe
Posts
4,108
DECODE can be used in SELECT statement only; you can not, for example, do something like

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

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. Registered User
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. Registered User
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?

8. Registered User
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. Registered User
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. Registered User
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 > 0 and qty < 1 then 1 if qty < 0 and qty > -1 then 1 if qty > 1 or qty = 0 then qty  ```
any nulls???

11. Registered User
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. Registered User
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???

13. Registered User
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 > 0 and sum_qty < 1  then 1            when sum_qty < 0 and sum_qty > -1 then -1            when sum_qty = 0 or  sum_qty > 1  then sum_qty            else 0   end new_sum_per_dmdgrp from (     select       dmdgroup,       startdate,       sum(f.qty) sum_qty     from manurpt.tempfcst     group by dmdgroup, startdate) f;  ```

14. Registered User
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. Registered User
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.

#### Posting Permissions

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