How do we get the following script to have an output fixed column width of 80 characters?

paul.sousa · September 29, 2015, 5:48am

Can someone please help us in getting the following script to have an output of 80 characters…in order to load the text file it must have a fixed width of 80 characters but we don’t see a way of doing this…we are currently on version 12.07. Any help would be greatly appreciated

select ‘"’||‘CA’||’"’||’,’||’"’||substr(PWCUSTOM.PWC_OU_MAPPING.TERRITORY,1,3)||’"’||’,’||’"’||substr(PWCUSTOM.PWC_ACCOUNT_MAPPING.GLOBAL_FIN,1,5)

||’"’||’,’||’"’||‘2016’||’"’||’,’||’"’||‘02’ ||’"’||’,’||’"’||substr(PWCUSTOM.PWC_OU_MAPPING.GLOBAL_OU,1,3)||’"’||’,’||sum

(APPS.IPR_PWC_ACCT_INQ_SUMMARY.ENTERED_YEAR_TO_DATE_NUM) Global_Financials

FROM PWCUSTOM.PWC_ACCOUNT_MAPPING

INNER JOIN APPS.IPR_PWC_ACCT_INQ_SUMMARY ON PWCUSTOM.PWC_ACCOUNT_MAPPING.ACCOUNT = APPS.IPR_PWC_ACCT_INQ_SUMMARY.SEGMENT3

INNER JOIN PWCUSTOM.PWC_OU_MAPPING ON PWCUSTOM.PWC_OU_MAPPING.OU = APPS.IPR_PWC_ACCT_INQ_SUMMARY.SEGMENT2

WHERE APPS.IPR_PWC_ACCT_INQ_SUMMARY.PERIOD_NAME = ‘Aug-15’

AND SEGMENT3 >= ‘30000’ AND SEGMENT3 <= ‘89999’

AND APPS.IPR_PWC_ACCT_INQ_SUMMARY.ACTUAL_FLAG = ‘A’

AND ENTERED_YEAR_TO_DATE_NUM <> ‘0.00’

AND PWCUSTOM.PWC_ACCOUNT_MAPPING.GLOBAL_FIN IS NOT NULL AND APPS.IPR_PWC_ACCT_INQ_SUMMARY.CURRENCY_CODE = ‘CAD’

Group by substr(PWCUSTOM.PWC_OU_MAPPING.TERRITORY,1,3),substr(PWCUSTOM.PWC_ACCOUNT_MAPPING.GLOBAL_FIN,1,5),substr(PWCUSTOM.PWC_OU_MAPPING.GLOBAL_OU,1,3)

Order by substr(PWCUSTOM.PWC_OU_MAPPING.TERRITORY,1,3),substr(PWCUSTOM.PWC_ACCOUNT_MAPPING.GLOBAL_FIN,1,5),substr(PWCUSTOM.PWC_OU_MAPPING.GLOBAL_OU,1,3)

NormTeamT · September 29, 2015, 6:33am

Hi Paul,

you need to decide on how much filler each column will be allowed to take. Specifically the columns that you are getting from the tables. Then simply lpad or rpad them with the appropriate number of spaces.

This assumes that spaces are allowed of course? If this is not the case, then I must assume that you have a record definition that states how wide each field in the 80 character record will be?

Also, you have a lot of concatenation that isn’t really required in the example. This, for example:

select ‘"’||‘CA’||’"’||’,’||’"’

should/could be replaced by the much easier to read:

select ‘“CA”,’ – (Open single quote, double, CA, double, comma, close single quote)

You are concatenating fixed character literals and they can all be combined into a single literal - assuming the example bears a resemblance to reality and isn’t just an example with anything sensitive obfuscated of course!

As all of your leading columns are fixed length, and assuming that the padding only needs to go at the end of the last column, this should give you fixed 80 character width records:

select

‘"’||‘CA’||’"’||’,’||

‘"’||substr(PWCUSTOM.PWC_OU_MAPPING.TERRITORY,1,3)||’"’||’,’||

