2

I'm wondering if it's possible to have one datawindow display multiple sql results? I have 4 or 5 sql scripts that I would like to run at once, display in one datawindow, and then save the results. I want in one window to keep it clean, instead of having different Windows opening. How can I do this?

PBNick
  • 55
  • 1
  • 11

4 Answers4

2

Yes, and no...

A single datawindow can only have one "Primary" buffer - that's the rows and columns that you see. But you can create a Composite datawindow that is really nothing more than a "container" of other datawindows - and each of those "nested" datawindows can run their own independent SQL statements.

To your users, it's as if one datawindow contains all the data from the different queries, but you really are showing them X different datawindows.

Another option, if the result sets from the different queries are all congruent (same columns, same datatypes). You can use the RetrieveStart event to tell the datawindow to append new rows instead of replacing them in the datawindow buffer. So, let's say I want to retrieve a customer list for regions 1, 2, and 5, but my datawindow only has the single argument for sales_region_num. I can retrieve( 1 ), then retrieve( 2 ), but use the RetrieveStart event to append the new rows, then retrieve( 5 ), appending rows again. It's three separate queries, using different retrieval args.

-Paul-

NoazDad
  • 608
  • 3
  • 9
  • I tried doing the composite. When I try to code the actual window I get "Bad Argument for function: SetSQLSelect" I want the datawindow to pull the process id from what they enter on the screen, and the obj_id based on the document number. – PBNick Feb 17 '15 at 02:41
  • This is my code 'string s_process_id, s_doc_count, s_litem_count long l_obj_id s_process_id = sle_process_id.Text gx_s_doc_nmbr = sle_document_number.Text Select obj_id Into :l_obj_id From dsk_obj where obj_usr_num = :gx_s_doc_nmbr Using SQLCA; dw_composite.SetTransObject(SQLCA) dw_composite.SetSQLSelect(l_obj_id) dw_composite.SetSQLSelect(s_process_id) dw_composite.Retrieve(l_obj_id) dw_composite.Retrieve(s_process_id)' – PBNick Feb 17 '15 at 02:42
  • I tried converting the l_obj_id to string, and got passed the error...but when i run the program, it keeps asking me to enter the retrieval arguments instead of pulling the information from the entered text on the window. – PBNick Feb 17 '15 at 03:14
  • 1
    No dude... You're not understanding composite datawindows at all. dw_composite won't have any SQL behind it at all. It's just a container, as I mentioned. What are you using that embedded SQL statement for? And you didn't even look at the Help on SetSQLSelect(), did you? You pass that function a string containing an entire SQL statement, not just the parameter to the query... – NoazDad Feb 18 '15 at 04:23
  • 1) Build a dw called d_object. Its SQL statement will be SELECT obj_id FROM dsk_obj WHERE obj_usr_num = :as_doc_nmbr. It will take one string argument named as_doc_nmbr. 2) Build another dw called d_process. Its SQL statement will be SELECT "whatever columns you need" FROM "whatever table you're querying" WHERE "the process_id column" = :as_process_id. It will take a single string argument named as_process_id. 3) Create a 3rd dw named d_composite. Use an external datasource - no SQL needed. Add two string retrieval arguments: s_obj and s_process_id. – NoazDad Feb 18 '15 at 04:30
  • In d_composite, add two NESTED datawindows. For the first, use d_object as the dataobject, and link it's argument as_doc_nmbr to s_obj (the first arg to d_composite). Add the second nested dw right underneath the first, and use d_process for the dataobject. Link it's argument as_process_id to s_process_id from d_composite. Now, in your code, you say d_composite.Retrieve( l_obj_id, sle_document_number.text) – NoazDad Feb 18 '15 at 04:34
  • I understand that composite dw is just a container...I don't sql on the container itself. The Sql is on the window that the dw is on. The user is not entering an obj_id I want the system to get the obj_id from the doc_nbr and pass that to the dw's in the composite. Sorry I've been looking at the help and googling setsqlselect() just trying to figure it out. – PBNick Feb 18 '15 at 04:37
1

Had you considered writing a Stored procedure? Organizing the desired results within the Stored procedure using a #temptable? Then returning the temp table as the result set?

You mentioned 'then saving'. There is a FileSaveas within PB script.

Chris
  • 11
  • 1
0

The only way I have found is to use logic with the results from one query in DBase A to compare data with the results from query in DBase B.

If you want to display the results together (A and B) then I would code into a display temp table. Col_1 = "", col_2=""... and loop through A and B and use logic to pick through the records.

A little sloppy but it appears we are trying the limits of PB.

Little John
  • 9
  • 5
0

Antoher possibility is to use a sql 'union', if all your queries return the same set of columns:

select A.col1, A.col2 from A where ...
UNION
select B.col1, B.col2 from B where ...
UNION
...

Use this as a source for your datawindow column. From then on, you can just issue one 'retrieve()' command to get all columns from all tables at once. Of course, editong data will be more of a problem...

Marc Vanhoomissen
  • 395
  • 2
  • 12