RE: RE: FW: Query
DECODE works perfectly well in Informix, I've used it for several years and
I'm still on 7.31 :(
Regards
Colin
There are 10 types of people in the world, those that understand binary and
those that don't
>From: "shahid.mehmood" <shahid.mehmood@aku.edu>
>To: "Jean Sagi" <jeansagi@myrealbox.com>
>CC: <informix-list@iiug.org>
>Subject: RE: RE: FW: Query
>Date: Wed, 14 Sep 2005 14:43:43 +0500
>
>Thanks a lot J ...
>
>But "decode" runs on ORACLE only! Am I right?
>How about Informix?
>
>I missed the email from Doug Lawry ... Can you forward that to me?
>
>I have IX 7.3 on SCO UNIX 5.
>
>Thanks
>Sm/..
>
>
>-----Original Message-----
>From: owner-informix-list@iiug.org [mailto:owner-informix-list@iiug.org]
>On Behalf Of Jean Sagi
>Sent: Monday, September 12, 2005 07:43 PM
>To: shahid.mehmood
>Cc: informix-list@iiug.org
>Subject: Re: RE: FW: Query
>
>Well,
>
>Then suppose you do the following:
>
>create table sales(
> year_month CHAR(7), -- No datetime for now
> city CHAR(20),
> sale DECIMAL(10, 2)
>)>;
>
>insert into sales values ('2005-01', 'Cali', 100);
>insert into sales values ('2005-01', 'Buga', 29);
>insert into sales values ('2005-01', 'Palmira', 20);
>insert into sales values ('2005-02', 'Cali', 120);
>insert into sales values ('2005-02', 'Buga', 12);
>insert into sales values ('2005-02', 'Palmira', 45);
>insert into sales values ('2005-03', 'Cali', 80);
>insert into sales values ('2005-03', 'Buga', 9);
>insert into sales values ('2005-03', 'Palmira', 75);>
>Displaying result for:
>---------------------
>select *
>from sales>;
>
>year_month city sale
>---------- ---------- ------------
>2005-01 Cali 100.00
>2005-01 Buga 29.00
>2005-01 Palmira 20.00
>2005-02 Cali 120.00
>2005-02 Buga 12.00
>2005-02 Palmira 45.00
>2005-03 Cali 80.00
>2005-03 Buga 9.00
>2005-03 Palmira 75.00
>
>9 Row(s) affected
>
>Then this migth be what you want:
>
>phase 1:
>Displaying result for:
>---------------------
>select city,
> decode( year_month, '2005-01', sale, 0) _2005_01,
> decode( year_month, '2005-02', sale, 0) _2005_02,
> decode( year_month, '2005-03', sale, 0) _2005_03
>from sales>;
>
>city _2005_01 _2005_02 _2005_03
>---------- -------------- -------------- --------------
>Cali 100.00 0.00 0.00
>Buga 29.00 0.00 0.00
>Palmira 20.00 0.00 0.00
>Cali 0.00 120.00 0.00
>Buga 0.00 12.00 0.00
>Palmira 0.00 45.00 0.00
>Cali 0.00 0.00 80.00
>Buga 0.00 0.00 9.00
>Palmira 0.00 0.00 75.00
>
>9 Row(s) affected
>
>Do you see the trick to acomodate sales in their respective columns?
>
>phase 2
>Now finally a group by do it:
>
>Displaying result for:
>---------------------
>select city,
> sum( decode( year_month, '2005-01', sale, 0) ) _2005_01,
> sum( decode( year_month, '2005-02', sale, 0) ) _2005_02,
> sum( decode( year_month, '2005-03', sale, 0) ) _2005_03
>from sales
>order by 3 desc>;
>
>city _2005_01 _2005_02 _2005_03
>
>---------- -------------- -------------- --------------
>Cali 100.00 120.00 80.00
>Palmira 20.00 45.00 75.00
>Buga 29.00 12.00 9.00
>
>3 Row(s) affected
>
>That is what we want!
>
>Now the totals:
>
>Displaying result for:
>---------------------
>select 'Totals',
> sum( decode( year_month, '2005-01', sale, 0)) _2005_01,
> sum( decode( year_month, '2005-02', sale, 0)) _2005_02,
> sum( decode( year_month, '2005-03', sale, 0)) _2005_03
>from sales
>;
>
> _2005_01 _2005_02 _2005_03
>------ ------------ ------------ ------------
>Totals 149.00 177.00 164.00
>
>1 Row(s) affected
>
>
>
>J.
>
>
>-----Original Message-----
>From: "shahid.mehmood" <shahid.mehmood@aku.edu>
>To: "Jean Sagi" <jeansagi@myrealbox.com>
>Date: Mon, 12 Sep 2005 18:40:13 +0500
>Subject: RE: FW: Query
>
>Thank you very much for replying me again. Earlier something went wrong
>with my M$-Outlook, and I lost lots email on Saturday ... My requirement
>is similar to following
>
>City 2005-01 2005-02 2005-03
>-------- ------- ------- -------
>Cali 100 120 80
>Palmira 20 45 75
>Buga 29 12 9
>
>Thanks
>Sm/..
>
>-----Original Message-----
>From: owner-informix-list@iiug.org [mailto:owner-informix-list@iiug.org]
>On Behalf Of Jean Sagi
>Sent: Monday, September 12, 2005 11:28 AM
>To: shahid.mehmood
>Cc: informix-list@iiug.org
>Subject: Re: FW: Query
>
>I previosuly wrote:
>
>Try what Doug Lawry wrote.
>
>If that don't help, then What exactly are you trying to do? An example
>could clarify.
>
>J.
>
>PS:
>
>For example let say you have a table like this:
>
>Year-Month City Sale
>---------- -------- ----
>2005-01 Cali 100
>2005-01 Buga 29
>2005-01 Palmira 20
>2005-02 Cali 120
>2005-02 Buga 12
>2005-02 Palmira 45
>2005-03 Cali 80
>2005-03 Buga 9
>2005-03 Palmira 75
>
>Do you need a reorganization like this?
>
>City 2005-01 2005-02 2005-03
>-------- ------- ------- -------
>Cali 100 120 80
>Palmira 20 45 75
>Buga 29 12 9
>
>Maybe totals?
>
>2005-01 2005-02 2005-03
>------- ------- -------
> 149 177 164
>
>
>-----Original Message-----
>From: "shahid.mehmood" <shahid.mehmood@aku.edu>
>To: "Jean Sagi" <jeansagi@myrealbox.com>
>Date: Fri, 9 Sep 2005 17:28:04 +0500
>Subject: RE: FW: Query
>
>I am novice to this ... Can you send me the SQL for limited number of
>columns, say 5?
>
>Thanks
>Sm/..
>
>-----Original Message-----
>From: Jean Sagi [mailto:jeansagi@myrealbox.com]
>Sent: Friday, September 09, 2005 10:55 AM
>To: shahid.mehmood
>Cc: informix-list@iiug.org
>Subject: Re: FW: Query
>
>It could be done, but you must know the exact number of columns, if so
>then You can use decode to achieve what you want.
>
>If the number of columns is not know then I don't know, althoug in this
>case I think that could not be done using normal SQL.
>
>
>J.
>
>
>shahid.mehmood escribis:
>
> >> I want the return rows into columns, like >> >> >>
> >> The return rows in a select
> >>
> >> 1
> >>
> >> 2
> >>
> >> 3
> >>
> >> 4@@