Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2010
    Posts
    5

    Question Unanswered: Need help with sybase query

    How can i achieve the following in sybase also? (The following code is working fine in Oracle):

    Code:
    CREATE TABLE TESTMASTER (
        ID1 NUMERIC(4,0) NOT NULL,
        ID2 NUMERIC(4,0) NOT NULL,
        DESCRIPTION VARCHAR(30) NOT NULL,
     PRIMARY KEY ( ID1, ID2 )  );
    
    CREATE TABLE TESTDETAIL (    
        ID1  NUMERIC(4,0) NOT NULL  ,
        ID2  NUMERIC(4,0) NOT NULL  ,
        SNO NUMERIC(4,0) NOT NULL,
        TESTCOL VARCHAR(10) NOT NULL  ,
     PRIMARY KEY ( ID1, ID2, SNO ) );
    
    insert into testMaster(id1, id2, description)
    values(1, 1, 'hello');
    insert into testMaster(id1, id2, description)
    values(1, 2, 'hello'); 
    
    INSERT INTO TESTDETAIL(ID1, ID2, 
      SNO,
      TESTCOL)
    SELECT A.ID1, A.ID2, 
      (SELECT NVL(MAX(SNO),0) FROM TESTDETAIL) + ROWNUM AS SNO,
      'HELLO'
    FROM TESTMASTER A, (SELECT 1 ID1, 1 ID2 FROM DUAL UNION ALL SELECT 1 ID1, 1 ID2 FROM DUAL ) B
    WHERE A.ID1 = B.ID1
    AND A.ID2 = B.ID2 ;
    
    select * from TESTDETAIL;
    -- RESULT --
    ID1 ID2 SNO TESTCOL
    1    1    1	 HELLO
    1    1    2	 HELLO

    i tried the following in sybase, but sybase doesnt support ROWNUM keyword:

    Code:
    CREATE TABLE TESTMASTER (
        ID1 NUMERIC(4,0) NOT NULL,
        ID2 NUMERIC(4,0) NOT NULL,
        DESCRIPTION VARCHAR(30) NOT NULL,
     PRIMARY KEY ( ID1, ID2 )  );
    
    CREATE TABLE TESTDETAIL (    
        ID1  NUMERIC(4,0) NOT NULL  ,
        ID2  NUMERIC(4,0) NOT NULL  ,
        SNO NUMERIC(4,0) NOT NULL,
        TESTCOL VARCHAR(10) NOT NULL  ,
     PRIMARY KEY ( ID1, ID2, SNO ) );
    
    select * from TESTMASTER;
    select * from TESTDETAIL;
    
    INSERT INTO TESTMASTER(ID1, ID2, DESCRIPTION)
    VALUES(1, 1, 'hello');
    INSERT INTO TESTMASTER(ID1, ID2, DESCRIPTION)
    VALUES(1, 2, 'hello');
    
    INSERT INTO TESTDETAIL(ID1, ID2, 
      SNO,
      TESTCOL)
    SELECT A.ID1, A.ID2, 
      (SELECT ISNULL(MAX(SNO),0) FROM TESTDETAIL) + ROWNUM AS SNO,
      'HELLO'
    FROM TESTMASTER A, (SELECT 1 ID1, 1 ID2 UNION ALL SELECT 1 ID1, 1 ID2) B
    WHERE A.ID1 = B.ID1 
    AND A.ID2 = B.ID2 ;
    I also tried identity column but it seems it doesnt generate value based on a compostie key.

    Note: i need to only do this using a query, and dont want a solution based on any procedure.

    Thanks

  2. #2
    Join Date
    Jun 2010
    Posts
    51
    Can you please write what you want in English also? It will help us to understand what you are trying to do.

    Also,
    Shouldn't ID2 be 2 for 2nd row?

    Code:
    ID1 ID2 SNO TESTCOL
    1    1    1	 HELLO
    1    2    2	 HELLO

    Thanks,
    Meet
    Please always reply to the post if it was helpful. Others may find it helpful.

  3. #3
    Join Date
    Oct 2010
    Posts
    5
    In TESTDETAIL table ID1 and ID2 are the foriegn keys coming from TESTMASTER table and ID1,ID2 and SNO are composite primary key of detail table.

    I need to generate a serial number for column SNO based on ID1 and ID2.

    e.g. if for master table record ID1=1 ID2=1, has two detail rows then SNO should be like following:

    ID1 ID2 SNO TESTCOL
    1 1 1 HELLO
    1 1 2 HELLO

    e.g. if for another master table record ID1=1 ID2=2, has three detail rows then SNO should be like following:

    ID1 ID2 SNO TESTCOL
    1 2 1 HELLO
    1 2 2 HELLO
    1 2 3 HELLO

  4. #4
    Join Date
    Jun 2010
    Posts
    51
    Ok....I am a bit confused at the moment. Can you please clarify..
    e.g. if for master table record ID1=1 ID2=1, has two detail rows then SNO should be like following:

    ID1 ID2 SNO TESTCOL
    1 1 1 HELLO
    1 1 2 HELLO
    If above rows should be there in testdetail table then what should be in testmaster? Perhaps below:
    Code:
    ID1 ID2 Description
    1 1 HELLO
    1 1 HELLO
    But, how can this be possible if you have primary key on ID1 and ID2 table?
    Please always reply to the post if it was helpful. Others may find it helpful.

  5. #5
    Join Date
    Oct 2010
    Posts
    5
    Test Master will have (Master table has composite PRIMARY KEY (ID1, ID2)) :

    ID1 ID2 DESCRIPTION
    1 1 HELLO
    1 2 HELLO

    Test Detail can have for each of the above master records (Detail has composite PRIMARY KEY (ID1, ID2, SNO) ):

    ID1 ID2 SNO TESTCOL

    -- For first record of master
    1 1 1 HELLO
    1 1 2 HELLO

    -- For second record of master
    1 2 1 HELLO
    1 2 2 HELLO
    1 2 3 HELLO

  6. #6
    Join Date
    Jun 2010
    Posts
    51
    Ok..that clears most of the things. One last question: How are you deciding how many rows you want?

    1. when ID1/2 is 1-1 you decided to insert 2 rows
    -- For first record of master
    1 1 1 HELLO
    1 1 2 HELLO
    2. when ID1/2 is 1-2 you decided to insert 3 rows
    -- For second record of master
    1 2 1 HELLO
    1 2 2 HELLO
    1 2 3 HELLO
    Please always reply to the post if it was helpful. Others may find it helpful.

  7. #7
    Join Date
    Oct 2010
    Posts
    5
    That will be dynamic based on another query result.

    for that i used a sample insert query earlier:
    Code:
    INSERT INTO TESTDETAIL(ID1, ID2, 
      SNO,
      TESTCOL)
    SELECT A.ID1, A.ID2, 
      (SELECT ISNULL(MAX(SNO),0) FROM TESTDETAIL) + ROWNUM AS SNO,
      'HELLO'
    FROM TESTMASTER A, (SELECT 1 ID1, 1 ID2 UNION ALL SELECT 1 ID1, 1 ID2) B
    WHERE A.ID1 = B.ID1 
    AND A.ID2 = B.ID2 ;

  8. #8
    Join Date
    Jun 2010
    Posts
    51
    I wanted to know your answer in plain English but you posted the query.

    If we execute your coloured query independently it only returns:

    Code:
    ID1	ID2
    1	1
    1	1
    You are using the output of this query and joining with testmaster table. This will omit ID2 = 2 (or anything else which is not equal to 1)

    Is that what you want?

    it may be troublesome but i wont be able to solve it unless i know the requirement.
    Please always reply to the post if it was helpful. Others may find it helpful.

  9. #9
    Join Date
    Oct 2010
    Posts
    5
    sorry for the inconvenience,

    Test master will join with another table (lets say D )and D can generate more than one row for each master table record.

    and the resulting records of this join query will be inserted into the detail table.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •