9 Replies Latest reply: Sep 1, 2013 7:17 AM by EdStevens RSS

    substr of date as Column name

    929107


      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

          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


            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

              Show us your expected o/p

              • 4. Re: substr of date as Column name
                929107

                Something like this

                 

                Today  6 Months ago (Feb 2013)

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

                1234        24563

                • 5. Re: substr of date as Column name
                  Pacmann

                  Hi,

                   

                  Some solutions here :

                   

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

                  • 6. Re: substr of date as Column name
                    BluShadow

                    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

                      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

                        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

                          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.