CONCENATION QUESTION

[acct098]

TABLE LAYOUT:

ID NOT NULL NUMBER (38)
LABEL NOT NULL NVARCHAR2 (60)
PARENTID NOT NULL NUMBER (38)



SELECT
*
FROM
(SELECT sYear, sMonth, SUM(DataLoad) AS DataLoad,
Entity, sAcct, sGovCode
FROM
(SELECT
EABB_FACT.YearID AS sYear,
EABB_PERIOD.Label AS sMonth,
EABB_FACT.dData AS DataLoad,
EABB_ENTITY.Label AS Entity,
(CASE
WHEN EABB_ACCOUNT.Label = '4210US'
AND SUBSTR (HAL_FACTS_II_APPROP_STATUS.STAT,1,1) = 'E'
THEN '4650' || SUBSTR (EABB_ACCOUNT.Label,5,2)
WHEN EABB_ACCOUNT.Label in ('4540US','4510US','4560US')
AND SUBSTR (HAL_FACTS_II_APPROP_STATUS.STAT,1,1) = 'E'
THEN '4650' || SUBSTR (EABB_ACCOUNT.Label,5,2)

Can someone tell me why I have to use the substr() function in order to select a part of a record(s) that has number and lettes i.e. 4650US and then us the concenation function to put the letters "US" back? Is there a simpler way to get this function to work the will also keep the attributes of the table?

 

[LKBrwnDBA]

Maybe because you have a BAD design?



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[Turkbear]

Hi,
Also ( your posted code would not work, by the way - I assume it is only a snippet)

Why not be specific :
Instead of:

WHEN EABB_ACCOUNT.Label = '4210US'
     AND SUBSTR (HAL_FACTS_II_APPROP_STATUS.STAT,1,1) = 'E' 
   THEN '4650' || SUBSTR (EABB_ACCOUNT.Label,5,2)
   WHEN EABB_ACCOUNT.Label in ('4540US','4510US','4560US')
     AND SUBSTR (HAL_FACTS_II_APPROP_STATUS.STAT,1,1) = 'E' 
   THEN '4650' || SUBSTR (EABB_ACCOUNT.Label,5,2)

Why not:

WHEN EABB_ACCOUNT.Label IN ('4210US','4540US','4510US','4560US')
     AND SUBSTR (HAL_FACTS_II_APPROP_STATUS.STAT,1,1) = 'E' 
   THEN '4650US' )

Your code makes no distinctions based on the EABB_ACCOUNT.Label so why so many Case statements?





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[acct098]

Tried your suggestion it fails. Error: ORA-12704: character set mismatch
Below is the SQL Statement:

SELECT
EA2_FACT.YearID AS sYear,
EA2_PERIOD.Label AS sMonth,
-EA2_FACT.dData AS DataLoad,
EA2_ENTITY.Label AS Entity,
(CASE
WHEN EA2_ACCOUNT.Label = '1410US'
THEN '4802US'
WHEN EA2_ACCOUNT.Label = '1450US'
THEN '4802US'
WHEN EA2_ACCOUNT.Label in ('4060US', '4070US', '4210US')
AND SUBSTR (HAL_FACTS_II_APPROP_STATUS.STAT,1,1) = 'U'
THEN '4590US'
WHEN EA2_ACCOUNT.Label = '1321'
THEN '4901US'
ELSE EA2_ACCOUNT.Label
END) AS sAcct,
EA2_CUSTOM2.Label AS sGovCode
FROM EA2_FACT
INNER JOIN EA2_ENTITY
ON EA2_FACT.EntityID = EA2_ENTITY.ID
INNER JOIN HAL_USER.HAL_FACTS_II_APPROP_STATUS
ON HAL_FACTS_II_APPROP_STATUS.APPROP = EA2_ENTITY.Label
INNER JOIN (SELECT DISTINCT ID, Label FROM EA2_ACCOUNT) EA2_ACCOUNT
ON EA2_FACT.AccountID = EA2_ACCOUNT.ID
INNER JOIN EA2_ICP
ON EA2_FACT.ICPID = EA2_ICP.ID
INNER JOIN EA2_CUSTOM1
ON EA2_FACT.Custom1ID = EA2_CUSTOM1.ID
INNER JOIN (SELECT DISTINCT ID, Label FROM EA2_CUSTOM2) EA2_CUSTOM2
ON EA2_FACT.Custom2ID = EA2_CUSTOM2.ID
INNER JOIN EA2_CUSTOM3
ON EA2_FACT.Custom3ID = EA2_CUSTOM3.ID
INNER JOIN EA2_PERIOD
ON EA2_FACT.PERIODID = EA2_PERIOD.ID
WHERE EA2_CUSTOM1.LABEL = 'TotalC1'
AND EA2_CUSTOM2.Label = 'TotalC2'
AND EA2_CUSTOM3.LABEL = 'TotalC3'
AND EA2_ENTITY.USERDEFINED3 = 'F2'
AND (EA2_ACCOUNT.Label LIKE '14%US'
OR (EA2_ACCOUNT.Label in ('4060US', '4070US', '4210US')
AND SUBSTR (HAL_FACTS_II_APPROP_STATUS.STAT,1,1) = 'U' )
OR EA2_ACCOUNT.Label = '1321' )
;