Please help JOIN or subquery

[KirillToronto]

I have two perfectly working views and I need to join them but dont know how. Here is statement #1

SELECT CAST(foy.SOChangeLog.SONum AS varchar(12)) + '-' + (CASE WHEN SUBSTRING(TheChange, 8, 1) = ':' THEN CAST(SUBSTRING(TheChange, 6, 2)
AS varchar(12)) ELSE CAST(SUBSTRING(TheChange, 6, 1) AS varchar(12)) END) AS SOFull, foy.SOChangeLog.SONum,
(CASE WHEN SUBSTRING(TheChange, 8, 1) = ':' THEN CAST(SUBSTRING(TheChange, 6, 2) AS INT) ELSE CAST(SUBSTRING(TheChange, 6, 1) AS INT)
END) AS SOItemNum, foy.SOChangeLog.TimeChanged AS Create_Date, foy.SOChangeLog.TheChange, foy.Currency.Rate AS Exchange_Rate,
foy.Currency.CurrencyCode, foy.Cust.DiscountSchedule, foy.Cust.CustNum, foy.Cust.CustName
FROM foy.SOChangeLog INNER JOIN
foy.SO ON foy.SOChangeLog.SONum = foy.SO.SONum INNER JOIN
foy.Cust ON foy.SO.CustBill = foy.Cust.CustNum INNER JOIN
foy.Currency ON foy.Cust.CurrencyCode = foy.Currency.CurrencyCode
WHERE (foy.SOChangeLog.TheChange LIKE '%Created%')



here is statement #2
SELECT MIN(CAST(foy.SOItemDept.SONum AS varchar(12)) + '-' + CAST(foy.SOItemDept.SOItemNum AS varchar(12))) AS SOFul, MIN(foy.SOItemDept.SONum)
AS SoNum, MIN(foy.SOItemDept.SOItemNum) AS SOItemNum, AVG(foy.SOItemDept.RunWt) AS Run_Wt, MIN(foy.SOItemDept.RunDt) AS Run_Dt,
SUM((CASE WHEN foy.SOItemPrice.GLCode = 'MILL' THEN foy.SOItemPrice.PricePerWt * foy.SOItemDept.RunWt ELSE NULL END)) AS Extended_MILL,
SUM((CASE WHEN foy.SOItemPrice.GLCode = 'ENERG' THEN foy.SOItemPrice.PricePerWt * foy.SOItemDept.RunWt ELSE NULL END))
AS Extended_ENERG
FROM foy.SOItemDept INNER JOIN
foy.SOItemPrice ON foy.SOItemDept.SONum = foy.SOItemPrice.SONum AND foy.SOItemDept.SOItemNum = foy.SOItemPrice.SOItemNum
WHERE (foy.SOItemPrice.GLCode = 'MILL' OR
foy.SOItemPrice.GLCode = 'ENERG') AND (foy.SOItemDept.DeptNum = 'ORDER')
GROUP BY CAST(foy.SOItemDept.SONum AS varchar(12)) + '-' + CAST(foy.SOItemDept.SOItemNum AS varchar(12))




I am trying to join them by SOFull. Can someone suggest how to do it.

Thanks a lot for your help.

 

[bborissov]

If these are views then just join them

SELECT ...
FROM View1Name
INNER JOIN View2Name ON View1Name.SOFull = View2Name.SOFull

if these are queries then use them as derived tables:

SELECT ....
FROM (
SELECT     CAST(foy.SOChangeLog.SONum AS varchar(12)) + '-' + (CASE WHEN SUBSTRING(TheChange, 8, 1) = ':' THEN CAST(SUBSTRING(TheChange, 6, 2)
                      AS varchar(12)) ELSE CAST(SUBSTRING(TheChange, 6, 1) AS varchar(12)) END) AS SOFull, foy.SOChangeLog.SONum,
                      (CASE WHEN SUBSTRING(TheChange, 8, 1) = ':' THEN CAST(SUBSTRING(TheChange, 6, 2) AS INT) ELSE CAST(SUBSTRING(TheChange, 6, 1) AS INT)
                      END) AS SOItemNum, foy.SOChangeLog.TimeChanged AS Create_Date, foy.SOChangeLog.TheChange, foy.Currency.Rate AS Exchange_Rate,
                      foy.Currency.CurrencyCode, foy.Cust.DiscountSchedule, foy.Cust.CustNum, foy.Cust.CustName
FROM         foy.SOChangeLog INNER JOIN
                      foy.SO ON foy.SOChangeLog.SONum = foy.SO.SONum INNER JOIN
                      foy.Cust ON foy.SO.CustBill = foy.Cust.CustNum INNER JOIN
                      foy.Currency ON foy.Cust.CurrencyCode = foy.Currency.CurrencyCode
WHERE     (foy.SOChangeLog.TheChange LIKE '%Created%')) Tbl1
INNER JOIN (SELECT     MIN(CAST(foy.SOItemDept.SONum AS varchar(12)) + '-' + CAST(foy.SOItemDept.SOItemNum AS varchar(12))) AS SOFul, MIN(foy.SOItemDept.SONum)
                      AS SoNum, MIN(foy.SOItemDept.SOItemNum) AS SOItemNum, AVG(foy.SOItemDept.RunWt) AS Run_Wt, MIN(foy.SOItemDept.RunDt) AS Run_Dt,
                      SUM((CASE WHEN foy.SOItemPrice.GLCode = 'MILL' THEN foy.SOItemPrice.PricePerWt * foy.SOItemDept.RunWt ELSE NULL END)) AS Extended_MILL,
                      SUM((CASE WHEN foy.SOItemPrice.GLCode = 'ENERG' THEN foy.SOItemPrice.PricePerWt * foy.SOItemDept.RunWt ELSE NULL END))
                      AS Extended_ENERG
FROM         foy.SOItemDept INNER JOIN
                      foy.SOItemPrice ON foy.SOItemDept.SONum = foy.SOItemPrice.SONum AND foy.SOItemDept.SOItemNum = foy.SOItemPrice.SOItemNum
WHERE     (foy.SOItemPrice.GLCode = 'MILL' OR
                      foy.SOItemPrice.GLCode = 'ENERG') AND (foy.SOItemDept.DeptNum = 'ORDER')
GROUP BY CAST(foy.SOItemDept.SONum AS varchar(12)) + '-' + CAST(foy.SOItemDept.SOItemNum AS varchar(12))) Tbl2

