1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

  2. Anuncie Aqui ! Entre em contato fdantas@4each.com.br

Better way to write joins

Discussão em 'StackOverflow' iniciado por fdantas, Março 13, 2019.

  1. fdantas

    fdantas Administrator Moderador

    I have put together the below statement i know that is is long and when originally created this was done in Microsoft Query so it does not do proper joins. What would be the best way to do the joins? I am working form a Progress database pulling the query directly into Excel.

    SELECT

    Company_0.CoaCompanyName
    ,Company_0.CompanyID
    , SopOrder_0.SooOrderDate
    , Count(DISTINCT SopOrder_0.SooParentOrderReference) AS 'Orders'
    , SUM(CASE WHEN SopOrder_0.SooOrderNumber IS NOT NULL THEN 1 ELSE 0 END) AS 'Order Items'
    , SUM(SopOrderItem_0.SoiValue) AS 'Order Value'
    ,(CASE WHEN SopOrder_0.SooParentOrderReference LIKE 'INT%' THEN 'INT' ELSE 'WEB' END) AS 'OrderType'
    ,(CASE WHEN Company_0.CompanyID IN (7942127,7950986,7955733,7955922,7956194,9166261,9167003,9167015,9167821,9168185,71108583,153823092,239325930,248936585,484537132,484562257,492867962,497661455) THEN 'Blue'
    WHEN Company_0.CompanyID IN (7941326,7942863,7951258,7985610,8054787,8059257,8071540,9165903,9166385,9167199,9167239,9168059,9168092,9168309,9176378,87527213,137281027,141171263,187080272,206550932,206567582,206653611,261731657,291593618,332362604,335570516,335584157,335636738,335780251,337122588,337321409,337495183,338813206,339895994,340298080,345796060,352141359,355461038,367864957,368581219,380483346,388367115,394146462,420408339,422017277,482844312,484535001,490217153,500689836,530081645,626234139,626240584) THEN 'Green'
    WHEN Company_0.CompanyID IN (7937292,10245154,12081274,69822571,244700075,348041567,350378220,369621811,492752273,492810572,495589681,497434153,497436315,497447995,497501580,497640064,497687156,524765392,543828284,544700552,577727766) THEN 'InterGroup'
    WHEN Company_0.CompanyID IN (390329465,7939077,7939153,7939643,7941067,7941441,7948530,7954220,7954463,7967527,7967564,7967581,7967598,7967615,7967632,7972683,8010930,8055406,8145843,8298176,8555272,8557302,9165068,9165858,9165941,9165990,9166173,9166187,9166221,9166275,9166408,9166733,9166771,9166817,9166839,9167420,9167441,9167462,9167785,9167852,9167909,9168157,9168555,9168579,9168656,9168739,9872539,12079739,12085840,12090519,13801203,18579021,34030102,34324342,34387765,58651641,70893751,75695054,94804172,104968711,124831809,135938287,140557486,155773253,170949925,181601032,181716302,181882963,185256839,191685680,195478063,196446370,196668711,198801097,203101459,217752161,230159704,241060623,246550560,248306594,252917455,272669511,275471863,294618970,302920726,305222885,327567159,328144055,330709733,332849207,337549264,337731848,340242946,340680961,348334040,349629764,350498903,357825478,358320695,362987262,391331042,400869283,401815465,428957939,429336116,432275881,432279597,443630203,450567544,453988169,484512602,484520712,484533033,484534199,484535847,484544428,484545125,484547021,484553137,487900076,491242614,492744710,492787927,492799726,492866923,497444080,497483018,499764323,501511914,502110491,503540613,503636535,504164530,508658401,508888435,508928101,511003520,513859770,517955290,519195801,523016532,527792211,542417909,547466213,549446456,553500528,553903855,557276314,558877342,575056260,591279217,592140130,600576497,602002033,615324116,626695365,633057105) THEN 'RED'
    WHEN Company_0.CompanyID IN (7939412,7939655,7941712,8054218,8054544,8054567,8059303,9166056,9168330,94823141,303192985,337650213,340055053,357598514,506835671,512310844,574877608,576590130,603197632) THEN 'Yellow'
    ELSE NULL
    END) AS 'Cell'
    ,(CASE
    WHEN SopOrderItem.SoiProcessMethod = 0 AND SopOrderItem.SoiReplenishmentOrder = 1 AND SopProduct.SopPrePostPaid = 0 THEN 'Replenishment Pay on Replenishment'
    WHEN SopOrderItem.SoiProcessMethod = 0 AND SopOrderItem.SoiReplenishmentOrder = 1 AND SopProduct.SopPrePostPaid <> 0 THEN 'Replenishment Pay on Delivery'
    WHEN SopOrderItem.SoiProcessMethod = 0 AND SopOrderItem.SoiReplenishmentOrder = 0 THEN 'Call off'
    WHEN SopOrderItem.SoiProcessMethod = 1 THEN 'On Demand'
    WHEN SopOrderItem.SoiProcessMethod = 2 THEN 'Personalised'
    ELSE 'Service'
    END) AS 'OrderMethod'

    FROM

    SBS.PUB.Company Company_0
    , SBS.PUB.SopOrder SopOrder_0
    , SBS.PUB.SopOrderItem SopOrderItem_0
    , SBS.PUB.SopProduct SopProduct_0

    WHERE

    SopOrder_0.SopOrderID = SopOrderItem_0.SopOrderID
    AND Company_0.CompanyID = SopOrder_0.CompanyID
    AND SopOrderItem_0.SopProductID = SopProduct_0.SopProductID
    AND SopOrder_0.SooOrderDate > '2018-01-01'
    AND Company_0.CompanyID <> '66643115'


    GROUP BY

    Company_0.CoaCompanyName
    ,SopOrder_0.SooOrderDate
    ,Company_0.CompanyID
    ,Cell
    ,OrderType
    ,OrderMethod


    My thoughts where that it should look something like this:

    FROM

    SBS.PUB.SopOrderItem SopOrderItem_0
    INNER JOIN SBS.PUB.Company Company_0 ON SopOrder_0.CompanyID = Company_0.CompanyID
    INNER JOIN SBS.PUB.SopOrder SopOrder_0 ON SopOrderItem_0.SopOrderItemID = SopOrder_0.SopOrderID
    LEFT JOIN SBS.PUB.SopProduct SopProduct_0 ON SopOrderItem_0.SopProductID = SopProduct_0.SopProductID


    WHERE


    But when i try to return this i get an error reading that SopOrder_0.CompanyID can not be found even though i know it is in the table as the original basic join query worked.

    Continue reading...

Compartilhe esta Página