Hello, when I first started on webfocus (last december) someone told me that one of the biggest WF versatilities is the capacity to connect to multiple data sources. So I want to take that to a next level. Here's my issue:

I need to make 30 reports. Each and one of them with the option (via parameters previously chosen) to choose between multiple data sources.

So I want to implement some code that builds a compilation of the data in all the data sources based on a single query.

Eeach Data source is represented with an individual connection.

So my final goal is to -include this code into any fex and be able to query that common data source.

Any Ideas?

I already have some code I'll be posting later. Don't wish to do so now since I want to get some new ideas and know if any of you has already done this before (so I won't reinvent sugared water, lol).

For all your help, pretty thankfull.

This message has been edited. Last edited by: <José Andrés Vargas Aguilar>,

Personally I am not so sure that is such a good idea. One must be very careful as to how to structure data retrieval across multiple data sources in order to avoid impacting performance negatively.

quote:

I need to make 30 reports. Each and one of them with the option (via parameters previously chosen) to choose between multiple data sources.

Are all of those reports different from the others?
Do your data sources return the same columns/fields?
how will you validate that reports 1, 5 and 11 can select only data sources A, F, H and P and not others that may not return the same expected fields.

Now, if you are talking about a "meta report" in which users will select whatever table and set of fields from your internal query, then why would you need 30 reports? Wouldn't you be able to do that with a single yet complext report code? I don't know, it seems to me like trying to re-invent what already exists (with some limitations of course) which is called Report Assistant and/or Infoassist.

Anyway, if you'll continue with your project, please DO NOT attempt to join your disparate data sources in a single data retrieval strucute unless they all relate to the same connection. Though it is true that WebFOCUS syntax will allow you do to that in the code, it is not "semantically correct" and WebFOCUS will have to resort to not so efficient methods as to being able to retrieve and join all of the data as you wish.

Thanks Nijsden.

Yes, these are 30 different reports we're talking about. All of them completely independent between them. The fact that they're so many doesn't really mather I only mentioned it to justify my need to use a -include instead of repeating code on each fex.

All the data sources use exactly the same schemas, tables, columns, they were all built based on the same Model Data Base. What differences them is only the data they contain. So I won't have issues on that.


To give y'all more info....

We already have these 30 reports working. How? Well, the business I work on has a client that aglomerates lots of credit companies. So, each company has their own ERP developed by us. Each one of these companies has an Oracle10g DBMS over which they work. And there, they run these reports locally, being able to view the data from their own company. Now, our client's "mothership office" wishes to be able to view these data from any of the companies. So, we have to make our fex's able to connect to the other DBMS'. The necessary infrastructure for that to be posible has already been implemented.

Since the table schemas are all identical, can you use the SET SERVER command to direct your queries to the user-selected database? This approach will require removing any CONNECTION= or SERVER= attributes from your ACX files.

SET SQLENGINE = SQLORA
 SQL SQLORA SET SERVER &SERVERNAME ;
END

And the &SERVERNAME variable is (I guess) one of my current console connections???


I'll give it a try.

Thanks Dan.

José,

I find it very refreshing that someone, who has only been using the product for a few months, has obviously done some checking beforehand and learnt about the various components of WF, such that responses are understood

Some of us can be quick to chastise those who don't prepare but rarely offer praise, when that preparation is obvious.

T

Well said, Tony!

I've seen other posts by José Andrés and it shows that he prepares for and tests "obvious" scenarios before asking for help, therefore making use of this forum for what I assume was designed for, which is providing support and sharing knowledge instead of baby-sitting those who don't even care to take a look at the product manuals in the first place.

Thanks for the compliments guys! I greatly appreciate them.

I'm currently working on something else and haven't been able to test Dan's suggestion.

Nevertheless, here's the code I mentioned on the first post. It was developed by someonel else over here and the comments are in Spanish. I'll take a while to translate to English.

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

-* File bkwunion.fex
-* SCRIPT PARA RECUPERAR INFORMACION SOLICITADA EN UN QUERY DE VARIAS CONEXIONES
-* -------------------------------------------------------------------------------
-SET &BLANK = ' ';
-SET &INICIO = 0;
-DEFAULT &CONNSTR = 'BANKW0000';
-DEFAULT &REPORTE = 'rep1.fex';
-* -------------------------------------------------------------------------------
-* VARIABLE DONDE SE GUARDA EL QUERY EN SQL QUE SE EJECUTARA EN CADA UNA DE LAS
-* CONEXIONES.
-* -------------------------------------------------------------------------------
-DEFAULT &QUERY = '';

-* -------------------------------------------------------------------------------
-* CONNECT ES EL ARCHIVO DONDE ESTAN LAS CONEXIONES QUE PUEDEN EMPLEARSE
-* -------------------------------------------------------------------------------
FILEDEF CONNECT DISK C:\ibi\apps\test\conexiones.txt
-* -------------------------------------------------------------------------------
-* FEXFILE ES EL NOMBRE DEL ARCHIVO QUE SE CONTIENE EL SCRIPT PARA RECUPERAR
-* DATOS DE VARIAS CONEXIONES QUE LUEGO SE GUARDAN EN UN TEMPORAL LLAMADO 'DATOS'
-* -------------------------------------------------------------------------------
FILEDEF FEXFILE DISK C:\ibi\apps\test\&REPORTE

-RUN
-* -------------------------------------------------------------------------------
-* SE INICIA LA GENERACION DEL ARCHIVO FEX
-* -------------------------------------------------------------------------------

-*READ CONNECT &CONNSTR
-*REPEAT LOOP WHILE &IORETURN EQ 0;
-*TYPE ESTABLECIENDO CONEXION A: &CONNSTR
-*READ CONNECT &CONNSTR
-*LOOP

-*EXIT

-* -------------------------------------------------------------------------------
-* LOOP QUE GENERA EL CODIGO PARA RECUPERAR LA INFORMACION DE LAS DIFERENTES
-* CONEXIONES
-* -------------------------------------------------------------------------------
-READ CONNECT &CONNSTR
-REPEAT LOOP WHILE &IORETURN EQ 0;
-TYPE ESTABLECIENDO CONEXION A: &CONNSTR
-SET &INICIO = &INICIO + 1;
-SET &FEX = 'ENGINE SQLORA SET DEFAULT_CONNECTION ' |&CONNSTR;
-WRITE FEXFILE NOCLOSE &FEX
-SET &FEX = 'SQL SQLORA PREPARE SQLOUT FOR';
-WRITE FEXFILE NOCLOSE &FEX
-* ESTE ES UN EJEMPLO:
-SET &QUERY = 'SELECT COUNT(*) AS CONTEO,''' | &CONNSTR |
- ''' AS CONEXION FROM CIFGENERALES;';
-WRITE FEXFILE NOCLOSE &QUERY
-SET &FEX = 'END';
-WRITE FEXFILE NOCLOSE &FEX
-SET &FEX = 'TABLE FILE SQLOUT';
-WRITE FEXFILE NOCLOSE &FEX
-SET &FEX = 'PRINT * ';
-WRITE FEXFILE NOCLOSE &FEX
-SET &FEX = 'ON TABLE HOLD AS ''HOLD' || &INICIO || ''';
-WRITE FEXFILE NOCLOSE &FEX
-SET &FEX = 'END';
-WRITE FEXFILE NOCLOSE &FEX
-WRITE FEXFILE NOCLOSE &BLANK
-READ CONNECT &CONNSTR
-LOOP

-* -------------------------------------------------------------------------------
-* SE GENERA CODIGO QUE UNIRA LOS DATOS RECUPERADOS DE LAS DIFERENTES CONEXIONES
-* -------------------------------------------------------------------------------

-SET &FEX = 'SQL';
-WRITE FEXFILE NOCLOSE &FEX
-SET &FEX = 'SELECT * FROM HOLD1';
-REPEAT UNIR FOR &HOLDNUM FROM 2 TO &INICIO STEP 1
-SET &FEX = &FEX | ' UNION ';
-SET &FEX = &FEX | 'SELECT * FROM HOLD' | &HOLDNUM;
-UNIR
-SET &FEX = &FEX | ';';
-WRITE FEXFILE NOCLOSE &FEX
-SET &FEX = 'TABLE';
-WRITE FEXFILE NOCLOSE &FEX
-SET &FEX = 'ON TABLE HOLD AS DATOS';
-WRITE FEXFILE NOCLOSE &FEX
-SET &FEX = 'END';
-WRITE FEXFILE NOCLOSE &FEX
-CLOSE CONNECT
-CLOSE FEXFILE
-RUN

-* -------------------------------------------------------------------------------
-* SE FINALIZA GENERACION DE REPORTE (FEX) Y SE EJECUTA PARA RECUPERAR DATOS
-* -------------------------------------------------------------------------------
EX &REPORTE

-* -------------------------------------------------------------------------------
-* ESTO SOLO MUESTRA LOS DATOS GENERADOS PARA EL EJEMPLO
-* -------------------------------------------------------------------------------
TABLE FILE DATOS
PRINT
*
BY CONEXION
END

Hi José Andrés, I can see that you already have a very interesting framework and I think I'm getting a better understanding of what you're trying to achieve.

You sample code only gets a count of record for a given table throughout a set of diverse database connections but it of course you'd have a more complex set of data depending on the type of report your business users run. Is that correct?

Here's an alternative I'd like to put to your consideration as I can see that your target database is Oracle.

The reason why you have diverse connections ... is it because you're accessing different Oracle instances? or is it just because you're accessing different user schemas within the same database?

This is the idea:

1. Choose one of your databases (if you have multiple of them) as the target connection from WebFOCUS.

2. Define database links from this database to the remaining ones and set all object privileges in each of the remote databases in such a way that they can be read from the chosen database.

3. For each "table set" you want to access create a view in your chosen database that collects information from each of the corresponding tables in the remote databases:

CREATE OR REPLACE VIEW V_CIFGENERALES AS
SELECT id, name, sales, ..., 'dblink01' as connection_name FROM CIFGENERALES@dblink01
UNION ALL
SELECT id, name, sales, ..., 'dblink02' as connection_name FROM CIFGENERALES@dblink02
UNION ALL
SELECT id, name, sales, ..., 'dblink03' as connection_name FROM CIFGENERALES@dblink03
...

4. Create a WebFOCUS synonym (masterfile) on that view and use it in your report instead. The nice thing about this is that all of the data handling/aggregation would be performed by Oracle directly which will usually give you significant performance benefits as opposed to making multiple connections and reading chunks of information and concatenating it in WebFOCUS.

5. If you need a report on a different table, all you need to do is create a new view selecting the intended data from each remote database in a similar fashion as the one shown above; you can then define a WF synonym on that view and use it in your reports instead.

There could be some security policies in your place that might restrict you from attempting such a thing but it may be worth trying. If you can take advantage of Oracle parallel query, partition pruning and/or index usage the performance benefits could be significant ... and your report development team can focus on the actual business rules involved in the production of the report leaving the data heavy lifting to the database engine; all that they will need to do in WebFOCUS is selecting one masterfile or the other depending on the report.

Just a thought!

José,

I concur with Neftali that Oracle parallel query should give you better performance.

However, to improve the code you have, I would first make it more readable by removing unneccessary -SET statements and the last UNION of the various HOLD files created.

Getting rid of the -SETs because you don't need them as all they do is obscure the code.

Removing the final UNION of the HOLD files created, because you could issue a FILEDEF DATOS DISK [path\]DATOS.FTM (APPEND so that each ON TABLE HOLD AS DATOS would append each dataset to the previous holds.

The following code should be able to be substituted for the code you have given and should produce the same results.

-* File bkwunion.fex
-* SCRIPT PARA RECUPERAR INFORMACION SOLICITADA EN UN QUERY DE VARIAS CONEXIONES
-* Script to retrieve information requested in a multi-query connections
-* -------------------------------------------------------------------------------
-SET &BLANK = ' ';
-SET &INICIO = 0;
-DEFAULT &CONNSTR = 'BANKW0000';
-DEFAULT &REPORTE = 'rep1.fex';
-* -------------------------------------------------------------------------------
-* VARIABLE DONDE SE GUARDA EL QUERY EN SQL QUE SE EJECUTARA EN CADA UNA DE LAS
-* CONEXIONES.
-* Variable storage where the query in sql running on each connection.
-* -------------------------------------------------------------------------------
-DEFAULT &QUERY = '';

-* -------------------------------------------------------------------------------
-* CONNECT ES EL ARCHIVO DONDE ESTAN LAS CONEXIONES QUE PUEDEN EMPLEARSE
-* CONNECT is the file where are the links that can be used
-* -------------------------------------------------------------------------------
FILEDEF CONNECT DISK C:\ibi\apps\test\conexiones.txt
-* -------------------------------------------------------------------------------
-* FEXFILE ES EL NOMBRE DEL ARCHIVO QUE SE CONTIENE EL SCRIPT PARA RECUPERAR
-* DATOS DE VARIAS CONEXIONES QUE LUEGO SE GUARDAN EN UN TEMPORAL LLAMADO 'DATOS'
-* FEXFILE is the name of the file that contains the script to retrieve data
-* connections to different then stored in a call time 'data'
-* -------------------------------------------------------------------------------
FILEDEF FEXFILE DISK C:\ibi\apps\test\&REPORTE

-RUN
-WRITE FEXFILE -* Filedef un archivo con APPEND para que cada bodega posteriores
-WRITE FEXFILE -* se añaden a la anterior
-WRITE FEXFILE -* Filedef a file with APPEND so that each subsequent hold is added
-WRITE FEXFILE -* to the previous
-WRITE FEXFILE FILEDEF DATOS DISK DATOS.FTM (APPEND
-WRITE FEXFILE -RUN
-* -------------------------------------------------------------------------------
-* LOOP QUE GENERA EL CODIGO PARA RECUPERAR LA INFORMACION DE LAS DIFERENTES
-* CONEXIONES
-* Loop to generate the code to retrieve information from different connections
-* -------------------------------------------------------------------------------
-READ CONNECT &CONNSTR
-REPEAT LOOP WHILE &IORETURN EQ 0;
-TYPE ESTABLECIENDO CONEXION A: &CONNSTR
-SET &INICIO = &INICIO + 1;
-WRITE FEXFILE ENGINE SQLORA SET DEFAULT_CONNECTION &CONNSTR
-WRITE FEXFILE SQL SQLORA PREPARE SQLOUT FOR
-* ESTE ES UN EJEMPLO:
-WRITE FEXFILE SELECT COUNT(*) AS CONTEO, '&CONNSTR' AS CONEXION FROM CIFGENERALES;
-WRITE FEXFILE TABLE FILE SQLOUT
-WRITE FEXFILE PRINT *
-WRITE FEXFILE ON TABLE HOLD AS DATOS
-WRITE FEXFILE END
-WRITE FEXFILE 
-READ CONNECT &CONNSTR
-LOOP
-CLOSE CONNECT
-CLOSE FEXFILE
-RUN

¡Buena suerte

T

This message has been edited. Last edited by: Tony A,

The last piece of code can be slimmed down a bit more The output will still be the same, just a bit clearer (to my eyes, anyway).

-* -------------------------------------------------------------------------------
-* LOOP QUE GENERA EL CODIGO PARA RECUPERAR LA INFORMACION DE LAS DIFERENTES
-* CONEXIONES
-* Loop to generate the code to retrieve information from different connections
-* -------------------------------------------------------------------------------
-READ CONNECT &CONNSTR
-REPEAT :LOOP WHILE &IORETURN EQ 0;
-TYPE ESTABLECIENDO CONEXION A: &CONNSTR
-SET &INICIO = &INICIO + 1;
-WRITE FEXFILE ENGINE SQLORA SET DEFAULT_CONNECTION &CONNSTR
-WRITE FEXFILE SQL SQLORA
-* ESTE ES UN EJEMPLO:
-WRITE FEXFILE SELECT COUNT(*) AS CONTEO, '&CONNSTR' AS CONEXION FROM CIFGENERALES;
-WRITE FEXFILE TABLE ON TABLE HOLD AS DATOS
-WRITE FEXFILE END
-WRITE FEXFILE 
-READ CONNECT &CONNSTR
-:LOOP
-CLOSE CONNECT
-CLOSE FEXFILE
-RUN

Word of caution: your code does not check for any errors that may arise during the SQL execution on either of the connections. If you have ... say 6 different DB connections, and connections 3 and 5 are not available for whatever reason, your code will graciously skip over the error and continue reading from the CONNECT file and attempt retrieving data from the next connection. This is dangerous because you will still be providing output to your users but such data will be incomplete.

Using an underlying database object that handles everything comes with an automatic all-or-nothing approach which, in most instances, is my preferred method to deliver information to the business. Partial information that users wrongly consider "complete" has a more negative impact than delivering no information at all; at least you'll know that something was wrong and an investigation can be initiated right away.

Of course, you can achieve a similar control using your existing framework, just make sure you add some error checking right after each "SQL SQLORA ... END" structure (don't forget to stick -WRITE FEXFILE -RUN there) and before the next "-READ CONNECT ...".

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

Thanks nijsden!

Do you have any ideas to implement this connection error?

José Andrés, I'm borrowing Tony's code with some minor changes of my own to illustrate a very basic error checking which will hopefully give you some ideas:

-* File bkwunion.fex
-* SCRIPT PARA RECUPERAR INFORMACION SOLICITADA EN UN QUERY DE VARIAS CONEXIONES
-* Script to retrieve information requested in a multi-query connections
-* -------------------------------------------------------------------------------
-DEFAULT &CONNSTR = 'BANKW0000';
-DEFAULT &TEMPDIR = 'C:\ibi\apps\test';
-DEFAULT &REPORTE = 'rep1.fex';
-* -------------------------------------------------------------------------------
-* VARIABLE DONDE SE GUARDA EL QUERY EN SQL QUE SE EJECUTARA EN CADA UNA DE LAS
-* CONEXIONES.
-* Variable to store SQL query that will run on each connection.
-* -------------------------------------------------------------------------------
-SET &QUERY = 'SELECT COUNT(*) AS CONTEO, ''&|CONNSTR'' AS CONEXION FROM CIFGENERALES';

-* -------------------------------------------------------------------------------
-* CONNECT ES EL ARCHIVO DONDE ESTAN LAS CONEXIONES QUE PUEDEN EMPLEARSE
-* CONNECT is the file the available database connections are defined
-* -------------------------------------------------------------------------------
FILEDEF CONNECT DISK &TEMPDIR\conexiones.txt
-* -------------------------------------------------------------------------------
-* FEXFILE ES EL NOMBRE DEL ARCHIVO QUE SE CONTIENE EL SCRIPT PARA RECUPERAR
-* DATOS DE VARIAS CONEXIONES QUE LUEGO SE GUARDAN EN UN TEMPORAL LLAMADO 'DATOS'
-* FEXFILE is file that will contain the script to retrieve data from each
-* database connection and that will be temporarily stored in the 'DATOS' hold file.
-* -------------------------------------------------------------------------------
FILEDEF FEXFILE DISK &TEMPDIR\&REPORTE
-RUN
-* FILEDEF a file with (APPEND so that each subsequent hold is added to the previous one
-* Definir un archivo con (APPEND para que los datos obtenidos de cada conexión sean 
-* añadidos a los anteriores
-*
-WRITE FEXFILE FILEDEF DATOS DISK DATOS.FTM (APPEND
-WRITE FEXFILE -RUN
-*
-* Initialize error indicator both here and within the dynamic script
-SET &ERROR_IND = 0;
-WRITE FEXFILE -SET &|ERROR_IND = 0;
-*
-* -------------------------------------------------------------------------------
-* LOOP QUE GENERA EL CODIGO PARA RECUPERAR LA INFORMACION DE LAS DIFERENTES
-* CONEXIONES
-* Loop to generate the code to retrieve information from different connections
-* -------------------------------------------------------------------------------
-READ CONNECT &CONNSTR
-REPEAT :LOOP WHILE &IORETURN EQ 0;
-TYPE ESTABLECIENDO CONEXION A: &CONNSTR
-WRITE FEXFILE ENGINE SQLORA SET DEFAULT_CONNECTION &CONNSTR
-WRITE FEXFILE SQL SQLORA
-* ESTE ES UN EJEMPLO:
-WRITE FEXFILE &QUERY.EVAL ;
-WRITE FEXFILE TABLE ON TABLE HOLD AS DATOS
-WRITE FEXFILE END
-* Add some logic to check for errors
-WRITE FEXFILE -RUN
-WRITE FEXFILE -SET &|ERROR_IND = IF (&|FOCERRNUM NE 0) OR (&|RETCODE NE 0) OR (&|IORETURN NE 0) THEN 1 ELSE &|ERROR_IND;
-READ CONNECT &CONNSTR
-:LOOP
-CLOSE CONNECT
-CLOSE FEXFILE
-RUN
-*
-* Run dynamic script to populate DATOS HOLD file
-SET &INC_REPORT = '-INCLUDE &TEMPDIR.EVAL\&REPORTE.EVAL';
&INC_REPORT.EVAL
-*
-* Verify that no errors where found, or abort execution otherwise
-IF &ERROR_IND EQ 0 THEN GOTO :END_UNIT;
-* << Errors were found!!! Display some error message and/or log error to a database >>
-* and stop execution
-EXIT
-:END_UNIT
-* No errors!

I removed &BLANK and &INICIO variables as they are no longer needed thanks to Tony's adjustments. I also prefixed user-defined labels with a colon ( : ) but that's just a personal preference (borrowed from the C language syntax). What you'll notice is the addition of a new variable: &ERROR_IND which will act as an error indicator (0: No error, 1: Error) and which is set to 1 in case an error had been found after each SQL execution.

Once your dynamic script is run all you need to do is make sure that &ERROR_IND is equal to 0 so you can continue your regular process. Otherwise you can notify that an error was found and stop execution.

Again, what is shown here is very basic; you might want to capture the different errors triggered (i.e. &FOCERRNUM) in & variables of your own (similar to what is done with &ERROR_IND) so you can use them to provide a more descriptive error message to your users or log it somewhere for further investigation.

The code was not fully tested so be aware of syntax errors I might have inadvertently made.

Best,
Neftali.

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

Perhaps I overdid it with the comments and made the code a bit hard to follow but hopefully you''ll find your way through them

Oh, I forgot to mention that I also took your SQL statement from within the passthru structure and set it within the &QUERY variable instead since I assumed that was the reason you had that variable in the first place, but I may be wrong (it wouldn't be the first time ). You can tweak the logic to make it more "dynamic" based on extra variables you can set before -INCLUDE'ing this procedure so both the columns and table are dynamic as well.

How about something like:

..
-DEFAULTS &QRY_COLUMNS='COUNT(*) AS CONTEO';
-DEFAULTS &QRY_TABLE  ='CIFGENERALES';
..
..
-SET &QUERY='SELECT &QRY_COLUMNS.EVAL , ''&|CONNSTR'' AS CONEXION FROM &QRY_TABLE.EVAL';
..
..

That way you'll have more control over the source table and field(s) you want to retrieve (the CONEXION field will always be automatically added for you). You may even add an &QRY_WHERE to include particular filter criteria. All of that would go into the &QUERY variable and would be resolved in the SQL passthru section.

Does that make sense to you?

Thanks for all Nijsden. Yesterday I wasn't at my office and today I was working with a friend so I wasn't able to read the posts or test the solutions.

I still haven't tested the error managing. Will do, and comment about it. I'm currently developing this having in mynd the fact that the IT personal at our clients office will be well capacitated in order for our Systems dont fail or show dummy data because of lacking connections. Anyway, the file "conexiones.txt" (which lists the connections to consult) should be modified only by their DBA, which should be a trustworthy guy.

About the variables. I started working before reading your posts and, curiosly, ended doing something similar:

-SET &QUERY = &consulta1 | ' ''' | &CONNSTR | ''' AS CONEXION ' | &consulta2;

Where &consulta1 describes all the columns to be retrieved and &consulta2 has the FROM and WHERE clauses.

Now, I've finally taken the time to read you all.

Neftali (pretty name btw, it means "My Struggle"), yes, the sets of data will usually be very complex.

About your suggestions on using Oracle's Parallel Querying I agree too. But that's something that currently escapes my job-allowed tasks. So I'll suggest to it to our DBA.

I think the commentary traductions on the code are no longer necessary since y'all managed not only to understand it but to improve it also.

Currently I'm using the original code and have managed to implement it as an include on the report that has the SQL sentence. Now I'm going to test it with a real report.

I'll have the following issues now:

A) What's the limit on WF for a String? I'll probably override it with some large SQL sentences.

B) This:

-SET &QUERY = &consulta1 | ' ''' | &CONNSTR | ''' AS CONEXION ' | &consulta2;

Should work fine even for large SQL (depending on point A) but what would happen when having something like "SELECT X FROM DUMMY UNION SELECT X FROM DUMMY". In this joined queries I'd still need to have the &CONNSTR variable concatenated in between each single query.

Any thoughts on how to manage that issue?

Thanks for all your help guys.

quote:

what would happen when having something like "SELECT X FROM DUMMY UNION SELECT X FROM DUMMY"

I am still not so sure I'd like to handle those queries in dynamic mode as future maintenance might be nightmarish. I am a big advocate for database views to hide and simplify that kind of logic but, to keep consistency with what you're doing and based on my previous example about the use of &QUERY, this is what your query would look like:

..
-DEFAULTS &QRY_COLUMNS='X';  <-- Column you want to retrieve
-DEFAULTS &QRY_TABLE  ='(SELECT X FROM DUMMY UNION SELECT X FROM DUMMY)';  <-- "source" table
..
..
-SET &QUERY='SELECT &QRY_COLUMNS.EVAL , ''&|CONNSTR'' AS CONEXION FROM &QRY_TABLE.EVAL';
..
..

The key part there is the use of an inline view as your data source which can be as complex as you need.

After running the code above, &QUERY would have the following value:

 SELECT X, '&CONNSTR' AS CONEXION FROM (SELECT X FROM DUMMY UNION SELECT X FROM DUMMY)

which, once evaluated in the connections loop later in your code, would resolved to something like this (inside of the dynamically created rep1.fex file):

ENGINE SQLORA SET DEFAULT_CONNECTION DB_CONN_01
SQL SQLORA
 SELECT X, 'DB_CONN_01' AS CONEXION FROM (SELECT X FROM DUMMY UNION SELECT X FROM DUMMY) ;
TABLE ON TABLE HOLD AS DATOS
END
..
ENGINE SQLORA SET DEFAULT_CONNECTION DB_CONN_02
SQL SQLORA
 SELECT X, 'DB_CONN_02' AS CONEXION FROM (SELECT X FROM DUMMY UNION SELECT X FROM DUMMY) ;
TABLE ON TABLE HOLD AS DATOS
END
..
..

Hopefully that resolves your need. As for the maximum length of a string in Dialog Manager, I'm sorry I don't know but hopefully someone might step in and provide us both with that information which is always good to know

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

quote:

About the variables. I started working before reading your posts and, curiosly, ended doing something similar

Confused minds think alike, I guess

quote:

SELECT X, '&CONNSTR' AS CONEXION FROM (SELECT X FROM DUMMY UNION SELECT X FROM DUMMY)

That output is exactly what I need for this last Issue. I'd also like to manage this in views but I'm not responsible in this proyect for the administration of the DB.

Thanks for all the help Neftaly, Tony & Dan!

BTW, I suggested the Oracle parallel querying solution you suggested at the beginning. And It was rejected 'cause it is possible that we implement this dynamic reports on other projects that don't run on an Oracle DBMS.

quote:

I suggested the Oracle parallel querying solution you suggested at the beginning. And It was rejected 'cause it is possible that we implement this dynamic reports on other projects that don't run on an Oracle DBMS

It's a sad reality everywhere. Companies invest big bucks in infrastructure just to decide not to use many of the great features they payed for, all in the name of keeping "platform independence", which is exactly the reason I suggested using database views which would encapsulate and make use of whatever features were available, making them accessible to WF through a simple masterfile but hiding the implementation complexities.

Moving to another project which uses MS SQL Server, Postgresql, MySQL, DB2, etc. would require to define database views particular to each environment while still using them via masterfiles so your reports would not need to be modified.

Anyway, I'm glad that the workaround provided for your last question worked, but keep in mind that such an approach may not be appropriate if you try to run it "as is" against a DB2, MS SQL Server or other database platform and may need to be adjusted which invalidates the platform-independent approach that was intended to be kept in the first place.

Regards,
Neftali.