[SOLVED] (JOINS) (SQL) (EXCEL OUTPUT) Cant export report in any format?

I'm confused (a lot of that going on around here lately)... Are you wanting this report to output in two different reports within one procedure?

Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005

Ignored post by Doug posted

Waz

Expert

posted

A couple of things.

1. You need a semi-colon after your SQL as a terminator.

2. You are PCHOLDing, then tableing from a HOLD file that, from the code you posted, has not been created.

You should also check your joins as you have duplicates from one of your data sources.

Waz...

Prod:	WebFOCUS 7.6.10/8.1.04	Upgrade:	WebFOCUS 8.2.07	OS:	Linux	Outputs:	HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006

Ignored post by Waz posted

Member

posted

Sorry for the confusion Doug, I only want this report to output in one report in excel, and the output script

*TABLE FILE
ON TABLE PCHOLD
END
-RUN
TABLE FILE HOLD
PRINT *
ON TABLE PCHOLD FORMAT EXL2K
END
-RUN
*

was something I stole from another post about exporting sql tables to excel but obviously didnt work for me and so any example of how to write that piece so it does export would be helpful.

Waz, Do you mean that at the top where I have SQL at the start I need the semicolon? I have put a semicolon after "NN.ADCOD3 ASC" it just hasn't copied it through to here. And the report works fine for 12 joins, and then stops working with that error code. If I run it for only 12 joins and substitute out the last join for another from later on in the script it still runs fine for each one proving that there are no duplicate records...

This message has been edited. Last edited by: Michael_86,

Webfocus 7.7.03
Windows
Excel

Posts: 21 | Registered: February 08, 2013

Ignored post by Michael_86 posted

Ram Prasad E

Master

posted

You need to change PCHOLD to HOLD as shown below.

ORDER BY
NN.ADCOD3 ASC
;
TABLE FILE
ON TABLE HOLD AS HLD_1
END
-RUN
TABLE FILE HLD_1
PRINT *
ON TABLE PCHOLD FORMAT EXL2K
END

This will export your sql result set to Excel.

Thanks,
Ram

WebFOCUS 8.1.05
Windows
http://ibiwebfocus.wordpress.com
https://www.facebook.com/groups/ibi.webfocus/

Posts: 542 | Location: Dearborn, MI | Registered: June 03, 2009

Ignored post by Ram Prasad E posted

Member

posted

Thanks for the code Ram,

Unfortuntely the following error msg comes up

Your request did not return any output to display.
Possible causes:
- No data rows matched the specified selection criteria.
- Output was directed to a destination such as a file or printer.
- An error occurred during the parsing or running of the request.

--------------------------------------------------------------------------------

0 NUMBER OF RECORDS IN TABLE= 27 LINES= 27
0 NUMBER OF RECORDS IN TABLE= 27 LINES= 27
0 NUMBER OF RECORDS IN TABLE= 27 LINES= 27
0 NUMBER OF RECORDS IN TABLE= 27 LINES= 27
0 NUMBER OF RECORDS IN TABLE= 27 LINES= 27
0 NUMBER OF RECORDS IN TABLE= 27 LINES= 27
0 NUMBER OF RECORDS IN TABLE= 27 LINES= 27
0 NUMBER OF RECORDS IN TABLE= 27 LINES= 27
0 NUMBER OF RECORDS IN TABLE= 27 LINES= 27
0 ERROR AT OR NEAR LINE 258 IN PROCEDURE FOCSQL01FOCEXEC *
(FOC002) A WORD IS NOT RECOGNIZED: FILE
BYPASSING TO END OF COMMAND
(FOC009) INCOMPLETE REQUEST STATEMENT
0 ERROR AT OR NEAR LINE 52 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: HLD_1
BYPASSING TO END OF COMMAND

Webfocus 7.7.03
Windows
Excel

Posts: 21 | Registered: February 08, 2013

Ignored post by Michael_86 posted

FrankDutch

Virtuoso

posted

before the FIRST line of your SQL query you need to setup a connection
In that connection you also tell the application what it should do with the output.
Most of the time that is SQLOUT.
if you search this forum for that special word, you will find several examples
I would suggest you first start with a simple SQL script from one table with a few columns
If that works, you can make it a bit more complex.

As said before, at the end of your SQL script you need to add a semicolon. And and END
then you can start with the webfocus commands to read the SQLOUT and create your report.

	*Frank*
	prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7

Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006

Ignored post by FrankDutch posted

Member

posted

quote:
Originally posted by FrankDutch:
before the FIRST line of your SQL query you need to setup a connection

I don't know what the SQL Engine that im using is, but the report works as is...

quote:
Originally posted by FrankDutch:
In that connection you also tell the application what it should do with the output.
Most of the time that is SQLOUT.
if you search this forum for that special word, you will find several examples

