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

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.

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

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

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


that 1 guy