Hi,

I am trying to write an SQL statement to display a named BOM for reporting services. I found that Named BOM values are stored in "Bomsheetvalue' table. But when I edit values in the named BOM, The values are stored as 'new value"@@@"Old value"

eg: If I change quantity from 20 to 30, It is stored as 30@@@20. Why It is stored like this?

and how to display the new value(in this case 20) from that table.

My SQL query looks like this:

IF EXISTS (
Select
CONVERT(int,a0.RowNo) + 1 as RowNo,
ISNULL(a5.ValueText,'') as [Item No],
ISNULL(a0.ValueText,'') as [Part Number],
ISNULL(a1.ValueText,'') as [Rev],
ISNULL(a2.ValueText,'') as [Qty],
ISNULL(a4.ValueText,'') as [Description],
ISNULL(a3.ValueText,'') as [UOM]
from
(select
RowNo,
ValueText
from BomSheetColumn
inner join BomSheetValue on
BomSheetColumn.BomDocumentID = BomSheetValue.BomDocumentID and
BomSheetColumn.VersionNo = BomSheetValue.VersionNo and
BomSheetColumn.ColumnNo = BomSheetValue.ColumnNo
where BomSheetColumn.ColumnName = 'Part Number'
and BomSheetColumn.BomDocumentID = @BomDocumentID and
BomSheetColumn.VersionNo = @BomVersionNo
) a0
full join
(select
RowNo,
ValueText
from BomSheetColumn
inner join BomSheetValue on
BomSheetColumn.BomDocumentID = BomSheetValue.BomDocumentID and
BomSheetColumn.VersionNo = BomSheetValue.VersionNo and
BomSheetColumn.ColumnNo = BomSheetValue.ColumnNo
where BomSheetColumn.ColumnName = 'Rev'
and BomSheetColumn.BomDocumentID = @BomDocumentID and
BomSheetColumn.VersionNo = @BomVersionNo
) a1 on
a0.RowNo = a1.RowNo
full join
(select
RowNo,
ValueText
from BomSheetColumn
inner join BomSheetValue on
BomSheetColumn.BomDocumentID = BomSheetValue.BomDocumentID and
BomSheetColumn.VersionNo = BomSheetValue.VersionNo and
BomSheetColumn.ColumnNo = BomSheetValue.ColumnNo
where BomSheetColumn.ColumnName = 'Qty'
and BomSheetColumn.BomDocumentID = @BomDocumentID and
BomSheetColumn.VersionNo = @BomVersionNo
) a2 on
a0.RowNo = a2.RowNo
full join
(select
RowNo,
ValueText
from BomSheetColumn
inner join BomSheetValue on
BomSheetColumn.BomDocumentID = BomSheetValue.BomDocumentID and
BomSheetColumn.VersionNo = BomSheetValue.VersionNo and
BomSheetColumn.ColumnNo = BomSheetValue.ColumnNo
where BomSheetColumn.ColumnName = 'UOM'
and BomSheetColumn.BomDocumentID = @BomDocumentID and
BomSheetColumn.VersionNo = @BomVersionNo
) a3 on
a0.RowNo = a3.RowNo
full join
(select
RowNo,
ValueText
from BomSheetColumn
inner join BomSheetValue on
BomSheetColumn.BomDocumentID = BomSheetValue.BomDocumentID and
BomSheetColumn.VersionNo = BomSheetValue.VersionNo and
BomSheetColumn.ColumnNo = BomSheetValue.ColumnNo
where BomSheetColumn.ColumnName = 'Description'
and BomSheetColumn.BomDocumentID = @BomDocumentID and
BomSheetColumn.VersionNo = @BomVersionNo
) a4 on
a0.RowNo = a4.RowNo
full join
(select
RowNo,
ValueText
from BomSheetColumn
inner join BomSheetValue on
BomSheetColumn.BomDocumentID = BomSheetValue.BomDocumentID and
BomSheetColumn.VersionNo = BomSheetValue.VersionNo and
BomSheetColumn.ColumnNo = BomSheetValue.ColumnNo
where BomSheetColumn.ColumnName = 'Position No.'
and BomSheetColumn.BomDocumentID = @BomDocumentID and
BomSheetColumn.VersionNo = @BomVersionNo
) a5 on
a0.RowNo = a5.RowNo
)
BEGIN
Select
CONVERT(int,a0.RowNo) + 1 as RowNo,
ISNULL(a5.ValueText,'') as [Item No],
ISNULL(a0.ValueText,'') as [Part Number],
ISNULL(a1.ValueText,'') as [Rev],
ISNULL(a2.ValueText,'') as [Qty],
ISNULL(a4.ValueText,'') as [Description],
ISNULL(a3.ValueText,'') as [UOM]
from
(select
RowNo,
ValueText
from BomSheetColumn
inner join BomSheetValue on
BomSheetColumn.BomDocumentID = BomSheetValue.BomDocumentID and
BomSheetColumn.VersionNo = BomSheetValue.VersionNo and
BomSheetColumn.ColumnNo = BomSheetValue.ColumnNo
where BomSheetColumn.ColumnName = 'Part Number'
and BomSheetColumn.BomDocumentID = @BomDocumentID and
BomSheetColumn.VersionNo = @BomVersionNo
) a0
full join
(select
RowNo,
ValueText
from BomSheetColumn
inner join BomSheetValue on
BomSheetColumn.BomDocumentID = BomSheetValue.BomDocumentID and
BomSheetColumn.VersionNo = BomSheetValue.VersionNo and
BomSheetColumn.ColumnNo = BomSheetValue.ColumnNo
where BomSheetColumn.ColumnName = 'Rev'
and BomSheetColumn.BomDocumentID = @BomDocumentID and
BomSheetColumn.VersionNo = @BomVersionNo
) a1 on
a0.RowNo = a1.RowNo
full join
(select
RowNo,
ValueText
from BomSheetColumn
inner join BomSheetValue on
BomSheetColumn.BomDocumentID = BomSheetValue.BomDocumentID and
BomSheetColumn.VersionNo = BomSheetValue.VersionNo and
BomSheetColumn.ColumnNo = BomSheetValue.ColumnNo
where BomSheetColumn.ColumnName = 'Qty'
and BomSheetColumn.BomDocumentID = @BomDocumentID and
BomSheetColumn.VersionNo = @BomVersionNo
) a2 on
a0.RowNo = a2.RowNo
full join
(select
RowNo,
ValueText
from BomSheetColumn
inner join BomSheetValue on
BomSheetColumn.BomDocumentID = BomSheetValue.BomDocumentID and
BomSheetColumn.VersionNo = BomSheetValue.VersionNo and
BomSheetColumn.ColumnNo = BomSheetValue.ColumnNo
where BomSheetColumn.ColumnName = 'UOM'
and BomSheetColumn.BomDocumentID = @BomDocumentID and
BomSheetColumn.VersionNo = @BomVersionNo
) a3 on
a0.RowNo = a3.RowNo
full join
(select
RowNo,
ValueText
from BomSheetColumn
inner join BomSheetValue on
BomSheetColumn.BomDocumentID = BomSheetValue.BomDocumentID and
BomSheetColumn.VersionNo = BomSheetValue.VersionNo and
BomSheetColumn.ColumnNo = BomSheetValue.ColumnNo
where BomSheetColumn.ColumnName = 'Description'
and BomSheetColumn.BomDocumentID = @BomDocumentID and
BomSheetColumn.VersionNo = @BomVersionNo
) a4 on
a0.RowNo = a4.RowNo
full join
(select
RowNo,
ValueText
from BomSheetColumn
inner join BomSheetValue on
BomSheetColumn.BomDocumentID = BomSheetValue.BomDocumentID and
BomSheetColumn.VersionNo = BomSheetValue.VersionNo and
BomSheetColumn.ColumnNo = BomSheetValue.ColumnNo
where BomSheetColumn.ColumnName = 'Position No.'
and BomSheetColumn.BomDocumentID = @BomDocumentID and
BomSheetColumn.VersionNo = @BomVersionNo
) a5 on
a0.RowNo = a5.RowNo
END
ELSE
BEGIN
Select
top 1 '' as RowNo,
'' as [Item No],
'' as [Part Number],
'' as [Rev],
'' as [Qty],
'' as [Description],
'' as [UOM]
FROM
BomSheetColumn
END