This discussion is archived
9 Replies Latest reply: Sep 1, 2013 5:17 AM by EdStevens RSS

substr of date as Column name

929107 Newbie
Currently Being Moderated


Hi All,

 

Can we have substr(date) as column name. I am trying to create a query where in I need to achieve something like this

 

select s.xyz Today,

s.abcd "6 Months ago" || to_char(add_months(sysdate,-6),'Mon rrrr') ||')'

from sales_tab s

 

Any idea.

 

Thanks,

  • 1. Re: substr of date as Column name
    Rahul_India Journeyer
    Currently Being Moderated

    You mean

     

    select

    '6 Months ago' || to_char(add_months(sysdate,-6),'Mon YYYY') ||')'

    from DUAL

  • 2. Re: substr of date as Column name
    929107 Newbie
    Currently Being Moderated


    Not really, I am looking for column alias

     

    something like

     

    select  'Value here' "6 Months ago" || to_char(add_months(sysdate,-6),'Mon YYYY')

    from DUAL

  • 3. Re: substr of date as Column name
    Rahul_India Journeyer
    Currently Being Moderated

    Show us your expected o/p

  • 4. Re: substr of date as Column name
    929107 Newbie
    Currently Being Moderated

    Something like this

     

    Today  6 Months ago (Feb 2013)

    --------   -----------------------------------

    1234        24563

  • 5. Re: substr of date as Column name
    Pacmann Journeyer
    Currently Being Moderated

    Hi,

     

    Some solutions here :

     

    http://www.orafaq.com/forum/t/8782/2/

  • 6. Re: substr of date as Column name
    BluShadow Guru Moderator
    Currently Being Moderated

    What you're talking about is having a dynamic SQL Projection.  That's not easily possible.

     

    First read this to understand what SQL Projection is...

     

    PL/SQL 101 : Cursors and SQL Projection

     

    And then see this to understand how you can achieve it, though it's not easy.

     

    How to pipeline a function with a dynamic number of columns?

     

    The question you should be asking is, why do you want to have a dynamically changing column name as you would need dynamically changing code to be able to use the dynamic results.

    Dynamic naming of output should be done via a suitable reporting tool.

  • 7. Re: substr of date as Column name
    EdStevens Guru
    Currently Being Moderated

    929107 wrote:

     


    Hi All,

     

    Can we have substr(date) as column name. I am trying to create a query where in I need to achieve something like this

     

    select s.xyz Today,

    s.abcd "6 Months ago" || to_char(add_months(sysdate,-6),'Mon rrrr') ||')'

    from sales_tab s

     

    Any idea.

     

    Thanks,

     

    Just to clarify, in spite of your subject line, you are not talking about the column name (which is a fixed property of the table) but rather the column header displayed on the report ... presumably in sqlplus.  Yes, as you mentioned in a later follow-up, you are talking about the column alias  (select column_a AS this_is_my_column_alias), but in the end, the reason you want that as your column alias is so that it will show up in a report.  So you really need to step back and explain your environment and what you really want to achieve, not how to implement your pre-conceived solution.  Ultimately, the best solution is going to depend on what your reporting mechanism is ...

  • 8. Re: substr of date as Column name
    Barbara Boehmer Oracle ACE
    Currently Being Moderated

    SCOTT@orcl12c> CREATE TABLE sales_tab

      2    (xyz   NUMBER,

      3      abcd  NUMBER)

      4  /

     

    Table created.

     

    SCOTT@orcl12c> INSERT INTO sales_tab VALUES (1234, 24563)

      2  /

     

    1 row created.

     

    SCOTT@orcl12c> VARIABLE g_ref REFCURSOR

    SCOTT@orcl12c> DECLARE

      2    v_sql  VARCHAR2(32767);

      3  BEGIN

      4    v_sql := 'SELECT xyz "Today",

      5              abcd "6 Months ago ('

      6              || TO_CHAR (ADD_MONTHS (SYSDATE, -6), 'Mon YYYY')

      7              || ')"

      8           FROM    sales_tab';

      9    OPEN :g_ref FOR v_sql;

    10  END;

    11  /

     

    PL/SQL procedure successfully completed.

     

    SCOTT@orcl12c> PRINT g_ref

     

         Today 6 Months ago (Feb 2013)

    ---------- -----------------------

          1234                   24563

     

    1 row selected.

  • 9. Re: substr of date as Column name
    EdStevens Guru
    Currently Being Moderated

    BarbaraBoehmer wrote:

     

    SCOTT@orcl12c> CREATE TABLE sales_tab

      2    (xyz   NUMBER,

      3      abcd  NUMBER)

      4  /

     

    Table created.

     

    SCOTT@orcl12c> INSERT INTO sales_tab VALUES (1234, 24563)

      2  /

     

    1 row created.

     

    SCOTT@orcl12c> VARIABLE g_ref REFCURSOR

    SCOTT@orcl12c> DECLARE

      2    v_sql  VARCHAR2(32767);

      3  BEGIN

      4    v_sql := 'SELECT xyz "Today",

      5              abcd "6 Months ago ('

      6              || TO_CHAR (ADD_MONTHS (SYSDATE, -6), 'Mon YYYY')

      7              || ')"

      8           FROM    sales_tab';

      9    OPEN :g_ref FOR v_sql;

    10  END;

    11  /

     

    PL/SQL procedure successfully completed.

     

    SCOTT@orcl12c> PRINT g_ref

     

         Today 6 Months ago (Feb 2013)

    ---------- -----------------------

          1234                   24563

     

    1 row selected.

     

    Ok.

    Exactly what I was talking about.  Creating a customized, date dependent column header in the report.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points