Working-Storage equivalent in SQL?

[Member 310024]

Does SQL have an equivalent of COBOL's Working-Storage Section?
I am trying to do a number of things in SQL, which are are causing me lots of trouble, that in COBOL would be simple.
For example, I have a CHAR(8) field containg a number ie digits in all positions. I want to find the 9's complement of it, and place the result in another CHAR(8) field.
I am having all sorts of trouble doing this.
Using COBOL, you have a number of basic tools at your disposal, that would allow you to apply logic to determine the answer. SQL seems to draw on a different mind set. SQL seems to have pushed the need for logic, into the background.
Instead, it favours 'memory based' people rather than 'logic minded' people. In otherwords, in SQL, you just 'have to know' (ie remember) the function that will do the thing you are wanting done - and if you don't know the function, then no amount of logic being applied with basic tools, will solve your problem. Any comments?

 

[sathyarams]

COBOL and SQL are totally different in what they can achive, or what they 'exist' for ...

COBOL is procedural to enable you to perform complex operations, calculations etc ...

SQL is basically for accessing a relational database ... It is a standard interface to access the data without bothering about how the data is stored internally Eg, DB2 Mainframe stores data in VSAM... You can store and retrieve data from VSAM directly using COBOL but using SQL masks the storage complexities from the user ...

Hence, I feel, you cannot compare SQL with COBOL ...

To get 9's complement, char(99999999 - int(<field1>))
...

HTH

Sathyaram


More DB2 questions answered at

http://www.dbforums.com/f8

 

[Member 310024]

Thanks for the response.
I believe there may be an another issue involved here.
I am using DB2 V5.1. A friend of mine uses another partition on which we have DB2 6.0.
I believe there are differences between the two versions that are impacting on the way in which the SQL statements can be written.
Apart from that, I did say that it was a CHAR(8) field that holds the 8-digit number which I want to get the 9's complement of, and also that the result has to be put into a CHAR(8) as well. On my version of DB2, the INTEGER function must operate on a decimal . It wont operate on a CHAR(8) containing all digits. Also, you can't abbreviate INTEGER to INT on my version. Also, the SUBSTR may have to be used as well.

I have come up with code that 'almost' works.
To simplify things, I will stop one step short of the subtraction from 99999999, and show you that the number I get is missing the 8th digit from the left (ie the rightmost digit) and has a blank at the leftmost position.
I was expecting 20030715 but am getting b2003071 as my result, where b = space.

I am assuming that the table contains a record on which we have fields called ORD_NUM and NC_NUM (among others).
Both are CHAR(8) and at the moment ORD_NUM contains '20030715' and NC_NUM contains spaces.
I want update the record so that NC_NUM contains the 9's complement of ORD_NUM. This is as close as I can get, and I presume there is a sign somehow affecting the result, but I don't know how to get rid of it.

/CODE
UPDATE DB2TBL
SET
NC_NUM =
SUBSTR
(
CHAR
(
DECIMAL
(
ORD_NUM
,8
)
)
,1
,8
)
WHERE PK = 'XYZ';

This almost works.
I get NC_NUM = b2003071, instead of 20030715
If I can get 20030715, the subtraction from 99999999 will be the last simple step.

 

[Member 310024]

For those interested, this is the solution.
So far for not having to know how the data is stored ...

I once read an article entitled &quot;How SQL has missed the boat&quot;, and after this episode, I can say I agree 100%.

UPDATE DB2TBL
SET
NC_DATE =
SUBSTR
(
CHAR
(
99999999 -
DECIMAL
(
ORD_DATE
,8
)
)
,3
,8
)
WHERE PK = 'XYZ';