J
Jack Williams
Guest
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...
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...