• JDBC-Query in View using column alias

    By Karsten Kropp 1 decade ago

    I have a JDBC query within a view control. The query contains alias names for the columns (like "select col1 as test from table").

    I want to use this alias as columnName in the xp:viewColumn. But this doesn't work. It is only possible to use the original column name or to use the index of the column. (In my special case I don't have a column name because the value is returnded by a database function)

    • Need some more information

      By Andrejus Chaliapinas 1 decade ago

      Hi,

      While trying your case I've took recently posted by Jack DDL:

      CREATE TABLE [MonthLookup] (
        [month_id] INT IDENTITY(1,1) NOT NULL,
        [month_value] VARCHAR(40) NOT NULL,
        CONSTRAINT [PK_MonthLookup] PRIMARY KEY ([month_id])
      )

       

      and created my JDBC row set this way (here '+' is used as strings concatenation function under MS SQL, for Oracle/MySQL you would use CONCAT() function):

       

                          showDeleted="true" sqlQuery="select month_value+'123' as mv from MonthLookup">
              

       

      and then specified my view column as this:

       

                     
                          
                                                           id="viewColumnHeader3" value="Month value" sortable="true">
                              

                          

                      

       

      That works for me and shows properly values taken from that original month_value field, concatenated with '123' string, via mv alias.

      If you have some DDL/SQL query you are trying to use - could you provide it?

      • More details / tests

        By Karsten Kropp 1 decade ago

        I tested a little bit.

        Here are my findings:

        My SQL statement: "select dummy as col1, dummy || '' as col2, 1 as col3 from sysibm.dual" (I use a DB2 database).

        My view contains all 3 columns referenced by using col1, col2 and col3. The first column keeps empty. So I found out that the column alias works only when the value is fixed (like col3) or the value is computed (like col2). Another possiblity which works is to use a case statement (like "case 1 = 2 then dummy else '' end as col4").

        If you have further questions please let me know.

         

        Here is the screenshot:

         

        Here is the relevant XPage-Code:




        dummy || '' as col2,
        1 as col3
        from sysibm.dual]]>




        id="viewColumnHeader28">



        id="viewColumnHeader1">



        id="viewColumnHeader2">


         

        • What is the use case for dummy?

          By Andrejus Chaliapinas 1 decade ago

          I'm able to reproduce that sample with DB2, but would like to know what is the general use case for you instead of using that dummy? If that is for calling some stored procedure instead - could you send your syntax?

          • UDF

            By Karsten Kropp 1 decade ago

            The problem was caused by using an user-defined database function. ("select my_function(parm1, param2) as colname")

            But, I think this should work also in my exapmple situation. Another szenario is when you select different columns from different tables which have the same column name (I haven't tried this, but this may be a possible situation).

            • My result is different

              By Andrejus Chaliapinas 1 decade ago

              I have this DB2 UDF defined:

              CREATE FUNCTION  my_function1 (param1 VARCHAR(100))
                RETURNS VARCHAR(100)
                RETURN 'Hello ' || param1

               

              Now in my XPage I have this:

                  
                          sqlQuery="select my_function1('test') as col1 from sysibm.dual"
                    var="jdbcData1">
                   

                  

                  
                          id="viewColumnHeader1">    
                  

               

              And result of this:

              • Another example

                By Karsten Kropp 1 decade ago

                Your code works. But the following function doesn't work:

                CREATE FUNCTION  my_function2 (param1 INTEGER)
                 RETURNS integer
                DETERMINISTIC
                LANGUAGE SQL
                READS SQL DATA
                BEGIN ATOMIC
                 RETURN (param1+1);
                 END.

                SQL-Statement: select my_function2(1) as col1 from sysibm.dual

                 

    • Please send a sample page

      By Philippe Riand 1 decade ago

      Working on top of the XPagesJDBC demo DB. The runtime binds using the column names returned by the JDBC meta data.

      • More Details

        By Markus W 1 decade ago

        Okay Philippe i tryed with Derby on the JDBCXPages database. There it works.

        But for DB2 Karsten already posted our code example which is not working.




        dummy || '' as col2,
        1 as col3
        from sysibm.dual]]>




        id="viewColumnHeader28">



        id="viewColumnHeader1">



        id="viewColumnHeader2">


        • Fix delivered,will be in next build

          By Andrejus Chaliapinas 1 decade ago

          With this fix these DB2 related situations will work correctly:

          sqlQuery="select dummy as col1, dummy || '' as col2, 1 as col3 from sysibm.dual"
          var="jdbcData1">


          as well as this (returns integer as UDF result) from Karsten's previous sample:

          sqlQuery="select my_function2(1) as col1 from sysibm.dual"
          var="jdbcData1">