Getting column value based on column name from another table?

[Chadi]

Hi.

I have two predefined tables: Errors and Transactions. Columns in Errors table are Transaction ID, Error Code, Error Column, Error Message. Columns in Transactions table are Transaction ID, Quantity, Unit Price, Approval, etc.

I would like (one) SQL statement that will look up all the error transactions from Errors Table and depending on what column name is stored in Errors Column Name get the value of the respective column from Transactions table.

For example, if Errors table contain a record with Errors Column Name = 'Unit Price' then I would like whatever value is stored in the respective record in column name Unit Price from Transactions table. If the value in Errors Error Column Name = 'Approval' then the respective Transactions record's Approval value should be displayed.

Would appreciate any help. Thanks.

Chadi

 

[Cagliostro]

select errors.* from
transactions inner join errors on
errors.TransactionID = transactions.TransactionID John Fill
ivfmd@mail.md

 

[grega]

Chadi ... at first glance it looks like this sort of thing can't be done in standard SQL. I'll have a think and maybe others will have other ideas, but this doesn't seem hopeful.

Greg.

 

[Chadi]

I am bit surprised that this would not be possible. There should be a method to substitute a variable in SQL with the value and then the SQL can fetch that value as a column name in a table. If I have to resort to programming logic then it would look something like this (this is simplified logic just for the purpose of this discussion):

SELECT Errors.Column_Name, Errors.TransactionID
INTO vColumn_Name, vTransactionID
FROM Errors, Transactions
WHERE Errors.TransactionID = Transactions.Transaction_ID

IF vColumn_Name = 'Quantity' Then
SELECT Transactions.TransactionID, Transactions.Quantity
FROM Transactions
ELSE IF vColumn_Name = 'Unit_Price' Then
SELECT Transactions.TransactionID, Transactions.Unit_Price
FROM Transactions
ELSE IF vColunm_Name = 'Approver' Then
SELECT Transactions.TransactionID, Transactions.Approver
FROM Transactions
ELSE IF ...
SELECT ...
FROM Transactions
ENDIF

As you can see this gets bit messy and would be an overhead in terms of maintenance. If the table structure is changed then this logic will have to be changed as well.

If there is a SQL way out then it would be so neat and no maintenance would be required, since it will sort out the column names dynamically.

Well, lets all sretch out heads!

Chadi

 

[Swany]

What RDBMS are you using?

 

[MalcolmW]

Dynamically building a select statement is possible, but the easiest approach is probably RDBMS specific. The head scratching part is dealing with the different data types, as well as the different precision for those data types.

When faced with difficult specifications, the most prudent thing is often to attempt to change the specifications. For example, this could be done easily with a report writer that displayed the error field in a different color, and it might be even helpful if the correct field(s) were shown. And yes, I work with report writers a lot ;-)
ie
[tt]TransactionID Quantity Price Approval
------------- --------- ------ ---------
QEDQEDQEDQEDQ [red]15[/red] 13.99 Able[/tt] Malcolm

 

[Chadi]

Cool, Malcolm. You are absolutely right. It is so much easier to do this sort of thing with report writers. Unfortunately, I can not use a report write and have to rely on SQL only. The main reason for using SQL only is because I need to SQL to plug into an Oracle Alert I am designing. With Alert, I only have a choice of SQL, and PL/SQL packages. But PL/SQL packages is complicated to implement and, therefore, if I can avoid it altogether then it would be good.

Chadi

 

[carp]

Chadi -

I think it would really be in your best interest to get more comfortable with PL/SQL. In point of fact, I believe your problem would be far simpler using PL/SQL. What you have is a problem that is extremely difficult (if not impossible) with SQL.

 

[Swany]

Well In oracle you should be able to do it with a decode statement..

select e.error, e.column, decode(UPPER(e.column),'QUANTITY',t.quantity,'UNIT_PRICE',t.unit_price...)
from errors e, transactions t
where e.transaction_id = t.transaction_id

 

[MikeJones]

This still requires the decode to be maintained and as new cases come about, for them to be added to the decode statement.

 

[Swany]

Yes it does, but it is the only pure SQL construct I'm aware of that is capable of doing the task.

Besides, in all reality how often is the transaction table going to change?

Probably only after a major revision change, but it would be rather trivial to write a quick program in just about any language that would create the SQL statement from all the available columns in transactions..

in vb you could do the following:
rs.open "select * from transactions", db
sql = "select e.error, e.column, decode(UPPER(e.column),"
for each field in rs
sql = sql & "'" & ucase(field) & "',t." & field & ","
next
sql = left(sql, len(sql)-1) 'remove extraneous comma
sql = sql & &quot value from errors e, transactions t where e.transaction_id = t.transaction_id"

 

[MalcolmW]

Technically speaking, DECODE is an Oracle construct rather than a SQL one. The plain vanilla SQL is the CASE statement, which Oracle in recent versions has started supporting as well. For either the Decode or the Case statement, you will also need to cast the datatype to a consistent type, as it seems you have different datatypes.
select e.error,
e.column,
CASE UPPER(e.column)
WHEN 'QUANTITY' THEN CAST(t.quantity AS VARCHAR(10))
WHEN 'UNIT_PRICE' THEN CAST(t.unit_price AS VARCHAR(10))
WHEN 'APPROVAL' THEN t.approval
[....]
ELSE 'Unknown'
END
from errors e, transactions t
where e.transaction_id = t.transaction_id

Malcolm