Re: Tough One: How do u denormailize a table via a view, if column is text not numb

From: Paul Quenneville <paulq_at_home.com>
Date: Wed, 31 Oct 2001 03:39:54 GMT
Message-ID: <eoKD7.119382$5h5.47978308@news3.rdc2.on.home.com>

• Data definitions and population

DROP TABLE A;
 CREATE TABLE A
  ( REQ NUMBER,
    LINE_NUM NUMBER,
    DESCP VARCHAR2(30))
 /
 INSERT INTO A
 VALUES(9,10000,'PED IS OFF');
 INSERT INTO A
 VALUES(9,20000,'SO PROCEED');
 INSERT INTO A
 VALUES(9,30000,'WITH X'); DROP TABLE B;
CREATE TABLE B
( REQ NUMBER,
  LOCATION VARCHAR2(10),
  WHEN DATE)
/

INSERT INTO B
VALUES(9,'LA',SYSDATE);
INSERT INTO B
VALUES(10,'NY',SYSDATE);

• The function CREATE OR REPLACE FUNCTION GET_FULL_DESC (P_REQ IN NUMBER) RETURN VARCHAR2 IS

  V_LVAR VARCHAR2(255);
CURSOR SEL_DESC IS
  SELECT DESCP
  FROM A
  WHERE REQ= P_REQ
  ORDER BY LINE_NUM;
 DESC_REC SEL_DESC%ROWTYPE;
BEGIN  OPEN SEL_DESC;
  LOOP
  FETCH SEL_DESC INTO DESC_REC;
  EXIT WHEN SEL_DESC%NOTFOUND;
  V_LVAR := V_LVAR || DESC_REC.DESCP||' ';  END LOOP; RETURN (V_LVAR); END;
/

-- The query

SELECT REQ,LOCATION,WHEN,GET_FULL_DESC(REQ) FROM B
WHERE REQ=9 Modify to your hearts content, I assumed req and line_num are numbers in both tables.

"johnthan" <jthn342_at_hotmail.com> wrote in message news:3bdf1269$0$968$45beb828_at_newscene.com...

> Wow a function what is that?
>
> Of course that that what I need, I was asking for an algorithum to

implement
> it. Grouping requires some sort of aggreate function, a sum, max, etc. it

> doesn't work if u dont use that.  I need a technique to get around that
>
>
> In article <jhCD7.117316$5h5.47365719_at_news3.rdc2.on.home.com>, "Paul
> Quenneville" <paulq_at_home.com> wrote:
> >write a function to return the full concatenatedstring from table A
> >
> >"johnthan" <jthn342_at_hotmail.com> wrote in message
> >news:3bdee6fe$0$14833$45beb828_at_newscene.com...
> >>
> >> We have a table   A as such:
> >>
> >> REQ      LINE_NUM    DESCP
> >>
> >>       9        10000        PED IS OFF
> >>       9        20000        SO PROCEED
> >>       9        30000        WITH X
> >>
> >>
> >> that needs to be joined to a table  B   as such
> >>
> >>
> >> REQ     location        date
> >>
> >> 9               LA              01/01/01
> >>
> >> to produce
> >>
> >>
> >> REQ     location           date                 DESCP
> >>
> >> 9               LA              01/01/01          PED IS OFF SO PROCEED
> >WITH X
> >>
> >>
> >> (the app that creates the table A only allows x number of characters

per
> >> column if DESCP is longer than that it creates additional rows until whole

> >> DESCP is stored)
> >>
> >> I know how to denormalize a table if the column that is denormalized is

a

> >> number e.g.
> >> SELECT a,b,
> >> sum(decode(y, z, 99, 0)),
> >> sum(decode(y, z, 88, 0))
> >> FROM TB1
> >>        GROUP BY a,b
> >>
> >> but what if its a varchar?
> >>
> >> We need this done dynamically via a view
> >>
> >> Any ideas?
> >>
> >
> >

Received on Tue Oct 30 2001 - 21:39:54 CST