Results 1 to 2 of 2

Thread: [RESOLVED] SQL DB2: Looking for Advice on creating Report

  1. #1

    Thread Starter
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Resolved [RESOLVED] SQL DB2: Looking for Advice on creating Report

    Hi Folks,

    up front: Production Database IBM DB2 of the company i work for.
    Any advice like "Create a temp. Table" or "Add a column" are non-starters.
    I have to work with what i have

    I have to create a report which has to respect the following "issues":

    We have a lot of clients we have a VMI with them (Consignment stock).

    For that we have a table "X2KOKA" with stock locations, looking like this (stripped down):
    PartNo, StockLocation, BinQuantity, IndexNo, SomeOtherFields --> IndexNo is unique

    Now, when we ship bins to the separate stock-locations, the moment the shipping department creates the shipping note, it also creates a barcode-label, which contains a unique package-no.
    This barcode is scanned, when the empty bin returns, and the system creates an invoice for it.
    This package-no is also saved in another table "LMLI" alongside other data (Shipping Note, Date, Stock-Location, Qty etc.)

    As of now, the current report works as follows:
    Take the items, stock-locations, qty etc. from the table "X2KOKA", and look into "LMLI", compare the stock-locations, and sum it up (filtered by date)

    It happens pretty often, that i ship a bin for Item 123456 to Location ABC, say, at May 2nd 2021.
    Now, on May 10th, i get a message from the client, that the Item has been relocated to Location XYZ (physically moved!)
    In that case, i cannot close down Location ABC, and open location XYZ (and ship new bins), since i don't have the physical space for the bins!
    So i overwrite the stock location ABC with XYZ.

    I think you can see the issue, since the now current stock location XYZ is nowhere to be found in "LMLI" (shipping), and the report throws up a red flag "no turnover"
    Never mind, that in the past it also generated an error after scanning the barcode, since the packageno. in the barcode still refers to the old stock location ABC, because it looks up the packageno in "LMLI" to pull the stock location.
    But, as i said: in the past.
    The scanning-issue has been solved now.
    What i didn't know is, that there is a table "MDLGSL" in another schema of the database, which saves the shipping data, here important the package-no. from "LMLI" and the IndexNo from "X2KOKA".

    The code/algorithm for scanning the barcodes has been changed to follow this algorithm:
    Scan the barcode, receiving the package-no.
    Look into the "MDLGSL" and look for the Package-No.
    If found, pull the Index-No
    Look up the Stock Location for this Index-no. in "X2KOKA" and use (!!) the Stock Location from there
    Continue processing the scan as in the past
    Result: No more scan-errors.

    Now, for me to change the SQL for the report, i was thinking about using this table "MDLGSL" in the same way, but currently i'm stumped how to proceed.

    Should i still start from "X2KOKA" (Stock Locations), pull the data from there incl. the Index-No, go into "MDLGSL", look for the Index-no. to pull the package-No, and then with the package-no. go into "LMLI" (shipping)?

    Is there any other way (more efficient?)?

    I'm no stranger to complex and convoluted SQL.

    I'm asking for advice if there is a maybe more efficient way to skin that cat.

    If you need more info, just tell me.
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  2. #2

    Thread Starter
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: [RESOLVED] SQL DB2: Looking for Advice on creating Report

    Resolved (for now)

    It was easier than i thought

    My original SELECT had the StockLocation in the WHERE-Clause
    SELECT blablablablabl FROM SomeTables WHERE blablablabla AND StockLocation='" & LocationFromX2KOKA & "'"

    Changed the Last Filter to
    SELECT blablablablabl FROM SomeTables WHERE blablablabla AND StockLocation IN (SELECT DISTINCT StockLocation FROM MDLGSL WHERE Index=" & IndexFromX2KOKA & ")"

    Which basically translates to OR-ing all possible former StockLocations

    Nice!
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

Posting Permissions

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



Click Here to Expand Forum to Full Width