March 07, 2008, 09:03 AM
Tracie JonesUnwanted leading zeros on ALPHA report
When I execute the following code:
JOIN
SHRDGMR.SHRDGMR.SHRDGMR_PIDM IN SHRDGMR TO MULTIPLE
V_NCAT_AS_ACAD_HIST_DETAIL.V_NCAT_AS_ACAD_HIST_DETAIL.PIDM_KEY
IN V_NCAT_AS_ACAD_HIST_DETAIL AS J0
END
DEFINE FILE SHRDGMR
GRADUATION_DATE/YYMD = HDATE(SHRDGMR_GRAD_DATE,'YYMD');
DATE1/A8YYMD = GRADUATION_DATE;
GRAD_DATE/A08 = DATE1;
-*LAST_SHRDGMR_PIDM/I08 = SHRDGMR_PIDM;
END
-*BY SSN
-*PRINT
TABLE FILE SHRDGMR
-*WHERE RECORDLIMIT EQ 10000
PRINT
LAST_NAME
FIRST_NAME
MIDDLE_INITIAL
SHRDGMR_MAJR_CODE_1
GRAD_DATE
GRADUATED_IND
COMPUTE CNTR/I1 = IF SHRDGMR_PIDM EQ LAST SHRDGMR_PIDM THEN CNTR + 1 ELSE 1;
-*BY LAST_NAME NOPRINT
-*BY FIRST_NAME NOPRINT
-*BY MIDDLE_INITIAL NOPRINT
BY SHRDGMR_PIDM NOPRINT
WHERE SHRDGMR_MAJR_CODE_1 EQ '0104' OR '0112' OR '0246';
WHERE GRADUATED_IND EQ 'Y';
WHERE TOTAL CNTR EQ 1;
ON TABLE HOLD AS VINSON
END
-*
TABLE FILE VINSON
PRINT
LAST_NAME
FIRST_NAME
MIDDLE_INITIAL
SHRDGMR_MAJR_CODE_1
GRAD_DATE
GRADUATED_IND
BY LAST_NAME NOPRINT
BY FIRST_NAME NOPRINT
BY MIDDLE_INITIAL NOPRINT
BY SHRDGMR_PIDM NOPRINT
HEADING
""
FOOTING
""
WHERE SHRDGMR_MAJR_CODE_1 EQ '0104' OR '0112' OR '0246';
WHERE GRADUATED_IND EQ 'Y';
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD AS LONNIE FORMAT ALPHA
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
GRID=OFF,
FONT='ARIAL',
SIZE=9,
$
TYPE=TITLE,
STYLE=BOLD,
$
TYPE=TABHEADING,
SIZE=12,
STYLE=BOLD,
$
TYPE=TABFOOTING,
SIZE=12,
STYLE=BOLD,
$
TYPE=HEADING,
SIZE=12,
STYLE=BOLD,
$
TYPE=FOOTING,
SIZE=12,
STYLE=BOLD,
$
TYPE=SUBHEAD,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBFOOT,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBTOTAL,
BACKCOLOR=RGB(210 210 210),
$
TYPE=ACROSSVALUE,
SIZE=9,
$
TYPE=ACROSSTITLE,
STYLE=BOLD,
$
TYPE=GRANDTOTAL,
BACKCOLOR=RGB(210 210 210),
STYLE=BOLD,
$
ENDSTYLE
END
I receive a file that looks like this:
000012Abdus-Salaam 000008Husniyah 000001B024619001231000001Y
000011Abdussalaam 000005Diaab 000001.010420020511000001Y
000009Abukhalaf 000006Bassam 000001A010419850601000001Y
Why does each field have zeros in front of it?
Thanks!
tbj
March 07, 2008, 09:28 AM
FrankDutchTracie
Without seeing the master I guess your fields LAST_NAME FIRST_NAME MIDDLE_INITIAL all are of a format like A*V (a Varchar)
The first 6 digits represent the lengt of the real string.
You see in the report
000012Abdus-Salaam 000008Husniyah 000001B024619001231000001Y
000011Abdussalaam 000005Diaab 000001.010420020511000001Y
000009Abukhalaf 000006Bassam 000001A010419850601000001Y
Abukhalaf=9 characters, Bassam = 6, A=1 and so on.
To prefend this there are some options.
1) change the master form A*V to A* (that's what we do)
2) create a defined field that's an Alpha without the V.
I hope this helps
March 07, 2008, 09:32 AM
GinnyJakesThe field in the master is probably defined with a V format for variable, i.e. A12V. Refresh the master after placing this in your edasprof:
ENGINE rdbms_type SET VARCHAR OFF
where
rdbms_type is for example SQLORA or DB2, whatever your backend source is. You can look at the suffix in the master for this information.
Using this command will remove the V's from your master and you won't get the numbers.
March 10, 2008, 02:34 AM
<Shyam L>Did you try using
SET HOLDLIST = PRINTONLY.
That might help.
Or other solution is negating Varchar as already mentioned above.
March 10, 2008, 02:43 PM
Darin LeeGRRRR on the varchar stuff. It has caused me so many headaches!! Joins, flat file extracts, etc., etc. etc. We've just turned them all of as Ginny suggests and stick with An instead of AnV.