ON Tbl1.SOFull = Tbl2.SOFull

I didn't check what you did here. Just get both queries and put them together. Maybe there is better way to do this. but I am too sleepy right now



Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[KirillToronto]

I dont understand why my sebquery duplicates rows now. Any help would be appretiated !!!

SELECT MIN(CAST(foy.SOItemDept.SONum AS varchar(12)) + '-' + CAST(foy.SOItemDept.SOItemNum AS varchar(12))) AS SOFul, MIN(foy.SOItemDept.SONum)
AS SoNum, MIN(foy.SOItemDept.SOItemNum) AS SOItemNum, AVG(foy.SOItemDept.RunWt) AS Run_Wt, MIN(foy.SOItemDept.RunDt) AS Run_Dt,
SUM((CASE WHEN foy.SOItemPrice.GLCode = 'MILL' THEN foy.SOItemPrice.PricePerWt * foy.SOItemDept.RunWt ELSE NULL END)) AS Extended_MILL,
SUM((CASE WHEN foy.SOItemPrice.GLCode = 'ENERG' THEN foy.SOItemPrice.PricePerWt * foy.SOItemDept.RunWt ELSE NULL END))
AS Extended_ENERG, foy.SOItemPrice.GLCode, c.Create_Date, c.CustName, c.CustNum, c.CurrencyCode, c.Exchange_Rate, c.DiscountSchedule
FROM foy.SOItemDept INNER JOIN
foy.SOItemPrice ON foy.SOItemDept.SONum = foy.SOItemPrice.SONum AND foy.SOItemDept.SOItemNum = foy.SOItemPrice.SOItemNum INNER JOIN
(SELECT CAST(foy.SOChangeLog.SONum AS varchar(12)) + '-' + (CASE WHEN SUBSTRING(TheChange, 8, 1)
= ':' THEN CAST(SUBSTRING(TheChange, 6, 2) AS varchar(12)) ELSE CAST(SUBSTRING(TheChange, 6, 1) AS varchar(12)) END) AS SOFull,
foy.SOChangeLog.SONum, (CASE WHEN SUBSTRING(TheChange, 8, 1) = ':' THEN CAST(SUBSTRING(TheChange, 6, 2) AS INT)
ELSE CAST(SUBSTRING(TheChange, 6, 1) AS INT) END) AS SOItemNum, foy.SOChangeLog.TimeChanged AS Create_Date,
foy.SOChangeLog.TheChange, foy.Currency.Rate AS Exchange_Rate, foy.Currency.CurrencyCode, foy.Cust.DiscountSchedule,
foy.Cust.CustNum, foy.Cust.CustName
FROM foy.SOChangeLog INNER JOIN
foy.SO ON foy.SOChangeLog.SONum = foy.SO.SONum INNER JOIN
foy.Cust ON foy.SO.CustBill = foy.Cust.CustNum INNER JOIN
foy.Currency ON foy.Cust.CurrencyCode = foy.Currency.CurrencyCode
WHERE (foy.SOChangeLog.TheChange LIKE '%Created%')) AS c ON c.SONum = foy.SOItemDept.SONum AND
c.SOItemNum = foy.SOItemDept.SOItemNum
WHERE (foy.SOItemPrice.GLCode = 'MILL' OR
foy.SOItemPrice.GLCode = 'ENERG') AND (foy.SOItemDept.DeptNum = 'ORDER')
GROUP BY CAST(foy.SOItemDept.SONum AS varchar(12)) + '-' + CAST(foy.SOItemDept.SOItemNum AS varchar(12)), c.Create_Date, c.CustName, c.CustNum,
c.CurrencyCode, c.Exchange_Rate, c.DiscountSchedule, foy.SOItemPrice.GLCode