Slamming into the wall on this one.

• Dave

  Newbie

  Points: 5

  More actions

  January 12, 2005 at 12:05 pm

  #162259

  Hi all,

  I keep slamming into the wall on this one. I am trying to combine the data from the source table and

  insert into the target table where there would be one new line for each of the OrderedUnits columns, and

  include a column that would would represent the bucket number (bucket number is the specific OrderedUnits

  column number)

  Source:

  Table1: tmpOrderDetail

  Columns: OrderNumber, OrderLine, Company, Sizescale

  Example Data: 97824,1,XYZInc,D

  Table2: tmpOrderDetailSize

  Columns: OrderNumber, Orderline, Company, OrderedUnits1, OrderedUnits2, OrderedUnits3

  Example Data: 97824,1,XYZInc,4,12,20

  Target:

  Table: OrderDetail

  Columns: OrderNumber, Orderline, Company, SizeScale, BucketNumber, OrderedUnit

  97824,1,XYZInc,D,1,4

  97824,1,XYZInc,D,2,12

  97824,1,XYZInc,D,3,20

  I'm not sure if I have just been staring at this too long, or what. Any help would be greatly

  appreciated!

  Thanks

  Dave

   

• noeld

  SSC Guru

  Points: 96590

  More actions

  January 12, 2005 at 12:23 pm

  #537106

  insert into  OrderDetail ( OrderNumber, Orderline, Company, SizeScale, BucketNumber, OrderedUnit)

  Select OrderNumber, Orderline, Company, SizeScale, n,

   Case   when n = 1 then OrderedUnit1

          when n = 2 then OrderedUnit2

          else OrderedUnit3 end As OrderedUnits

  From

   tmpOrderDetails

   Cross join

   ( Select 1 n

     union all

     select 2

     union all

     select 3) Numbers

  Note I am assuming all Buckets are full but with a little twick you can filter the null rows if that is not the case.

  HTH

   

   

  
  * Noel

• Dave

  Newbie

  Points: 5

  More actions

  January 12, 2005 at 1:01 pm

  #537121

  Thanks for the quick response Noel!

  Although the OrderedUnits columns are in the other source table "OrderDetailSize", adding that seems to throw it into a loop. And you are correct that sometimes there will be a vaule of "0" which should be excluded from the results.. sorry I left that info out in the original post.

  Thanks Dave

• noeld

  SSC Guru

  Points: 96590

  More actions

  January 12, 2005 at 2:18 pm

  #537153

  create a view

  MyView

  Select OrderNumber, Orderline, Company, SizeScale, n ,

   Case   when n = 1 then OrderedUnit1

          when n = 2 then OrderedUnit2

          else OrderedUnit3 end As OrderedUnits

  From

   tmpOrderDetailSize  S

   cross join

   ( Select 1 n

     union all

     select 2

     union all

     select 3) Numbers

  where

      ( n=1 and OrderedUnits1 > 0)

       or

      ( n=2 and OrderedUnits2 > 0&nbsp

       or

      ( n=3 and OrderedUnits3 > 0&nbsp

  and then

   

  insert into  OrderDetail ( OrderNumber, Orderline, Company, SizeScale, OrderedUnit, BucketNumber )

  select  OrderNumber, Orderline, Company, SizeScale, OrderedUnits, (Select Count(*) from MyView V2

            where v2.orderNumber = V.OrderNumber

           and v2.OrderLine = v.orderLine

           and v.n <= v2.n)

  from

   Myview V

   

  with this little twick you can remove the extra lines and get the right "n" too

   

  

  HTH

  
  * Noel

• Dave

  Newbie

  Points: 5

  More actions

  January 12, 2005 at 4:08 pm

  #537166

  Good call on the view, I think I was staring at it way too long

  Thanks again for all the help. That put me on the right track!