Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2009
    Posts
    3

    Lightbulb Unanswered: Needs help with query ... using multiple case statements

    The below query ... I have to pull in multiple rows from different tables to get a certain state of a debt ... hoping someone can help me figure out why I get error stating that unexpected token after select ...



    SELECT fin.CASE_ID, dbtr.BUSINESS_NAME, fin.REMAIN_PRINCIPAL + fin.REMAIN_INTEREST + fin.REMAIN_PENALTY + fin.REMAIN_ADMIN_COST as "Current Balance",
    dbt.HEALTH_INSURANCE_CLAIM as "HIC #", dbt.CUR_COLL_ORG_lOC_TYPE as " Location", dbt.CUR_COLL_ORG_LOC_PCA_ID as " Debt Location",
    CASE

    WHEN dbt.CUR_COLL_ORG_lOC_TYPE = 'DMS'AND
    dbt.PASSIVE_AT_TOP is NULL AND
    cdbtr.AT_TOP = 'N' AND
    dbt.ELIGIBLE_FOR_DISTRIBUTION = 'N'
    THEN "At DMSOC"

    WHEN dbt.CUR_COLL_ORG_lOC_TYPE = 'DMS'AND
    dbt.PASSIVE_AT_TOP is NULL AND
    cdbtr.AT_TOP = 'Y' AND
    dbt.ELIGIBLE_FOR_DISTRIBUTION = 'N'
    THEN "At DMSOC, At TOP"

    WHEN dbt.CUR_COLL_ORG_lOC_TYPE = 'DMS'AND
    dbt.PASSIVE_AT_TOP is NOT NULL AND
    cdbtr.AT_TOP = 'N' AND
    dbt.ELIGIBLE_FOR_DISTRIBUTION = 'N'
    THEN "Passive at TOP"


    WHEN dbt.CUR_COLL_ORG_lOC_TYPE = 'DMS' AND
    org.LOCATION_TYPE = 'DMS' AND
    cdbtr.AT_TOP = 'N' AND
    dbt.ELIGIBLE_FOR_DISTRIBUTION = 'Y'
    THEN "At DMSOC, Pending Referral to PCA1"

    WHEN dbt.CUR_COLL_ORG_lOC_TYPE = 'DMS' AND
    org.LOCATION_TYPE = 'DMS' AND
    cdbtr.AT_TOP = 'Y' AND
    dbt.ELIGIBLE_FOR_DISTRIBUTION = 'Y'
    THEN "At TOP, Pending referral to PCA1"

    WHEN dbt.CUR_COLL_ORG_lOC_TYPE = 'PCA' AND
    org.LOCATION_TYPE = 'DMS' AND
    cdbtr.AT_TOP = 'N' AND
    dbt.ELIGIBLE_FOR_DISTRIBUTION = 'N'
    THEN "At PCA1"

    WHEN dbt.CUR_COLL_ORG_lOC_TYPE = 'DMS' AND
    org.LOCATION_TYPE = 'PCA' AND
    cdbtr.AT_TOP = 'N' AND
    dbt.ELIGIBLE_FOR_DISTRIBUTION = 'Y'
    THEN "Debt is Pending Referral to PCA2"

    WHEN dbt.CUR_COLL_ORG_lOC_TYPE = 'PCA'AND
    org.LOCATION_TYPE = 'PCA' AND
    cdbtr.AT_TOP = 'N' AND
    dbt.ELIGIBLE_FOR_DISTRIBUTION = 'N'
    THEN "At PCA2"

    WHEN dbt.CUR_COLL_ORG_lOC_TYPE = 'PCA' AND
    org.LOCATION_TYPE = 'PCA' AND
    cdbtr.AT_TOP = 'Y' AND
    dbt.ELIGIBLE_FOR_DISTRIBUTION = 'N'
    THEN "At PCA2 At TOP"

    WHEN dbt.CUR_COLL_ORG_lOC_TYPE = 'CA'
    THEN "AT CA"

    WHEN dbt.CUR_COLL_ORG_lOC_TYPE = 'DOJ'
    THEN "At DOJ"
    END as "Debt State"





    FROM
    FDBT.FINCL_HIST fin,
    FDBT.DEBT dbt,
    FDBT.DEBTOR dbtr,
    FDBT.CASE_DEBT_DEBTOR cdbtr,
    FDBT.DEBT_CLTN_ORG_INFO org,
    FDBT.CRTOR_PGM_PROF prof
    (SELECT MAX(TIMESTAMP1) as maxdate, OBJECTID, CASE_ID FROM FDBT.FINCL_HIST
    GROUP BY OBJECTID, CASE_ID )maxresult



    WHERE
    cdbtr.DEBT_OBJECTID = dbt.OBJECTID AND
    cdbtr.DEBTOR_OBJECTID = dbtr.OBJECTID AND
    maxresult.CASE_ID = cdbtr.CASE_ID AND
    maxresult.CASE_ID = fin.CASE_ID AND
    org.DEBT_OBJECTID = dbt.OBJECTID AND
    fin.CASE_ID = cdbtr.CASE_ID AND
    cdbtr.IS_CLOSED = 'N' AND
    prof.OBJECTID = dbt.PROGRAMPROFILE_OBJECTID AND
    prof.EIN = '421619108'

    ORDER BY fin.CASE_ID;

  2. #2
    Join Date
    Oct 2009
    Posts
    3

    error code

    SQL0104N An unexpected token "fin.CASE_ID, dbtr.BUSINESS_NAME,
    fin.REMAIN_PRINCIPAL " was found following "SELECT ". Expected tokens may
    include: "<space>". SQLSTATE=42601


    didn't include in previous post sorry ...

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You forgot a comma.
    .....
    FDBT.CRTOR_PGM_PROF prof ,
    (SELECT MAX(TIMESTAMP1) as maxdate, .....

  4. #4
    Join Date
    Oct 2009
    Posts
    3
    Thanks .... just needed that extra set of eyes ... must have been working too late last night .... thanks again




  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I want to recommend you to place commas(and other operators, like 'AND', '+', etc.) at the top of a line.
    So, it would be easier to find lost comma.

    Here is an example of formatting your SQL code with such way.

    Code:
    SELECT fin. CASE_ID
         , dbtr.BUSINESS_NAME
         ,   fin. REMAIN_PRINCIPAL
           + fin. REMAIN_INTEREST
           + fin. REMAIN_PENALTY
           + fin. REMAIN_ADMIN_COST     as "Current Balance"
         , dbt. HEALTH_INSURANCE_CLAIM  as "HIC #"
         , dbt. CUR_COLL_ORG_lOC_TYPE   as " Location"
         , dbt. CUR_COLL_ORG_LOC_PCA_ID as " Debt Location"
         , CASE
           WHEN dbt.  CUR_COLL_ORG_lOC_TYPE     = 'DMS'
            AND dbt.  PASSIVE_AT_TOP            is NULL
            AND cdbtr.AT_TOP                    = 'N'
            AND dbt.  ELIGIBLE_FOR_DISTRIBUTION = 'N'   THEN
                "At DMSOC"
           WHEN dbt.  CUR_COLL_ORG_lOC_TYPE     = 'DMS'
            AND dbt.  PASSIVE_AT_TOP            is NULL
            AND cdbtr.AT_TOP                    = 'Y'
            AND dbt.  ELIGIBLE_FOR_DISTRIBUTION = 'N'   THEN
                "At DMSOC, At TOP"
           WHEN dbt.  CUR_COLL_ORG_lOC_TYPE     = 'DMS'
            AND dbt.  PASSIVE_AT_TOP            is NOT NULL
            AND cdbtr.AT_TOP                    = 'N'
            AND dbt.  ELIGIBLE_FOR_DISTRIBUTION = 'N'   THEN
                "Passive at TOP"
           WHEN dbt.  CUR_COLL_ORG_lOC_TYPE     = 'DMS'
            AND org.  LOCATION_TYPE             = 'DMS'
            AND cdbtr.AT_TOP                    = 'N'
            AND dbt.  ELIGIBLE_FOR_DISTRIBUTION = 'Y'   THEN
                "At DMSOC, Pending Referral to PCA1"
           WHEN dbt.  CUR_COLL_ORG_lOC_TYPE     = 'DMS'
            AND org.  LOCATION_TYPE             = 'DMS'
            AND cdbtr.AT_TOP                    = 'Y'
            AND dbt.  ELIGIBLE_FOR_DISTRIBUTION = 'Y'   THEN
                "At TOP, Pending referral to PCA1"
           WHEN dbt.  CUR_COLL_ORG_lOC_TYPE     = 'PCA'
            AND org.  LOCATION_TYPE             = 'DMS'
            AND cdbtr.AT_TOP                    = 'N'
            AND dbt.  ELIGIBLE_FOR_DISTRIBUTION = 'N'   THEN
                "At PCA1"
           WHEN dbt.  CUR_COLL_ORG_lOC_TYPE     = 'DMS'
            AND org.  LOCATION_TYPE             = 'PCA'
            AND cdbtr.AT_TOP                    = 'N'
            AND dbt.  ELIGIBLE_FOR_DISTRIBUTION = 'Y'   THEN
                "Debt is Pending Referral to PCA2"
           WHEN dbt.  CUR_COLL_ORG_lOC_TYPE     = 'PCA'
            AND org.  LOCATION_TYPE             = 'PCA'
            AND cdbtr.AT_TOP                    = 'N'
            AND dbt.  ELIGIBLE_FOR_DISTRIBUTION = 'N'   THEN
                "At PCA2"
           WHEN dbt.  CUR_COLL_ORG_lOC_TYPE     = 'PCA'
            AND org.  LOCATION_TYPE             = 'PCA'
            AND cdbtr.AT_TOP                    = 'Y'
            AND dbt.  ELIGIBLE_FOR_DISTRIBUTION = 'N'   THEN
                "At PCA2 At TOP"
           WHEN dbt.  CUR_COLL_ORG_lOC_TYPE     = 'CA'  THEN
                "AT CA"
           WHEN dbt.  CUR_COLL_ORG_lOC_TYPE     = 'DOJ' THEN
                "At DOJ"
           END  as "Debt State"
      FROM
           FDBT.FINCL_HIST         fin
         , FDBT.DEBT               dbt
         , FDBT.DEBTOR             dbtr
         , FDBT.CASE_DEBT_DEBTOR   cdbtr
         , FDBT.DEBT_CLTN_ORG_INFO org
         , FDBT.CRTOR_PGM_PROF     prof
         , (SELECT MAX(TIMESTAMP1) as maxdate
                 , OBJECTID
                 , CASE_ID
              FROM FDBT.FINCL_HIST
             GROUP BY
                   OBJECTID
                 , CASE_ID
           )                       maxresult
     WHERE
           cdbtr.    DEBT_OBJECTID   = dbt.  OBJECTID
       AND cdbtr.    DEBTOR_OBJECTID = dbtr. OBJECTID
       AND maxresult.CASE_ID         = cdbtr.CASE_ID
       AND maxresult.CASE_ID         = fin.  CASE_ID
       AND org.      DEBT_OBJECTID   = dbt.  OBJECTID
       AND fin.      CASE_ID         = cdbtr.CASE_ID
       AND cdbtr.    IS_CLOSED       =       'N'
       AND prof.     OBJECTID        = dbt.  PROGRAMPROFILE_OBJECTID
       AND prof.     EIN             =       '421619108'
    
     ORDER BY
           fin.CASE_ID
    ;

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Double quotations like:
    THEN "At DMSOC"
    would be syntax error.

Posting Permissions

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