RE: RE: FW: Query
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
>>
>> 5
>>
>>
>>
>> Appear as
>>
>> 1 2 3 4 5
>>
>>
>>
>> Is it possible?
>>
>>
>>
>> Thanx,
>>
>> S m
>> sending to informix-list
sending to informix-list
Jean Sagi
jeansagi@myrealbox.com
jeansagi@gmail.com
sending to informix-list
shahid.mehmood escribis:
> Sending again ... I didn't get the reply ... IF you have already done
so ... Sorry about that ... But ...
>
> 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