| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
10-22-09, 08:14
|
|
Registered User
|
|
Join Date: Oct 2009
Posts: 3
|
|
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;
|
|
10-22-09, 08:18
|
|
Registered User
|
|
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 ...
|
|
10-22-09, 08:33
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
|
|
You forgot a comma.
Quote:
.....
FDBT.CRTOR_PGM_PROF prof ,
(SELECT MAX(TIMESTAMP1) as maxdate, .....
|
|
|
10-22-09, 08:51
|
|
Registered User
|
|
Join Date: Oct 2009
Posts: 3
|
|
Thanks .... just needed that extra set of eyes ... must have been working too late last night .... thanks again
|
|
10-22-09, 10:59
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
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
;
|
|
10-22-09, 11:05
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
Double quotations like:
would be syntax error.
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Needs help with query ... using multiple case statements
