Home » SQL & PL/SQL » SQL & PL/SQL » n00b View question!
icon5.gif  n00b View question! [message #186743] Wed, 09 August 2006 05:27 Go to next message
DomDriver
Messages: 4
Registered: August 2006
Junior Member
Hello gents.

I'm having trouble creating a view in Oracle 9i - as a bit of a n00b, I'm struggling a bit. Here's the breakdown:

I have 2 tables. Table one contains records of rolls, each record of which has a roll ID field and a cassette ID field. Table 2 is a cassette table which contains a field with cassette ID. The cassette IDs for the rolls in table one reference the cassette IDs in table two (although there is no specific foreign key set up).

I need to create a view which takes the cassette ID and the majority of the cassette data from table two, but also contains columns called roll1, roll2 etc. up to roll 5. These columns must contain the roll Ids of each record in table one which has the same cassette ID.

I was looking at using a temporary table to query table one where cassid = desired cassid, then go through the results and insert the rollid from each record in sequence into the appropriate column in the results of the view - but I don't have a clue how to start! Any suggestions?

Cheers,

Dom

Report message to a moderator

Re: n00b View question! [message #186747 is a reply to message #186743] Wed, 09 August 2006 05:58 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
Can you provide create table statements, sample data (insert statements) and desired output? Don't forget code tags.

MHE

Report message to a moderator

Re: n00b View question! [message #186752 is a reply to message #186747] Wed, 09 August 2006 06:18 Go to previous messageGo to next message
DomDriver
Messages: 4
Registered: August 2006
Junior Member
Sorry about the format:


Table 1 Creation
-------------------
CREATE TABLE roll_prop_lev
(cassid NUMBER(2,0) NOT NULL,
rollid VARCHAR2(10) NOT NULL,
rolltype VARCHAR2(1) NOT NULL,
rollpos VARCHAR2(1) NOT NULL,
rollidincass NUMBER(2,0),
actualdiameter FLOAT(25),
centerdia FLOAT(25),
coredia FLOAT(25),
actualcrown FLOAT(25))
PCTFREE 10
INITRANS 1
MAXTRANS 255
TABLESPACE c144_ts_dev
STORAGE (
INITIAL 65536
MINEXTENTS 1
MAXEXTENTS 2147483645
)
/

-- Constraints for ROLL_PROP_LEV

ALTER TABLE roll_prop_lev
ADD CONSTRAINT roll_prop_lev_pk PRIMARY KEY (rollid, cassid)
USING INDEX
PCTFREE 10
INITRANS 2
MAXTRANS 255
TABLESPACE c144_ts_dev
STORAGE (
INITIAL 65536
MINEXTENTS 1
MAXEXTENTS 2147483645
)
/

Table 2 Creation
-----------------
CREATE TABLE roll_prop_lev_cass
(cassid NUMBER(2,0) NOT NULL,
casspos VARCHAR2(1) NOT NULL,
status NUMBER(2,0) NOT NULL,
levellerid VARCHAR2(1),
totalavlrdiameter FLOAT(25),
totalavburdiameter FLOAT(25),
dateinservice DATE,
dateoutservice DATE,
levelledtons FLOAT(25),
levelledlength FLOAT(25),
changereason NUMBER(2,0),
remarktext VARCHAR2(30),
updtime DATE)
PCTFREE 10
INITRANS 1
MAXTRANS 255
TABLESPACE c144_ts_dev
STORAGE (
INITIAL 65536
MINEXTENTS 1
MAXEXTENTS 2147483645
)
/

-- Constraints for ROLL_PROP_LEV_CASS

ALTER TABLE roll_prop_lev_cass
ADD CONSTRAINT roll_prop_lev_cass_casspos_ck CHECK (casspos IN ('T', 'B'))
/

ALTER TABLE roll_prop_lev_cass
ADD CONSTRAINT roll_prop_lev_cass_levid_ck CHECK (levellerid IN ('H', 'C'))
/

ALTER TABLE roll_prop_lev_cass
ADD CONSTRAINT roll_prop_lev_cass_pk PRIMARY KEY (cassid)
USING INDEX
PCTFREE 10
INITRANS 2
MAXTRANS 255
TABLESPACE c144_ts_dev
STORAGE (
INITIAL 65536
MINEXTENTS 1
MAXEXTENTS 2147483645
)
/

Table 1 Data
---------------
INSERT INTO roll_prop_lev
(CASSID,ROLLID,ROLLTYPE,ROLLPOS,ROLLIDINCASS,ACTUALDIAMETER,CENTERDIA,COREDIA,ACTUALCROWN)
VALUES
(99,'100','L','T',1,322,150,100,1)
/
INSERT INTO roll_prop_lev
(CASSID,ROLLID,ROLLTYPE,ROLLPOS,ROLLIDINCASS,ACTUALDIAMETER,CENTERDIA,COREDIA,ACTUALCROWN)
VALUES
(99,'101','L','T',2,321,150,100,1)
/
INSERT INTO roll_prop_lev
(CASSID,ROLLID,ROLLTYPE,ROLLPOS,ROLLIDINCASS,ACTUALDIAMETER,CENTERDIA,COREDIA,ACTUALCROWN)
VALUES
(99,'102','L','T',3,322,150,100,0)
/

Table 2 Data
------------
INSERT INTO roll_prop_lev_cass
(CASSID,CASSPOS,STATUS,LEVELLERID,TOTALAVLRDIAMETER,TOTALAVBURDIAMETER,DATEINSERVICE,DATEOUTSERVICE,LEVELLEDTONS,LEVELLEDLENGTH,CHANG EREASON,REMARKTEXT,UPDTIME)
VALUES
(99,'T',-1,'H',320,0,'31 JULY 2006','08 AUGUST 2006',1,1,1,'Broken','09 AUGUST 2006')
/




Phew! This basically means that there are 3 rolls for cassette 99, with roll Ids 100,101,102.

The view I want to create will be the majority of the data from table 2, plus 5 columns with RollId1, RollId2 ... RollId5. In this particualr case, Columns RollId4 and RollId5 will not contain any data.

Thanks in advance!

Dom

PS - Is there some fancy method for posting code in the thread? Or is it alright just in the text?

Report message to a moderator

Re: n00b View question! [message #186753 is a reply to message #186743] Wed, 09 August 2006 06:25 Go to previous messageGo to next message
DomDriver
Messages: 4
Registered: August 2006
Junior Member
Sorry. Here you go.

Table 1 Creation
-------------------
CREATE TABLE roll_prop_lev
    (cassid                         NUMBER(2,0) NOT NULL,
    rollid                         VARCHAR2(10) NOT NULL,
    rolltype                       VARCHAR2(1) NOT NULL,
    rollpos                        VARCHAR2(1) NOT NULL,
    rollidincass                   NUMBER(2,0),
    actualdiameter                 FLOAT(25),
    centerdia                      FLOAT(25),
    coredia                        FLOAT(25),
    actualcrown                    FLOAT(25))
  PCTFREE     10
  INITRANS    1
  MAXTRANS    255
  TABLESPACE  c144_ts_dev
  STORAGE   (
    INITIAL     65536
    MINEXTENTS  1
    MAXEXTENTS  2147483645
  )
/

-- Constraints for ROLL_PROP_LEV

ALTER TABLE roll_prop_lev
ADD CONSTRAINT roll_prop_lev_pk PRIMARY KEY (rollid, cassid)
USING INDEX
  PCTFREE     10
  INITRANS    2
  MAXTRANS    255
  TABLESPACE  c144_ts_dev
  STORAGE   (
    INITIAL     65536
    MINEXTENTS  1
    MAXEXTENTS  2147483645
  )
/

Table 2 Creation
-----------------
CREATE TABLE roll_prop_lev_cass
    (cassid                         NUMBER(2,0) NOT NULL,
    casspos                        VARCHAR2(1) NOT NULL,
    status                         NUMBER(2,0) NOT NULL,
    levellerid                     VARCHAR2(1),
    totalavlrdiameter              FLOAT(25),
    totalavburdiameter             FLOAT(25),
    dateinservice                  DATE,
    dateoutservice                 DATE,
    levelledtons                   FLOAT(25),
    levelledlength                 FLOAT(25),
    changereason                   NUMBER(2,0),
    remarktext                     VARCHAR2(30),
    updtime                        DATE)
  PCTFREE     10
  INITRANS    1
  MAXTRANS    255
  TABLESPACE  c144_ts_dev
  STORAGE   (
    INITIAL     65536
    MINEXTENTS  1
    MAXEXTENTS  2147483645
  )
/

-- Constraints for ROLL_PROP_LEV_CASS

ALTER TABLE roll_prop_lev_cass
ADD CONSTRAINT roll_prop_lev_cass_casspos_ck CHECK (casspos IN ('T', 'B'))
/

ALTER TABLE roll_prop_lev_cass
ADD CONSTRAINT roll_prop_lev_cass_levid_ck CHECK (levellerid IN ('H', 'C'))
/

ALTER TABLE roll_prop_lev_cass
ADD CONSTRAINT roll_prop_lev_cass_pk PRIMARY KEY (cassid)
USING INDEX
  PCTFREE     10
  INITRANS    2
  MAXTRANS    255
  TABLESPACE  c144_ts_dev
  STORAGE   (
    INITIAL     65536
    MINEXTENTS  1
    MAXEXTENTS  2147483645
  )
/

Table 1 Data
---------------
INSERT INTO roll_prop_lev
(CASSID,ROLLID,ROLLTYPE,ROLLPOS,ROLLIDINCASS,ACTUALDIAMETER,CENTERDIA,COREDIA,ACTUALCROWN)
VALUES
(99,'100','L','T',1,322,150,100,1)
/
INSERT INTO roll_prop_lev
(CASSID,ROLLID,ROLLTYPE,ROLLPOS,ROLLIDINCASS,ACTUALDIAMETER,CENTERDIA,COREDIA,ACTUALCROWN)
VALUES
(99,'101','L','T',2,321,150,100,1)
/
INSERT INTO roll_prop_lev
(CASSID,ROLLID,ROLLTYPE,ROLLPOS,ROLLIDINCASS,ACTUALDIAMETER,CENTERDIA,COREDIA,ACTUALCROWN)
VALUES
(99,'102','L','T',3,322,150,100,0)
/

Table 2 Data
------------
INSERT INTO roll_prop_lev_cass
(CASSID,CASSPOS,STATUS,LEVELLERID,TOTALAVLRDIAMETER,TOTALAVBURDIAMETER,DATEINSERVICE,DATEOUTSERVICE,LEVELLEDTONS,LEVELLEDLENGTH,CHANGEREASON,REMARKTEXT,UPDTIME)
VALUES
(99,'T',-1,'H',320,0,'31 JULY 2006','08 AUGUST 2006',1,1,1,'Broken','09 AUGUST 2006')
/

Report message to a moderator

Re: n00b View question! [message #186755 is a reply to message #186753] Wed, 09 August 2006 06:38 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
I've simplified your sample a little bit. Here's my script:
CREATE TABLE table1(cassid NUMBER(2,0) NOT NULL,
                           rollid VARCHAR2(10) NOT NULL,
                           CONSTRAINT table1_pk PRIMARY KEY (rollid, cassid)
                          )
/

CREATE TABLE table2(cassid NUMBER(2,0) NOT NULL,
                                CONSTRAINT table2_pk PRIMARY KEY (cassid)
                               )
/


INSERT INTO table1(CASSID,ROLLID) VALUES (99,'100')
/
INSERT INTO table1(CASSID,ROLLID) VALUES (99,'101')
/
INSERT INTO table1(CASSID,ROLLID) VALUES (99,'102')
/

INSERT INTO table2(CASSID) VALUES (99)
/

SELECT *
FROM table1
/

SELECT *
FROM table2
/

SELECT t2.cassid
     , MAX(DECODE(rn, 1, t1.rollid, null)) rollid1
     , MAX(DECODE(rn, 2, t1.rollid, null)) rollid2
     , MAX(DECODE(rn, 3, t1.rollid, null)) rollid3
     , MAX(DECODE(rn, 4, t1.rollid, null)) rollid4
     , MAX(DECODE(rn, 5, t1.rollid, null)) rollid5
FROM table2 t2
   , ( SELECT cassid
            , rollid
            , ROW_NUMBER() OVER ( PARTITION BY cassid ORDER BY rollid) rn
       FROM   table1
     ) t1
WHERE t1.cassid = t2.cassid
GROUP BY t2.cassid
/

DROP TABLE table1
/
DROP TABLE table2
/


And here's the output:
SQL> @orafaq

Table created.


Table created.


1 row created.


1 row created.


1 row created.


1 row created.


    CASSID ROLLID
---------- ----------
        99 100
        99 101
        99 102


    CASSID
----------
        99


    CASSID ROLLID1    ROLLID2    ROLLID3    ROLLID4    ROLLID5
---------- ---------- ---------- ---------- ---------- ----------
        99 100        101        102


Table dropped.


Table dropped.

SQL>


I could have told you to search the board for pivot queries but you tried that already, right?

MHE

Report message to a moderator

Re: n00b View question! [message #186761 is a reply to message #186755] Wed, 09 August 2006 06:53 Go to previous message
DomDriver
Messages: 4
Registered: August 2006
Junior Member
I would've searched the forums for pivot queries - but I've never heard that term before, so I didn't know that's what it's called!

That's the trouble when you're new and you don't work with DBs every day - I could describe what i wanted in C++, but don't know the word for what I want to do in SQL!

Cheers for the response,

Dom

Report message to a moderator

Previous Topic: Return structure from DLL to PL/SQL
Next Topic: Error during fetching data in cursor
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue May 19 22:32:27 CDT 2026
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.