quote:
Originally posted by Michael_86:And after reading several discussions

as per above I did search on SQLOUT, and SQL Engine, and SQL to EXCEL etc etc etc but without an SQL Engine part of the script that I don't have I have no idea what I need to include or not too include...

quote:
Originally posted by FrankDutch:I would suggest you first start with a simple SQL script from one table with a few columns
If that works, you can make it a bit more complex.

My script will output to html correctly when I have upto and including 12 joins, on the 13th join it fails to work. And lets be honest my SQL script is a very simple table to be testing this on.

quote:
Originally posted by FrankDutch:As said before, at the end of your SQL script you need to add a semicolon.

quote:
Originally posted by Michael_86:I have put a semicolon after "NN.ADCOD3 ASC" it just hasn't copied it through to here

quote:
Originally posted by FrankDutch:And and END

Have done this and there was no change.

So going on from here, how do I export my report to export? and is there a limit on maximum number of joins before the whole lot falls over?

Webfocus 7.7.03
Windows
Excel

Posts: 21 | Registered: February 08, 2013

Ignored post by Michael_86 posted

Member

posted

Oh and I can bypass the sql passthru and access the metadata directly as per this article that I found during my searches

http://forums.informationbuild...?r=32110538#32110538

This message has been edited. Last edited by: Michael_86,

Webfocus 7.7.03
Windows
Excel

Posts: 21 | Registered: February 08, 2013

Ignored post by Michael_86 posted

Member

posted

Ok So I have managed to get half my report to export to excel or at least I think so by putting this code at the bottom

TABLE
ON TABLE HOLD AS TABLEF
END

TABLE FILE TABLEF
PRINT *
ON TABLE HOLD FORMAT EXL2K
END
-RUN

however I now get told

Your request did not return any output to display.
Possible causes:
- No data rows matched the specified selection criteria.
- Output was directed to a destination such as a file or printer.
- An error occurred during the parsing or running of the request.

--------------------------------------------------------------------------------

0 NUMBER OF RECORDS IN TABLE= 27 LINES= 27
0 NUMBER OF RECORDS IN TABLE= 27 LINES= 27
0 NUMBER OF RECORDS IN TABLE= 27 LINES= 27
0 NUMBER OF RECORDS IN TABLE= 27 LINES= 27
0 NUMBER OF RECORDS IN TABLE= 27 LINES= 27
0 NUMBER OF RECORDS IN TABLE= 27 LINES= 27
0 NUMBER OF RECORDS IN TABLE= 27 LINES= 27
0 NUMBER OF RECORDS IN TABLE= 27 LINES= 27
0 NUMBER OF RECORDS IN TABLE= 27 LINES= 27
0 NUMBER OF RECORDS IN TABLE= 27 LINES= 27
0 NUMBER OF RECORDS IN TABLE= 27 LINES= 27
0 EXL2K FILE SAVED ...

Why has it saved and not opened in excel as when I run a report from report writer it opens it in excel....

Also does anyone know if there is a limit to the maximum number of joins?

Webfocus 7.7.03
Windows
Excel

Posts: 21 | Registered: February 08, 2013

Ignored post by Michael_86 posted

Member

posted

Never mind about the excel output I have finally cracked that part

So about the multiple joins?

Webfocus 7.7.03
Windows
Excel

Posts: 21 | Registered: February 08, 2013

Ignored post by Michael_86 posted

Member

posted

And FINALLY I have the report I was trying to create,

. Just incase anyone is interested in the code of how I managed it then it is below.

By the way I still have no idea why the "Magical" 13th join creates duplicates (maybe just unlucky) but I still have all my joins and the same data being extracted and there are no duplicates (I have just tested and verified the output).

