Re: any thoughts please?!

On Dec 5, 2:36 am, Totti <saliba.toufic.geo..._at_gmail.com> wrote:
> CREATE TABLE SQL_TABL (
> ORD_DATE Date,
> PROD_CODE Varchar2(10),
> CUST_CODE Varchar2(10),
> QTY Number(2),
> UNIT_PRICE Number(5,2),
> SHIP_DATE Date,
> CARR_CODE Varchar2(12),
> SHIP_COST Number(5,2),
> PMT_DATE Date
> );
>
> insert into SQL_TABL values ('08-Jan-05', 'A-50-0013', 'Cu-08-143',
> 4,
> 181.79, '09-Jan-05', 'TransCo', 60.27, '15-Jan-05');
> insert into SQL_TABL values ('15-Jan-05', 'A-25-0753', 'Cu-08-180',
> 13, 265.61, '23-Jan-05', 'Expedit', 278.36, '29-Jan-05');
> insert into SQL_TABL values ('19-Jan-05', 'A-24-0101', 'Cu-08-133',
> 20, 120.18, '22-Jan-05', 'Expedit', 188.28, '29-Jan-05');
> insert into SQL_ASSGN_2 values ('30-Jan-05', 'B-63-0023', 'Cu-4-078',
> 4,
> 280.88, '09-Feb-05', 'Expedit', 92.93, '04-Feb-05');
>
> hi every one i have a a table like such and many things to do on it,
> since i am new to sql and i know how important it is, i want you
> please to help if you see me posting again and agian dont get bored
> with me please, i ll be posting my attempts and whoever wants can
> help me, as a first task i want to
>
> A - Produce total sales per ratio of Delivery/Payment (ratio of
> interval lengths)
> and Group them in intervals of 0.2.
> (for this A question i ahve no idea guys so if you want to help
> please
> explain me the procedure)
>
> B - Produce total sales per Product Hyper group.
> (A hyper group is a group of Product Group Codes. Group codes from 0
> to 9 form Group0. Codes from 10 to 19 form Group10 etc.)
>
> and here is my attempt which doesnt work because of 2 reasons i
> suspect :
> select (QTY*UNIT_PRICE), to_number(substr(PROD_CODE,3,2)) % 10 from
> SQL_TABL
> 1st is because of the 2 inthe substr thing since it is not dynamic so
> a group of one character wont be acceptable as i think , i am not
> sure
> at all and the second is that i am trying to use % for modulo, you
> experts know,
> NB for anybody who helps
> first thanks and second please give me some details to know whats the
> philosophy because i am here to improve my skills

To get the product group use
SUBSTR(prod_code,1,LENGTH(prod_code)-1)||'0'

This will strip off the last character of the prod_code and replace it with a 0. That's what I understand you're trying to do. Received on Thu Dec 06 2007 - 11:09:22 CST