IBM i Access Client Solutions

 View Only

  • 1.  issue with uploading results from Run SQL Scripts

    Posted Fri July 28, 2023 10:45 AM
    Edited by David Strawn Fri July 28, 2023 11:19 AM
      |   view attached

    on upload option from ACS Run SQL Scripts, the database file is keeping the double quotes.  I don't want the underscore in column names because I am trying to match headings from a 3rd import requirement...

    see attachment...

    also, after 19 characters in the column name the upload process inserts a blank in the column name...

    ------------------------------
    David Strawn
    ------------------------------

    Attachment(s)

    docx
    ACS Screen Shot.docx   211 KB 1 version


  • 2.  RE: issue with uploading results from Run SQL Scripts

    IBM Champion
    Posted Fri July 28, 2023 08:26 PM
    Edited by Satid Singkorapoom Sun July 30, 2023 08:30 AM

    Dear David

    The DSPFFD screen you provided shows that " is included in the heading text but you can change the heading to whatever text you want (with or without " or underscore) by using SQL statement LABEL ON COLUMN. Samples are provided here : https://www.ibm.com/docs/en/i/7.4?topic=language-creating-descriptive-labels-using-label-statement

    >>>> also, after 19 characters in the column name the upload process inserts a blank in the column name.. <<<<

    The provided format of a column heading is 3 lines of 20 characters each.  This means the first 20 characters (including blank spaces) of the heading text belong to the first line to be displayed, then the next 20 characters each for 2nd and 3rd line.  Samples here:

     LABEL ON COLUMN CORPDATA.EMPLOYEE.EMPNO IS  'Employee            Number'

    LABEL ON SAMPLECOLL.INVENTORY_LIST
    (ITEM_NUMBER       IS 'ITEM                NUMBER ',
    ITEM_NAME           IS 'ITEM                NAME ',
    UNIT_COST           IS 'UNIT                COST ',
    QUANTITY_ON_HAND   IS 'QUANTITY            ON HAND ',
    LAST_ORDER_DATE     IS 'LAST                ORDER               DATE ',
    ORDER_QUANTITY     IS 'NUMBER              ORDERED ')


    ------------------------------
    Education is not the learning of facts but the training of the mind to think. -- Albert Einstein.
    ------------------------------
    Satid S.
    ------------------------------

    Original Message


  • 3.  RE: issue with uploading results from Run SQL Scripts

    Posted Wed August 09, 2023 11:18 AM

    to my knowledge you can't use label on column with a select statement.  i am trying to upload the results of a select statement to the ibm i



    ------------------------------
    David Strawn
    ------------------------------

    Original Message


  • 4.  RE: issue with uploading results from Run SQL Scripts

    IBM Champion
    Posted Thu August 10, 2023 01:16 AM
    Edited by Satid Singkorapoom Thu August 10, 2023 01:16 AM

    I never said  that you needed to use LABEL ON ... with SELECT.  In fact, I said you run SELECT after LABEL ON ..  in one of my posts in this thread.    

    To address your original question, you have 2 choices :

    1) Use SELECT ..... AS (Alias) to rename each column name to what you wanted in your original question.   Or

    2) Use the column label (Run LABEL ON COLUMN ...  before running SELECT) and the choice to display column label (instead of column name) provided in Run SQL Script tool as I described in this thread. 



    ------------------------------
    Education is not the learning of facts but the training of the mind to think. -- Albert Einstein.
    ------------------------------
    Satid S.
    ------------------------------

    Original Message


  • 5.  RE: issue with uploading results from Run SQL Scripts

    Posted Thu August 10, 2023 11:20 AM

    i am using select ...  as (see attachment above).  i believe there is a bug in the upload option under file in the results tab...



    ------------------------------
    David Strawn
    ------------------------------

    Original Message


  • 6.  RE: issue with uploading results from Run SQL Scripts

    IBM Champion
    Posted Sun July 30, 2023 08:03 AM
    Edited by Satid Singkorapoom Sun July 30, 2023 08:17 AM

    I forgot to tell you that, to see Column Heading instead of the column name in Run SQL Script tool and when you export your result set into a spreadsheet file, you use Run SQL Script's main menu Edit --> Preferences --> Results tab and set the Column headings drop-down box to LABEL and click OK.   Then when you run SELECT after you run LABEL ON COLUMN, specify the column names you want without a need to use "as".


    ------------------------------
    Education is not the learning of facts but the training of the mind to think. -- Albert Einstein.
    ------------------------------
    Satid S.
    ------------------------------

    Original Message


  • 7.  RE: issue with uploading results from Run SQL Scripts

    Posted Wed August 09, 2023 11:19 AM

    I have name selected, not label...



    ------------------------------
    David Strawn
    ------------------------------

    Original Message