Unconfigured Ad Widget



No announcement yet.

Very Simple SQL issue

  • Filter
  • Time
  • Show
Clear All
new posts

  • Very Simple SQL issue

    I am new to DB2 on an AS/400 V4R3.

    On mysql this works: SELECT 1 + 1 AS Number

    I've searched for DB2 table synthesis, DB2 Math. Anyways it insists on a FROM clause is there a common dummy table? Clearly I don't know the right terminology or I'd have found the answer by now.

  • #2
    I'm very new with the AS/400... so I need to create the table SYSIBM.SYSDUMMY1.


    • #3
      In order to establish a file, you would use the CREATE TABLE command.

      If you use a SELECT statement elsewhere, you have must be selecting something FROM something else. You could use SELECT when INSERTing data into an existing table.



      • #4
        SYSIBM/SYSDUMMY1 already exists. just use it in the from clause

        SELECT 1 + 1 AS Number from sysibm/sysdummy1

        you can use this table for any other SQL function, such as retrieving date, time, etc.


        • #5
          All right I left out some information because I didn't think it was relevant.

          I am connecting to the database using JTOpen. Using the AS400JDBCDriver.

          When using STRSQL in green screen "SELECT 1 + 1 AS Number from sysibm/sysdummy1" works fine (I know this now because of your advice) however when using JTOpen the syntax is slightly different in that dots are used in place of slashes between the schema and table.

          So I would expect: "SELECT 1 + 1 AS Number from sysibm.sysdummy1 " to produce the same result as the green screen. However the green screen gives the expected result of 2, while JDBC is returning "null".

          I have used JTOpen to display a lot of data thus far this is the first real issue I've had with it.


          • #6
            This database uses separate fields for year, month, day, hour, min...

            So without that ability to create new fields for calculations (because of some issue with my tools) makes my work quite tricky. First things first I'll create a date range function that will return a string with the correct boolean logic for use with the WHERE clause (I was using an Epoch time like calculation before for comparison which required it's own synthesized field). This will address most of my immediate issues.

            After this there are several options but they come down to how do I add Date fields to the database? An old IBM DDS AS/400 book talks about field concatenation to create easily comparable dates. I want a true date data type which is provided by DB2 (of course!) but the concern is that date types are hard to work with in RPG, is this true?

            Also can we create tables which are linked to their parent tables (by primary key) which have DATE data types composed of the date elements we are currently using (year field, month field... ) which will say consistent with their parents?

            If you have any advice even if it is short and cryptic that would be great (I'll research to understand I need the right path to start on).


            • #7
              The issue with your tools inability to create fields, may also include tables, indexes, etc. Generally an ODBC connection is under a default "read-only" access to the database.

              You might need to look into the user profile connecting to the database if you want to perform DDL (data-description-language) SQL statements through an ODBC connection. And many times, only a limited implementation of DDL statements are supported through the ODBC driver. You should be able to "proof" any SQL statements you want to execute in interactive STRSQL session before attempting through an ODBC connection first.

              You can create "date" type fields in your "select-list" for example by creating a VIEW over a parent table with date parts and returning a valid date with the DATE(place your date parts here) function.

              And I don't believe that "date" data types are any more difficult to manage, but it can get somewhat complex dealing with legacy and date data types together which would almost always be the case.

              I recommend a DB2 SQL Reference manual of an appropriate release so you can see the wealth of capabilities there within. Here's a link to V5R4 DB2 SQL Reference. When you get down to statement syntax or problems with...Give us a shout.



              • #8
                Date Fields are easy in RPG IV

                Date fields are extremely easy to use in RPG IV. If you are on V5R3 or later. It is a little harder in earlier releases. In RPG III it is not possible/practical, except by writing callable RPG IV programs.

                Check out these articles:



                Sam (tooting his own horn...)


                • #9
                  Hi Quaternion,
                  In AS400 If you are going to perform Selection Operation with Select than From clause and Table name is must.
                  Last edited by Guest.Visitor; 07-21-2010, 06:45 AM.


                  • #10
                    The IBM i 6.1 release includes support for table-less queries with the VALUES statement. You can just code up: VALUES 1+1