Home » SQL & PL/SQL » SQL & PL/SQL » New Column based on Logical Test
New Column based on Logical Test [message #21688] Thu, 22 August 2002 13:29 Go to next message
sat
Messages: 3
Registered: January 2001
Junior Member
Hi there,

I have a table that has the following:-

cust invdat invno invduedat vendor_no
1000 060702 I00001 071502 V1000
1000 060702 I00002 071502 V1000
1000 060702 I00003 072502 V2000
2000 060702 I00004 071502 V3000
2000 060702 I00005 071502 V3000
2000 060702 I00006 072502 V4000
2000 060702 I00007 072502 V4000

The customer's terms depend on vendor terms which in this case is V1000 & V3000 = 15th following month while V2000 & V4000 =25th following month.
I would like to change all Invoice_due_dates <=07/15/02 to 09/15/02 for vendor in 15th groupf (V1000 & V3000) and move all invoice_due_dates <=07/25/02 to 09/25/02 for Vendors V2000 & V4000)
Is there a way in SQL to create a New Column (as in example below) to accomplish this mission. Thanks in Adavnce

Example
-------

cust invdat invno invduedat vendor_no new_invduedat
1000 060702 I00001 071502 V1000 091502
1000 060702 I00002 071502 V1000 091502
1000 060702 I00003 072502 V2000 092502
2000 060702 I00004 071502 V3000 091502
2000 060702 I00005 071502 V3000 091502
2000 060702 I00006 072502 V4000 092502
2000 060702 I00007 072502 V4000 092502

Report message to a moderator

Re: New Column based on Logical Test [message #21689 is a reply to message #21688] Thu, 22 August 2002 16:24 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
In 8i you can use case - before that you need to use a combination of decode() and sign().

create table xxx(cust number, invdat date, invno varchar2(10), invduedat date, vendor_no varchar2(10));

insert into xxx values( 1000, to_date('060702', 'mmddrr'), 'I00001', to_date('071502', 'mmddrr'), 'V1000');
insert into xxx values( 1000, to_date('060702', 'mmddrr'), 'I00002', to_date('071502', 'mmddrr'), 'V1000');
insert into xxx values( 1000, to_date('060702', 'mmddrr'), 'I00003', to_date('072502', 'mmddrr'), 'V2000');
insert into xxx values( 2000, to_date('060702', 'mmddrr'), 'I00004', to_date('071502', 'mmddrr'), 'V3000');
insert into xxx values( 2000, to_date('060702', 'mmddrr'), 'I00005', to_date('071502', 'mmddrr'), 'V3000');
insert into xxx values( 2000, to_date('060702', 'mmddrr'), 'I00006', to_date('072502', 'mmddrr'), 'V4000');
insert into xxx values( 2000, to_date('060702', 'mmddrr'), 'I00007', to_date('072502', 'mmddrr'), 'V4000');
commit;

create view xxx_v as
(select cust, invdat, invno, invduedat, vendor_no,
case
when vendor_no in ('V1000', 'V3000') and Invduedat <= to_date('07/15/02', 'mm/dd/rr') then to_date('09/15/02', 'mm/dd/rr')
when vendor_no in ('V2000', 'V4000') and Invduedat <= to_date('07/25/02', 'mm/dd/rr') then to_date('09/25/02', 'mm/dd/rr')
else to_date(null)
end new_invduedat
from xxx);

select * from xxx_v;
1000 6/7/02 I00001 7/15/02 V1000 9/15/02
1000 6/7/02 I00002 7/15/02 V1000 9/15/02
1000 6/7/02 I00003 7/25/02 V2000 9/25/02
2000 6/7/02 I00004 7/15/02 V3000 9/15/02
2000 6/7/02 I00005 7/15/02 V3000 9/15/02
2000 6/7/02 I00006 7/25/02 V4000 9/25/02
2000 6/7/02 I00007 7/25/02 V4000 9/25/02

Report message to a moderator

Previous Topic: Bind variable "COUNT" not declared.
Next Topic: UTL FILE DIR Error
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Wed Apr 24 15:11:14 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.