Hi all,

Firstly I'd like to apologise in advance as I'm probably a DBA's worst nightmare. I'm an accountant and I recently began report writing for my last Finance department. Small time reports using PL/SQL in SQL Developer. I have now moved to a new job and I have been given DB2 Web Query (Web Focus) and shown the basics of using the report assistant and the info assistant. However I'm not overly keen on creating reports this way as I find the program a tad slow and clunky and would prefer to write the script (as this is what I did in SQL Dev).

So I have two questions:

Firstly is there a simple guide of this is Focus syntax and it equals this in SQL syntax? (i.e Select = Table File) (and yes I have seen lots of information about SQL Passthrough, but I dont have access to this)

Secondly I have been attempting to create a report in the Info Assist that joins two tables together into one report that has values from both tables based on common set of labels.

I have got the joins in place and working however to have both sets of values I have to use "Document View" and this means having two tables rather than one (which I can live with if I must). However, and finally the crux of the question, how do I export this to one excel tab?! I keep getting an error when attempting to run with an output file of either Excel 2000 or 2007.

Again sorry for the mundane question hopefully with all your help I will pick up this as quickly as I did with SQL Dev.

Thanks in advance for your help

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

Ok so I have answered my first point, I've got admin access to web query which now allows me to write / edit code directly rather than using info assist, which helped me solve my dilema of being able to write a report with values from two tables on one set of labels.

So now how could I do that in info assist (just because it is really annoying that I couldn't and had to figure out the coding after getting admin rights)?

And is there a PL/SQL to Focus dictionary for lack of a better phrase?

There are many parallels with SQL and FOCUS code.

TABLE FILE {MASTER FILE} = SELECT FROM {table}
WHERE {condition} = WHERE {condition}
IF {condition} = WHERE {condition}
PRINT {fields} = {No GROUP BY}
SUM {fields} = GROUP BY {Field list}
BY {Field} = ORDER BY {Field list}

I would suggest having a look at the documentation on general syntax, and also how to join files, the JOIN command. There are many post also in this form about JOIN.

Selecting and getting the data is the first step, building a good looking report the second.
SQL can help you with the first.
But you still need to build the report.

How about some basic training?
How about reading some manuals ?

Thanks Waz I can see some parallels but just wondered if anyone had a definitive list, to make it easier to move across for novices like myself. (The last IT course was at college 7 years ago)

As I said I worked out the join syntax eventually but am still frustrated by the lack of functionality in info assist to create queries on one report from multiple tables (but I`d prefer to write the queries anyway than rely on what is effectively microsoft query wizard!)

As for only being able to get the data is a start and that I need to be able to create a pretty report, that`s not the case at all.

I only want to use this to create data queries from multiple tables to extract into excel to help create reconciliations, to speed up production of existing accounts, and to create lookup tables so for example I can take all raw material product codes from a stock table and join it to completed purchase orders to give me the latest buying price, which in turn I can use to lookup against a pre existing spreadsheet that is updating 12 others. I only need the data I tart it up elsewhere.

And I had a look for a webfocus for dummies book could only find sql books that to be honest went mostly over my head as im not a programmer nor have any aspirations to be. I learnt my limited but very useful amount of sql through google, forums and websites such as tech on the net. So am trying to do the same with focus hence asking these questions.

I don't use Info Assist, but understand that is it for simpler queries, and is menu driven, hence the limited functionality.

I think you need to do some playing to get a better understanding and familiarity with the product. Does anyone else have WebFOCUS knowledge where you are ?, they may be able to give hints.

Another option is to try and get a copy Developer Studio.

I think playing around is going to be my best bet as the "training" I had was how to use report assist after which I was told use info it`s just the same.... I don`t think I have much support available from my IT either as when asked if anyone had a schema for our database I was informed tougher reports are out sourced.

So in short no help to be had.

Though I will see if I can get the developer software...

If you get in contact with Ibi they can send you the manuals and give you access to the PDF help software.
It can fill your desk and you have enough to read for the next year.