14 Replies Latest reply on Jun 3, 2016 5:34 AM by jihuyao

    Create Column Alias using a Function

    3245903

      Hello all!

      I am fairly new to SQL, and am using Developer currently to query a database. My query is returning calculations, which are working fine, but I want them to roll monthly and to trend for the previous six months. So far my code is working great at pulling the correct calculations and trending for the previous months, the only bit I have left to get is the column aliases. The aliases are currently hard coded as "May", "Apr", etc, but I would like to have those calculate and roll with the rest of the calculations if possible. My current SELECT query is:

      SELECT MONTH1.AGREETYP, MONTH1.CURR AS (SELECT TRUNC(ADD_MONTHS(SYSDATE, -1), 'MM') FROM DUAL), MONTH2.CURR AS "APR", MONTH3.CURR AS "MAR", MONTH4.CURR AS "FEB",MONTH5.CURR AS "JAN", MONTH6.CURR AS "DEC"

      FROM [MONTH1 sub-select],[MONTH2 sub-select],etc.

       

      As you can see above, I'm trying to get month 1 to work and then will move on to the other months. The current code returns an error of

      ORA-00923: FROM keyword not found where expected

      00923. 00000 -  "FROM keyword not found where expected"

      *Cause:   

      *Action:

      Error at Line: 1 Column: 47

      But if I only run that sub-select then it returns the date of 01-May-16, so I know that code works.

      If I change the sub-select to "May" just like the other months then the code works perfectly. If I need to I can keep the column headings hard coded as they are, but I would much rather have the whole report automated and think that surely there has to be a way to make that happen. Any help is appreciated!

        • 1. Re: Create Column Alias using a Function
          Frank Kulash

          Hi,

           

          Sorry, the names of tables and columns have to be given before the query can be compiled.  No data is fetched until after the query is compiled, so it's impossible for table- or column names to be derived from data found in the same query.

           

          One way to get around that is Dynamic SQL, where you do 2 separate queries.  First, you do a Preliminary Query that finds the table- or column names that you can't hard-code.  The output of the Preliminary Query is some (or all) of the code for the Main Query that you run next.  (Watch out if the tables might change in the time between running the 2 queries, even if that's only a split second.)  Sorry, I don't anythig about Developer.  In SQL*Plus, Substitution Variables are often used for dynamic SQL.  You can have a substitution variable called MONTH1, which might get set to APRIL or MAY or JUNE in a preliminary query, and then you can use &MONTH1 as a table- or column name in the main query.

          1 person found this helpful
          • 2. Re: Create Column Alias using a Function

            A result set does NOT have column headings.

             

            If you want column headings then write a query to produce the headings you want and use that.

            • 3. Re: Create Column Alias using a Function
              jihuyao

              As long as you only want month without year for header or column name there are only 12 months and 1-6, 2-7, 3-8, 4-9, 5-10, 6-11, 7-12, 8-1, 9-2, 10-3, 11-4, 12-5 ranges to choose from a view as below.  So if you can determine the first month you can then add in the where clause like range =1-6 or range=12-5.

               

              select 1-6 range, *(Jan to Jun) from t

              union all

              .....

              union all

              select 12-5 range, *(Dec to May) from t

              • 4. Re: Create Column Alias using a Function
                Jarkko Turpeinen

                Hi,

                 

                here's one dynamic concept.

                 

                set serveroutput on
                declare
                
                
                  l_sql clob default q'{
                
                
                  -- This is dynamic SQL template that has
                  -- column headings generated dynamically
                  -- from now six months back
                  select 
                    dummy, 
                    0 as "@1", 
                    0 as "@2", 
                    0 as "@3", 
                    0 as "@4",
                    0 as "@5", 
                    0 as "@6"
                  from dual
                
                
                  }';
                
                
                  l_date date;
                
                
                  --
                  function column_header(p_date date, p_index number) return varchar2
                  is
                    l_date date default add_months(p_date, - p_index);
                  begin
                    return to_char(l_date, 'dd-Mon-yyyy');
                  end;
                
                
                begin
                
                
                  l_date := trunc( sysdate, 'month' );
                
                
                  l_sql := replace(replace(replace(replace(replace(replace(l_sql,
                    '@1', column_header(l_date, 0) ),
                    '@2', column_header(l_date, 1) ),
                    '@3', column_header(l_date, 2) ),
                    '@4', column_header(l_date, 3) ),
                    '@5', column_header(l_date, 4) ),
                    '@6', column_header(l_date, 5) );
                
                
                  -- debug
                  dbms_output.put_line( l_sql );
                
                
                end;
                /
                
                
                
                
                
                
                  -- This is dynamic SQL template that has
                  -- column headings generated dynamically
                  -- from now six months back
                  select 
                    dummy, 
                    0 as "01-Jun-2016", 
                    0 as "01-May-2016", 
                    0 as "01-Apr-2016", 
                    0 as "01-Mar-2016",
                    0 as "01-Feb-2016", 
                    0 as "01-Jan-2016"
                  from dual
                
                
                • 5. Re: Create Column Alias using a Function
                  3245903

                  I think that makes sense - that table/column names and aliases are defined prior to running any actual queries, so you cannot use a query or function to define them?

                   

                  So if I'm understanding you, I need a preliminary query that I then union to the second (primary) query, effectively resulting in a single output with one row that is my column headings. I had a coworker suggest that, and it does work, but it causes me an additional problem - the results of this query wind up in the middle of the results of the primary query. In other words, I now have a sorting issue, where my 'column headings' are in the middle of my results instead of at the top.
                  This query now looks like:

                  SELECT 'MONTH' AS AGREETYP

                  ,TO_CHAR((CURRENT_DATE-EXTRACT(DAY FROM CURRENT_DATE)+1),'MM') AS MONTH_1    

                  ,TO_CHAR((SELECT TRUNC(ADD_MONTHS(SYSDATE, -1), 'MM') FROM DUAL),'MM') AS MONTH_2

                  FROM DUAL

                  UNION

                  SELECT MONTH1.AGREETYP, TO_CHAR(MONTH1.CURR) AS "1_MON_PREV"

                    ,TO_CHAR(MONTH2.CURR) AS "2_MON_PREV"

                  FROM [month1 code],[month2 code]

                  • 6. Re: Create Column Alias using a Function

                    I think that makes sense - that table/column names and aliases are defined prior to running any actual queries, so you cannot use a query or function to define them?

                    Did you read what I told you earlier?

                    A result set does NOT have column headings.

                    If you want column headings then write a query to produce the headings you want and use that.

                     

                    I had a coworker suggest that, and it does work, but it causes me an additional problem - the results of this query wind up in the middle of the results of the primary query. In other words, I now have a sorting issue, where my 'column headings' are in the middle of my results instead of at the top.

                    You MUST use an ORDER BY in Oracle if you want results in a particular order.

                     

                    So add '1' myOrderColumn to the header query and '2' myOrderColumn to the second query and then use

                    ORDER BY myOrderColumn on a SELECT wrapper of the UNION ALL (don't use UNION).

                     

                    Your result set will now have STRING for every column datatype.

                     

                    That is why reporting tools DO NOT use queries to generate column headers and DO NOT use 'unions' to add the column headers. They use Java/JDBC or c/oci and just write the column headers to the file first.

                    1 person found this helpful
                    • 7. Re: Create Column Alias using a Function
                      Frank Kulash

                      Hi,

                      3245903 wrote:

                       

                      I think that makes sense - that table/column names and aliases are defined prior to running any actual queries, so you cannot use a query or function to define them?

                      Close.  When you run a query (or any SQL statement), the entire statement is compiled first, then later, if there are no compilation errors, it starts trying to produce output.  The compiler needs to know the table- and column names, so you cannot use output from the later stage of the process as part of the earlier (compilation) stage of the same query.  Dynamic SQL can use queries and functions, because execution of the preliminary query will be completed before compilation of the main query (which is a separate statement) begins.

                      So if I'm understanding you, I need a preliminary query that I then union to the second (primary) query, effectively resulting in a single output with one row that is my column headings. I had a coworker suggest that,

                      Actually, no; that's a completely different technique for getting similar output.  That's static SQL, not dynamic SQL.  The different branches of the UNION are part of the same SQL statement.  The entire statement has to be compiled before ny part of it can produce any output.

                      In the query you posted, you still have static column names, MONTH_1 and MONTH_2.  Using that technique, you just ignore the actual column names, if you even see them.  Usually, people using that technique, if they have a front end that normally produces column headers, tell the front end not to display the headers at all

                        and it does work, but it causes me an additional problem - the results of this query wind up in the middle of the results of the primary query. In other words, I now have a sorting issue, where my 'column headings' are in the middle of my results instead of at the top.
                      This query now looks like:

                      SELECT 'MONTH' AS AGREETYP

                      ,TO_CHAR((CURRENT_DATE-EXTRACT(DAY FROM CURRENT_DATE)+1),'MM') AS MONTH_1

                      ,TO_CHAR((SELECT TRUNC(ADD_MONTHS(SYSDATE, -1), 'MM') FROM DUAL),'MM') AS MONTH_2

                      FROM DUAL

                      UNION

                      SELECT MONTH1.AGREETYP, TO_CHAR(MONTH1.CURR) AS "1_MON_PREV"

                        ,TO_CHAR(MONTH2.CURR) AS "2_MON_PREV"

                      FROM [month1 code],[month2 code]

                      Naturally, if you don't have an ORDER BY clause, there's no telling in what order the rows will be displayed.  If you care in what order the rows are displayed, then you have to use an ORDER BY clause.  You can include a literal (I used the 1-character string ' ' below) in the header branch of the UNION, and a different literal (I used the 2-character string '  ') in the body branch, and then sort by that column.  One way to do that is:

                      SELECT 'MONTH' AS AGREETYP

                      ,TO_CHAR((CURRENT_DATE-EXTRACT(DAY FROM CURRENT_DATE)+1),'MM') AS MONTH_1   

                      ,TO_CHAR((SELECT TRUNC(ADD_MONTHS(SYSDATE, -1), 'MM') FROM DUAL),'MM') AS MONTH_2

                      ,' ' AS SORT_1

                      FROM DUAL

                      UNION

                      SELECT MONTH1.AGREETYP, TO_CHAR(MONTH1.CURR) AS "1_MON_PREV"

                        ,TO_CHAR(MONTH2.CURR) AS "2_MON_PREV"

                        ,'  ' AS SORT_1

                      FROM [month1 code],[month2 code]

                      ORDER BY  SORT_1

                      ;

                      By the way, you're using date arithmetic, EXTRACT, TRUNC and a scalar sub-query to get the header row, but none of that is necessary, or even helpful.  It will be simpler and more efficient to do it like this:

                      SELECT  'MONTH'                                        AS AGREETYP

                      ,       TO_CHAR (            CURRENT_DATE,      'MM')  AS MONTH_1

                      ,       TO_CHAR (ADD_MONTHS (SYSDATE,      -1), 'MM')  AS MONTH_2

                      ,       ' '                                            AS SORT_1

                      FROM    DUAL

                      Why are you using CURRENT_DATE for MONTH_1, but SYSDATE for MONTH_2?  Why not use the same function for both columns?

                      1 person found this helpful
                      • 8. Re: Create Column Alias using a Function
                        Barbara Boehmer

                        I have provided the following demonstration with examples to help clarify some of what has been discussed here.

                         

                        I gather that you have something like the following simplified query and  you want to automate the hard-coded column headers of "MAY", "APR", "MAR", "FEB", "JAN", and "DEC" for the previous 6 months, based on SYSDATE.

                         

                        SCOTT@orcl_12.1.0.2.0> SELECT MONTH1.AGREETYP,

                          2          MONTH1.CURR AS "MAY",

                          3          MONTH2.CURR AS "APR",

                          4          MONTH3.CURR AS "MAR",

                          5          MONTH4.CURR AS "FEB",

                          6          MONTH5.CURR AS "JAN",

                          7          MONTH6.CURR AS "DEC"

                          8  FROM  (SELECT 'TYP1' agreetyp, 'May data' curr FROM DUAL) month1,

                          9        (SELECT 'TYP1' agreetyp, 'April data' curr FROM DUAL) month2,

                        10        (SELECT 'TYP1' agreetyp, 'March data' curr FROM DUAL) month3,

                        11        (SELECT 'TYP1' agreetyp, 'February data' curr FROM DUAL) month4,

                        12        (SELECT 'TYP1' agreetyp, 'January data' curr FROM DUAL) month5,

                        13        (SELECT 'TYP1' agreetyp, 'December data' curr FROM DUAL) month6

                        14  ORDER BY agreetyp

                        15  /

                         

                        AGRE MAY      APR        MAR        FEB          JAN          DEC

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

                        TYP1 May data April data March data February data January data December data

                         

                        1 row selected.


                        You can use the following method that you mentioned that you had difficulty ordering.  As others have mentioned, you should use UNION ALL and an ordering column.  You can suppress the display of that ordering column using NOPRINT if you like as below, or by selecting from an outer query, after ordering within the inner query.  I have also suppressed the additional heading.

                         

                        SCOTT@orcl_12.1.0.2.0> SET HEADING OFF

                        SCOTT@orcl_12.1.0.2.0> COLUMN myOrderColumn NOPRINT

                        SCOTT@orcl_12.1.0.2.0> SELECT 1 myOrderColumn,

                          2          'MONTH' agreetyp,

                          3          TO_CHAR (ADD_MONTHS (SYSDATE, -1), 'MON'),

                          4          TO_CHAR (ADD_MONTHS (SYSDATE, -2), 'MON'),

                          5          TO_CHAR (ADD_MONTHS (SYSDATE, -3), 'MON'),

                          6          TO_CHAR (ADD_MONTHS (SYSDATE, -4), 'MON'),

                          7          TO_CHAR (ADD_MONTHS (SYSDATE, -5), 'MON'),

                          8          TO_CHAR (ADD_MONTHS (SYSDATE, -6), 'MON')

                          9  FROM    DUAL

                        10  UNION ALL

                        11  SELECT 2 myOrderColumn,

                        12          MONTH1.AGREETYP,

                        13          MONTH1.CURR,

                        14          MONTH2.CURR,

                        15          MONTH3.CURR,

                        16          MONTH4.CURR,

                        17          MONTH5.CURR,

                        18          MONTH6.CURR

                        19  FROM  (SELECT 'TYP1' agreetyp, 'May data' curr FROM DUAL) month1,

                        20        (SELECT 'TYP1' agreetyp, 'April data' curr FROM DUAL) month2,

                        21        (SELECT 'TYP1' agreetyp, 'March data' curr FROM DUAL) month3,

                        22        (SELECT 'TYP1' agreetyp, 'February data' curr FROM DUAL) month4,

                        23        (SELECT 'TYP1' agreetyp, 'January data' curr FROM DUAL) month5,

                        24        (SELECT 'TYP1' agreetyp, 'December data' curr FROM DUAL) month6

                        25  ORDER BY myOrderColumn, agreetyp

                        26  /

                         

                        MONTH MAY          APR          MAR          FEB          JAN          DEC

                        TYP1  May data    April data  March data  February data January data December data

                         

                        2 rows selected.


                        Here is another method that generates the column headers dynamically.

                         

                        SCOTT@orcl_12.1.0.2.0> SET HEADING ON

                        SCOTT@orcl_12.1.0.2.0> VARIABLE g_ref REFCURSOR

                        SCOTT@orcl_12.1.0.2.0> DECLARE

                          2    v_sql    VARCHAR2(32767);

                          3  BEGIN

                          4    v_sql := 'SELECT month1.agreetyp';

                          5    FOR i IN 1..6 LOOP

                          6      v_sql := v_sql || ', month' || i || '.curr "' || TO_CHAR (ADD_MONTHS (SYSDATE, -i), 'MON') || '"';

                          7    END LOOP;

                          8    v_sql := v_sql ||

                          9      ' FROM  (SELECT ''TYP1'' agreetyp, ''May data'' curr FROM DUAL) month1,

                        10        (SELECT ''TYP1'' agreetyp, ''April data'' curr FROM DUAL) month2,

                        11        (SELECT ''TYP1'' agreetyp, ''March data'' curr FROM DUAL) month3,

                        12        (SELECT ''TYP1'' agreetyp, ''February data'' curr FROM DUAL) month4,

                        13        (SELECT ''TYP1'' agreetyp, ''January data'' curr FROM DUAL) month5,

                        14        (SELECT ''TYP1'' agreetyp, ''December data'' curr FROM DUAL) month6

                        15        ORDER BY agreetyp';

                        16    OPEN :g_ref FOR v_sql;

                        17  END;

                        18  /

                         

                        PL/SQL procedure successfully completed.

                         

                        SCOTT@orcl_12.1.0.2.0> PRINT g_ref

                         

                        AGRE MAY      APR        MAR        FEB          JAN          DEC

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

                        TYP1 May data April data March data February data January data December data

                         

                        1 row selected.

                        1 person found this helpful
                        • 9. Re: Create Column Alias using a Function
                          BluShadow

                          Here's a community document that details how cursors work and why the column names (projection) must be known at compilation/parse time of the query, and cannot be simply determined by the data that is fetched...

                           

                          PL/SQL 101 : Cursors and SQL Projection

                           

                          Whilst it can be achieved using "dynamic" query techniques, those are generally a last resort and should be avoided.

                          Such requirements to change column headings are part of the presentation layer of application development, and are best achieved by reporting tools (e.g. Business Objects or suchlike) that can query the data first, and then layout the data (pivot it or whatever) and give it headings based on the data itself.  It's not a job for SQL which is optimal for querying the data.

                          1 person found this helpful
                          • 10. Re: Create Column Alias using a Function
                            Billy~Verreynne

                            You are grossly mistaken in thinking that column labels need to be dynamically handled by the server (SQL cursor) side.

                             

                            Client-server architecture does not work that way.

                             

                            Whatever labeling used when rendering the cursor's output, is a client issue. The client is responsible for rendering, formatting, and labeling.

                             

                            Do yourself a favour, read up on what client-server is, how it works, where the boundaries are, and look at Oracle APEX's report region (for a web page) on how to deal with formatting and column labeling issues in the real world.

                            • 11. Re: Create Column Alias using a Function
                              3245903

                              Well, I don't know anything about Dynamic SQL, and don't know if you can use it in Developer which is all I have access to that will give me the data I need, so I think I've used static SQL (but again really don't know). Thanks all for the help, here is what I have come up with using suggestions from several of you. I've shortened it to 2 months for brevity. I basically just turned the whole query into a sub-select, did select * from and then used a case statement in the order by clause. I have no idea if this is the cleanest way to do it, but the result is that the top row gives me the month in MM format. I'll probably use a case statement there to get the month name later, but for now this will work for my purpose.

                              SELECT * FROM

                              (SELECT 'MONTH' AS AGREETYP
                              ,TO_CHAR(ADD_MONTHS(SYSDATE,-1),'MM') AS MONTH_1    
                              ,TO_CHAR(ADD_MONTHS(SYSDATE,-2),'MM') AS MONTH_2
                              FROM DUAL
                              UNION ALL
                              SELECT MONTH1.AGREETYP
                                ,TO_CHAR(MONTH1.CURR) AS "1_MON_PREV"
                                ,TO_CHAR(MONTH2.CURR) AS "2_MON_PREV"

                              FROM [month1 code],[month2 code]

                              WHERE MONTH1.AGREETYP = MONTH2.AGREETYP)

                              ORDER BY CASE WHEN AGREETYP = 'MONTH' THEN 1

                                       WHEN AGREETYP = 'PLAT' THEN 2

                                       WHEN AGREETYP = 'VERT' THEN 3

                                       WHEN AGREETYP = 'DEBREW' THEN 4

                              • 12. Re: Create Column Alias using a Function
                                3245903

                                Sounds like I need to learn about Dynamic SQL and whether it can be used in Developer, but I suspect I need to learn more about SQL in general before I could understand that. Gotta walk before I run.

                                As for the sort, I tried a couple of different methods to get the sort and was annoyed that I needed a sort column. I'm an Excel junkie personally and have basically learned that if you want to do something a certain way there is probably a way to do it, you just need to figure it out! Anyway I did find a way around it as you can see from my final code, though it may not be the best solution it does display the way I wanted it to. I did use some of your suggestions to clean up the code.

                                 

                                By the way, you're using date arithmetic, EXTRACT, TRUNC and a scalar sub-query to get the header row, but none of that is necessary, or even helpful.  It will be simpler and more efficient to do it like this:

                                SELECT  'MONTH'                                        AS AGREETYP

                                ,       TO_CHAR (            CURRENT_DATE,      'MM')  AS MONTH_1

                                ,       TO_CHAR (ADD_MONTHS (SYSDATE,      -1), 'MM')  AS MONTH_2

                                ,       ' '                                            AS SORT_1

                                FROM    DUAL

                                Why are you using CURRENT_DATE for MONTH_1, but SYSDATE for MONTH_2?  Why not use the same function for both columns?

                                Chalk that up to my being a rookie and not really understanding either the functions or their syntax. Date functions in particular lately have been challenging because so much of what I do depends on a date range, most of the time a trending range, and I can't stand the idea of having to hard code my query every time it needs to be updated. Again, that's the Excel junkie in me talking, because I can do it easily there, but I also have considerably more experience in that system.

                                • 13. Re: Re: Create Column Alias using a Function
                                  Barbara Boehmer

                                  To get the three-letter abbreviation for the month instead of the number of the month, just use MON in your date format instead of MM, as shown below.  Also, when using UNION ALL, only the column names from the top select will show and the rest are ignored.  So, if you want your "1_MONTH_PREV" to show instead of "MONTH_1", then you need to put that in the top select of  your queries joined with UNION ALL, as shown below.  Other than that, it looks you have done a good job of using static SQL with the tools you have.  I have just substituted simple selects for your [month1 code] and [month2 code] for demonstration purposes below.

                                   

                                   

                                  SCOTT@orcl_12.1.0.2.0> SELECT *

                                    2  FROM  (SELECT 'MONTH' AS AGREETYP

                                    3            , TO_CHAR(ADD_MONTHS(SYSDATE,-1),'MON') AS "1_MONTH_PREV"

                                    4            , TO_CHAR(ADD_MONTHS(SYSDATE,-2),'MON') AS "2_MONTH_PREV"

                                    5          FROM  DUAL

                                    6          UNION ALL

                                    7          SELECT MONTH1.AGREETYP

                                    8            , TO_CHAR(MONTH1.CURR)

                                    9            , TO_CHAR(MONTH2.CURR)

                                  10          FROM  (SELECT 'PLAT' agreetyp, 'May data' curr FROM DUAL) month1

                                  11            , (SELECT 'PLAT' agreetyp, 'April data' curr FROM DUAL) month2

                                  12          WHERE MONTH1.AGREETYP = MONTH2.AGREETYP)

                                  13  ORDER  BY CASE WHEN AGREETYP = 'MONTH'  THEN 1

                                  14              WHEN AGREETYP = 'PLAT'  THEN 2

                                  15              WHEN AGREETYP = 'VERT'  THEN 3

                                  16              WHEN AGREETYP = 'DEBREW' THEN 4

                                  17            END

                                  18  /

                                   

                                  AGREE 1_MONTH_PREV 2_MONTH_PREV

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

                                  MONTH MAY          APR

                                  PLAT  May data    April data

                                   

                                  2 rows selected.

                                   

                                   

                                   

                                  • 14. Re: Create Column Alias using a Function
                                    jihuyao
                                    Also, when using UNION ALL, only the column names from the top select will show and the rest are ignored. 

                                     

                                    That is true and it is done when the query is parsed.  My initial thought on using the view for header/column name is wrong.

                                     

                                    Consider any SQL query must have context in its application and host environment, there should be other options besides dynamic sql to display the header or field name in desirable way, such as returning columns as month1 to month6 and in the forms each mapping field name change to the correct month name when the sheet being activated and opened.  Or simply return 12 columns with Jan to Dec but only populate the column values selectively based on the input range in the where clause, and then use embedded sql in the application code to return only those requested month columns, or load all columns in all fields on the form sheet but only make selected month fields visible with associated trigger/post events.