Re: Newbie SQL help needed - URGENT

Thanks Karsten. When I sais that it does not work I meant that I am not getting the desired result. If you look at the whole query - then I am selecting from 3 different tables and grouping by "by a.storeID,a.STORE_NM" but in the last part of the query where I select c.BRD_ALT_DS,c.EVENT_START_DT,c.PRICE_AMT for the vrious products that the store sold - I am not doing any SUM or MAX so now in the group by clause I have to include these three (c.BRD_ALT_DS, c.EVENT_START_DT, c.PRICE_AMT) fields and hence for each store I get say 'n' number of lines for 'n' products. What I need is for each store I should get the STORE_ID,STORE_NM that is selected from table A and then CATG information from table B and the
c.BRD_ALT_DS,c.EVENT_START_DT,c.PRICE_AMT for the 'n' products that the store sold from table C.
I think this is not possible with a single SQL and maybe I have to write a PL/SQL (cursor??). I am new and would appreciate your help.

Thanks

Karsten Farrell <kfarrell_at_belgariad.com> wrote in message news:<MPG.193826d437f4a8c2989796_at_news.la.sbcglobal.net>...
> pawanputrahanuman_at_yahoo.com said...
> > Thanks Karsten. But it does not work.
> >
> > Karsten Farrell <kfarrell_at_belgariad.com> wrote in message news:<MPG.19368fe941161d5e98978d_at_news.la.sbcglobal.net>...
> > > pawanputrahanuman_at_yahoo.com said...
> > > [snip]
> > > > SUM(decode(upper(b.CATG),'OTH',b.PMT_AMT,null)) ||'|'||
> > > > c.BRD_ALT_DS,
> > > > c.EVENT_START_DT,
> > > > c.PRICE_AMT
> [snip]
> > > >
> > > sum(decode(upper(b.catg),'OTH',b.pmt_amt,null)) || '|' ||
> > > c.brd_alt_ds || '|' ||
> > > to_char(c.event_start_dt,'yyyy-mm-dd') || '|' ||
> > > to_char(c.price_amt,'999.99')
> >
> Depends on how you define "does not work."
>
> SQL> create table xyz(
> 2 mystring varchar2(10),
> 3 mydate date,
> 4 myamt number(8,2));
>
> Table created.
>
> SQL> insert into xyz values ( 'Product1',
> 2 to_date('2003-01-01','yyyy-mm-dd'),
> 3 3.94);
>
> 1 row created.
>
> SQL> insert into xyz values ( 'Product2',
> 2 to_date('2003-02-01','yyyy-mm-dd'),
> 3 3.65);
>
> 1 row created.
>
> SQL> insert into xyz values ( 'Product3',
> 2 to_date('2003-02-22','yyyy-mm-dd'),
> 3 6.64);
>
> 1 row created.
>
> SQL> insert into xyz values ( 'Product3',
> 2 to_date('2003-01-01','yyyy-mm-dd'),
> 3 9.58);
>
> 1 row created.
>
> SQL> insert into xyz values ( 'Product4',
> 2 to_date('2003-01-01','yyyy-mm-dd'),
> 3 12.99);
>
> 1 row created.
>
> SQL> select mystring || '|' ||
> 2 to_char(mydate,'yyyy-mm-dd') || '|' ||
> 3 to_char(myamt,'999.99')
> 4 from xyz;
>
> MYSTRING||'|'||TO_CHAR(MYDATE
> -----------------------------
> Product1|2003-01-01| 3.94
> Product2|2003-02-01| 3.65
> Product3|2003-02-22| 6.64
> Product3|2003-01-01| 9.58
> Product4|2003-01-01| 12.99
>
> 5 rows selected.
>
> SQL>
Received on Sat May 24 2003 - 08:44:12 CDT