Need help with sybase query

imation3m · #1 (**permalink**) 10-05-10, 03:05

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

agrawal.meet · #2 (**permalink**) 10-05-10, 03:49

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

imation3m · #3 (**permalink**) 10-05-10, 04:01

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

agrawal.meet · #4 (**permalink**) 10-05-10, 04:48

Ok....I am a bit confused at the moment. Can you please clarify..

Quote:

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?

imation3m · #5 (**permalink**) 10-05-10, 05:11

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

agrawal.meet · #6 (**permalink**) 10-05-10, 05:21

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

Quote:

-- 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

Quote:

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

imation3m · #7 (**permalink**) 10-05-10, 05:31

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 ;

agrawal.meet · #8 (**permalink**) 10-05-10, 05:59

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.

imation3m · #9 (**permalink**) 10-05-10, 06:18

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech