Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

URGENT! Working with SQLLE. Can't convert character string '-100' to negative for SQL output.

Posted on 2004-11-15
11
Medium Priority
?
355 Views
Last Modified: 2012-08-13

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  
0
Comment
Question by:pipster1
  • 5
  • 4
  • 2
11 Comments
 
LVL 16

Expert Comment

by:theo kouwenhoven
ID: 12591341
Hi Pipster1,

Have you tried to change the CSV file to a PF with the command CPYFRMIMPF (Copy From Import File)

Regards,
Murph

0
 
LVL 14

Expert Comment

by:daveslater
ID: 12591691
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
0
 

Author Comment

by:pipster1
ID: 12592935
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
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
LVL 14

Accepted Solution

by:
daveslater earned 750 total points
ID: 12593866
Hi
I think it is in the todec procedure
chage the last bit to this

C** test for -ve                                          
c                   eval      i=%scan('-':var)            
c                   if        i<>0                        
c                   eval      %subst(var:I:1)=' '          
c                   endif                                  
c                                                          
c                   MOVEL     CHAR10        NBR            
c                   MOVE      CHAR5         NBR            
c                                                          
c                   if         I<>0                        
c                   z-sub     nbr           nbr            
c                   endif                                  
C                                                          
C                   RETURN    NBR                          


dave
0
 

Author Comment

by:pipster1
ID: 12596872
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
0
 
LVL 14

Expert Comment

by:daveslater
ID: 12597589
Hi try
if            %subst(Nbr:1:1)='-'
eval         nbr=%subsr(nbr: 2) + '-'
endif


before the SQL

Dave
0
 

Author Comment

by:pipster1
ID: 12597631
Will do.

Pipster1
0
 
LVL 16

Assisted Solution

by:theo kouwenhoven
theo kouwenhoven earned 750 total points
ID: 12606960
I didn't use tham my self, but isn't the move zone suff the things Pipster1 is looking for:

“MHHZO (Move High to High Zone)”
“MHLZO (Move High to Low Zone)”
“MLHZO (Move Low to High Zone)”
“MLLZO (Move Low to Low Zone)”

If not, maybe it's nice to explain.
0
 

Author Comment

by:pipster1
ID: 12607110
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.
0
 

Author Comment

by:pipster1
ID: 12607126
How do I split points among respondents?
0
 
LVL 14

Expert Comment

by:daveslater
ID: 12608497
Hi
just above where you typethese comments there is a "Split Points" link

Dave
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Choosing the right mix of apps is very much necessary for CPAs for making the most of the latest technology through which they can boost their growth.
Often, the users face difficulty in accessing Outlook 2016 PST files on Windows 10 computer. One of the reasons behind it is the improper functioning of MS Outlook when the user tries to open it. MS Outlook suddenly stops working, or it will not op…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Kernel Data Recovery is a renowned Data Recovery solution provider which offers wide range of softwares for both enterprise and home users with its cost-effective solutions. Let's have a quick overview of the journey and data recovery tools range he…
Suggested Courses
Course of the Month15 days, 9 hours left to enroll

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question