‘"’||substr(PWCUSTOM.PWC_ACCOUNT_MAPPING.GLOBAL_FIN,1,5)||’"’||’,’||

‘"’||‘2016’||’"’||’,’||

‘"’||‘02’ ||’"’||’,’||

‘"’||substr(PWCUSTOM.PWC_OU_MAPPING.GLOBAL_OU,1,3)||’"’||’,’||

rpad(trim(to_char(sum(APPS.IPR_PWC_ACCT_INQ_SUMMARY.ENTERED_YEAR_TO_DATE_NUM))),43) Global_Financials

This will pad the sum() column to 43 characters wide, padding on the right with spaces. If you want to pad with something else, then this will help, using hash chacters as padding:

rpad(trim(to_char(sum(APPS.IPR_PWC_ACCT_INQ_SUMMARY.ENTERED_YEAR_TO_DATE_NUM))), 43, ‘#’) Global_Financials

In case you are wondering, the fixed stuff is 37 characters in size, including double quotes and separating commas, so the 43 is required to bring the width up to 80.

HTH

Cheers,

Norm. [TeamT]

NormTeamT · September 29, 2015, 6:40am

Ugh! Sorry, brain fade there.

This is wrong:

rpad(trim(to_char(sum(APPS.IPR_PWC_ACCT_INQ_SUMMARY.ENTERED_YEAR_TO_DATE_NUM))),43) Global_Financials

It should, of course, be this:

substr(rpad(trim(to_char(sum(APPS.IPR_PWC_ACCT_INQ_SUMMARY.ENTERED_YEAR_TO_DATE_NUM))),43), 1, 80) Global_Financials

And the “43” can be replaced by any value big enough to get yo to a string that can be SUBSTR’d to 80 characters, so I’d probably replace it with 80 to be 100% certain:

substr(rpad(trim(to_char(sum(APPS.IPR_PWC_ACCT_INQ_SUMMARY.ENTERED_YEAR_TO_DATE_NUM))),80), 1, 80) Global_Financials

Cheers,

Norm.

NormTeamT · September 29, 2015, 6:40am

Ugh! Sorry, brain fade there.

This is wrong:

rpad(trim(to_char(sum(APPS.IPR_PWC_ACCT_INQ_SUMMARY.ENTERED_YEAR_TO_DATE_NUM))),43) Global_Financials

It should, of course, be this:

substr(rpad(trim(to_char(sum(APPS.IPR_PWC_ACCT_INQ_SUMMARY.ENTERED_YEAR_TO_DATE_NUM))),43), 1, 80) Global_Financials

And the “43” can be replaced by any value big enough to get yo to a string that can be SUBSTR’d to 80 characters, so I’d probably replace it with 80 to be 100% certain:

substr(rpad(trim(to_char(sum(APPS.IPR_PWC_ACCT_INQ_SUMMARY.ENTERED_YEAR_TO_DATE_NUM))),80), 1, 80) Global_Financials

Cheers,

Norm.

NormTeamT · September 29, 2015, 6:41am

Sorry, I seem to have managed to get that posted twice!

paul.sousa · September 29, 2015, 7:01am

Thank you so much Norm (TeamT)…your suggestion above is exactly what we were looking for :o)

NormTeamT · September 29, 2015, 8:21am

On 29/09/15 15:02, paul.sousa wrote:

Thank you so much Norm (TeamT)......your suggestion above is exactly

what we were looking for :o)

My work here is done!

paul.sousa · October 1, 2015, 7:06am

Hello Norm,

Thanks again for your help …this really helped

I though I would ask you one more question if you don’t mind…I’ve taken your suggestion in the below script and its working with the Union function however do you know of a way to do this without Union to make this a little easier? The first one selects the territory which is two digits and then I’m adding 44 spaces…the second one is taking the territory which is three digits and then I’m adding 43 spaces. Not sure if its possible to maybe use ‘case’ ? Any help would be greatly appreciated…thanks again

Select

‘"’||‘CA’||’"’||’,’||

