Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2003
    Posts
    8

    Unanswered: Expand a querry for more function

    I have an automated shipping software that reaches into our database and gets customer orders and processes them for UPS tags. The problem I am having is we have to manually break out line items. What I mean iss this, if there is multiples of the same thing on the order the UPS software shows a total wieght for all simular items as if it was one item.

    What I am trying to do is to pre process the Order table into individuale lines so the automated feature of my UPS shipping software works.

    The table looks something like this,

    Odr_no, Item_no, Item_des, QTY, Unit_wght, Tot_wght
    --------------------------------------------------------------------------
    1200, AB123, Some stuff, 1, 10, 10
    1200, AB324, More Stuff, 3, 12, 36
    1300, XY32, Junk, 2, 40, 80

    What I need to turn it into is"


    Odr_no, Item_no, Item_des, QTY, Unit_wght, Tot_wght
    --------------------------------------------------------------------------
    1200, AB123, Some stuff, 1, 10, 10
    1200, AB324, More Stuff, 1, 12, 36
    1200, AB324, More Stuff, 1, 12, 36
    1200, AB324, More Stuff, 1, 12, 36
    1300, XY32, Junk, 1, 40, 80
    1300, XY32, Junk, 1, 40, 80

    it would also be nice to add a column to the table to track the total number of packages for each order number, kind of a 1 of 3, 2 of 3 type thing, but that is much easyier than the breack out

    any help woulf be greatly apreciated

    Kelly

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Can you post the DDL of the table? There's got to me more to it...

    Because you do you get..

    1200, AB324, More Stuff, 1, 12, 36
    1200, AB324, More Stuff, 1, 12, 36
    1200, AB324, More Stuff, 1, 12, 36
    From this?

    1200, AB324, More Stuff, 1, 12, 36
    Doesn't make sense, unless there are other things on the row...

    Script the table and post the DDL like..

    CREATE TABLE myTable99 (Col1 int, ect....
    GO

    And sample Data like..

    INSERT INTO myTable99 (Col1, col2, ect)
    SELECT sample data UNION ALL
    SELECT sample data UNION ALL
    SELECT sample data UNION ALL
    SELECT sample data

    and it'll be easier for us to help..
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Aug 2003
    Posts
    8

    here is the create script for the table,

    There is a lot of extra columns we do not even use as this is a canned ERP package this is from , we are a simple manufacturing company and do not require many of the features that this package provides, I have posted two create statements, first with only the columns that we use, and second with every thing.

    the columns we are interested in are,

    CREATE TABLE [OEORDLIN_SQL] (
    [ord_type] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [ord_no] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [line_seq_no] [smallint] NOT NULL ,
    [item_no] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [loc] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [item_desc_1] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [item_desc_2] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [qty_ordered] [decimal](13, 4) NULL ,
    [qty_to_ship] [decimal](13, 4) NULL ,
    [unit_price] [decimal](13, 6) NULL ,
    [uom] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [unit_weight] [decimal](13, 6) NULL ,
    [tot_qty_ordered] [decimal](13, 4) NULL ,
    [cus_no] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [A4GLIdentity] [numeric](9, 0) IDENTITY (1, 1) NOT NULL
    ) ON [PRIMARY]
    GO

    The entire table is below,


    CREATE TABLE [OEORDLIN_SQL] (
    [ord_type] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [ord_no] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [line_seq_no] [smallint] NOT NULL ,
    [item_no] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [item_filler] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [loc] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [pick_seq] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [cus_item_no] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [item_desc_1] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [item_desc_2] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [qty_ordered] [decimal](13, 4) NULL ,
    [qty_to_ship] [decimal](13, 4) NULL ,
    [unit_price] [decimal](13, 6) NULL ,
    [discount_pct] [decimal](5, 2) NULL ,
    [request_dt] [int] NOT NULL ,
    [qty_bkord] [decimal](13, 4) NULL ,
    [qty_return_to_stk] [decimal](13, 4) NULL ,
    [bkord_fg] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [uom] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [uom_ratio] [decimal](9, 5) NULL ,
    [unit_cost] [decimal](13, 6) NULL ,
    [unit_weight] [decimal](13, 6) NULL ,
    [comm_calc_type] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [comm_pct_or_amt] [decimal](7, 2) NULL ,
    [promise_dt] [int] NOT NULL ,
    [tax_fg] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [stocked_fg] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [controlled_fg] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [select_cd] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [tot_qty_ordered] [decimal](13, 4) NULL ,
    [tot_qty_shipped] [decimal](13, 4) NULL ,
    [tax_fg_1] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [tax_fg_2] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [tax_fg_3] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [orig_price] [decimal](13, 6) NULL ,
    [copy_to_bm_fg] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [explode_kit] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [mfg_ord_no] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [allocate_dt] [int] NULL ,
    [last_post_dt] [int] NULL ,
    [post_to_inv_qty] [decimal](13, 4) NULL ,
    [posted_to_inv] [decimal](13, 4) NULL ,
    [tot_qty_posted] [decimal](13, 4) NULL ,
    [qty_allocated] [decimal](13, 4) NULL ,
    [components_alloc] [decimal](13, 4) NULL ,
    [bin_fg] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [cost_meth] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [ser_lot_cd] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [mult_ftr_fg] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [line_type] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [prod_cat] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [end_item_cd] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [reason_cd] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [feature_return] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [rec_inspection] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [ship_from_stk] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [mult_release] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [req_ship_dt] [int] NULL ,
    [qty_from_stk] [decimal](13, 4) NULL ,
    [user_def_fld_1] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [user_def_fld_2] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [user_def_fld_3] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [user_def_fld_4] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [user_def_fld_5] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [picked_dt] [int] NULL ,
    [shipped_dt] [int] NULL ,
    [billed_dt] [int] NULL ,
    [update_fg] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [prc_cd_orig_price] [decimal](13, 6) NULL ,
    [tax_sched] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [cus_no] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [tax_amt] [decimal](14, 2) NULL ,
    [qty_bkord_fg] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [line_no] [smallint] NOT NULL ,
    [mfg_method] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [forced_demand] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [conf_pick_dt] [int] NULL ,
    [item_release_no] [int] NULL ,
    [bin_ser_lot_comp] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [offset_used_fg] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [ecs_space] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [sfc_order_status] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [total_cost] [decimal](14, 2) NULL ,
    [po_ord_no] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [rma_line_seq_no] [smallint] NULL ,
    [vend_no] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [filler_0004] [char] (71) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [A4GLIdentity] [numeric](9, 0) IDENTITY (1, 1) NOT NULL
    ) ON [PRIMARY]
    GO

  4. #4
    Join Date
    Aug 2003
    Posts
    8

    here is the Sample data we use,

    Here is an insert script with some data from our test database, I have only included the data we use, most of the rest is zeros or nulls,



    Insert Into [OEORDLIN_SQL]
    (ord_type,ord_no,line_seq_no,item_no,loc,item_desc _1,item_desc_2,qty_ordered,
    qty_to_ship,unit_price,uom,unit_weight,tot_qty_ord ered,cus_no,A4GLIdentity)

    Values (B,00000002,1,FPFFR2,WH,2 PK 11 LB FARRIERS FORMULA, REFILL,3.0000,.0000,64.420000,EA,26.000000,3.0000, SCHFNE,18301)
    (B,00000003,1,FPFFR4,WH,4 PK 11 LB FARRIERS FORMULA, REFILL,5.0000,.0000,128.840000, EA,46.000000,6.0000,PYEFAR,20247)
    (O,00056941,1,FPFF11CS,WH,2 PK 11 LB FARRIERS FORMULA, NULL,1.0000,1.0000,46.160000,EA,26.000000,1.0000,M ASCAL,34818)
    (O,00056941,2,FPFF22CS,WH,2 PK 22 LB FARRIERS FORMULA, NULL,1.0000,1.0000,92.320000,EA,49.000000,1.0000,M ASCAL,34819)
    (O,00056941,3,FPFF44,WH,Farrier's Formula 44 lb,NULL,5.0000,5.0000,88.220000,EA,49.000000,5.000 0,MASCAL,34820)
    (O,00056942,1,FPFF44,WH,Farrier's Formula 44 lb,NULL,1.0000,1.0000,88.220000,EA,49.000000,1.000 0,RAIEGA,34821)
    (O,00056943,1,FPFFR4,WH,4 PK 11 LB FARRIERS FORMULA, REFILL,1.0000,1.0000,87.720000,EA,46.000000,1.0000 ,CORVGA,34823)
    (O,00056944,1,FPFFR2,WH,2 PK 11 LB FARRIERS FORMULA, REFILL,1.0000,1.0000,64.420000,EA,23.000000,1.0000 ,MAR6TX,34824)
    (O,00056945,1,FPFF22CS,WH,2 PK 22 LB FARRIERS FORMULA, NULL,3.0000,3.0000,92.320000,EA,49.000000,3.0000,C TGFAL,34825)
    (O,00056945,2,FPFF44,WH,Farrier's Formula 44 lb,NULL,2.0000,2.0000,88.220000,EA,49.000000,2.000 0,CTGFAL,34826)
    (O,00056946,1,FPFFR2,WH,2 PK 11 LB FARRIERS FORMULA, REFILL,1.0000,1.0000,64.420000,EA,23.000000,1.0000 ,WYLKAL,34827)
    (O,00056946,2,FPHD8TT,WH,Disenfectant 8, Tea Tree Oil,1.0000,1.0000,12.000000,EA,.000000,1.0000,WYLK AL,34828)
    (O,00056947,1,FPFF44,WH,Farrier's Formula 44 lb,NULL,1.0000,1.0000,132.750000, EA,49.000000,1.0000,ZICBFL,34829)
    (O,00056948,1,FPFFR2,WH,2 PK 11 LB FARRIERS FORMULA, REFILL,1.0000,1.0000,43.860000, EA,23.000000,1.0000,YATRAL,348300
    (O,00056949,1,FPFF44,WH,Farrier's Formula 44 lb,NULL, 1.0000,1.0000,132.750000, EA,49.000000,1.0000,BERAIL,348310
    (O,00056950,1,FPFFR4,WH,4 PK 11 LB FARRIERS FORMULA, REFILL,10.0000,10.0000,87.720000,EA,46.000000,10.0 000,MODFCA,348320
    (O,00056951,1,FPFF11CS,WH,2 PK 11 LB FARRIERS FORMULA, NULL,2.0000,2.0000,46.160000,EA,26.000000,2.0000,S TAAOR,348330
    (O,00056952,1,FPFFR4,WH,4 PK 11 LB FARRIERS FORMULA, REFILL,3.0000,3.0000,87.720000,EA,46.000000,3.0000 ,EMCPNY,348340
    (O,00056952,2,FPFF22CS,WH,2 PK 22 LB FARRIERS FORMULA, NULL,2.0000,2.0000,92.320000,EA,49.000000,2.0000,E MCPNY,348350
    (O,00056952,3,FPFF44,WH,Farrier's Formula 44 lb,NULL,4.0000,4.0000,88.220000,EA,49.000000,4.000 0,EMCPNY,34836)
    (O,00056953,1,FPFF44,WH,Farrier's Formula 44 lb,NULL,1.0000,1.0000,132.750000, EA,49.000000,1.0000,BARBNY,34837)
    (O,00056955,1,FPFF44,HSP, Farrier's Formula 44 lb,NULL,2.0000,2.0000,88.220000,EA,49.000000,2.000 0,CASAVT,34839)
    (O,00056956,1,FPFF11CS,HSP, 2 PK 11 LB FARRIERS FORMULA, NULL, 2.0000,2.0000,46.160000,EA,26.000000,2.0000,HORNMA , 34840)
    (O,00056956,2,FPFF22,HSP, Farrier's Formula 22 lb,NULL,5.0000,5.0000,46.160000,EA,25.000000,5.000 0,HORNMA,34841)
    (O,00056956,3,FPFF44,HSP, Farrier's Formula 44 lb,NULL,1.0000,1.0000,88.220000,EA,49.000000,1.000 0,HORNMA,34842)
    Go

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •