Solved

# Problem wit spool

Posted on 2013-12-13
Medium Priority
435 Views
Hi, from the script test.sh I have the next spool:

``````2013/12/11 23:55:00!13006!PSM_4___P      Calculo Demanda!0!#..!0!#..!!0!#..!!0!#..!
2013/12/11 23:55:00!13007!PSM_4___Q      Calculo Demanda!0!#..!0!#..!!0!#..!!0!#..!
UTCTIME!POINTNUMBER!POINTNAME!VALUE_INST!TLQ_INST!VALUE_PROM!TLQ_PROM!UTCTIME_MAX!VALUE_MAX!TLQ_MAX!UTCTIME_MIN!VALUE_MIN!TLQ_MIN!
``````

But I need change the order of output, It must show:

``````UTCTIME!POINTNUMBER!POINTNAME!VALUE_INST!TLQ_INST!VALUE_PROM!TLQ_PROM!UTCTIME_MAX!VALUE_MAX!TLQ_MAX!UTCTIME_MIN!VALUE_MIN!TLQ_MIN!
2013/12/11 23:55:00!13006!PSM_4___P      Calculo Demanda!0!#..!0!#..!!0!#..!!0!#..!
2013/12/11 23:55:00!13007!PSM_4___Q      Calculo Demanda!0!#..!0!#..!!0!#..!!0!#..!
``````

Could you help me?,  Where is the error?
Regards
Test.sh
0
Question by:carlino70
• 4
• 3
• 2
• +1

LVL 23

Expert Comment

ID: 39717072
Drop the "ORDER BY 1" sort, in your character set the numerals have a lower value than alpha.  However, that will drop your nice sort.

Which char set are you using?
0

Author Comment

ID: 39717110
without "ORDER BY 1" it works,

but I need to see the output in order ascending order by "utctime"the output in order ascending from utctime

``````select * from database_properties
where PROPERTY_NAME in ('NLS_CHARACTERSET',
'NLS_NCHAR_CHARACTERSET');

PROPERTY_NAME|PROPERTY_VALUE|DESCRIPTION
NLS_CHARACTERSET|AL32UTF8|Character set
NLS_NCHAR_CHARACTERSET|AL16UTF16|NCHAR Character set
``````
0

LVL 32

Expert Comment

ID: 39717116
The problem is the order by 1 which sets the order after the union. Change it to order by 1 desc or just eliminate it altogether.
0

LVL 32

Expert Comment

ID: 39717123
Sorry, I didn't see the other posts before I submitted.
0

LVL 32

Expert Comment

ID: 39717137
Since you're using a yyyy/mm/dd hh24:mi:ss date format, you'll be okay (other date formats might not work).
0

LVL 23

Expert Comment

ID: 39717142
Show us your session values for these two variables:

echo \$LC_CTYPE      # accept iso-latin-1 characters
echo \$LC_COLLATE      # collate in strict numeric order
0

Author Comment

ID: 39717153
I don't see those variables
0

LVL 68

Accepted Solution

woolmilkporc earned 2000 total points
ID: 39717181
You could just add the constant character "1" as the first column of the first "select", and the constant character "2" as the first column of the second "select":

select '1' dummy,
'UTCTIME!POINTNUMBER!POINTNAME!VALUE_INST!TLQ_INST!VALUE_PROM!
...
union all
SELECT '2' dummy,
TO_CHAR(H.UTCTIME,'YYYY/MM/DD HH24:MI:SS')||'!'||TO_CHAR
...

column dummy noprint

and change the order statement to

order by 1,2

and you should be fine.

wmp
0

Author Closing Comment

ID: 39717203
Excellent
Thanks and regards!
0

LVL 32

Expert Comment

ID: 39717302
Were you not satisfied or not get the right results when removing the order by1 (or making it order by 1 desc)?
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.