Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Posts
    54

    Unanswered: concatenate the columns

    Hi,

    My query

    select op.bse_optn_num as c1, opt.des ,
    op.rspns_cutof_dte,
    op.rspns_cutof_tme
    FROM TNR241_optn op, TNR244_opt_typ opt
    WHERE op.opt_typ_num = opt.num
    AND op.annc_smry_id = 2767347
    ORDER BY c1;

    My result

    C1 DES RSPNS_CUTOF_DTE RSPNS_CUTOF_TME
    1 Cash 2004-06-17 15:00:00

    But i want result as follows

    Option Timestamp
    1. Cash 2004-06-17 15:00:00

    basically i need to concatenate the first 2 columns and last 2 columns some thing like this
    select op.bse_optn_num ||'. ' || opt.des as Option ,
    op.rspns_cutof_dte ||' '||op.rspns_cutof_tme as Timestamp
    FROM TNR241_optn op, TNR244_opt_typ opt
    WHERE op.opt_typ_num = opt.num
    AND op.annc_smry_id = 2767347
    ORDER BY Option;

    This will work in Oracle, but it is not working with DB2

    How do we do this?

    I appreciate ur help.

    Thanks
    Last edited by manth; 08-17-04 at 14:47.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What are the data types of:
    op.bse_optn_num, opt.des, op.rspns_cutof_dte, and op.rspns_cutof_tme?

    Andy

  3. #3
    Join Date
    Aug 2004
    Posts
    330
    What data type is the op.bse_optn_num? If it is numeric, you might consider casting it as Character in order to concatenate.

  4. #4
    Join Date
    Aug 2004
    Posts
    330
    What data type is the op.bse_optn_num? If it is numeric, you might consider casting it as Character in order to concatenate. Do the same with the timestamp.

  5. #5
    Join Date
    Mar 2004
    Posts
    54
    Here you go

    bse_optn_num: SMALLINT
    des: CHAR
    RSPNS_CUTOF_DTE: DATE
    RSPNS_CUTOF_TME: TIME

    Thanks

  6. #6
    Join Date
    Aug 2004
    Posts
    330
    Try something like this:

    select char(op.bse_optn_num) ||'. ' || opt.des as Option ,
    char(op.rspns_cutof_dte) ||' '||char(op.rspns_cutof_tme) as Timestamp
    FROM TNR241_optn op, TNR244_opt_typ opt
    WHERE op.opt_typ_num = opt.num
    AND op.annc_smry_id = 2767347
    ORDER BY Option;

Posting Permissions

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