Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2014
    Posts
    7

    Unanswered: DB2 sql assistance

    Good evening

    I was hoping you could assist me in writing a sql from the attached TABLE with schema info that will show me all orders (SLSCHKNBR) we receive in this table that has tracking number but do not have a scan id (that is RECORDTYPE).
    I am hoping to get the results display the delivery date, order date, origin, trackingnumber, shipweight, etc. The date range to pull this report can vary.

    If you could please assist me with a second query that would pull the same above data for a specific account say ORDER_ID 033021 that would be appreciated.
    I have attached the TOPADM TABLE with data.

    Best Regards,
    Attached Files Attached Files

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What DB2 version/release and platform OS are you using?


    I couldn't read the attached file meaningly.
    The unzipped file showed many meaningless characters(including chinese characers) when I read it by wordpad.

    So, I hope you post the followings by text.
    (include them in posted message directly OR attach file(s) with .txt extension which might be readable by wordpad).
    (1) CREATE TABLE statements.
    (2) INSERT statements to populate the tables creaed by (1).
    (3) Expected results from (1) and (2).

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    I think you mean something like:

    select some_cols from my_table
    where tracking number <> ''
    and scan id = '' --(or should this be "and scan id is null")

  4. #4
    Join Date
    Jan 2014
    Posts
    7

    DB 2 sql/statement assistance

    Good evening

    I was hoping you could assist me in writing a sql from the inserted CREATE TABLE with schema (TOPADM) info that will show me all orders (SLSCHKNBR) we receive in this table that has tracking number but do not have a scan id (that is RECORDTYPE).
    I am hoping to get the results display the delivery date, order date, origin, trackingnumber, shipweight, etc. The date range to pull this report can vary.

    If you could please assist me with a second query that would pull the same above data for a specific account say ORDER_ID 033021 that would be appreciated.
    Please see CREATE TABLE below. My version of DB2 is 8.0.12
    CREATE TABLE
    XCARRIERTRCKNG
    (
    XCARRIERTRACKING_ID BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    TRACKINGNUMBER VARCHAR(40),
    RECORDTYPE VARCHAR(20),
    RECORDTYPEDESC VARCHAR(30),
    ORDERS_ID BIGINT,
    ORDRELEASENUM INTEGER,
    SLSCHKNBR VARCHAR(256),
    DATESHIPPED TIMESTAMP,
    CARRIERSTATUS CHARACTER(4),
    ONLINESTATUS CHARACTER(4),
    SHIPWEIGHT DOUBLE,
    SHIPWEIGHTCODE CHARACTER(4),
    SHIPPERID VARCHAR(20),
    BILLDATE TIMESTAMP,
    PICKUPDATE TIMESTAMP,
    LEADSHIPMENTNUM VARCHAR(20),
    DELIVERYDATE TIMESTAMP,
    BILLTOACCNUM VARCHAR(20),
    PCKGACTIVITYDATE TIMESTAMP,
    BILLOPTIONDESC VARCHAR(4),
    TRANSCODE VARCHAR(6),
    SERVICELEVEL VARCHAR(256),
    RECORDQTY DOUBLE,
    ACTUALWEIGHT DOUBLE,
    BILLEDWEIGHT DOUBLE,
    BILLEDWEIGHTDESC VARCHAR(40),
    ZONE VARCHAR(10),
    LENGTH DOUBLE,
    WIDTH DOUBLE,
    HEIGHT DOUBLE,
    XCARRIERADDR_ID BIGINT,
    PACKAGEREF1 VARCHAR(256),
    PACKAGEREF2 VARCHAR(256),
    PACKAGEREF3 VARCHAR(256),
    PACKAGEREF4 VARCHAR(256),
    PACKAGEREF5 VARCHAR(256),
    SHIPMENTREF1 VARCHAR(256),
    SHIPMENTREF2 VARCHAR(256),
    CARRIERNETCHARGE DECIMAL(20,5),
    CARRIERFREIGHTCHARGE DECIMAL(20,5),
    CARRIERACCCHARGE DECIMAL(20,5),
    RCDSTATUSCODE VARCHAR(2),
    RCDSTATUSDESC VARCHAR(10),
    DELMSG1 VARCHAR(60),
    DELMSG2 VARCHAR(60),
    SHIPMODE_ID INTEGER,
    INSERTTIME TIMESTAMP,
    LASTUPDATE TIMESTAMP,
    DELMSG3 VARCHAR(60),
    BILLWEIGHTCODE CHARACTER(4),
    PRIMARY KEY (XCARRIERTRACKING_ID)
    )


    Best Regards,

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    this is a forum where we help others with issues they are having and point them in the right direction. If you would like one us to do the actual work for you, please let us know where we can send our bill for our time. But, in keeping with the helping of the forum, sounds like you need to AND in your condition.

  6. #6
    Join Date
    Jan 2014
    Posts
    7
    Statement query working now. Please close this thread. Wrong forum.

Posting Permissions

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