sql select - output to one line.

[Roshintosh]

when i run a select sql on the tandem on the telnet emulator, i.e.
select * from table1, it outputs the results over several lines,
which is a bit hard to read, and even harder to get the results into
an excel spreadsheet

How would you output the results to a file, and have each row
written to one line (so that it's very easy to import into excel)

thanks
Roshintosh

[Keith Dick]

I am assuming you are asking about SQL/MP and SQLCI. If you are talking about SQL/MX and mxci, post again to say that is your environment.

There are a few things you can do.

1. If you don't need all of the columns to be displayed, you could list just the ones you need, which might make the line short enough to avoid the problem. Yes, it is both obvious and not applicable in many cases, but I did not want to overlook it.

2. I believe it is possible to use the command SET LAYOUT RIGHT_MARGIN 255; to make the logical line be 255 bytes long, then use the OUT_REPORT command to direct the output to an entry-sequenced file whose record length is defined as being at least 255. That would give you a bit longer line to work with, but still could be not enough for getting all of a table with many columns to display on one line. I can never be sure which NonStop programs allow output to entry-sequenced file and which do not. I am pretty sure that SQLCI does allow output to an entry-sequenced file, but if it does not accept entry-sequenced files in OUT_REPORT, then this won't work.

3. You could write a simple program that knows how many lines each row of the table takes up and have the program join the lines together. Suppose the rows take three lines to display. The program would read three lines, concatentate them, and write the combined line to an output file. If you are going to send that file to a PC to load into Excel, you might make the program write an unstructured file and have the program append carriage return and linefeed characters to the end of each combined line. Then you could transfer the file to the PC in binary mode and use it with no further changes.

4. You could explicitly list each column of the table in the SELECT list, and at the points where it would move to another line, insert some special literal in the SELECT list. Then after getting the data to the PC, use an editor that has a global search and replace that allows you to replace that literal followed by a carriage return and line feed with a blank. That would join all the lines for one row into a single line, then you could load the modified file into Excel.

There might be other ways to solve the problem, but these are the ones that occur to me immediately. If none of them are suitable for your case, post again and explain what it is about your problem that makes none of these ideas work, and we'll try to think of something that will work for you.

[dimandja]

You should be able to explicitly insert commas between results (Excel should like that). I haven't tried it on Tandem, but at least SQL/MX should be able to support something like this:

SELECT
col1 + ',' +
col2 + ',' +
FROM
table1

[Harald]

I think you talk about the wrapping of lines in SQLCI.
There is no general solution for arbitrary long lines.
If the resulting lines are no longer than aboaut 250 characters you
can simply output to an edit file (code 101).
If the result line are longer (not more than about 4000 bytes) you can
use relative files.
1) Create a relative file like this (vary rec size and extent sizes
according to your needs):
FUP
> reset
> set type r
> set rec 4000
> set ext(1000,1000)
> set maxextents 500
> create xyz

2) The sqlci obey file looks something like this:
SET LAYOUT PAGE_LENGTH ALL ;
SET SESSION LIST_COUNT 0 ;
SET SESSION WRAP OFF ;
SET STYLE HEADINGS OFF ;

fup purgedata xyz;

select ... ;

out_report xyz;
list all;

3) FTP the resulting file xyz in ASCII mode

[wbreidbach]

The way I use to import contents of SQL tables into Excel or other tools like that is ODBC/MX (recommended) or ODBC/MP (possible).
ODBC/MX is able to access SQL/MP tables as long as you have SQL/MX installed and created an SQLMP ALIAS for the table. In addition you need a driver on the workstation and a connection to the catalog in question. Then you can directly access your table via Excel using the ODBC connection and there would be no need to deal with sequential files and all that stuff.

[Keith Dick]

As far as I know, SQLCI has a strict upper limit of 255 in the length of lines it will produce, so I do not believe sending the output to an Enscribe file of record length 4000 will help very much. If I'm wrong, I would appreciate being corrected on that. The setting of WRAP, as far as I understand, does not make SQLCI write longer lines. It just controls whether lines are truncated or continued on the next line when they exceed the RIGHT_MARGIN setting, which I'm pretty sure can be no more than 255.

The limit on the size of a line in an Edit file is a bit soft -- it actually depends on the data in the line. Many programs that read and write Edit files impose a strict 239 character limit so as not to have to deal with the possibility that some longer lines are acceptable while others are not.

I'm not sure why you would use relative instead of entry-sequenced, but I suppose relative would work. I seem to remember some difference in behaviour between the two file types for records of length 0 (entirely blank lines), which might be important in some cases, but probably not in yours.

I've never used ftp to try to transfer a structured file to a text file on a PC. Maybe it works (I believe I've seen it recommended before), but I'm a cautious guy and would be a bit wary of that. Do make sure you test the procedure carefully before relying on it.

See my other reply to your post for my suggestions.

[Bill Honaker]

If you have SQL/MX installed, a simpler option is to create SQL/MP
Aliases for each MP table, and use mxci (in OSS) to run the query.
Assuming you have the query in a file called query.sql. the following
at an OSS prompt works:

> mxci <query.sql >query.out.txt

mxci writes all of the data on a single 'line' (with a newline at the
end of each). I have not found any queries that use more than one
line of output.

If you want to see this in Windows, you may need to do an ASCII
transfer to add a Carriage Return with each New Line.
.

[Hemanth]

On Jun 11, 7:57 pm, Bill Honaker <no_spam_bhonaker__@x_i_d.com> wrote:
> On Sat, 9 Jun 2012 00:38:51 -0700 (PDT), Roshintosh
>

I normally use the way Harold suggested and get the desired results,
Interesting what Bill has said, will give a try

I guess there are some tools even SQLmagic from Merlon to view the
tables and get them in a file