Results 1 to 5 of 5

Thread: query problem

  1. #1
    Join Date
    Jul 2014
    Posts
    2

    Unanswered: query problem

    hi all,
    when i run this query the error is (unknown column "vwclaimret.isdeleted in having clause)
    can you please help me to find the problem?any suggestion/answered would be so much appreciated!
    thanks in advance!

    SELECT Count(if(( Isnull(approvalstatus)
    AND Isnull(overallstatus) )
    OR ( approvalstatus = "pending"
    AND Isnull(overallstatus) ), "submitted", if(
    approvalstatus = "approved"
    AND Isnull(overallstatus), "approved",
    if(approvalstatus = "resubmission"
    AND Isnull(overallstatus),"resubmission",
    if(approvalstatus ="rejected"
    AND Isnull(overallstatus),"rejected", if(approvalstatus ="approved"
    AND overallstatus ="processed","processed",
    if(approvalstatus = "approved"
    AND overallstatus ="cm released","cm released",
    if(Isnull(approvalstatus)
    AND overallstatus = "cancelled"
    OR approvalstatus = "cancelled"
    AND overallstatus = "cancelled", "deleted","submitted")))))))) AS Overall,
    if(( Isnull(approvalstatus)
    AND Isnull(overallstatus) )
    OR ( approvalstatus = "pending"
    AND Isnull(overallstatus) ), "submitted", if(
    approvalstatus = "approved"
    AND Isnull(overallstatus), "approved",
    if(approvalstatus = "resubmission"
    AND Isnull(overallstatus), "resubmission",
    if(approvalstatus = "rejected"
    AND Isnull(overallstatus), "rejected", if(approvalstatus = "approved"
    AND overallstatus ="processed", "processed",
    if(approvalstatus = "approved"
    AND overallstatus = "cm released",
    "cm released", if(Isnull( approvalstatus)
    AND overallstatus = "cancelled"
    OR approvalstatus = "cancelled"
    AND overallstatus = "cancelled", "deleted","submitted"))))))) AS Status,
    Month(claimvalidFrom) AS strMonth,
    Year(claimValidFrom) AS strYear,
    concat(month(vwclaimret.claimvalidto) , "/1/" ,year(
    vwclaimret.claimvalidto)) AS finalDate,
    tbcurrpromap.prevproposedcoastaldiscount * tbcurrpromap.prevcommittedvolltrs AS prevCoastalActual,
    vwclaimret.claimvalidfrom,
    vwclaimret.claimvalidto,
    tblclaimstatus.caseid,
    tblclaimstatus.cmnumber,
    tblclaimstatus.cmdate,
    vwclaimret.claimid,
    vwclaimret.clabatchno,
    vwclaimret.shiptocode,
    vwclaimret.actualcoastalvol,
    vwclaimret.actualcoastalphp,
    vwclaimret.actualdiscountperltr,
    vwclaimret.retsubmitteddate,
    tbcurrpromap.proposalid,
    tbcurrpromap.probatchno,
    tbcurrpromap.soldto,
    tbcurrpromap.productid,
    tbcurrpromap.accountname,
    tbcurrpromap.proposaltype,
    tbcurrpromap.proposedcoastaldiscount,
    tbcurrpromap.committedvolltrs,
    tbcurrpromap.prevproposedcoastaldiscount,
    tbcurrpromap.prevcommittedvolltrs,
    tbcurrpromap.fleetcustomer,
    tbcurrpromap.maxvol,
    tbcurrpromap.retjustification,
    tbcurrpromap.retsubmissiondate,
    tblclaimstatus.overallremarks
    AS rtmComment,
    tbcurrpromap.arnoc,
    tbcurrpromap.sitename,
    tbcurrpromap.rsop_prod,
    vwclaimret.retremarks,
    tbcurrpromap.districtcode,
    tbcurrpromap.type,
    tblclaimstatus.approvalstatus,
    tblclaimstatus.overallstatus
    FROM (vwclaimret
    LEFT JOIN tblclaimstatus
    ON vwclaimret.claimid = tblclaimstatus.claid)
    INNER JOIN tbcurrpromap
    ON vwclaimret.proid = tbcurrpromap.proposalid
    GROUP BY if(( Isnull(approvalstatus)
    AND Isnull(overallstatus) )
    OR ( approvalstatus = "pending"
    AND Isnull(overallstatus) ), "submitted", if(
    approvalstatus = "approved"
    AND Isnull(overallstatus), "approved",
    if(approvalstatus = "resubmission"
    AND Isnull(overallstatus), "resubmission",
    if(approvalstatus = "rejected"
    AND Isnull(overallstatus), "rejected",
    if(approvalstatus = "approved"
    AND overallstatus = "processed","processed",
    if(approvalstatus ="approved"
    AND overallstatus = "cm released","cm released",
    if(Isnull(approvalstatus)
    AND overallstatus = "cancelled"
    OR approvalstatus = "cancelled"
    AND overallstatus = "cancelled","deleted","submitted"))))))),
    Month(claimvalidFrom),
    Year(claimValidFrom),
    concat(month(vwclaimret.claimvalidto) , "/1/" ,year(vwclaimret.claimvalidto)),
    tbcurrpromap.prevproposedcoastaldiscount * tbcurrpromap.prevcommittedvolltrs,
    vwclaimret.claimvalidfrom,
    vwclaimret.claimvalidto,
    tblclaimstatus.caseid,
    tblclaimstatus.cmnumber,
    tblclaimstatus.cmdate,
    vwclaimret.claimid,
    vwclaimret.clabatchno,
    vwclaimret.shiptocode,
    vwclaimret.actualcoastalvol,
    vwclaimret.actualcoastalphp,
    vwclaimret.actualdiscountperltr,
    vwclaimret.retsubmitteddate,
    tbcurrpromap.proposalid,
    tbcurrpromap.probatchno,
    tbcurrpromap.soldto,
    tbcurrpromap.productid,
    tbcurrpromap.accountname,
    tbcurrpromap.proposaltype,
    tbcurrpromap.proposedcoastaldiscount,
    tbcurrpromap.committedvolltrs,
    tbcurrpromap.prevproposedcoastaldiscount,
    tbcurrpromap.prevcommittedvolltrs,
    tbcurrpromap.fleetcustomer,
    tbcurrpromap.maxvol,
    tbcurrpromap.retjustification,
    tbcurrpromap.retsubmissiondate,
    tblclaimstatus.overallremarks,
    tbcurrpromap.arnoc,
    tbcurrpromap.sitename,
    tbcurrpromap.rsop_prod,
    vwclaimret.retremarks,
    tbcurrpromap.districtcode,
    tbcurrpromap.type,
    tblclaimstatus.approvalstatus,
    tblclaimstatus.overallstatus,
    tbcurrpromap.isdeleted,
    vwclaimret.isdeleted=0 and vwclaimret.IsDeleted=0
    HAVING ( vwclaimret.claimvalidto ) < Month(Now()) & "/" & Day(Now()) & "/" & Year(Now())
    AND (vwclaimret.isDeleted=0)

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Check the column is in the specified table. Check for typos
    Btw dates in MySQL should be in ISO format yyyy-mm-dd not US or European formats. Also there is no need to format the now() function the db engine 'knows' now() returns a valid date time value.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jul 2014
    Posts
    2
    i checked it already but i got the same error..thanks for the help!

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Code:
    ...tbcurrpromap.isdeleted,
    vwclaimret.isdeleted=0 and vwclaimret.IsDeleted=0
    HAVING...
    vwclaimret.isdeleted=0 and vwclaimret.IsDeleted=0

    is not the same, that's suggesting you have two columns in your table vwclaimret. one called
    isdeleted one called IsDeleted

    Capitalisation is significant in MySQL (and AFAIK most if not all server DB's)

    why you have repeated similar columns beats me

    I'm guessing your column is actually called isdeleted, and not IsDeleted

    Im also guessing you are used to using Ms Access / JET, you need to get a grip on your bracketing. usign the query designer in Access seems to add brackets for the heck of it.


    right now Id strip out all the extreanous coulmns till you get the meat of the query working, prove the joins, then add thos columns back in. to be honest posting a query that long here just makes my eyes glaze over. the error message is reporting 'unknown column "vwclaimret.isdeleted in having clause', yet we dont' hjave the table design for vwclaimret which is rather fundamental to this error message
    Last edited by healdem; 08-01-14 at 06:11.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Aug 2014
    Posts
    3

    Re-check your entire code make sure!

    Some times, we make tiny mistakes and as a result, we've to suffer a lot ultimately. Please re check your entire code and hope for the best.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •