CODEWITHSUNDEEP
sqlsql-serverdatabaseoracle-database
Tiele Declercq
Tiele Declercq

Reputation: 2130

Get value of PRIMARY KEY during SELECT in ORACLE

For a specific task I need to store the identity of a row in a tabel to access it later. Most of these tables do NOT have a numeric ID and the primary key sometimes consists of multiple fields. VARCHAR & INT combined.

Background info: The participating tables have a trigger storing delete, update and insert events in a general 'sync' tabel (Oracle v11). Every 15 minutes a script is then launched to update corresponding tables in a remote database (SQL Server 2012).

One solution I came up with was to use multiple columns in this 'sync' table, 3 INT columns and 3 VARCHAR columns. A table with 2 VARCHAR columns would then use 2 VARCHAR columns in this 'sync' table.

A better/nicer solution would be to 'select' the value of the primary key and store this in this table.

Example:

CREATE TABLE [dbo].[Workers](
    [company] [nvarchar](50) NOT NULL,
    [number] [int] NOT NULL,
    [name] [nvarchar](50) NOT NULL,
    CONSTRAINT [PK_Workers] PRIMARY KEY CLUSTERED ( [company] ASC, [number] ASC )
)

// Fails:
SELECT [PK_Workers], [name] FROM [dbo].[Workers]
UPDATE [dbo].[Workers] SET [name]='new name' WHERE [PK_Workers]=@PKWorkers

// Bad (?) but works:
SELECT ([company] + CAST([number] AS NVARCHAR)) PK, [name] FROM [dbo].[Workers];
UPDATE [dbo].[Workers] SET [name]='newname' WHERE ([company] + CAST([number] AS NVARCHAR))=@PK

The [PK_Workers] fails in these queries. Is there another way to get this value without manually combining and casting the index?

Or is there some other way to do this that I don't know?

Upvotes: 0

Views: 964

Answers (1)

Pavel Gatnar
Pavel Gatnar

Reputation: 4053

for each table create a function returning a concatenated primary key. create a function based index on this function too. then use this function in SELECT and WHERE clauses

Upvotes: 2

Related Questions