1 Reply Latest reply on May 12, 2011 10:05 AM by Lee CS Young

    SQL Statement to display named Bom

    Balusu Krishna

      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