8 Replies Latest reply: Mar 31, 2008 7:21 AM by 519688 RSS

    Replacing / with a newline character using replace function

    472501
      Hi

      I tried using chr(10), chr (13) for replacing / with a newline.
      it does work but disrupts the format of other fields.
      I want the values to appear in 1 cell seperated by newline,instead it mixes with other columns.How can i keep the delimited values with in its column width.
      The query is
      SELECT
        (SELECT hierarchy_name
         FROM strategy_hierarchy_l
         WHERE hierarchy_id = z.aa)
      AS
      "Hieararchy Name",
          (SELECT level_name
         FROM entity_strat_level_type_l
         WHERE entity_id = 1
         AND level_type_id = z.bb)
      AS
      "Level Type",
          (SELECT long_description
         FROM system_codes,
           strategy_level_type slt
         WHERE system_code_id = slt.level_classification_cd
         AND z.bb = slt.level_type_id
         AND code_value = z.dd)
      AS
      "Level Classifcation",
        (
      CASE z.dd
      WHEN 3 THEN
        (SELECT SUBSTR(MAX(REPLACE(sys_connect_by_path(level_value_name, '/'), '/', ' , ')), 3) str
      FROM
      (SELECT a.*, row_number() over(PARTITION BY level_type_id
      ORDER BY level_type_id) row#
      FROM strategy_level_value a) START WITH row# = 1 CONNECT BY PRIOR level_type_id = level_type_id
      AND PRIOR row# = row# -1
      GROUP BY level_type_id HAVING level_type_id = z.bb)

      ELSE
        'Standard Allocations From ' || z.ee || ' Table'
      END)
      "Level Values",
        (SELECT level_name
      FROM entity_strat_level_type_l
      WHERE entity_id = 1
      AND level_type_id = z.cc)
      AS
      "Parent Level Type"
      FROM
      (SELECT t.aa,
         t.bb,
         t.cc,
         t.dd,
         t.ee --,case when level_classification=1 and hierarchy_id is null then (select strategy_level_value from dual)end

      FROM
        (SELECT shl.hierarchy_id AS
         aa,
           shl.level_type_id AS
         bb,
           shl.parent_level_type_id AS
         cc,
           slt.level_classification AS
         dd,
           slt.level_value_table AS
         ee
         FROM strategy_hierarchy_level shl,
           strategy_level_type slt
         WHERE hierarchy_id = 1
         AND shl.level_type_id = slt.level_type_id
         UNION --pick up leafnodes for the correct entity id, additional check for hierarchy id to kee results consistent with the above query
        SELECT NULL,
           slt.level_type_id,
           NULL AS
         parent_level_type_id,
           slt.level_classification,
           slt.level_value_table
         FROM strategy_level_type slt,
           strategy_hierarchy_r shr,
           entity_strat_level_type_r esltr
         WHERE level_classification = 1
         AND shr.entity_id = 1
         AND shr.entity_id = esltr.entity_id
         AND esltr.level_type_id = slt.level_type_id)
      t START WITH t.cc IS NULL CONNECT BY PRIOR t.bb = t.cc)
      z
      sample of output i am getting
      A       B
      1       a,b,c
      2       d,e,f
      if i replace comma with chr(13) i get
      A       B
      1       a
      b
      c
      2       d
      e
      f
      output desired
      A              B
      1              a
                     b
                     d
      2              e
                     f 
      [pre]

      Message was edited by:
              user469498