Re: Create Multi record

Hi,

"AK" <ak_tiredofspam_at_yahoo.com>
??????:46e627da.0403251714.34a81587_at_posting.google.com...
> mhakimjavadi_at_hotmail.com (Mohammad) wrote in message
 news:<cc59b627.0403251102.65817289_at_posting.google.com>...
> > Hi
> >
> > I am very new in SQL and i need you help.I have record like :
> >
> > IDNUM1 XX YY ZZ (one record FOR ONE IDNUM)
> > IDNUM2 XX YY ZZ
> > and I have to make new table of those record like this : (three record
> > for one IDNUM)
> >
> > IDNUM1 XX
> > IDNUM1 YY
> > IDNUM1 ZZ
> > IDNUM2 XX
> > IDNUM2 YY
> > IDNUM2 ZZ
> >
> > Please advise.
> >
> > Thanks a lot
>
> /* NOT TESTED */
> CREATE TABLE NUMBERS(N NUMBER(1));
> INSERT INTO NUMBERS VALUES(1);
> INSERT INTO NUMBERS VALUES(2);
> INSERT INTO NUMBERS VALUES(3);
>
> SELECT
> IDNUM,
> CASE
> WHEN NUMBERS.N=1 THEN XX
> WHEN NUMBERS.N=2 THEN YY
> WHEN NUMBERS.N=3 THEN ZZ
> END
> FROM YOUR_TABLE CROSS JOIN NUMBERS
For performance issue in Oracle (Please refer <Oracle performance and tunning>), avoid using JOIN if possible, and avoid using CASE if possible, especially avoid using them together. It costs much data buffer cache and CPU resource. More SQLs cost also more space in shared pool and SQLs (in shared pool, it is called curser) parse time. Received on Fri Mar 26 2004 - 22:04:36 CET