Hi all,
Our customer Bata facing an issue on Leading zeroes being truncated for packed decimal variable when view tables using Relativity Designer.
For example :
Variable Define in COBOL as :
- 01 WS-NUM PICTURE 9(7) COMP-3.
When Create Table in Relativity , the column defined as :
The value from data file is '0001234' but when view using the relativity designer the value shown as '1234'.
The result that customer required is '0001234' when viewing using Designer.
I have try the redefinition option in Relativity Designer but its not giving me the correct value. Is the any way to define with relativity to avoid the truncation? Can help to provide me steps? Thanks all.
#RELATIVITYGROUPS#RelativityWhen considered as a numeric value, the two are identical. So, should I conclude that the customer wants a character string (i.e. SQL CHAR) rather than a SQL numeric data type?
Have you tried changing the column's data type (on the table form) to Char? Integer is the default used by the Designer because virtually all users want that behavior.
Hi all,
Our customer Bata facing an issue on Leading zeroes being truncated for packed decimal variable when view tables using Relativity Designer.
For example :
Variable Define in COBOL as :
- 01 WS-NUM PICTURE 9(7) COMP-3.
When Create Table in Relativity , the column defined as :
The value from data file is '0001234' but when view using the relativity designer the value shown as '1234'.
The result that customer required is '0001234' when viewing using Designer.
I have try the redefinition option in Relativity Designer but its not giving me the correct value. Is the any way to define with relativity to avoid the truncation? Can help to provide me steps? Thanks all.
#RELATIVITYGROUPS#RelativityHi Tom,
What the customer wants actually read a packed decimal value without truncating the leading zeroes in relativity designer table.
For example if the variable defined as :
01 ARTICLE PICTURE 9(7) COMP-3. ----> value shown in datafile as compress value. if we read thru cobol
program the value will show as 7 digit inclusive the leading zeroes if
exists. eg ---> 0123456 .
The relativity designer will display as below when import FD :
01 ARTICLE S 9(7) PACKED DECIMAL. -----> the value will be shown as 123456.
What customer wants is the value to be shown as 0123456 because the leading zeroes is required for their operation purpose.
Therefore, I would like to know is there anyway that we can display the value as 0123456 in relativity itself. Customer prefer as numeric but if we only can get the value by changing to char then we will advice the customer accordingly.
Customer don't want to perform lot of workaround in SQL query level since Relativity suppose to be a easy tool to view the data at it is in COBOL datafile.
Much Appreciate for your help.
Hi all,
Our customer Bata facing an issue on Leading zeroes being truncated for packed decimal variable when view tables using Relativity Designer.
For example :
Variable Define in COBOL as :
- 01 WS-NUM PICTURE 9(7) COMP-3.
When Create Table in Relativity , the column defined as :
The value from data file is '0001234' but when view using the relativity designer the value shown as '1234'.
The result that customer required is '0001234' when viewing using Designer.
I have try the redefinition option in Relativity Designer but its not giving me the correct value. Is the any way to define with relativity to avoid the truncation? Can help to provide me steps? Thanks all.
#RELATIVITYGROUPS#RelativityHmmm. There are two issues here. As Tom points out, Relativity has converted the PIC 9(7) COMP-3 to a SQL numeric column. SQL numeric columns do not have a concept of packed decimal, so there really isn't a way to express a PIC 9(7) COMP-3. What you are seeing in the Designer is that the numeric column is being formatted for display, and that routine is being "helpful" by suppressing the leading zeros. However, your ODBC application can format the number anyway it wishes, including with leading zeroes.
The second issue is that, if this was a PIC 9(7) DISPLAY, we could add a new data item to the FD that redefines the PIC 9(7) DISPLAY as a PIC X(7), and then we could create a text column on in, and the leading zeros would still be there. Unfortunately, since this is a COMP-3 (PACKED-DECIMAL) each byte in the data item contains 2 digits (the packed part), and therefore, the bytes don't correspond to numeric characters (as DISPLAY does).
However, here's an idea, although, its a bit of a hack. In the Designer, take one of the PACKED DECIMAL data items, and REDEDINE it, but keep the new item's type as PACK DECIMAL. Then, in this new data item, go to the Transform column and set the Hexadecimal Transform. Then define a column on this new data item. While you are at it, create a column in the table on the original data item. Then show the data in the table. In the column with data from the data item with the hexadecimal transform, you'll see how the data is truly represented in the file, represented as hexadecimal. The resulting column will NOT be numeric. It will be text because a hex digit can be 0 through 9 or A through F. However, because of the way that packed decimal works, this just might work like you want. If there is a combined sign, that's going to prevent this from being a workable solution, but give it a try.
<<Customer don't want to perform lot of workaround in SQL query level since Relativity suppose to be a easy tool to view the data at it is in COBOL datafile.>>
If only you knew how much work goes into fetching the data from the file and converting to a SQL application can access it. However, Relativity can't solve all problems. Once Relativity converts the data to a SQL numeric item and releases it to the ODBC, Relativity is done. It's up to your ODBC application to format the data in the final format that it needs.
Hi all,
Our customer Bata facing an issue on Leading zeroes being truncated for packed decimal variable when view tables using Relativity Designer.
For example :
Variable Define in COBOL as :
- 01 WS-NUM PICTURE 9(7) COMP-3.
When Create Table in Relativity , the column defined as :
The value from data file is '0001234' but when view using the relativity designer the value shown as '1234'.
The result that customer required is '0001234' when viewing using Designer.
I have try the redefinition option in Relativity Designer but its not giving me the correct value. Is the any way to define with relativity to avoid the truncation? Can help to provide me steps? Thanks all.
#RELATIVITYGROUPS#Relativity"Customer don't want to perform lot of workaround in SQL query level since Relativity suppose to be a easy tool to view the data at it is in COBOL datafile."
I can understand that the customer does not want to do workarounds in the SQL query. However, the customer is also requiring something (leading non-significant digits on a SQL numeric datatype) that would not be available (without workarounds) in any standard relational database SQL query.
Also, the second part of your statement ("view the data at it is in COBOL datafile") is not at all what Relativity is about. The application using a Relativity relational database should be totally unaware of the storage mechanism for the data. Does the customer really know, for example, how numeric data are stored in Microsoft SQL Server? The answer is - of course not!
All this leads me to the following conclusion:
This data field represents something other than numeric data (that is, data which might reasonably be involved in arithmetic operations). It is instead character data that is constrained to decimal digits. Several examples come to mind: US ZIP codes, telephone numbers, UPC codes, etc. The fact that the digits are stored in a COMP-3 field probably refers back to the time when 'efficiency' dictated that one would use COMP-3 to save a few bytes in each record. (This notion of efficiency still lives on in the hearts of us antiquated COBOL programmers - but in reality efficiency is rarely measured this way anymore.)
That is my conclusion, based on 25 years of work with Relativity users.
It is for this reason that I suggested changing the SQL data type for the column from Integer (a default supplied by the Designer when the column was added to the table definition) to Char. This change is made in the table definition window containing the column definition. While I cannot conveniently test this right now, I think that the Relativity SQL engine will supply the appropriate converted character string, including leading zeros.
If that doesn't work, and since this is a COMP-3 COBOL data item, one could use the hexadecimal transform, and then use a CREATE VIEW to store a view (query) that uses the LEFT scalar SQL function to grab the leftmost 7 characters. The stored view would be used by the client application instead of the table that contains the column. But this is not for the faint of heart.
Finally
Based on those 25 years of experience mentioned earlier, I can safely guess that the customer might well have some other problem, and has focused on the leading zeros as the one and only solution for the problem. It would be very beneficial if we solve the customer's actual problem rather than this leading zero problem. But to do that, the customer has to be willing to back off the fixation on leading zeros and describe what really needs to be accomplished.
Further, Relativity is not "supposed to be an easy tool..." but rather it is an effective tool. I have met very few programmers (the folks that typically end up using Relativity to provide SQL access to their COBOL data) that can easily give up the notions of how COBOL stores and organizes data, and therefore bridge the gap from COBOL data file organization to relational concepts. If one does not have solid understanding of both COBOL data organization and relational data organization, one can easily 'design' some truly horrible 'tables' that a relational database programmer finds useless.
Hi all,
Our customer Bata facing an issue on Leading zeroes being truncated for packed decimal variable when view tables using Relativity Designer.
For example :
Variable Define in COBOL as :
- 01 WS-NUM PICTURE 9(7) COMP-3.
When Create Table in Relativity , the column defined as :
The value from data file is '0001234' but when view using the relativity designer the value shown as '1234'.
The result that customer required is '0001234' when viewing using Designer.
I have try the redefinition option in Relativity Designer but its not giving me the correct value. Is the any way to define with relativity to avoid the truncation? Can help to provide me steps? Thanks all.
#RELATIVITYGROUPS#RelativityMike, seems like we were replying simultaneously.
Could you test my theory of changing the column's Data type to Char? I don't have access to a simple test capability right now.
Hi all,
Our customer Bata facing an issue on Leading zeroes being truncated for packed decimal variable when view tables using Relativity Designer.
For example :
Variable Define in COBOL as :
- 01 WS-NUM PICTURE 9(7) COMP-3.
When Create Table in Relativity , the column defined as :
The value from data file is '0001234' but when view using the relativity designer the value shown as '1234'.
The result that customer required is '0001234' when viewing using Designer.
I have try the redefinition option in Relativity Designer but its not giving me the correct value. Is the any way to define with relativity to avoid the truncation? Can help to provide me steps? Thanks all.
#RELATIVITYGROUPS#Relativity<<Could you test my theory of changing the column's Data type to Char?>>
I can't try it at this second either.
Hi all,
Our customer Bata facing an issue on Leading zeroes being truncated for packed decimal variable when view tables using Relativity Designer.
For example :
Variable Define in COBOL as :
- 01 WS-NUM PICTURE 9(7) COMP-3.
When Create Table in Relativity , the column defined as :
The value from data file is '0001234' but when view using the relativity designer the value shown as '1234'.
The result that customer required is '0001234' when viewing using Designer.
I have try the redefinition option in Relativity Designer but its not giving me the correct value. Is the any way to define with relativity to avoid the truncation? Can help to provide me steps? Thanks all.
#RELATIVITYGROUPS#RelativityOkay, today I was able to test my theory that one could use Char. That theory is wrong. So, here are the specific steps to obtained the desired leading zeros:
- On the FD form, select WS-NUM, and click the button to add a redefinition. Choose alphanumeric as the type, and give the item a convenient name. For the indicated WS-NUM PIC 9(7) COMP-3, the picture for the redefinition (displayed in the dialog) will be X(4).
- After adding the redefined item, select the Hexadecimal transform for the redefined item.
- Save the changes to the FD.
- Open the table that is defined for the FD.
- Select the redefined item on the FD form, then drag and drop it on the Table form.
- Name the column appropriately.
- Save the changes to the Table form.
- In the SQL query, use the LEFT(columnName,7) scalar function to select the leftmost 7 characters. (COMP-3 has a sign nibble, so you will see the character 'F' as the rightmost, eighth character.)
I hope your customer finds this acceptable. You can use a CREATE VIEW statement to create a view that has the LEFT() function in place; this will allow the ultimate user to use the view (instead of the table) blissfully unaware of the LEFT function workaround.
In anticipation of someone reading this thread that has an even number of digits defined on a COMP-3 and wants this strange behavior, the SQL query will have to use both the LEFT and RIGHT scalar functions to eliminate both the sign nibble on the right and the slack digit nibble on the left.
As a final note - yes, one could apply the hexadecimal transform directly to the COMP-3 item. My extra step of redefinition leaves the original numeric data-item available for use.