‘"’||substr(O124951.TERRITORY,1,2)||’"’||’,’|| — This only picks up two characters

‘"’||substr(O124951.GLOBAL_FIN_ACCT,1,5)||’"’||’,’||

‘"’||‘2016’||’"’||’,’||

‘"’||‘02’ ||’"’||’,’||

‘"’||substr(O124951.GLOBAL_OU,1,3)||’"’||’,’||

substr(rpad(trim(to_char(sum(O124951.ENTERED_YEAR_TO_DATE_NUM))),44), 1, 80) Amt — 44 characters are added

FROM APPS.IPR_PWC_ACCT_INQ_SUMMARY O124951

WHERE

( O124951.GLOBAL_FIN_ACCT NOT LIKE ‘NOMAP’ ) AND

( O124951.CURRENCY_CODE = ‘CAD’ ) AND

( O124951.SEGMENT3 <= ‘89999’ ) AND

( O124951.SEGMENT3 >= ‘30000’ ) AND

( O124951.ACTUAL_FLAG = ‘A’ ) AND

( O124951.ENTERED_YEAR_TO_DATE_NUM <> ‘0.00’ ) AND

( O124951.PERIOD_NAME = ‘Aug-15’ )

and O124951.SEGMENT2 not in (‘GL1968’,‘GL1975’,‘GL1971’,‘CL1502’,‘QC1502’)

group by

substr(O124951.TERRITORY,1,2) ,

substr(O124951.GLOBAL_FIN_ACCT,1,5),

substr(O124951.GLOBAL_OU,1,3)

UNION

Select

‘"’||‘CA’||’"’||’,’||

‘"’||‘Z1B’||’"’||’,’|| — This one has to pick up three characters

‘"’||substr(O124951.GLOBAL_FIN_ACCT,1,5)||’"’||’,’||

‘"’||‘2016’||’"’||’,’||

‘"’||‘02’ ||’"’||’,’||

‘"’||substr(O124951.GLOBAL_OU,1,3)||’"’||’,’||

substr(rpad(trim(to_char(sum(O124951.ENTERED_YEAR_TO_DATE_NUM))),43), 1, 80) Amt — 43 characters are added

FROM APPS.IPR_PWC_ACCT_INQ_SUMMARY O124951

WHERE

( O124951.GLOBAL_FIN_ACCT NOT LIKE ‘NOMAP’ ) AND

( O124951.CURRENCY_CODE = ‘CAD’ ) AND

( O124951.SEGMENT3 <= ‘89999’ ) AND

( O124951.SEGMENT3 >= ‘30000’ ) AND

( O124951.ACTUAL_FLAG = ‘A’ ) AND

( O124951.ENTERED_YEAR_TO_DATE_NUM <> ‘0.00’ ) AND

( O124951.PERIOD_NAME = ‘Aug-15’ )

and O124951.SEGMENT2 in (‘GL1968’,‘GL1975’)

group by

substr(O124951.TERRITORY,1,2) ,

substr(O124951.GLOBAL_FIN_ACCT,1,5),

substr(O124951.GLOBAL_OU,1,3)

Dennis_Paulus · October 1, 2015, 7:47am

Paul - depending on your requirement, it seems like you could just pad the entire concatenated string, then it doesn’t matter how long a field is.

I agree with Norm, you should probably simplify your query by removing all the unnecessary concatenations (see Norms first post).
Wrap the entire string in the rpad function - then your guaranteed it will always be 80 characters.

Below is a simple example…

