SQL - Bit off more than I can chew INPUT requested - IBM AS400

This is a discussion on SQL - Bit off more than I can chew INPUT requested - IBM AS400 ; I have an SQL delima. We allow the users on a DDS screen to select 1 to 9 fields as Group By for summing purposes. The select statement is built dynamically based on their choices which means the fields are ...

+ Reply to Thread
Results 1 to 6 of 6

Thread: SQL - Bit off more than I can chew INPUT requested

  1. SQL - Bit off more than I can chew INPUT requested

    I have an SQL delima. We allow the users on a DDS screen to select 1
    to 9 fields as Group By for summing purposes. The select statement is
    built dynamically based on their choices which means the fields are
    not always in the same order, and here exists my problem. I created a
    Data Structure ALLDAT with a field defined as 1 - 259 Alpha (because
    numeric and alpha data would be put in this field by the FETCH). It
    bombed on me because my SELECT had 4 columns. So I redined the DS
    ALLDAT to be: Alldue 1 - 9(0), Allwag 10 - 18(0), Allduwg 19 - 27(0)
    and Allss 28 - 36(alpha). I did alpha because we could have alpha or
    numeric data coming into this last field. The FETCH bombed because it
    performs EVAL's to put the retrieved data in my DS fields and you
    guessed it the fileds need to be of the same type for the EVAL. Is
    there a way to change my SQL retrieved fields/columns into alpha so I
    can place them in the ALLDAT DS? Actually I need 9 alpha fields
    because they select 1 to many of the 9 fields/columns for summing. We
    had planned to %SUBST to get our values from the ALLDAT DS. I am such
    an SQL novice but a job is a job and I have been asked to do this.
    Can the above be done? If it can be done how can I do it? We're
    about ready to inform our client that the dynamic selection ability we
    are trying to give cannot be performed as I have spent too much time
    trying to figure this out. I am developing in V5R1M0 and the
    production machine is V5R3M0.

    TIA
    that 1 guy


  2. Re: SQL - Bit off more than I can chew INPUT requested

    I am not sure I understand the question but that didn't stop me
    before

    You can cast almost any data type to alpha in SQL, just wrap that
    column in CHAR(myNumericColumn).
    Or use "official" casting keyword: CAST(myNumericColumn as CHAR(18))

    Also, you can substring in SQL as well, i.e.:

    SUBSTR(CHAR(myNumericColumn),1,10)

    HTH

    On Feb 21, 10:55 am, "that 1 guy" wrote:
    > I have an SQL delima. We allow the users on a DDS screen to select 1
    > to 9 fields as Group By for summing purposes. The select statement is
    > built dynamically based on their choices which means the fields are
    > not always in the same order, and here exists my problem. I created a
    > Data Structure ALLDAT with a field defined as 1 - 259 Alpha (because
    > numeric and alpha data would be put in this field by the FETCH). It
    > bombed on me because my SELECT had 4 columns. So I redined the DS
    > ALLDAT to be: Alldue 1 - 9(0), Allwag 10 - 18(0), Allduwg 19 - 27(0)
    > and Allss 28 - 36(alpha). I did alpha because we could have alpha or
    > numeric data coming into this last field. The FETCH bombed because it
    > performs EVAL's to put the retrieved data in my DS fields and you
    > guessed it the fileds need to be of the same type for the EVAL. Is
    > there a way to change my SQL retrieved fields/columns into alpha so I
    > can place them in the ALLDAT DS? Actually I need 9 alpha fields
    > because they select 1 to many of the 9 fields/columns for summing. We
    > had planned to %SUBST to get our values from the ALLDAT DS. I am such
    > an SQL novice but a job is a job and I have been asked to do this.
    > Can the above be done? If it can be done how can I do it? We're
    > about ready to inform our client that the dynamic selection ability we
    > are trying to give cannot be performed as I have spent too much time
    > trying to figure this out. I am developing in V5R1M0 and the
    > production machine is V5R3M0.
    >
    > TIA
    > that 1 guy




  3. Re: SQL - Bit off more than I can chew INPUT requested

    that 1 guy wrote:
    > I have an SQL delima. We allow the users on a DDS screen to select 1
    > to 9 fields as Group By for summing purposes. The select statement is
    > built dynamically based on their choices which means the fields are
    > not always in the same order, and here exists my problem. I created a
    > Data Structure ALLDAT with a field defined as 1 - 259 Alpha (because
    > numeric and alpha data would be put in this field by the FETCH). It
    > bombed on me because my SELECT had 4 columns. So I redined the DS
    > ALLDAT to be: Alldue 1 - 9(0), Allwag 10 - 18(0), Allduwg 19 - 27(0)
    > and Allss 28 - 36(alpha). I did alpha because we could have alpha or
    > numeric data coming into this last field. The FETCH bombed because it
    > performs EVAL's to put the retrieved data in my DS fields and you
    > guessed it the fileds need to be of the same type for the EVAL. Is
    > there a way to change my SQL retrieved fields/columns into alpha so I
    > can place them in the ALLDAT DS? Actually I need 9 alpha fields
    > because they select 1 to many of the 9 fields/columns for summing. We
    > had planned to %SUBST to get our values from the ALLDAT DS. I am such
    > an SQL novice but a job is a job and I have been asked to do this.
    > Can the above be done? If it can be done how can I do it? We're
    > about ready to inform our client that the dynamic selection ability we
    > are trying to give cannot be performed as I have spent too much time
    > trying to figure this out. I am developing in V5R1M0 and the
    > production machine is V5R3M0.


    There must be some logic in the program to determine if
    the result column that is going to be loaded into the
    last field of the DS is numeric or character. If it's
    character, you don't need to do anything. If it's
    numeric, you need to convert the result column into
    character data. Depending on your need for consistent
    data length, you could use the CHAR or DIGITS built-in
    SQL function. DIGITS preserves leading zeros; CHAR
    suppresses them. If there are decimal digits to the
    result column in the SELECT statement, you'll have to
    find a way to handle those in such a way that you know
    where the decimal point is supposed to go.

  4. Re: SQL - Bit off more than I can chew INPUT requested

    Ok, thanks for the input. Looks like I was starting down the wrong
    path. I had started looking into CREATE TABLE, ALTER TABLE/ALTER
    COLUMN. I am such an SQL Noob. Thanks for the Ideas Elvis and
    Jonathon. From what you have replied with I belive that it will solve
    my woes. I can definately figure out what the field is going to be
    and when building the SELECT I can use CHAR or CAST. Thank you once
    again.


    that 1 guy


  5. Re: SQL - Bit off more than I can chew INPUT requested

    What you need is a dynamic SQL with a variable select list.
    This is not easy to resolve and a little too complex to describe it in
    a few words. Even though I'll try to list the necessary steps:

    1. Your have to work with a descriptor area, which is a data
    structrure where the information about the fields returned will be
    stored, i.e. data type, length and address where the field will be
    returned. This descriptor area must be included by using the SQL-
    Command INCLUDE SQLCA.
    C/EXEC SQL INCLUDE SQLCA
    C/END-EXEC

    2. This SQLCA must be designed to your dynamic SQL statement in either
    the PREPARE-Statement or in a separate DESCRIBE-Statement.

    C/Exec SQL Prepare DynC1 From :String
    C/End-Exec
    C/Exec SQL Describe DynC1 into :SQLDA using System Names
    C/End-Exec

    3. If you know the fields that can be returned, you have to define
    workfields for them in either a data structure or as stand alone
    fields. After the Describe-statement you can designe the addresses for
    the returned fields described in the SQLCA to your WorkFields.
    Example:

    For Index = 1 to SQL_NUM;
    SQLVAR = SQL_VAR(Index);
    Select;
    When SQLName = 'RTNFLD1';
    SQLData = %Addr(WRKFLD1);
    When SQLName = 'RTNFLD2';
    SQLData = %Addr(WRKFLD2);
    Other;
    Clear SQLVAR;
    EndSL;
    SQLIND = %Addr(SQLCOD);
    SQL_VAR(Index) = SQLVAR;
    EndFor;

    If you don't know all fields that can be selected and returned it
    is much more complicated, because you have to work with the pointers.

    4. Instead of Fetching the rows into a data structure or stand alone
    fields, you have to Fetch the rows into the Descriptor Area. If it was
    possible to allocate the fields, your Workfields are now filled
    correctly.

    C/EXEC SQL Fetch Next From C1 using Descriptor :SQLDA
    C/End-Exec

    For more information look at: Embedded SQL-Programming
    http://publib.boulder.ibm.com/infoce...ajpkickoff.htm

    Birgitta


  6. Re: SQL - Bit off more than I can chew INPUT requested

    Birgitta,
    Thanks for the input I will discuss this with my boss.


    that 1 guy


+ Reply to Thread