Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Tue, 01 Apr 2014 @ 12:23:53 GMT


     
  <Prev Next>   <<First <Prev Next> Last>>  


Subj:   Re: Arabic data and session character set
 
From:   Anomy Anom

<-- 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         ');


     
  <Prev Next>   <<First <Prev Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023