Oracle to SQL Server format - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Oracle to SQL Server format

Author

Topic

Crespo

85 Posts

Posted - 2002-10-21 : 08:18:52

Ok... does anybody know how to convert the following query from the Oracle format to an SQL Server format?

update tid a
set totgmp = (select sum(to_number(nvl(origmbrgmp,'0'))/5200)
from data_sam4 b
where a.member_key = b.member_key
and gmpcode = '1051'
)

Best Regards.

Crespo.
Hewitt Bacon & Woodrow
Epsom
Surrey
United Kingdom

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-10-21 : 08:24:00

update a
set totgmp = B.summ
from tid A INNER JOIN
(select member_key, sum(CAST(IsNull(origmbrgmp,'0') AS int)/5200 AS Summ
from data_sam4 GROUP BY member_key) b ON a.member_key = b.member_key
where gmpcode = '1051')

There might be a way to write this as a correlated subquery, like the original, but it's not my strong point.

Crespo

85 Posts

Posted - 2002-10-21 : 08:26:56

Thanks for the speedy reply!

Here is what I am working on but still has errors...

UPDATE GNS_TID_TUP
SET TOTGMP = (SELECT A.MEMBER_KEY, (SUM(CAST(ORIGMBRGMP AS NUMERIC))/5200)
FROM (SELECT * FROM TUP4 ) AS A
INNER JOIN (SELECT DISTINCT MEMBER_KEY FROM GNS_TID_TUP) AS B
ON A.MEMBER_KEY = B.MEMBER_KEY
GROUP BY A.MEMBER_KEY) AS C
WHERE GNS_TID_TUP.MEMBER_KEY = C.MEMBER_KEY)

Best Regards.

Crespo.
Hewitt Bacon & Woodrow
Epsom
Surrey
United Kingdom

Crespo

85 Posts

Posted - 2002-10-21 : 08:37:10

You see that is where the problems start. I've modified your code slightly

UPDATE GNS_TID_TUP
SET TOTGMP = (SELECT B.SUMM
FROM GNS_TID_TUP A INNER JOIN
(SELECT MEMBER_KEY, SUM(CAST (ORIGMBRGMP AS NUMERIC)/5200) AS SUMM
FROM TUP4 GROUP BY MEMBER_KEY) B
ON A.MEMBER_KEY = B.MEMBER_KEY) AS C
WHERE GNS_TID_TUP.MEMBER_KEY = C.MEMBER_KEY)

and it still returns errors

(SELECT B.SUMM
FROM GNS_TID_TUP A INNER JOIN
(SELECT MEMBER_KEY, SUM(CAST (ORIGMBRGMP AS NUMERIC)/5200) AS SUMM
FROM TUP4 GROUP BY MEMBER_KEY) B
ON A.MEMBER_KEY = B.MEMBER_KEY)

returns the sum for all members that exist in GNS_TID_TUP, now I want to update individual entries in the GNS_TID_TUP table with the relevant total. Any advice on how to fix this annoying query?

Best Regards.

Crespo.
Hewitt Bacon & Woodrow
Epsom
Surrey
United Kingdom

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-10-21 : 08:40:48

I need to see the structures for both tables, some sample data, and some more background on what these tables and UPDATEs are supposed to do. I have a feeling that the Oracle version is totally quirky and may not have been the best way to do it, and want to avoid patching that UPDATE statement if it can be rewritten from scratch.

Crespo

85 Posts

Posted - 2002-10-21 : 08:48:25

quote:

I need to see the structures for both tables, some sample data, and some more background on what these tables and UPDATEs are supposed to do. I have a feeling that the Oracle version is totally quirky and may not have been the best way to do it, and want to avoid patching that UPDATE statement if it can be rewritten from scratch.

O.K it's like this

GNS_TID_TUP is the table that I am attemtping to update. The column is TOTGMP.

The table that has the data is called TUP4 and the column is called ORIGMBRGMP. Now a MEMBER_KEY in GNS_TID_TUP could possibly have 2 or more entries in TUP4...

For example, MEMBER_KEY 2058 has ONE entry in GNS_TID_TUP... and 3 entries in TUP4... each entry in TUP4 has a GMP value. I want to update GNS_TID_TUP so that member 2058 has the total of those three entries in TUP4 in TOTGMP.

Does that make sense?

The table structure is of no importance to be honest and in any case I've set all the columns to VARCHAR(50).

I know it's a simple update statment but my mind is messed up at the moment... too much partying over the weekend

Best Regards.

Crespo.
Hewitt Bacon & Woodrow
Epsom
Surrey
United Kingdom

Crespo

85 Posts

Posted - 2002-10-21 : 09:44:18

Man... I am officially an IDIOT!!!!

UPDATE GNS_TID_TUP
SET TOTGMP = (SELECT SUM(CONVERT(NUMERIC(10,2),B.ORIGMBRGMP)/5200)
FROM TUP4 B
WHERE GNS_TID_TUP.MEMBER_KEY = B.MEMBER_KEY)

Does the job... what the hell was I one about trying to make it more complicated!!!!!

Sorry to waste your time

Best Regards.

Crespo.
Hewitt Bacon & Woodrow
Epsom
Surrey
United Kingdom

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources