Results 1 to 13 of 13
  1. #1
    Join Date
    Sep 2005
    Posts
    20

    Unanswered: Simple But Urgnt

    UPDATE TFM210_DAILY_SECR_TRN_HLDG
    SET ACCT_NUM_INCL_IND = 'Y',
    DIER_INCL_IND = 'Y',
    CLNT_INCL_IND = 'Y'
    WHERE ACCT_NUM IN (SELECT VAL_CDE FROM TFM115_IMPRT_FLTR
    WHERE SRC_CDE='ALL'
    AND ACTN_CDE='INCLUDE'
    AND ENTY_VAL='ACCOUNT') ;


    HERE THE SUBQuERY,when executed individually is giving results as list of 6 different val_cde's.

    But when the whole query is run its not updating the rows in table tfm210_daily_secr_trn_hldg.

    If we give a set of literals instead of select sub query it is updating acct_num.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I *think* that I understood your problem, and - I admit - it seems to be impossible ... What is the result of the following query?
    Code:
    SELECT COUNT (*)
      FROM tfm210_daily_secr_trn_hldg
     WHERE acct_num IN (
              SELECT val_cde
                FROM tfm115_imprt_fltr
               WHERE src_cde = 'ALL'
                 AND actn_cde = 'INCLUDE'
                 AND enty_val = 'ACCOUNT');

  3. #3
    Join Date
    Sep 2005
    Posts
    20
    I AM SORRY I DIDN'T MENTION MY QUESTION. Why is the query when executed whole is not updating any rec's in table tfm210 is my question . Thanks for help.

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    does ACCT_NUM and VAL_CDE have the same precision and everything?
    Are you sure you are not matching a varchar2 to a char?

    As stated above, are you positive that ACCT_NUM and VAL_CCDE match?

    Code:
    ACCT_NUM IN (SELECT VAL_CDE FROM TFM115_IMPRT_FLTR
    WHERE SRC_CDE='ALL'
    AND ACTN_CDE='INCLUDE'
    AND ENTY_VAL='ACCOUNT') ;
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Sep 2005
    Posts
    20
    Hi ,

    ACCT_NUM IS VARCHAR2(12) AND ACCT_NUM IS CHAR(12),

    Ididn't notice that. Is this the problem ?

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I know what was the question, but I don't know the answer; that's why asked you to run a query from post #2.

    In addition to Duck's post: how do the tables look like? What is their description?

    DESC TFM210_DAILY_SECR_TRN_HLDG;
    DESC TFM115_IMPRT_FLTR;

  7. #7
    Join Date
    Sep 2005
    Posts
    20
    desc TFM210_DAILY_SECR_TRN_HLDG
    (
    STMT_DTE CHAR(8),
    SYS_SRC_CDE CHAR(2),
    TRN_TYP_DES VARCHAR2(50),
    SECR_MVMT_CDE CHAR(5),
    TRN_TYP_CDE CHAR(3),
    DIER_CDE CHAR(12),
    CLNT_NUM CHAR(4),
    CLNT_NAM VARCHAR2(38),
    CLNT_GRP_ID CHAR(2),
    ACCT_NUM CHAR(11),
    ACCT_NAM VARCHAR2(38),
    L60_ACCT_NUM CHAR(11),
    L60_ACCT_NAM VARCHAR2(38),
    LOCN_ACCT_NUM CHAR(11),
    SOD_NUM CHAR(6),
    SOD_NAM VARCHAR2(38),
    MKT_DES CHAR(20),
    CURR_CDE CHAR(4),
    TRD_STAT_CDE CHAR(8),
    TCS_ADJ_CDE CHAR(1),
    TCS_TRN_CDE CHAR(3),
    METH_ISTR_CDE CHAR(10),
    MNL_AUTO_CDE CHAR(6),
    METH_OF_STLMT_DES CHAR(10),
    ISTR_DTE CHAR(8),
    TRD_DTE CHAR(8),
    CLR_DTE CHAR(8),
    PYBL_DTE CHAR(8),
    ACTV_DTE CHAR(8),
    STLMT_DTE CHAR(8),
    CXL_DTE CHAR(8),
    BBH_SECR_NUM CHAR(6),
    BBH_SECR_NAM VARCHAR2(38),
    ISITC_DES VARCHAR2(38),
    SVC_OFF_CDE CHAR(2),
    BKPG_OFF_CDE CHAR(2),
    OPER_ID CHAR(4),
    FUNC_CDE CHAR(2),
    CLNT_REF_NUM VARCHAR2(38),
    UNIVERSAL_ID_CDE VARCHAR2(20),
    SONIC_REF_NUM CHAR(13),
    TCKT_NUM CHAR(12),
    DLVR_RCV_CDE CHAR(1),
    PNS_CDE CHAR(1),
    TRD_BRKR_CDE CHAR(8),
    TRD_BRKR_NAM VARCHAR2(38),
    CLR_BRKR_CDE CHAR(8),
    CLR_BRKR_NAM VARCHAR2(20),
    CLNT_REP_IND CHAR(1),
    BBH_REP_IND CHAR(1),
    UNT_QTY CHAR(16),
    PRC_AMT CHAR(16),
    CSH_AMT CHAR(18),
    RPR_RSN_CDE CHAR(2),
    RPR_RSN_DES VARCHAR2(38),
    RPR_FLD_CDE CHAR(5),
    RPR_FLD_DES VARCHAR2(38),
    RPR_FLD_VAL_DES VARCHAR2(38),
    SONIC_PYMT_MDE_CDE CHAR(2),
    SONIC_PYMT_MDE_DES VARCHAR2(38),
    GTPS_PGM_ID CHAR(8),
    PNS_DAV_ORDR_ID VARCHAR2(38),
    PNS_EXCH_CDE CHAR(2),
    PNS_INSTR_CDE CHAR(2),
    PNS_SYM_ID CHAR(10),
    BBH_CMSN_AMT CHAR(10),
    CRS_CRNCY_IND CHAR(1),
    PSET_CDE CHAR(8),
    PSET_CNTRY_CDE CHAR(12),
    PSAFE_CDE CHAR(11),
    PSAFE_CNTRY_CDE CHAR(12),
    DIER_INCL_IND CHAR(1) default 'N',
    TRN_CDE_INCL_IND CHAR(1) default 'Y',
    CLNT_INCL_IND CHAR(1) default 'Y',
    ACCT_NUM_INCL_IND CHAR(1) default 'N'
    )


    desc TFM115_IMPRT_FLTR
    (
    SRC_CDE VARCHAR2(15) not null,
    ACTN_CDE VARCHAR2(7) not null,
    ENTY_VAL VARCHAR2(7) not null,
    VAL_CDE VARCHAR2(12) not null,
    VAL_DES VARCHAR2(50)
    )

  8. #8
    Join Date
    Sep 2005
    Posts
    20
    Acct_num ---- Char, Val_cde----- Varchar2

  9. #9
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    This might work:
    Code:
    UPDATE tfm210_daily_secr_trn_hldg
       SET acct_num_incl_ind = 'Y',
           dier_incl_ind = 'Y',
           clnt_incl_ind = 'Y'
     WHERE acct_num IN (
              SELECT CAST (val_cde AS CHAR (12))
                FROM tfm115_imprt_fltr
               WHERE src_cde = 'ALL'
                 AND actn_cde = 'INCLUDE'
                 AND enty_val = 'ACCOUNT');

  10. #10
    Join Date
    Sep 2005
    Posts
    20
    Thanks you very much for the help.

  11. #11
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    But - did it work or did it not?

  12. #12
    Join Date
    Sep 2005
    Posts
    20
    ofcourse , it did and updated the correct no of rows .

    One more question. Ididn't understand why this query didn't work and the below query works fine .


    UPDATE TFM210_DAILY_SECR_TRN_HLDG
    SET CLNT_INCL_IND='N' WHERE CLNT_NUM IN
    (SELECT VAL_CDE FROM TFM115_IMPRT_FLTR WHERE
    SRC_CDE='ALL' AND ACTN_CDE='EXCLUDE' AND ENTY_VAL='CLN_NUM');

  13. #13
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    CHAR(12) is a fixed length datatype. Therefore, if you stored 'ALL' into it and try to SELECT LENGTH(column_name) FROM table_name; you'll get NOT 3 (as you would if the column was VARCHAR2(12)), but 12.

    Therefore, if the situation was vice versa (update tfm115... table and the subquery written upon tfm210... table), you would - along with the CAST function, need to use RTRIM function which would "cut" the remaining "empty" bytes in order to make the query work properly.

Posting Permissions

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