Format a query for desired output...

[Amit Mehra_1]

Hi,
I am using HPUX 10.20 with Informix 5. My question is on a sql script which I use to print the daily report databased on this machine. My script is like this:

unload to "long_duration_call.lst"

select * from hist_faults f, hist_keywords k
where f.sfn = k.sfn
and f.message_type = 'AMA112'
and f.switch_id = 'SG2'
and ( k.keyword = 'Orig_CLLI'
or k.keyword = 'Term_CLLI'
or k.keyword = 'ConnectTime'
or k.keyword = 'CallingNumber'
or k.keyword = 'CalledNumber' )

With this I get a output which is attached in the file.

Now, I want my output in a different format but am not aware of the changes which I will have to adapt for this.

My desired and current output is attached in the file. The attached file is a text file and best viewable with wrap switched off.

One more question, for the report column headings, while using 'unload' in the sql script, is there any way to print the column headings along with the extracted data? (eg. Switch,Time Stamp, Alarm Log,SFN,CalledNumber, CallingNumber, etc..)

Thanks for the help.

Regards,
Amit

[Massimo Bianchi]

Hi,
i'm sorry that i don't know informix sql, but i found usefull this link:

http://www-3.ibm.com/software/data/informix/pubs/library/online5x.html

You can adapt the examples in the SQL guides to your needs.
Unfortunatly i have no informix under hand to test, but from my memory i recall that the sintax to format output is quite simple.


HTH,
Massimo

[R. Allan Hicks]

Sorry, I don't know informix either, but what you want to do is doable under standard sql. It's difficult to give you the exact syntax without knowing your table structure, but here's a crude attempt.


hist_faults
----------
sfn
message_type
switch_id
timestamp
message_type

hist_keywords
--------------
keyword making wild guesses here
value and here

select
f.switch_id "Switch id",
f.timestamp "TimeStamp",
f.message_type "Alarm Log",
f.sfn "SFN",
k1.value "Called Number",
k2.value "Calling Number",
k3.value "Connect Time",
k4.value "Orig CLLI",
k5.value "Term CLLI"
from
hist_fault f,
hist_keywords k1,
hist_keywords k2,
hist_keywords k3,
hist_keywords k4,
hist_keywords k5
where
f.switch_id = 'SG2' and
k1.sfn = f.sfn and
k2.sfn = f.sfn and
k3.sfn = f.sfn and
k4.sfn = f.sfn and
k5.sfn = f.sfn and
k1.keyword = 'CalledNumber' and
k2.keyword = 'CallingNumber' and
k3.keyword = 'ConnectTime' and
k4.keyword = 'OrigCLLI' and
k5.keyword = 'TermCLLI';

You simply join the k table to itself. These joins tend to be time hogs. So if you are going to do this daily and if the tables are large, you will want to look at using indexes (or indices I can never keep it straight) to get acceptable performance out of it.

-Hope that this helped

[John O'Driscoll_1]

Amit,

I use Informix, but find it easier to use awk to format the output from the SQL query. As long as there are no blank lines in your long_duration_call.lst file, this script will do 99% of what you need:

#!/usr/bin/sh
# Script to format the output of an Informix SQL script.
awk -v i=0 'BEGIN { # Write the report headings:
printf("%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s\n",
"Switch", "Time Stamp", "Alarm Log", "SFN", "Called Number",
"Calling Number", "Connect Time", "Orig_CLLI", "Term_CLLI")
}

# The main processing loop. Convert the input to one record per line. The
# current record (NR) value determines how the field should be output:
{
if (NR % 5 == 1) printf("%s\t%s %s\t%s\t%s\t%12d\t", $1, $2, $3, $4, $5, $7)
if (NR % 5 == 2) printf("%ld\t", $7) # Calling Number
if (NR % 5 == 3) printf("%s\t", $7) # Connect Time
if (NR % 5 == 4) printf("%s\t", $7) # Orig_CLLI
if (NR % 5 == 0) printf("%s\n", $7) # Term_CLLI
}' long_duration_call.lst > formatted.lst

It gives the following output:
Switch Time Stamp Alarm Log SFN Called Number Calling Number Connect Time Orig_CLLI Term_CLLI
SG2 7/16/2003 5:00 AMA112 SG20307BGIX 2147483647 67481737 197/0040282 SG2CONN1PEPI SG2POB01WE2B
SG2 7/16/2003 5:00 AMA112 SG20307BGIY 2147483647 62255111 197/0233095 SG2BLUD1PE2I SG2HK201RE2B
SG2 7/16/2003 5:00 AMA112 SG20307BGJA 2147483647 62255111 197/0042024 SG2BLUD1PE2I SG2HK201RE2B
SG2 7/16/2003 5:00 AMA112 SG20307BGJD 2147483647 67481737 197/0142277 SG2CONN1PEPI SG2HK201RE2B

The only problem is, I can't work out how to convert the Called Number from exponential format back to decimal and then to a string.

Regards

John

[Tim D Fulford]

IBM (well Informix if you are using V5) do not really have great formatting facilities as it looks at your TERM, COLUMMS & ROWS. As has been mentioned above it is best to "post format it" with awk etc... or my favorite perl

see http://forums.itrc.hp.com/cm/QuestionAnswer/1,,0x1c204b3ef09fd611abdb0090277a778c,00.html

I've also attached the ifxperf.tar.gz.uue here.

The script fires some sql at the database & perl reads the results & formats them.

Regards

Tim