select rpad(’“CA”,“Z1B”,"’ ||
substr(‘Dennis’,1,6) ||
‘,“2016”,“02”,"’ ||
substr(‘OPU’,1,3) ||
‘","’ ||
substr(‘YTDNUM’,1,4) ||
‘"’,80) My_String
from dual;

Dennis_Paulus · October 1, 2015, 8:04am

I guess I didn’t really address the ‘Union’ part of the question. That’s a little more difficult, because you have to know what the data is and when you want which data to show up. If you just want to put in the Constant ‘Z1B’ when Territory is null, then just use a nvl function (nvl(territory,‘Z1B’)). A case or decode might also work - just kind of depends on what you want it to do.

NormTeamT · October 1, 2015, 8:27am

Hi Paul,

I’d use a UNION ALL here instead of a UNION as it removes at least one sort. Plus, it doesn’t get rid of duplicate rows in the output - which might be something to consider.

this should be ok: (FAMOUS LAST WORDS?) aPOLOGIES IF THE FORMATTING GETS TRASHED IN THE SYSTEM, IT USUALLY DOES.

SELECT

substr(

rpad(

trim(

‘"’||‘CA’||’",’||

‘"’||

case

when O124951.SEGMENT2

not in (‘GL1968’,‘GL1975’)

then substr(O124951.TERRITORY,1,2)

else ‘Z1B’

end||’",’||

‘"’||substr(O124951.GLOBAL_FIN_ACCT,1,5)||’",’||

‘"’||‘2016’||’",’||

‘"’||‘02’ ||’",’||

‘"’||substr(O124951.GLOBAL_OU,1,3)||’",’||

to_char(sum(O124951.ENTERED_YEAR_TO_DATE_NUM))

),80 – end of trim(), size of rpad()

), 1, 80 – end of rpad(), size of substr()

) Amt – end of substr()

WHERE

( O124951.GLOBAL_FIN_ACCT NOT LIKE ‘NOMAP’ ) AND

( O124951.CURRENCY_CODE = ‘CAD’ ) AND

( O124951.SEGMENT3 <= ‘89999’ ) AND

( O124951.SEGMENT3 >= ‘30000’ ) AND

( O124951.ACTUAL_FLAG = ‘A’ ) AND

( O124951.ENTERED_YEAR_TO_DATE_NUM <> ‘0.00’ ) AND

( O124951.PERIOD_NAME = ‘Aug-15’ )

and O124951.SEGMENT2 not in (‘GL1971’,‘CL1502’,‘QC1502’)

group by

substr(O124951.TERRITORY,1,2) ,

substr(O124951.GLOBAL_FIN_ACCT,1,5),

substr(O124951.GLOBAL_OU,1,3)

;

I think that should work. However, I don’t have any test data in the correct table format etc, so make sure you test it and check the results.

I hope I’ve got the WHERE clause correct. When you are looking for two characters only, its excluding the three values I’ve left in above. When you want three characters as in ‘Z1B’ then it’s only for the two I’ve listed in the ‘case’ clause. That should be correct.

–

Cheers,

Norm. [TeamT]

NormTeamT · October 1, 2015, 8:28am

Ugh! It was indeed trashed. Sorry.

paul.sousa · October 1, 2015, 11:49am

Thanks for your help…I gave it a try and I’m getting ORA-00979: not a GROUP BY expression

I’m assuming its because I need to also group segment2…however when I do this its changing my results? Is there a workaround…sorry for all the questions. Thanks again

select ‘"’||‘CA’||’"’||’,’||’"’||

case when O124951.SEGMENT2 not in (‘GL1968’,‘GL1975’)then substr(O124951.TERRITORY,1,2) else ‘Z1B’end||’",’||’"’||substr(O124951.GLOBAL_STAT_ACCT,1,5)

||’"’||’,’||’"’||‘2016’||’"’||’,’||’"’||‘02’ ||’"’||’,’||’"’||substr(O124951.GLOBAL_OU,1,3)||’"’||’,’|| to_char(sum(O124951.ENTERED_YEAR_TO_DATE_NUM)),80

—substr(rpad(trim(to_char(sum(O124951.ENTERED_YEAR_TO_DATE_NUM * -1))),44), 1, 80) Amt

FROM APPS.IPR_PWC_ACCT_INQ_SUMMARY O124951

WHERE

( O124951.GLOBAL_FIN_ACCT NOT LIKE ‘NOMAP’ ) AND

( O124951.CURRENCY_CODE = ‘CAD’ ) AND

( O124951.SEGMENT3 <= ‘89999’ ) AND

( O124951.SEGMENT3 >= ‘30000’ ) AND

( O124951.ACTUAL_FLAG = ‘A’ ) AND

( O124951.ENTERED_YEAR_TO_DATE_NUM <> ‘0.00’ ) AND

( O124951.PERIOD_NAME = ‘Aug-15’ )

and O124951.SEGMENT2 not in (‘GL1971’,‘CL1502’,‘QC1502’)

group by substr(O124951.TERRITORY,1,2) , substr(O124951.GLOBAL_FIN_ACCT,1,5), substr(O124951.GLOBAL_OU,1,3)

jacquesrk · October 1, 2015, 12:14pm

(never mind, I just realized my query was incorrect)

jacquesrk · October 1, 2015, 5:17pm

Sorry for the delay. Here is how you can write the query without the UNION, using a subquery. But this assumes that territory_code ‘Z1B’ always corresponds to the same unique value in substr (O124951.TERRITORY, 1, 2) , which may or may not be the case. If that’s not the case, then I think you have to use a UNION.

select
rpad (’“CA”,"’ || subq.territory_code || ‘","’
|| subq.global_fin_acct_1_5 || ‘",“2016”,“02”,"’
|| subq.global_ou_1_3 || ‘",’
|| to_char (sum (subq.ENTERED_YEAR_TO_DATE_NUM)),
80, ’ ')
from
(select
case
when O124951.SEGMENT2 in (‘GL1968’,‘GL1975’) then ‘Z1B’
else substr (O124951.TERRITORY, 1, 2)
end as territory_code,
substr (O124951.GLOBAL_FIN_ACCT, 1, 5) as global_fin_acct_1_5,
substr (O124951.GLOBAL_OU, 1, 3) as global_ou_1_3,
O124951.ENTERED_YEAR_TO_DATE_NUM
FROM APPS.IPR_PWC_ACCT_INQ_SUMMARY O124951
WHERE
( O124951.GLOBAL_FIN_ACCT NOT LIKE ‘NOMAP’ ) AND
( O124951.CURRENCY_CODE = ‘CAD’ ) AND
( O124951.SEGMENT3 <= ‘89999’ ) AND
( O124951.SEGMENT3 >= ‘30000’ ) AND
( O124951.ACTUAL_FLAG = ‘A’ ) AND
( O124951.ENTERED_YEAR_TO_DATE_NUM <> ‘0.00’ ) AND
( O124951.PERIOD_NAME = ‘Aug-15’ )
and O124951.SEGMENT2 not in (‘GL1971’,‘CL1502’,‘QC1502’)
) subq
group by
subq.territory_code,
subq.global_fin_acct_1_5,
subq.global_ou_1_3 ;

paul.sousa · October 2, 2015, 7:39am

Thanks everyone for your help…this is such a great forum to get quick answers from experts!!

jacquesrk · October 2, 2015, 10:53am

I know you’re happy, but this was bugging me.

If territory_code ‘Z1B’ does NOT always correspond to the same unique value in substr (O124951.TERRITORY, 1, 2), which was a prerequisite for my previous query to have the same results as your original query, then there is still a way to rewrite your original query without a UNION, as so

select
rpad (’“CA”,"’
|| case subq.z1b_territory when ‘Y’ then ‘Z1B’ else subq.territory_1_2 end
|| ‘","’
|| subq.global_fin_acct_1_5 || ‘",“2016”,“02”,"’
|| subq.global_ou_1_3 || ‘",’
|| to_char (sum (subq.ENTERED_YEAR_TO_DATE_NUM)),
80, ’ ')
from
(select
case
when O124951.SEGMENT2 in (‘GL1968’,‘GL1975’) then ‘Y’
else ‘N’
end as z1b_territory,
substr (O124951.TERRITORY, 1, 2) as territory_1_2,
substr (O124951.GLOBAL_FIN_ACCT, 1, 5) as global_fin_acct_1_5,
substr (O124951.GLOBAL_OU, 1, 3) as global_ou_1_3,
O124951.ENTERED_YEAR_TO_DATE_NUM
FROM APPS.IPR_PWC_ACCT_INQ_SUMMARY O124951
WHERE
( O124951.GLOBAL_FIN_ACCT NOT LIKE ‘NOMAP’ ) AND
( O124951.CURRENCY_CODE = ‘CAD’ ) AND
( O124951.SEGMENT3 <= ‘89999’ ) AND
( O124951.SEGMENT3 >= ‘30000’ ) AND
( O124951.ACTUAL_FLAG = ‘A’ ) AND
( O124951.ENTERED_YEAR_TO_DATE_NUM <> ‘0.00’ ) AND
( O124951.PERIOD_NAME = ‘Aug-15’ )
and O124951.SEGMENT2 not in (‘GL1971’,‘CL1502’,‘QC1502’)
) subq
group by
subq.z1b_territory,
subq.territory_1_2,
subq.global_fin_acct_1_5,
subq.global_ou_1_3 ;

paul.sousa · October 2, 2015, 12:41pm

Hello Jacquesrk…the good thing is that ‘ZIB’ is a unique value but this may change. I have now run your script and it works like a charm. Thanks again and you can sleep tonight not thinking about this :o)

NormTeamT · October 3, 2015, 12:15pm

Just a quick comment. The rpad (..., 80) call adds 80 spaces to the selected data, so it needs to be substr (rpad (..., 80), 1, 80) to get the desired 80 character wide output. Other than that, nice script.

Cheers,

Norm [TeamT ]

On 3 October 2015 20:08:48 BST, jacquesrk bounce-jacquesrk@toadworld.com wrote:

RE: How do we get the following script to have an output fixed column width of 80 characters?

Reply by jacquesrk
I know you're happy, but this was bugging me.

If territory_code 'Z1B' does NOT always correspond to the same unique value in substr (O124951.TERRITORY, 1, 2), which was a prerequisite for my previous query to have the same results as your original query, then you can write it without a UNION, as so

select
rpad ('"CA","'
|| case subq.z1b_territory when 'Y' then 'Z1B' else subq.territory_1_2 end
|| '","'
|| subq.global_fin_acct_1_5 || '","2016","02","'
|| subq.global_ou_1_3 || '",'
|| to_char (sum (subq.ENTERED_YEAR_TO_DATE_NUM)),
80, ' ')
from
(select
case
when O124951.SEGMENT2 in ('GL1968','GL1975') then 'Y'
else 'N'
end as z1b_territory,
substr (O124951.TERRITORY, 1, 2) as territory_1_2,
substr (O124951.GLOBAL_FIN_ACCT, 1, 5) as global_fin_acct_1_5,
substr (O124951.GLOBAL_OU, 1, 3) as global_ou_1_3,
O124951.ENTERED_YEAR_TO_DATE_NUM
FROM APPS.IPR_PWC_ACCT_INQ_SUMMARY O124951
WHERE
( O124951.GLOBAL_FIN_ACCT NOT LIKE 'NOMAP' ) AND
( O124951.CURRENCY_CODE = 'CAD' ) AND
( O124951.SEGMENT3 <= '89999' ) AND
( O124951.SEGMENT3 >= '30000' ) AND
( O124951.ACTUAL_FLAG = 'A' ) AND
( O124951.ENTERED_YEAR_TO_DATE_NUM <> '0.00' ) AND
( O124951.PERIOD_NAME = 'Aug-15' )
and O124951.SEGMENT2 not in ('GL1971','CL1502','QC1502')
) subq
group by
subq.z1b_territory,
subq.territory_1_2,
subq.global_fin_acct_1_5,
subq.global_ou_1_3 ;

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or Unsubscribe from Toad for Oracle - General notifications altogether.

Toad for Oracle - Discussion Forum

Flag this post as spam/abuse.

--

Sent from my Android device with K-9 Mail. Please excuse my brevity.

jacquesrk · October 3, 2015, 3:53pm

No, rpad (‘string’, 80) pads up to 80 characters.

docs.oracle.com/…/dml_functions_2075.htm