Padding SQL query to created fixed width text file
Experts: I am stuck on 2 things:
1. How can I make this query control the size of the columns? I need a fixed width export for another system to read in.The loan number needs to be a width of 30, Name of 50
, Phone of 5, Fax of 12, email of 63
2. The fax data needs to be formatted at @@@.@@@.@@@@ with a DOT as a separator.
Many thanks!
SELECT
o.LN_NO,
a.PTY1_NM,
a.PTY_TELEPHONE_NO,
a.PTY_FAX_NO,
a.PTY_EMAIL_ADR_XX
FROM RDX_BDE.T_PARTY a
join RDX_BDE.T_POINT_OF_CONTACT
on o.POC_PROCESSOR_ID = a.PTY_ID
and o.POC_PROCESSOR_ID = a.PTY_ID
WHERE (((a.PTY_TITLE_XX) Like '%HPC%'));
1. How can I make this query control the size of the columns? I need a fixed width export for another system to read in.The loan number needs to be a width of 30, Name of 50
, Phone of 5, Fax of 12, email of 63
2. The fax data needs to be formatted at @@@.@@@.@@@@ with a DOT as a separator.
Many thanks!
SELECT
o.LN_NO,
a.PTY1_NM,
a.PTY_TELEPHONE_NO,
a.PTY_FAX_NO,
a.PTY_EMAIL_ADR_XX
FROM RDX_BDE.T_PARTY a
join RDX_BDE.T_POINT_OF_CONTACT
on o.POC_PROCESSOR_ID = a.PTY_ID
and o.POC_PROCESSOR_ID = a.PTY_ID
WHERE (((a.PTY_TITLE_XX) Like '%HPC%'));
ASKER
Kent,
Thanks for the quick reply. I am getting data shift after the 2nd column (name). Is there a way so that all the rows will be lined up as if a fixed width text file? i will need to export to a fixed width. Thanks!
Thanks for the quick reply. I am getting data shift after the 2nd column (name). Is there a way so that all the rows will be lined up as if a fixed width text file? i will need to export to a fixed width. Thanks!
Hmm.....
Casting to a fixed width type should produce a fixed width result.
Guess we'll have to brute force that one.
left (cast (a.PTY1_NM ||' ' as CHAR (50)), 50) ||
You posted this to both the MS SQL and DB2 zones. The solution should work for either, but there are DBMS specific functions for both that can be used, too.
Kent
Casting to a fixed width type should produce a fixed width result.
Guess we'll have to brute force that one.
left (cast (a.PTY1_NM ||' ' as CHAR (50)), 50) ||
You posted this to both the MS SQL and DB2 zones. The solution should work for either, but there are DBMS specific functions for both that can be used, too.
Kent
What is the data type for PTY_TELEPHONE_NO? If it's numeric, you may need to use DIGITS( PTY_TELEPHONE_NO ) in order to avoid 'data shift'.
Tom
Tom
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
NO POINTS FOR ME, I'm NOT the one who's been helping you, I was just reading q and noticed that concat operator was omitted after PTY_FAX_NO. Plus, you *may* need a final
CAST to a fixed width to keep the spaces in the last value:
SELECT
cast(
cast (o.LN_NO as CHAR(30)) ||
cast (a.PTY1_NM as CHAR (50)) ||
cast (a.PTY_TELEPHONE_NO as CHAR (5)) ||
/* cast (a.PTY_FAX_NO as CHAR (12)) || */
LEFT (a.PTY_FAX_NO, 3) || '.' || SUBSTR(a.PTY_FAX_NO, 4, 3) || '.' || SUBSTR (a.PTY_FAX_NO, 7, 4) || --<<--*****
cast (a.PTY_EMAIL_ADR_XX as CHAR (63))
as CHAR(<expected_record_leng
...
CAST to a fixed width to keep the spaces in the last value:
SELECT
cast(
cast (o.LN_NO as CHAR(30)) ||
cast (a.PTY1_NM as CHAR (50)) ||
cast (a.PTY_TELEPHONE_NO as CHAR (5)) ||
/* cast (a.PTY_FAX_NO as CHAR (12)) || */
LEFT (a.PTY_FAX_NO, 3) || '.' || SUBSTR(a.PTY_FAX_NO, 4, 3) || '.' || SUBSTR (a.PTY_FAX_NO, 7, 4) || --<<--*****
cast (a.PTY_EMAIL_ADR_XX as CHAR (63))
as CHAR(<expected_record_leng
...
ASKER
Scott - Thanks for the post...are you saying it should look like this ?
LEFT (a.PTY_FAX_NO, 3) || '.' || SUBSTR(a.PTY_FAX_NO, 4, 3) || '.' || SUBSTR (a.PTY_FAX_NO, 7, 4) || CONCAT
LEFT (a.PTY_FAX_NO, 3) || '.' || SUBSTR(a.PTY_FAX_NO, 4, 3) || '.' || SUBSTR (a.PTY_FAX_NO, 7, 4) || CONCAT
ASKER
Scott - I got it, thanks. I did not notice the '||' at the end of your statement...
Appreciate it.
Appreciate it.
As Scott points out:
LEFT (a.PTY_FAX_NO, 3) || '.' || SUBSTR(a.PTY_FAX_NO, 4, 3) || '.' || SUBSTR (a.PTY_FAX_NO, 7, 4) ||
The double-pipe is the concatenation operator. :)
And as Tom points out, we're assuming that the data type of PTY_FAX_NO is character based. If it's numeric, we've got more work to do.
I'm having issues posting today. :(
Kent
LEFT (a.PTY_FAX_NO, 3) || '.' || SUBSTR(a.PTY_FAX_NO, 4, 3) || '.' || SUBSTR (a.PTY_FAX_NO, 7, 4) ||
The double-pipe is the concatenation operator. :)
And as Tom points out, we're assuming that the data type of PTY_FAX_NO is character based. If it's numeric, we've got more work to do.
I'm having issues posting today. :(
Kent
I'm not particularly interested in points here, but the closing seems perhaps to be inappropriate. I don't see where Kent gets due credit for his effort.
Tom
Tom
ASKER
Kent - Thanks again. Got it all working as needed thanks to your quick assistance.
SELECT
cast (o.LN_NO as CHAR(30)) ||
cast (a.PTY1_NM as CHAR (50)) ||
cast (a.PTY_TELEPHONE_NO as CHAR (5))) ||
cast (a.PTY_FAX_NO as CHAR (12)) ||
cast (a.PTY_EMAIL_ADR_XX as CHAR (63))
FROM RDX_BDE.T_PARTY a
join RDX_BDE.T_POINT_OF_CONTACT
on o.POC_PROCESSOR_ID = a.PTY_ID
and o.POC_PROCESSOR_ID = a.PTY_ID
WHERE (((a.PTY_TITLE_XX) Like '%HPC%'));
For the FAX data, just convert it.
LEFT (PTY_FAX_NO, 3) || '.' || SUBSTR (PTY_FAX_NO, 4, 3) || '.' || SUBSTR (PTY_FAX_NO, 7, 4)
Kent