SET PAGE-NUM=NOLEAD
SQL
SELECT DISTINCT
PART1.ACCOUNT,
PART1.SUBACCOUNT,
PART1.FINANCE,
PART1.BIRMINGHAM,
PART1.BRISTOL,
PART1.CHESTERFIELD,
PART1.COATBRIDGE,
PART1.CYCLONE,
PART1.ELGIN,
PART1.BRISTOLGALVANISING "BRISTOL GALVANISING",
PART1.BRISTOLMANUFACTURING "BRISTOL MANUFACTURING",
PART1.NEWCASTLE,
PART1.OAKTREE,
PART1.JREYNOLDS "J REYNOLDS",
PART1.PLYMOUTH,
PART2.PRESTON,
PART2.SOUTHAMPTON,
PART2.FENCING,
PART2.LEEDS,
PART2.HULL,
PART2.FIXCENTRE,
PART2.REFLEX,
PART2.STAMFORD
FROM
(SELECT DISTINCT
NN.ADCOD2 ACCOUNT,
NN.ADCOD3 SUBACCOUNT,
NN.FINC FINANCE,
N04.NNLAD.ADAOB BIRMINGHAM,
N01.NNLAD.ADAOB BRISTOL,
N36.NNLAD.ADAOB CHESTERFIELD,
N15.NNLAD.ADAOB COATBRIDGE,
N19.NNLAD.ADAOB CYCLONE,
N17.NNLAD.ADAOB ELGIN,
N33.NNLAD.ADAOB BRISTOLGALVANISING,
N41.NNLAD.ADAOB BRISTOLMANUFACTURING,
N12.NNLAD.ADAOB NEWCASTLE,
N28.NNLAD.ADAOB OAKTREE,
N29.NNLAD.ADAOB JREYNOLDS,
N03.NNLAD.ADAOB PLYMOUTH
FROM
(SELECT DISTINCT
N99.NNLAD.ADCOD1 ADCOD1,
N99.NNLAD.ADCOD2 ADCOD2,
N99.NNLAD.ADCOD3 ADCOD3,
N99.NNLAD.ADAOB FINC
FROM
NNNAD99 N99
WHERE
N99.NNLAD.ADCOD2 < '8100' AND
N99.NNLAD.ADCOD2 >= '8000' AND
N99.NNLAD.ADCOD3 NOT LIKE '%R' AND
N99.NNLAD.ADCOD3 NOT LIKE ' ' AND
N99.NNLAD.ADCOD4 LIKE ' '
) NN
LEFT OUTER JOIN NNNAD01 N01 ON (NN.ADCOD1 = N01.NNLAD.ADCOD1 AND NN.ADCOD2 = N01.NNLAD.ADCOD2 AND NN.ADCOD3 = N01.NNLAD.ADCOD3)
LEFT OUTER JOIN NNNAD04 N04 ON (NN.ADCOD1 = N04.NNLAD.ADCOD1 AND NN.ADCOD2 = N04.NNLAD.ADCOD2 AND NN.ADCOD3 = N04.NNLAD.ADCOD3)
LEFT OUTER JOIN NNNAD36 N36 ON (NN.ADCOD1 = N36.NNLAD.ADCOD1 AND NN.ADCOD2 = N36.NNLAD.ADCOD2 AND NN.ADCOD3 = N36.NNLAD.ADCOD3)
LEFT OUTER JOIN NNNAD41 N41 ON (NN.ADCOD1 = N41.NNLAD.ADCOD1 AND NN.ADCOD2 = N41.NNLAD.ADCOD2 AND NN.ADCOD3 = N41.NNLAD.ADCOD3)
LEFT OUTER JOIN NNNAD15 N15 ON (NN.ADCOD1 = N15.NNLAD.ADCOD1 AND NN.ADCOD2 = N15.NNLAD.ADCOD2 AND NN.ADCOD3 = N15.NNLAD.ADCOD3)
LEFT OUTER JOIN NNNAD19 N19 ON (NN.ADCOD1 = N19.NNLAD.ADCOD1 AND NN.ADCOD2 = N19.NNLAD.ADCOD2 AND NN.ADCOD3 = N19.NNLAD.ADCOD3)
LEFT OUTER JOIN NNNAD17 N17 ON (NN.ADCOD1 = N17.NNLAD.ADCOD1 AND NN.ADCOD2 = N17.NNLAD.ADCOD2 AND NN.ADCOD3 = N17.NNLAD.ADCOD3)
LEFT OUTER JOIN NNNAD33 N33 ON (NN.ADCOD2 = N33.NNLAD.ADCOD2 AND NN.ADCOD3 = N33.NNLAD.ADCOD3)
LEFT OUTER JOIN NNNAD12 N12 ON (NN.ADCOD1 = N12.NNLAD.ADCOD1 AND NN.ADCOD2 = N12.NNLAD.ADCOD2 AND NN.ADCOD3 = N12.NNLAD.ADCOD3)
LEFT OUTER JOIN NNNAD28 N28 ON (NN.ADCOD1 = N28.NNLAD.ADCOD1 AND NN.ADCOD2 = N28.NNLAD.ADCOD2 AND NN.ADCOD3 = N28.NNLAD.ADCOD3)
LEFT OUTER JOIN NNNAD29 N29 ON (NN.ADCOD1 = N29.NNLAD.ADCOD1 AND NN.ADCOD2 = N29.NNLAD.ADCOD2 AND NN.ADCOD3 = N29.NNLAD.ADCOD3)
LEFT OUTER JOIN NNNAD03 N03 ON (NN.ADCOD1 = N03.NNLAD.ADCOD1 AND NN.ADCOD2 = N03.NNLAD.ADCOD2 AND NN.ADCOD3 = N03.NNLAD.ADCOD3)
WHERE
NN.ADCOD2 < '8100' AND
NN.ADCOD2 >= '8000' AND
NN.ADCOD3 NOT LIKE '%R'
) PART1
LEFT OUTER JOIN
(SELECT DISTINCT
NN.ADCOD2 ACCOUNT,
NN.ADCOD3 SUBACCOUNT,
N10.NNLAD.ADAOB PRESTON,
N02.NNLAD.ADAOB SOUTHAMPTON,
N24.NNLAD.ADAOB FENCING,
N39.NNLAD.ADAOB LEEDS,
N30.NNLAD.ADAOB HULL,
N08.NNLAD.ADAOB FIXCENTRE,
N26.NNLAD.ADAOB REFLEX,
N06.NNLAD.ADAOB STAMFORD
FROM
(SELECT DISTINCT
N99.NNLAD.ADCOD1 ADCOD1,
N99.NNLAD.ADCOD2 ADCOD2,
N99.NNLAD.ADCOD3 ADCOD3,
N99.NNLAD.ADAOB FINC
FROM
NNNAD99 N99
WHERE
N99.NNLAD.ADCOD2 < '8100' AND
N99.NNLAD.ADCOD2 >= '8000' AND
N99.NNLAD.ADCOD3 NOT LIKE '%R' AND
N99.NNLAD.ADCOD3 NOT LIKE ' ' AND
N99.NNLAD.ADCOD4 LIKE ' '
) NN
LEFT OUTER JOIN NNNAD10 N10 ON (NN.ADCOD1 = N10.NNLAD.ADCOD1 AND NN.ADCOD2 = N10.NNLAD.ADCOD2 AND NN.ADCOD3 = N10.NNLAD.ADCOD3)
LEFT OUTER JOIN NNNAD02 N02 ON (NN.ADCOD1 = N02.NNLAD.ADCOD1 AND NN.ADCOD2 = N02.NNLAD.ADCOD2 AND NN.ADCOD3 = N02.NNLAD.ADCOD3)
LEFT OUTER JOIN NNNAD24 N24 ON (NN.ADCOD1 = N24.NNLAD.ADCOD1 AND NN.ADCOD2 = N24.NNLAD.ADCOD2 AND NN.ADCOD3 = N24.NNLAD.ADCOD3)
LEFT OUTER JOIN NNNAD39 N39 ON (NN.ADCOD1 = N39.NNLAD.ADCOD1 AND NN.ADCOD2 = N39.NNLAD.ADCOD2 AND NN.ADCOD3 = N39.NNLAD.ADCOD3)
LEFT OUTER JOIN NNNAD30 N30 ON (NN.ADCOD1 = N30.NNLAD.ADCOD1 AND NN.ADCOD2 = N30.NNLAD.ADCOD2 AND NN.ADCOD3 = N30.NNLAD.ADCOD3)
LEFT OUTER JOIN NNNAD08 N08 ON (NN.ADCOD1 = N08.NNLAD.ADCOD1 AND NN.ADCOD2 = N08.NNLAD.ADCOD2 AND NN.ADCOD3 = N08.NNLAD.ADCOD3)
LEFT OUTER JOIN NNNAD26 N26 ON (NN.ADCOD1 = N26.NNLAD.ADCOD1 AND NN.ADCOD2 = N26.NNLAD.ADCOD2 AND NN.ADCOD3 = N26.NNLAD.ADCOD3)
LEFT OUTER JOIN NNNAD06 N06 ON (NN.ADCOD1 = N06.NNLAD.ADCOD1 AND NN.ADCOD2 = N06.NNLAD.ADCOD2 AND NN.ADCOD3 = N06.NNLAD.ADCOD3)
WHERE
NN.ADCOD2 < '8100' AND
NN.ADCOD2 >= '8000' AND
NN.ADCOD3 NOT LIKE '%R'
) PART2
ON (PART1.ACCOUNT = PART2.ACCOUNT AND PART1.SUBACCOUNT = PART2.SUBACCOUNT)
ORDER BY
PART1.SUBACCOUNT ASC
;
TABLE
ON TABLE HOLD AS HLD01
END

TABLE FILE HLD01
PRINT *
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE NOTOTAL
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET SQUEEZE ON
ON TABLE SET EMPTYREPORT ON
ON TABLE SET HTMLCSS ON
ON TABLE SET HTMLENCODE ON
ON TABLE SET STYLE *
INCLUDE=IBFS:/CFG/FILE/IBI_HTML_DIR/javaassist/intl/EN/ENIADefault_combine.sty,$
ENDSTYLE
END

-RUN

Webfocus 7.7.03
Windows
Excel

Posts: 21 | Registered: February 08, 2013

Ignored post by Michael_86 posted