How to join multiple tables

[support_4]

 The below SQL is what I want to do. The outline doesn't parse it correctly and while I don't get an error, I don't get any results either. How do I go about making the software grasp what  I want to do?
 

select    co.couponDescription,    cn.ContractDescription,    count(1) as "num loaded",    sum(co.CouponValue) as "loaded value"from TCardCoupon cc    left join TCoupon co        on co.Couponid = cc.couponid    left join TCampaign ca        on ca.CampaignID = co.CampaignID    left join TContract cn        on ca.ContractID =cn.ContractIDwhere DateRetrieved is null    and DateLoaded >= '20201231'    and DateLoaded <= '20200101'group by cc.CouponId[/code]

 

[support_4]

Apparently the problem here is that JasperReports can't work with table aliases. The following form works:

SELECT db.`TContract`.`TennantID`,    db.`TContract`.`ContractID`,    db.`TContract`.`ContractDescription`,    db.`TCampaign`.`CampaignID`,    db.`TCampaign`.`CampaignDescription`,    db.`TCampaign`.`StartDate` AS CampaignStartDate,    db.`TCampaign`.`EndDate` AS CampaignEndDate,    db.`TCampaign`.`CampaignStatus`,    db.`TCoupon`.`CouponID`,    db.`TCoupon`.`CouponDescription`,    db.`TCoupon`.`ValidityDays`,    db.`TCoupon`.`StartDate` AS CouponStartDate,    db.`TCoupon`.`EndDate` AS CouponEndDate,    sum( db.`TCoupon`.`CouponValue` ),    count( db.`TCardCoupon`.`CardCouponID` )FROM db.`TCardCoupon`    LEFT OUTER JOIN db.`TCoupon` ON     db.`TCoupon`.`CouponID` = db.`TCardCoupon`.`CouponID`    LEFT OUTER JOIN db.`TCampaign` ON     db.`TCoupon`.`CampaignID` = db.`TCampaign`.`CampaignID`    LEFT OUTER JOIN db.`TContract` ON     db.`TContract`.`ContractID` = db.`TCampaign`.`ContractID`WHERE     db.`TCardCoupon`.`DateRetrieved` IS NULL  GROUP BY db.`TCardCoupon`.`CouponID`[/code]

 

[jgust]

I believe the issue is not that Jasper can't deal with aliases but the Outline Wizzard tab can't parse SQL that is not super simple.  What you can do is drop your SQL on the texts tab then read fields.  If no errors then your query is valid.

SELECT
      jg.Requester as MyRequester
    , jg.ReqID     as MyReqID
    , jg.Status    as MyStatus
    , jg.StatusID  as MyStatusID
    , jg.Status       'MyStatus2'
    , jg.Status       "My Status 3"
    , u.loginname as loginname
    , u.userfname
FROM TEMPJG jg
  left outer join usrusers u
    on jg.reqid = u.userid