Processing a result set output to a Table

[sonofsa]

I have the following SQL*Plus query (executed via a Cobol program) that I'd like to output to a Table:

REPHEADER CENTER 'CUSTOMER NAME AND ADDRESS' SKIP2
COLUMN CLIENT_ID HEADING 'CUSTOMER #'
COLUMN CUST_NAME HEADING 'CUSTOMER NAME'
COLUMN ADD_1 HEADING 'ADDRESS LINE 1'
COLUMN ADD_2 HEADING 'ADDRESS LINE 2'
COLUMN ADD_3 HEADING 'ADDRESS LINE 3'
SELECT A.CLIENT_ID, A.CUST_NAME, A.ADD_1, A.ADD_2, A.ADD_3
FROM CUSTOMER A
WHERE CUST_TYPE LIKE 'A%'
ORDER BY A.CUST_ID;

Any help would be appreciated - Thanks in advance.

Rick

 

[ChrisHunt]

Define "output to a table".

Do you mean you want to create a new database table filled with the values you've selected, or perhaps that you want to insert that data into an existing table?

Or do you mean nothing at all to do with database tables, and that you just want to present the results of your query in some kind of tabular form?

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[fredericofonseca]

Sonofosa --- looks like you are still trying to get the formatted output onto your Cobol program as per your thread thread1662-1717209 - if that is the case there is no point in creating new threads as it won't get you the answer you wish for.

If on the other hand your question is different then could you be more precise on what you are attempting to do

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[sonofsa]

Chris,

Thank you for your response - a table variable is what I was looking for.

Frederico:

You have been following me around every since I started posting, making assumptions and alway sending me links. I was a member previously (under a different handle), circa 2002 (for almost 8 years) and I developed a web based Direct Marketing tool that generated SQL from user enter free form Marketing data. Most of the SQL help came from very smart people at Tek-Tips. A youtube demo of the tool:

http://www.youtube.com/watch?v=d9bKlQIVLoA

Rick

 

[fredericofonseca]

then lets not make any assumptions.

What operating system(s) you using
What COBOL vendor and version
What Oracle client version
What SQL*Plus version

Will you be doing this across multi platforms or are you developing for a single OS

What is the expected output of your script should it be executed from a command line SQL*Plus, and what is the expected output should it be executed from a COBOL program (on which case only the Select bit of the SQL*Plus code you gave us can be executed as the remaining parts are SQL*Plus specific and can not be done directly from COBOL)

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[sonofsa]

Frederico,

I don't have all the facts - it's a IBM Z/OS operating system, using Oracle's PRO Cobol (version 10.1). As I've stated previously, I can produce a report with all SQL*Plus features, control breaks and headings, column name changes, report titles, computes etc. I can do this running a Cobol program in batch mode (the online submission of my tool submits JCL to the internal reader that triggers the Job - output must be retrieved via TSO). This satisfies one mode of submission, the other mode (online) is what I'm looking to accomplish. The approach I'm currently exploring is to save the generated Batch output to a Database table, then retrieve and display (with vertical and horizontal scrolling) via the tool. Another point, the SQL statements are all dynamically generated. Lastly, I'm new to Oracle.

Thanks,
Rick

 

[fredericofonseca]

both online (CICS) and batch (TSO) can behave the same for this particular purpose.

Define the JCL you submit from either CICS or TSO so that it outputs the result of your sql*plus script to a temp dataset, and then a very last step on the JCL reads this dataset as is and loads onto a database table.

Such table would normally require a structure similar to the following

request_id - used so the submitter knows the request to read back to display
line_id - used to order the lines
report_line - to avoid having to define a clob, and depending on your own requirements I would advise that this is made up of several varchar(2000) which you populate as needed

An additional table is normally used to contain the request_id, description and other data that is useful to allow the user to identify the report they submitted.

The above is a approach that is used by many applications regardless of what tool produces the report - it works.

As I've stated previously, I can produce a report with all SQL*Plus features, control breaks and headings, column name changes, report titles, computes etc. I can do this running a Cobol program in batch mode

Curious about this - could you please post here a small working JCL, corresponding COBOL program and sqlplus script


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[fredericofonseca]

So sonofosa any advance on this?



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[sonofsa]

Frederico,

The customer is quite happy with the batch option - so I've moved on to other tasks.

Rick