SQL*PLUS formatting issues, can't use || to concat fields 1

[Zike]

Ok, Here's my issue.

I'm redoing a set of queries to produce reports from a manual process to an automated process. The requirement was that I use SQL*PLUS to produce to reports. I can get the queries to produce the reports using SPOOL with several SET options. I get close to matching the exact output that the manual process produced but I still get huge whitespace in the records. I know this is from SQL*PLUS liking to make everything pretty but it's really annoying.

I've tried to concat the fields together like I've seen elsewhere but as soon as I get it to concat right, I get an GROUP BY error because I know have them all concat'd and not selecting them individually.

So, I guess I have 2 questions.

ONE: Since I know the order I want the column to display in, do I still need to group by section.
TWO: is there another way to get rid of the extra spaces other then concat'ing all the fields. I tried using the following: TRIM, TRIM BOTH, LTRIM(RTRIM()) but none of them get rid of the extra spaces.

Here are the set commands I'm using:
SET TERMOUT OFF
SET TRIMSPOOL ON
SET TRIMOUT ON
SET LINESIZE 1024
SET FEEDBACK OFF
SET COLSEP |
SET PAGESIZE 0
SET TAB OFF

This gets me close but I still get the extra spaces.

Any help would be appreciated as I've burnt any hours on this already.

 

[carp]

