select query

Report · Jul 17, 2012

I am trying to output the value of each corresponding data column on unsaccountB tbl. However it returns with the name of the column not the value (newVal =). What is missing or I am doing wrong?

======

SELECT p.firstname,p.lastname, u.XProxyContext, dw.[OldValue],newVal =

(select dbc.ColumnName from unsaccountB where dbc.ColumnName in

(SELECT name FROM sys.columns WHERE OBJECT_NAME(object_id) = 'unsaccountb') and u.AccountName = AccountName

)

,dwo.operationDate, dwo.OperationType,dbc.ColumnName,dbc.TableName

FROM DialogWatchProperty dw

INNER JOIN DialogWatchOperation dwo ON dw.UID_DialogWatchOperation = dwo.UID_DialogWatchOperation

INNER JOIN UNSAccountB u ON dwo.ObjectKey = u.xObjectKey

INNER JOIN Person p ON p.uid_person = u.uid_person

INNER JOIN DialogColumn dbc on dbc.UID_DialogColumn = dw.UID_DialogColumn

where ---OperationType = 'U'

dwo.operationDate between convert(varchar,@sDate,1) and convert(varchar,@eDate,1)

----And dbc.ColumnName = u1.

And u.XProxyContext = @TargetSystem

And p.FirstName = (case when @FirstName <>'' then @FirstName else p.FirstName end)

And p.LastName = (case when @LastNamea <>'' then @LastName else p.LastName end)

And p.CentralAccount =

(case when @USERNAME <>'' then @USERNAME else p.CentralAccount end)

And PersonnelNumber = (case when @personelNumber <>'' then @personelNumber else PersonnelNumber end)

order by firstname,lastname,dbc.ColumnName asc, dwo.operationDate desc

==========

Output Data:

firstname lastname XProxyContext OldValue newVal operationDate OperationType ColumnName TableName

Özlem Karaman FINSOFT FINSOFT/FINSOFT/Özlem Karaman CanonicalName 2012-07-12 11:40:19.283 U CanonicalName UNSAccountB

Özlem Karaman FINSOFT FINSOFT/FINSOFT/TEST ÖZLEM SOYADI 35474 CanonicalName 2012-07-12 11:36:59.050 U CanonicalName UNSAccountB

Özlem Karaman FINSOFT Özlem Karaman cn 2012-07-12 11:40:19.283 U cn UNSAccountB

Özlem Karaman FINSOFT TEST ÖZLEM SOYADI 35474 cn 2012-07-12 11:36:59.050 U cn UNSAccountB

Özlem Karaman FINSOFT CN=Özlem Karaman,CN=FINSOFT,dc=finsoft DistinguishedName 2012-07-12 11:40:19.283 U DistinguishedName UNSAccountB

Özlem Karaman FINSOFT CN=TEST ÖZLEM SOYADI 35474,CN=FINSOFT,dc=finsoft DistinguishedName 2012-07-12 11:36:59.050 U DistinguishedName UNSAccountB

Özlem Karaman FINSOFT TEST ÖZLEM FirstName 2012-07-12 11:36:59.050 U FirstName UNSAccountB

Özlem Karaman FINSOFT Karaman LastName 2012-07-12 11:40:19.283 U LastName UNSAccountB

Özlem Karaman FINSOFT SOYADI 35474 LastName 2012-07-12 11:36:59.050 U LastName UNSAccountB

Report · Jul 17, 2012

This isn't a CF question, it's a SQL Server question. There is absolutely nothing relating to CF in your question.

You're better off asking this on a SQL Server forum.

In the meantime, google up how to reference a column name dynamically in SQL Server. That's the bit you're missing.

This code:

(select dbc.ColumnName from unsaccountB where dbc.ColumnName in

(SELECT name FROM sys.columns WHERE OBJECT_NAME(object_id) = 'unsaccountb') and u.AccountName = AccountName

)

Just returns the column name, not the content of the column (which I guess you know, but that's the bit you need to focus on).

--

Adam