Add table Id to union View

(0) Share

Report

Posted on by Nastaran

290

I have a view which is the union of the CustTrans and VendTrans. I want to know which records are for the CustTrans and which records are for VendTrans. I try to add table Id to the fields of the view but the error comes that mapped view field cannot to be set to TableId. I can demonstrate the records using AccountNum sequence number but I prefer not to do that because in the future, the sequence number will be changed.

So How can I show the records reference table in a union view?

PS: my current solution is creating two separate view for custtrans and vendtrans. Then adding a computed column which returns the table name and union these two views. but I hope that there is a more efficient way

I have the same question (0)

All responses (4)

Answers (1)

Suggested answer

Rahul Mohta 21,032 on at

Like (0)

Report

check if voucher number could be used

Was this reply helpful? Yes No
Verified answer

vinitgoyal2005 6,332 on at

Like (1)

Report

Hi Nastaran,

Create 1 Union query for CustTrans and VendTrans

Create View out of this union query.

add a computed column to this union view with following code:

private static server str tableName(int _branchNum)
{
DictView dictView = new DictView(tableNum(TestView));

if(_branchNum == 1)
{
return "'CustTrans'";
}
else
{
return "'VendTrans'";
}
}

Which create a SQL view as :

SELECT T1.ACCOUNTNUM AS ACCOUNTNUM, T1.DATAAREAID AS DATAAREAID, T1.PARTITION AS PARTITION, 1010 AS RECID, (CAST(('CustTrans') AS NVARCHAR(10))) AS TABLENAME
FROM CUSTTRANS T1
UNION
SELECT T1.ACCOUNTNUM AS ACCOUNTNUM, T1.DATAAREAID AS DATAAREAID, T1.PARTITION AS PARTITION, 1010 AS RECID, (CAST(('VendTrans') AS NVARCHAR(10))) AS TABLENAME
FROM VENDTRANS T1

Was this reply helpful? Yes No
Nastaran 290 on at

Like (0)

Report

Thank Vinit. it works.

The method argument is the number of data source? and also DictView dictView = new DictView(tableNum(TestView)) is not required yes?

Was this reply helpful? Yes No
Suggested answer

vinitgoyal2005 6,332 on at

Like (1)

Report

Hi,

Yes integer parameter gives the number of datasource in the Union view, This is applicable only for Union view.

DictView dictView = new DictView(tableNum(TestView)) is not required, I had tried someting else, that line was required for that.

you can safely remove it.

Was this reply helpful? Yes No