If you could provide us with a sample of the query that comes the closest to what you want (along with a sample of the output and identification with what's wrong with the output), we can probably help you get where you want to go a lot more quickly.

 

[Zike]

Ok, Here's the script and query (made it generic so I could post it here). The &1 is just the sysDate from the beginning script that is being passed to all the subscripts.

SPOOL /home/user/sql/out/test.&1..txt APPEND

SET TERMOUT OFF
SET TRIMSPOOL ON
SET TRIMOUT ON
SET LINESIZE 1024
SET FEEDBACK OFF
SET COLSEP |
SET PAGESIZE 0
SET TAB OFF

SELECT
TO_CHAR(Table1.DTE_SVC,'mm/dd/yyyy'),
TO_CHAR(Table2.DTE_PAID,'mm/dd/yyyy'),
' ' INDICATOR,
TRIM(BOTH FROM Table1.CDE_STATUS),
TRIM(BOTH FROM Table1.NUM),
TRIM(BOTH FROM Table1.NUM_DTL),
TRIM(BOTH FROM Table1.ID),
TRIM(BOTH FROM Table3.CDE_AID_CATEGORY),
TRIM(BOTH FROM Table3.DSC_AID_CATEGORY),
TRIM(BOTH FROM Table4.CDE_PGM_HEALTH),
TRIM(BOTH FROM Table4.DSC_PGM_HEALTH),
TRIM(BOTH FROM Table1.CDE_REVENUE),
TRIM(BOTH FROM Table1.CDE_1),
TRIM(BOTH FROM Table1.CDE_2),
TRIM(BOTH FROM Table1.CDE_3),
TRIM(BOTH FROM Table1.CDE_4),
TRIM(BOTH FROM Table1.CDE_5),
TRIM(BOTH FROM Table1.CDE_6),
TRIM(BOTH FROM Table1.CDE_7),
TRIM(BOTH FROM Table1.CDE_8),
TRIM(BOTH FROM Table1.CDE_9),
TRIM(BOTH FROM Table1.CDE_10),
TRIM(BOTH FROM Table4.cde_TYPE),
TRIM(BOTH FROM Table4.cde_POS),
TRIM(BOTH FROM Table5.CDE_PROC),
TRIM(BOTH FROM Table1.CDE_PROC_MOD),
TRIM(BOTH FROM Table1.CDE_MOD_2),
TRIM(BOTH FROM Table1.CDE_MOD_3),
TRIM(BOTH FROM Table1.CDE_MOD_4),
TRIM(BOTH FROM Table1.CDE_DRG),
TO_CHAR(Table1.DTE_FIRST_SVC,'mm/dd/yyyy'),
TO_CHAR(Table1.DTE_LAST_SVC,'mm/dd/yyyy'),
TO_CHAR(Table1.DTE_ADMIN,'mm/dd/yyyy'),
TO_CHAR(Table1.DTE_DISCHARGE,'mm/dd/yyyy'),
TRIM(BOTH FROM (Table1.QTY_BILLED)),
' ' FFS_EQUIV_AMT,
(Table1.AMT),
(Table1.ALWD),
(Table1.PAID),
(Table1.CO_PAY),
(Table1.DEDUCT),
(Table1.COINSURE),
TRIM(BOTH FROM Table1.PROV),
TRIM(BOTH FROM Table1.PERFORMING),
TRIM(BOTH FROM Table6.CDE_PROV_SPEC),
TRIM(BOTH FROM Table6.DSC_PROV_SPEC),
TRIM(BOTH FROM Table6.CDE_PROV_TYPE),
TRIM(BOTH FROM Table6.DSC_PROV_TYPE),
TRIM(BOTH FROM Table1.CDE_LOC),
TRIM(BOTH FROM Table1.AGE),
TRIM(BOTH FROM Table7.CDE_SEX),
TRIM(BOTH FROM Table1.DTE_LAST_SVC-Table1.DTE_FIRST_SVC),
TRIM(Table8.CDE_TYPE||Table9.CDE_TYPE),
TRIM(Table8.CDE_HOUR||Table9.CDE_HOUR),
TRIM(Table8.CDE_A_TYPE||Table9.CDE_A_TYPE),
MAX(CASE
WHEN b.num_seq=1
THEN b.CDE_OCCURRENCE
ELSE ''
END),
MAX(CASE
WHEN b.num_seq=1
THEN b.DTE_OCCURRENCE
ELSE NULL
END),
TRIM(BOTH FROM Table1.CDE_STATUS),
TRIM(BOTH FROM Table1.DAYS_COVD),
TRIM(BOTH FROM Table1.ATTACHMENT),
TRIM(BOTH FROM Table1.REFERRING),
TRIM(Table8.CDE_CERT||Table9.CDE_CERT),
TRIM(BOTH FROM Table1.EMERGENCY),
TRIM(BOTH FROM Table1.PREG),
MAX(CASE
WHEN a.num_seq=1
THEN a.cde_PROC_1
ELSE ''
END),
MAX(CASE
WHEN a.num_seq=1
THEN a.dte_Proc_1
ELSE NULL
END),
MAX(CASE
WHEN a.num_seq=2
THEN a.cde_PROC_2
ELSE ''
END),
MAX(CASE
WHEN a.num_seq=2
THEN a.dte_Proc_2
ELSE NULL
END),
MAX(CASE
WHEN a.num_seq=3
THEN a.cde_PROC_3
ELSE ''
END),
MAX(CASE
WHEN a.num_seq=3
THEN a.dte_Proc_3
ELSE NULL
END),
MAX(CASE
WHEN a.num_seq=4
THEN a.cde_PROC_4
ELSE ''
END),
MAX(CASE
WHEN a.num_seq=4
THEN a.dte_Proc_4
ELSE NULL
END),
MAX(CASE
WHEN a.num_seq=5
THEN a.cde_PROC_5
ELSE ''
END),
MAX(CASE
WHEN a.num_seq=5
THEN a.dte_Proc_5
ELSE NULL
END),
MAX(CASE
WHEN a.num_seq=6
THEN a.cde_PROC_6
ELSE ''
END),
MAX(CASE
WHEN a.num_seq=6
THEN a.dte_Proc_6
ELSE NULL
END),
TRIM(BOTH FROM Table1.CDE_EMERG)
FROM
Table1@SERVER2 Table1,
Table2@SERVER2 Table2,
Table3@SERVER2 Table3,
Table4@SERVER2 Table4,
Table5@SERVER2 Table5,
Table6@SERVER2 Table6,
Table7@SERVER2 Table7,
Table8@SERVER2 Table8,
Table9@SERVER2 Table9,
Table10@SERVER2 b,
Table11@SERVER2 Table11,
Table12@SERVER2 a
WHERE
( Table1.SAK=a.SAK(+) )
AND ( b.SAK(+)=Table1.SAK )
AND ( Table1.SAK_PROC=Table5.SAK_PROC(+) )
AND ( Table8.SAK(+)=Table1.SAK)
AND (Table9.SAK(+)=Table1.SAK)
AND ( Table1.SAK_REC=Table7.SAK_REC )
AND ( Table3.R_KEY=Table1.R_KEY )
AND ( Table1.P_KEY=Table6.P_KEY )
AND ( Table4.C_KEY=Table1.C_KEY )
AND ( Table2.P_T_KEY=Table1.P_T_KEY )
AND Table4.CDE_TYPE in ('A','C','H','I','L','O')
AND Table1.SAK_REC=Table11.SAK_REC
and Table2.dte_paid between add_months(sysdate, -36) and sysdate
GROUP BY
Table1.DTE_FIRST_SVC,
Table2.DTE_PAID,
Table1.CDE_DTL_STATUS,
Table1.NUM_ICN,
Table1.NUM_DTL,
Table1.ID,
Table3.CDE_CATEGORY,
Table3.DSC_CATEGORY,
Table4.CDE_PGM_H,
Table4.DSC_PGM_H,
Table1.CDE_REVENUE,
Table1.CDE_1,
Table1.CDE_2,
Table1.CDE_3,
Table1.CDE_4,
Table1.CDE_5,
Table1.CDE_6,
Table1.CDE_7,
Table1.CDE_8,
Table1.CDE_9,
Table1.CDE_10,
Table4.cde_TYPE,
Table4.cde_POS,
Table5.CDE_PROC,
Table1.CDE_PROC_MOD,
Table1.CDE_MOD_2,
Table1.CDE_MOD_3,
Table1.CDE_MOD_4,
Table1.CDE_DRG,
Table1.DTE_FIRST_SVC,
Table1.DTE_LAST_SVC,
Table1.DTE_ADMIN,
Table1.DTE_DIS,
(Table1.QTY),
(Table1.AMT),
(Table1.ALWD),
(Table1.PAID),
(Table1.CO_PAY),
(Table1.DEDUCT),
(Table1.COINSURE),
Table1.PROV,
Table1.PERFORMING,
Table6.CDE_PROV_SPEC,
Table6.DSC_PROV_SPEC,
Table6.CDE_PROV_TYPE,
Table6.DSC_PROV_TYPE,
Table1.CDE_LOC,
Table1.AGE,
Table7.CDE_SEX,
Table1.DTE_LAST_SVC-Table1.DTE_FIRST_SVC,
Table8.CDE_TYPE||Table9.CDE_TYPE,
Table8.CDE_HOUR||Table9.CDE_HOUR,
Table8.CDE_A_TYPE||Table9.CDE_A_TYPE,
Table1.CDE_STATUS_P,
Table1.DAYS_COVD,
Table1.ATTACHMENT,
Table1.REFERRING,
Table8.CDE_CERT||Table9.CDE_CERT,
Table1.EMERGENCY,
Table1.PREG,
Table1.CDE_EMERG;

I guess my problem is that when using the TRIM (any of them that I listed) none of them take off the leading or trailing spaces. I always get the SQL*PLUS formatting issue where it adds the extra spaces to match the length of the column for each field. The fields that are concat'd with the || (Table8.CDE_TYPE||Table9.CDE_TYPE, Table8.CDE_HOUR||Table9.CDE_HOUR, and Table8.CDE_A_TYPE||Table9.CDE_A_TYPE) are producing large amounts of whitespace even though the concat'd records only produce a 1, 2 or 3 digit number.

Where's what coming out in the spool file

12/12/1212|12/12/1212| |X|2299123112345|12 |1234123412| X|random description | | | | | |

I need it to produce the same thing but no extra spaces. So, like this:

12/12/1212|12/12/1212||X|2299123112345|12|1234123412|X|random description||||||

Like I said before, I went to use the concat trick that I've seen elsewhere but it had issues with the GROUP BY clause. From what I can see, there are more fields not being called anywhere else in the query that are in the grp by so it looks like it is needed to produce the correct data. I have about 6 subscipts that are all similar to this and they all have the same issue, Extra whitespace. Two of them even use UNIONs so not sure if the concat trick would work corectly on them.

Any thought?

 

[carp]

Zike -
I think it most likely that the problem may reside within your data. It may be possible that there are invisible characters other than spaces (tabs, etc) in your data that are not being stripped out by your TRIM commands. In your sample output, it looks like Table3.DSC_AID_CATEGORY is one of the problem children. Let's try this:

SELECT dsc_aid_category, DUMP(dsc_aid_category), TRIM(dsc_aid_category), DUMP(TRIM(dsc_aid_category)
FROM Table1@SERVER2 Table1,
  Table2@SERVER2 Table2,
  Table3@SERVER2 Table3,
  Table4@SERVER2 Table4,
  Table5@SERVER2 Table5,
  Table6@SERVER2 Table6,
  Table7@SERVER2 Table7,
  Table8@SERVER2 Table8,
  Table9@SERVER2 Table9,
  Table10@SERVER2 b,
  Table11@SERVER2 Table11,
  Table12@SERVER2 a
WHERE
  ( Table1.SAK=a.SAK(+) ) 
  AND  ( b.SAK(+)=Table1.SAK  )
  AND  ( Table1.SAK_PROC=Table5.SAK_PROC(+)  )
  AND ( Table8.SAK(+)=Table1.SAK)
  AND (Table9.SAK(+)=Table1.SAK)
  AND  ( Table1.SAK_REC=Table7.SAK_REC  )
  AND  ( Table3.R_KEY=Table1.R_KEY  )
  AND  ( Table1.P_KEY=Table6.P_KEY  )
  AND  ( Table4.C_KEY=Table1.C_KEY  )
  AND  ( Table2.P_T_KEY=Table1.P_T_KEY  )
AND Table4.CDE_TYPE in ('A','C','H','I','L','O')
 AND  Table1.SAK_REC=Table11.SAK_REC
and Table2.dte_paid between add_months(sysdate, -36) and sysdate;

As a practical alternative, if you know of a specific in table3 that exhibits this behavior, winnow the FROM and WHERE clauses down accordingly. This will allow us to see exactly what is in that column before and after the TRIM operation. We can go from there.

 

[harebrain]

You're barking up the wrong tree with TRIM--SQL*Plus doesn't care how wide the returned data is, it calculates the column width from the table/column definition. The way to control the width of a column for a SQL*Plus report is with the aptly named column directive.

One of my reports starts (something) like this:

ttitle 'User Report'
repfooter SKIP 6 'Manager:  ______________________________  Date:  ____-______-____'
column USERLOGIN format a20
column ROLE format a10
set linesize 100
set pagesize 89
set newpage NONE
set feedback off
spool user.lst

You get the idea. You can find more about the column directive in any decent SQL*Plus documentation, including Oracle's.

 

[carp]

harebrain -
Thanks for taking a step back and looking at the full situation. I had looked at the sample output string with the word "concatenate" in my mind and mistook the pipe separator as an embedded character resulting from a concatenation. You are quite right, of course. This is SQL*Plus behaving like SQL*Plus.
Zike -
If you can find a column width that will give you the results you want, then go with it. Otherwise, you may have to live with truncations and/or extra spaces. Alternatively, we can look at concatenations. Please let us know how you want to proceed.
Meanwhile, harebrain - have a star for saving us a lot of brain damage!

 

[Zike]

Ok, sorry for the long delay in a reply. I have been looking at using the COLUMN directive to get the lengths cut down to appropriate sizes but here's the problem. I'm running a "select max(length(column_name)) from table" and that works fine, and I get a certain length for the column. The output I get works right only when the data in the column is the same length as I set the column to using "COLUMN column_name FORMAT". Any values are are still less then that length have the extra spaces left in it. This is as I understand it, the nature of using SQL*PLUS. It likes to pretty up our output for us.

Since I can't make sql*plus not act like sqlplus and not add the extra spaces to match the column lengths, I guess my next step is either, see if using the concat trick of "column_name || '|' || column_name2" works or find a free software or code that will run on a windows server to remove the extra spaces in the files after they are generated. I'd like for the concat to work so nothing has to be done the the windows server but as far as I can tell, the huge query I posted before won't work due to the GROUP BY statement. If I concat the columns together is it safe to say that I could drop the GROUP BY statement and still get the same results that I get now?

Any further help would be appreciated and thanks hare and carp for the info so far. It has helped.

 

[carp]

No, it is not safe. As long as you have aggregate functions in your query, you'll need a GROUP BY.

You might try using a dynamic view, which would go something like this:

SELECT date_svc||'|'||date_paid||'|'||
       ... <rest of the columns from your original query>
       max_case_6
FROM 
(
SELECT   
  TO_CHAR(Table1.DTE_SVC,'mm/dd/yyyy') AS date_svc,
  TO_CHAR(Table2.DTE_PAID,'mm/dd/yyyy') AS date_paid,
  ... 
MAX(CASE
    WHEN a.num_seq=6
    THEN a.dte_Proc_6
    ELSE NULL
    END) AS max_case_6
 <rest of your original query here>
)

This would allow you to use the concatenation operator, trim the character strings, and allow you to steer around the GROUP BY issues.

 

[carp]

Zike -
Is this really the final output of your report, or is there another formatter downstream that could prettify your final output?

 

[Zike]

Hey everyone. Carp, you're right, the GROUP BY is definitely needed. I tried removing it just to see if I could get the concat operator to work right and did but got back a huge difference in the results. I added the GROUP BY back in and it actually worked. I only had issues with columns where I was trying to set a column alias up, but since all the columns are being concatenated, I removed the aliases and made sure the where causes was not trying to use those aliases for comparisons.

I still did need the column headers to be printed in the spooled file but I got around that by using a query similar to the following:

SELECT 'column_name_1' || '|' || 'column_name_2' || '|' ||
'column_name_N' || '|' FROM DUAL;

I did that first so I had the "column headings" then ran my query and everything seems to be working now. It appears that I was having troubles with the TRIM and column aliases the first time around and that's why I thought the concat operator wasn't going to work for me.

And Yes Carp, the output I am generating is the final step in what I have control over. The file is used by a client for some processing and that's why it was important to get the data into a set format but I seem to have accomplished that now.

So, thanks Carp and Harebrain for the help.

 

[carp]

Excellent news, Zike! Glad to hear you got to where you needed to be.