|
|
|
 |
 |
Archives of the TeradataForumMessage Posted: Tue, 01 Apr 2014 @ 12:23:53 GMT
<-- Anonymously Posted: Tuesday, April 01, 2014 05:15 --> Thanks Marek for your reply V_source_cd is VARCHAR(100) character set unicode The sqlwithvariables.txt is working fine only with character ASCII, it means no syntax error, right? So I changed to UTF8 I received the error. I omit all the variables from SQLwithvariables.txt, so the prodedure is working successfully with ASCII and UTF8 thanks SQLWITHVARIABLES.TXT
CALL DBC.SYSEXECSQL('INSERT INTO '||:V_USER||'.BBR6TEMP SELECT
''BBR'' as CircuNumb_T
, ''6'' as T_CircPage_T,
'''||:SITUATION||''' as SituationType
, '''||:VDATE||''' AS T_DATE,
'''||:VBANK_N||''' as T_BankNumb_N
, CASE WHEN Y.HEAD_OFFICE = ''10''
THEN ''33''
ELSE Y.BRANCH_BDL
END AS T_BranchNumb_N,
P.CODE AS T_ItemNumb_N
, ''1'' AS T_CountNumb_N,
CAST(SUM(CAST( CASE C.SPOT_RATE_RECIP
WHEN ''Y'' THEN (( COALESCE( '||:VHDPF||',0) +
(LEDGER_BALANCE+SHADOW_ADJUST)) * CAST (C.RATE AS DECIMAL(18,3)) )
ELSE (( COALESCE( '||:VHDPF||',0) + (LEDGER_BALANCE+SHADOW_ADJUST))/ CAST
(C.RATE AS DECIMAL(18,3)) )
END AS DECIMAL (18,3)) * CAST( ( '||:LED_FOREIGN_RATE_LEB||') AS
DECIMAL(18,3)) ) AS DECIMAL(18 ,0) ) /'||:DIVIDEDBY||' AS T_Value3_N,
CASE WHEN '||:VBRANCH_N||' IN '||:VAR_BRANCH_LIM_BAH||'
THEN CAST(SUM(CAST( CASE C.SPOT_RATE_RECIP WHEN
''Y'' THEN (( COALESCE( '||:VHDPF||',0) + (LEDGER_BALANCE+SHADOW_ADJUST)) * CAST (C.RATE
AS DECIMAL(18,3)) )
ELSE (( COALESCE( '||:VHDPF||',0) + (LEDGER_BALANCE+SHADOW_ADJUST))/ CAST (C.RATE AS
DECIMAL(18,3)) )
END AS DECIMAL(18,3)) * CAST( ( '||:LED_FOREIGN_RATE_LEB||') AS
DECIMAL(18,3)) ) AS DECIMAL(18 ,0) )
ELSE 0
END /'||:DIVIDEDBY||' AS T_Value2_N,
CASE WHEN '||:VBRANCH_N||' IN '||:VAR_BRANCH_CL_CLIB||'
THEN CAST(SUM(CAST( CASE C.SPOT_RATE_RECIP WHEN ''Y''
THEN (( COALESCE( '||:VHDPF||',0) + (LEDGER_BALANCE+SHADOW_ADJUST)) * CAST (C.RATE AS
DECIMAL(18,3)) )
ELSE (( COALESCE( '||:VHDPF||',0) + (LEDGER_BALANCE+SHADOW_ADJUST))/ CAST (C.RATE AS
DECIMAL(18,3)) )
END AS DECIMAL(18,3)) * CAST( ( '||:LED_FOREIGN_RATE_LEB||') AS
DECIMAL(18,3)) ) AS DECIMAL(18 ,0) )
ELSE 0
END /'||:DIVIDEDBY||' as T_Value1_N
FROM '||:INT_TBL ||' AS A INNER JOIN
'||:BRANCH_TBL||' AS Y ON
A.Account_MODIFIER_NUM = Y.BRANCH_CODE
INNER JOIN
'||:CURR_TBL ||' AS C ON
A.Today_Date = C.Today_Date
AND
A.CURRENCY_CD = C.ISO_CODE_NUMBER
AND
A.SOURCE_CD = C.SOURCE_CD
LEFT OUTER JOIN
'||:PL_TBL ||' AS H ON
A.Source_cd =H.Source_cd
AND
A.ACCOUNT_NUM = H.Account_num
AND
A.Today_Date = H.Today_Date
CROSS JOIN
'||:CBK_TBL||' AS P
WHERE
CAST(A.Today_Date AS VARCHAR(8)) =
'''||:VCASTDATE ||'''
AND
A.SOURCE_CD
IN '||:V_SOURCE_CODE||'
AND
(P.CodeProg
= '''||:CDPRG||''') AND
SUBSTR(A.ACCOUNT_NUM,5,6) BETWEEN COALESCE(P.BASICINF,''000000'')
AND
COALESCE(P.BASICSUP,''000000'') AND
Y.BANK_CODE
= '||:VBRANCH_N||'
GROUP BY P.CodeProg, P.Code,
CASE WHEN Y.HEAD_OFFICE = ''10'' THEN ''33''
ELSE Y.BRANCH_BDL END ');
SQLWITHOUTVARIABLES.TXT
CALL DBC.SYSEXECSQL('INSERT INTO '||:V_USER||'.BBR6TEMP SELECT
''BBR'' as CircuNumb_T
, ''6'' as T_CircPage_T,
''0'' as SituationType
, ''20140329'' AS T_DATE,
''53'' as T_BankNumb_N
, CASE WHEN Y.HEAD_OFFICE = ''10'' THEN ''33''
ELSE Y.BRANCH_BDL END AS T_BranchNumb_N,
P.CODE AS T_ItemNumb_N
, ''1'' AS T_CountNumb_N,
CAST(SUM(CAST( CASE C.SPOT_RATE_RECIP
WHEN ''Y'' THEN (( COALESCE( H.PL_January+H.PL_February+H.PL_March ,0) +
(LEDGER_BALANCE+SHADOW_ADJUST)) * CAST (C.RATE AS DECIMAL(18,3)) )
ELSE (( COALESCE( H.PL_January+H.PL_February+H.PL_March ,0) +
(LEDGER_BALANCE+SHADOW_ADJUST))/ CAST (C.RATE AS DECIMAL(18,3)) )
END AS DECIMAL (18,3)) * CAST( ( 1 ) AS DECIMAL(18,3)) ) AS DECIMAL(18 ,0) ) /1000 AS
T_Value3_N,
CAST(SUM(CAST( CASE C.SPOT_RATE_RECIP WHEN
''Y'' THEN (( COALESCE( H.PL_January+H.PL_February+H.PL_March ,0) +
(LEDGER_BALANCE+SHADOW_ADJUST)) * CAST (C.RATE AS DECIMAL(18,3)) )
ELSE (( COALESCE( H.PL_January+H.PL_February+H.PL_March ,0) +
(LEDGER_BALANCE+SHADOW_ADJUST))/ CAST (C.RATE AS DECIMAL(18,3)) )
END AS DECIMAL(18,3)) * CAST( ( 1 ) AS DECIMAL(18,3)) ) AS DECIMAL(18 ,0)
)
/1000 AS T_Value2_N,
CAST(SUM(CAST( CASE C.SPOT_RATE_RECIP WHEN ''Y''
THEN (( COALESCE( H.PL_January+H.PL_February+H.PL_March ,0) +
(LEDGER_BALANCE+SHADOW_ADJUST)) * CAST (C.RATE AS DECIMAL(18,3)) )
ELSE (( COALESCE( H.PL_January+H.PL_February+H.PL_March ,0) +
(LEDGER_BALANCE+SHADOW_ADJUST))/ CAST (C.RATE AS DECIMAL(18,3)) )
END AS DECIMAL(18,3)) * CAST( ( 1 ) AS DECIMAL(18,3)) ) AS DECIMAL(18 ,0) )
/1000 as T_Value1_N
FROM PROD_TAB.CORE_LEDGER AS A INNER JOIN
PROD_STG_CORE.BRANCHES_BANKS_BDL1 AS Y
ON A.Account_MODIFIER_NUM = Y.BRANCH_CODE INNER JOIN
PROD_TAB.CORE_CURRENCY_LIST
AS C ON A.Today_Date
= C.Today_Date
AND
A.CURRENCY_CD
= C.ISO_CODE_NUMBER AND
A.SOURCE_CD
= C.SOURCE_CD
LEFT OUTER JOIN
PROD_TAB.CORE_PL_STATIS
AS H ON A.Source_cd
=H.Source_cd AND
A.ACCOUNT_NUM
= H.Account_num AND
A.Today_Date = H.Today_Date
CROSS JOIN
PROD_STG_CORE.CBKPARAMTBL
AS P
WHERE
CAST(A.Today_Date AS VARCHAR(8)) = ''20140329''
AND
A.SOURCE_CD
=2
AND
(P.CodeProg
=''BR6'' ) AND
SUBSTR(A.ACCOUNT_NUM,5,6) BETWEEN COALESCE(P.BASICINF,''000000'')
AND
COALESCE(P.BASICSUP,''000000'') AND
Y.BANK_CODE
= 53
GROUP BY P.CodeProg, P.Code,
CASE WHEN Y.HEAD_OFFICE = ''10'' THEN
''33'' ELSE Y.BRANCH_BDL END ');
| ||||||||||||||||||||||||||||||||||||||||||||||
| | ||||||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||||||
| Copyright 2016 - All Rights Reserved | ||||||||||||||||||||||||||||||||||||||||||||||||
| Last Modified: 15 Jun 2023 | ||||||||||||||||||||||||||||||||||||||||||||||||
