SELECT F0006.MCDL01 PROP_NAME,
F1507.NHMCU PROP_NUM,
F1507.NHUNIT UNIT_NUM,
CASE
WHEN F1507.NHUST='O' OR F1507.NHUST='D'
THEN F15017.NWDOCO
WHEN F1507.NHUST='V' OR F1507.NHUST='C'
THEN '0'
END LEASE_NUM,
CASE
WHEN F1507.NHUST='O' OR F1507.NHUST='D'
THEN F1501B.NEDL01
WHEN F1507.NHUST='V' OR F1507.NHUST='C'
THEN 'AVAILABLE'
END TENANT_DBA,
F0116.ALADDS STATE,
F0116.ALCTY1 CITY,
CONVERT(VARCHAR,CONVERT(MONEY,SUM(A.NWPMU1)),1) GLA,
CONVERT(VARCHAR,CONVERT(MONEY,SUM(B.NWPMU1)),1) ACM
FROM PRODDTA.F0006, PRODDTA.F15017, PRODDTA.F0116, PRODDTA.F1501B, PRODDTA.F1507
LEFT OUTER JOIN PRODDTA.F1514 A ON F1507.NHMCU=A.NWMCU
AND F1507.NHUNIT=A.NWUNIT
AND A.NWARTY='GLA'
LEFT OUTER JOIN PRODDTA.F1514 B ON F1507.NHMCU=B.NWMCU
AND F1507.NHUNIT=B.NWUNIT
AND B.NWARTY='ACM'
-- (SELECT NHMCU, NHUNIT
-- FROM PRODDTA.F15017
-- INNER JOIN PRODDTA.F1507 ON F15017.NWMCU=F1507.NHMCU
-- AND F15017.NWUNIT=F1507.NHUNIT
-- AND F1507.NHUTTY='OTHER'
-- INNER JOIN PRODDTA.F1501B ON F15017.NWDOCO=F1501B.NEDOCO
-- AND F15017.NWLSVR=F1501B.NELSVR
-- AND F1501B.NELSET='OA') C
WHERE F1507.NHUTTY!='DISAB'
AND F0006.MCAN8=F0116.ALAN8
AND F0006.MCMCU=F1507.NHMCU
AND F0006.MCMCU=F15017.NWMCU
AND F1507.NHMCU=F15017.NWMCU
AND F1507.NHUNIT=F15017.NWUNIT
AND F15017.NWDOCO=F1501B.NEDOCO
GROUP BY F0006.MCDL01, F1507.NHMCU, F1507.NHUNIT, F15017.NWDOCO,
F0116.ALADDS, F0116.ALCTY1, A.NWPMU1, F1507.NHUST, A.NWARTY,
F1501B.NEDL01
-- ......
LEFT OUTER JOIN PRODDTA.F1514 B ON F1507.NHMCU=B.NWMCU
AND F1507.NHUNIT=B.NWUNIT
AND B.NWARTY='ACM'
JOIN
(SELECT NHMCU, NHUNIT
FROM PRODDTA.F15017
INNER JOIN PRODDTA.F1507 ON F15017.NWMCU=F1507.NHMCU
AND F15017.NWUNIT=F1507.NHUNIT
AND F1507.NHUTTY='OTHER'
INNER JOIN PRODDTA.F1501B ON F15017.NWDOCO=F1501B.NEDOCO
AND F15017.NWLSVR=F1501B.NELSVR
AND F1501B.NELSET='OA') C
ON c.[columnname] = F15017.[matchingColumn]
AND c.[anotherColumnName] = F1507.[anotherMatchingColumn]
WHERE F1507.NHUTTY!='DISAB'
-- ........
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
From novice to tech pro — start learning today.
Anyway, try the code in the code box - you will probably need to add a unique column to the inner query, "C" and join that to some appropriate column(s) in the main query so you don't join to multiple rows, but I think this should do what you want .....
Open in new window