URGENT! Working with SQLLE. Can't convert character string '-100' to negative for SQL output.
Please keep in mind I'm working on V5R1M0.
I'm working with a comma delimited file.
I need to be able to extract numbers and currency that may contain negatives. ('-100')
for example: A field may contain -1257.35 or -3456 between commas.
I need a routine to take any combination (-1257.35 or -3456) and change it to -125735 or -34560 in a field to do an SQL update to field defined in an SQL fetch.
The problem is I can create a negative numeric field, but when the field is moved to a character field
to perform the SQL update, I lose the negative information. Help!
This solution is needed urgently.
Please query me if necessary for more detail if necessary.
I believe daveslater created the original routine back in june using embedded SQL.
Thanks,
Pipster1
Hi
there are 2 solutions that I posted
http:Q_21095755.html
that used a sub procedure and
http:Q_21043876.html
that uses SQL
which is the one that you are useing?
Dave
there are 2 solutions that I posted
http:Q_21095755.html
that used a sub procedure and
http:Q_21043876.html
that uses SQL
which is the one that you are useing?
Dave
ASKER
Dave,
I'm actually using both postings in the original program.
The problem is when this statement executes,
c eval sql='UPDATE CSVORD SET ' + fldname +
c ' = ' + nbr +
c ' where key =' + %editc(key:'Z')
I lose the negative information.
Pipster1
I'm actually using both postings in the original program.
The problem is when this statement executes,
c eval sql='UPDATE CSVORD SET ' + fldname +
c ' = ' + nbr +
c ' where key =' + %editc(key:'Z')
I lose the negative information.
Pipster1
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Dave,
Sorry, the 'nbr' field is character.
The problem I'm having is trying to use a numeric field in the sql string.
The compile throws up with RNF-7416 'The types of the right and left hand side do not match in
the EVAL operation'.
AND
RNF-7421 'Operands are not compatible with the type of operator.'
Thanks,
Pipster1
Sorry, the 'nbr' field is character.
The problem I'm having is trying to use a numeric field in the sql string.
The compile throws up with RNF-7416 'The types of the right and left hand side do not match in
the EVAL operation'.
AND
RNF-7421 'Operands are not compatible with the type of operator.'
Thanks,
Pipster1
Hi try
if %subst(Nbr:1:1)='-'
eval nbr=%subsr(nbr: 2) + '-'
endif
before the SQL
Dave
if %subst(Nbr:1:1)='-'
eval nbr=%subsr(nbr: 2) + '-'
endif
before the SQL
Dave
ASKER
Will do.
Pipster1
Pipster1
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I ended up contacting IBM because the ideas being provided here weren't working out. (no offense anyone)
I tried using the 'UPDATE DB SET FLD001 = :nbr2 where key = 1'
option and the IBM dudes are puzzled why it isn't working. It's
being sent to development for further study.
I'm splitting the points for all respondents. Thanks.
I tried using the 'UPDATE DB SET FLD001 = :nbr2 where key = 1'
option and the IBM dudes are puzzled why it isn't working. It's
being sent to development for further study.
I'm splitting the points for all respondents. Thanks.
ASKER
How do I split points among respondents?
Hi
just above where you typethese comments there is a "Split Points" link
Dave
just above where you typethese comments there is a "Split Points" link
Dave
Have you tried to change the CSV file to a PF with the command CPYFRMIMPF (Copy From Import File)
Regards,
Murph