Solved

Unix Script Problem

Posted on 2004-09-23
5
436 Views
Last Modified: 2010-04-21
This script retrun values into a txt file. I need to insert a delimter "^", Currently the delimiter is only added to the headings. How can I add it to the end of the actual data.

e.g

Val1  val2  val3
ss^  dd^   mm^

select substring(Task_Type,1,10)"Type^",
       substring(Job_Name,1,10)"Jobname^",
       substring(MemName,1,30)"Member^",
       substring(W_Day_Str,1,15)"Week Days^" ,
       substring(Days_Cal,1,10)"Calendar^",
       substring(Group_Name,1,30)"Group^",
       substring(Description,1,60)"Description^",
       substring(Node_Id,1,12)"Data Center^" from def_job

Thanks
0
Comment
Question by:xy15973
  • 2
5 Comments
 

Expert Comment

by:pmathinos
ID: 12132625
Change this line to reflect your table/columns and try it:

e.g:
SELECT SUBSTR(NUMDATA,4,4)||'^ '||UPLINK||'^ '||DOWNLINK||'^ '||TOTAL
FROM TEMP_TABLE

will give you:

9480^ 0^ 0^ 0
9478^ 1170^ 58^ 1228
9470^ 0^ 0^ 0
9422^ 0^ 0^ 0
9473^ 0^ 0^ 0
9445^ 1281^ 366^ 1647
9472^ 0^ 0^ 0
9480^ 0^ 0^ 0
9479^ 0^ 0^ 0

pmathinos.
0
 

Author Comment

by:xy15973
ID: 12158757

Will this work?

select substring(Task_Type,1,10)||"^"||Type ,
       substring(Job_Name,1,10)||"^"||Jobname ,
       substring(MemName,1,30)||"^"||Member ,
       substring(W_Day_Str,1,15)||"^"||"Week Days" ,
       substring(Days_Cal,1,10)||"^"||"Calendar",
       substring(Group_Name,1,30)||"^"||"Group",
       substring(Description,1,60)||"^"||"Description",
       substring(Node_Id,1,12)||"^"||"Data Center" from def_job
go
0
 

Accepted Solution

by:
pmathinos earned 200 total points
ID: 12167640
double quotes will not work. Use single quotes to append Strings to columns...

To get the column headers as well use a separate SQL query.

Save the following to an temp.sql file, start your SQL session and type
SQL> @temp.sql
to execute the file...
"output.dat" will contain your '^' - delimited report

-- start of sql file

set heading off
set linesize 100
set feed off
set echo off
set show off
set pagesize 0
set space 0
set trimspool on

SPOOL output.dat

select 'Type'||'^'||'Jobname'||'^'||'Member'||'^'||'Week Days'||'^'||'Calendar'||'^'||'Group'||'^'||'Description'||'^'||'Data Center' from dual;

select      substring(Task_Type,1,10)||'^'||
      substring(Job_Name,1,10)||'^'||
      substring(MemName,1,30)||'^'||
      substring(W_Day_Str,1,15)||'^'||
      substring(Days_Cal,1,10)||'^'||
      substring(Group_Name,1,30)||'^'||
      substring(Description,1,60)||'^'||
      substring(Node_Id,1,12)
from def_job;

SPOOL OFF

-- end of sql file


0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Need some help with tutorials or training material/videos in unix 1 69
Access_log 17 101
How can i set a Environment Variable in my Unix script ? 3 52
Linux hostname change 2 56
My previous tech tip, Installing the Solaris OS From the Flash Archive On a Tape (http://www.experts-exchange.com/articles/OS/Unix/Solaris/Installing-the-Solaris-OS-From-the-Flash-Archive-on-a-Tape.html), discussed installing the Solaris Operating S…
Every server (virtual or physical) needs a console: and the console can be provided through hardware directly connected, software for remote connections, local connections, through a KVM, etc. This document explains the different types of consol…
Learn several ways to interact with files and get file information from the bash shell. ls lists the contents of a directory: Using the -a flag displays hidden files: Using the -l flag formats the output in a long list: The file command gives us mor…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now