Show all rows from a table with a SQL query, not retrieve.
Author: None (None)
Hello.
I want to execute a sql that have to do the query from different tables (24 tables with this format CC_01 to CC_24), all the tables have the same definition (the model was defined this way instead of making all in 1 table with an extra field for an implementation issue). The $$var_for_table_name is given by a dropdown list and the $$var_for_key is given by a normal var of the system.
For example:
sql "SELECT field1,field2,field3 FROM '%%$$var_for_table_name' WHERE primary_key ='%%$$var_for_key'
I need to show something like this (f.e. 3 rows returned):
field1 - field2 - field3
1a - 1b - 1c
2a - 2b - 2c
3a - 3b - 3c
If the result bring back several rows, I don't know how to show them all, like in a grid , cause when the result is only 1 row it can be show with $result.
If this can't be done or is too difficult, how can I implement 'read where' for a variable table? All of this have to be done in 1 form, my solution of openning 24 different forms depending on the dropdown list isn't a great solution.
Thanks for reading.
Show all rows from a table with a SQL query, not retrieve.
Author: None (None)
Hello.
I want to execute a sql that have to do the query from different tables (24 tables with this format CC_01 to CC_24), all the tables have the same definition (the model was defined this way instead of making all in 1 table with an extra field for an implementation issue). The $$var_for_table_name is given by a dropdown list and the $$var_for_key is given by a normal var of the system.
For example:
sql "SELECT field1,field2,field3 FROM '%%$$var_for_table_name' WHERE primary_key ='%%$$var_for_key'
I need to show something like this (f.e. 3 rows returned):
field1 - field2 - field3
1a - 1b - 1c
2a - 2b - 2c
3a - 3b - 3c
If the result bring back several rows, I don't know how to show them all, like in a grid , cause when the result is only 1 row it can be show with $result.
If this can't be done or is too difficult, how can I implement 'read where' for a variable table? All of this have to be done in 1 form, my solution of openning 24 different forms depending on the dropdown list isn't a great solution.
Thanks for reading.
Extra info.
In fact $result can only store 1 field, not the entire row. ) :
So its not viable.
What´s the point of creating a sql query with SELECT, if it can only store 1 field?, is there any ohter way of making a SELECT with multiple fields for multiple rows and display it in a table, grid, etc?
I´m a web developer so this type of paradygms are new to me.
Author: None (None)
Show all rows from a table with a SQL query, not retrieve.
Author: None (None)
Hello.
I want to execute a sql that have to do the query from different tables (24 tables with this format CC_01 to CC_24), all the tables have the same definition (the model was defined this way instead of making all in 1 table with an extra field for an implementation issue). The $$var_for_table_name is given by a dropdown list and the $$var_for_key is given by a normal var of the system.
For example:
sql "SELECT field1,field2,field3 FROM '%%$$var_for_table_name' WHERE primary_key ='%%$$var_for_key'
I need to show something like this (f.e. 3 rows returned):
field1 - field2 - field3
1a - 1b - 1c
2a - 2b - 2c
3a - 3b - 3c
If the result bring back several rows, I don't know how to show them all, like in a grid , cause when the result is only 1 row it can be show with $result.
If this can't be done or is too difficult, how can I implement 'read where' for a variable table? All of this have to be done in 1 form, my solution of openning 24 different forms depending on the dropdown list isn't a great solution.
Thanks for reading.
Hi
If you need to read data only, just use a SQL-View
CREATE VIEW MY_VIEW as
AS
SELECT 'TABLE01',* FROM TABLE01
UNION
SELECT 'TABLE02',* FROM TABLE02
UNION
SELECT 'TABLE02',* FROM TABLE03
...
UNION
SELECT 'TABLE24',* FROM TABLE24
In UnifAce define this view with an extra field TABLE_NAME.
Now you ca access this tables with a simple U_WHERE
READ U_WHERE (table_name=$$var_for_table_name and primary_key = $$var_for_key)
Ingo
Author: istiller (i2stiller@gmx.de)
Show all rows from a table with a SQL query, not retrieve.
Author: None (None)
Hello.
I want to execute a sql that have to do the query from different tables (24 tables with this format CC_01 to CC_24), all the tables have the same definition (the model was defined this way instead of making all in 1 table with an extra field for an implementation issue). The $$var_for_table_name is given by a dropdown list and the $$var_for_key is given by a normal var of the system.
For example:
sql "SELECT field1,field2,field3 FROM '%%$$var_for_table_name' WHERE primary_key ='%%$$var_for_key'
I need to show something like this (f.e. 3 rows returned):
field1 - field2 - field3
1a - 1b - 1c
2a - 2b - 2c
3a - 3b - 3c
If the result bring back several rows, I don't know how to show them all, like in a grid , cause when the result is only 1 row it can be show with $result.
If this can't be done or is too difficult, how can I implement 'read where' for a variable table? All of this have to be done in 1 form, my solution of openning 24 different forms depending on the dropdown list isn't a great solution.
Thanks for reading.
sql/print "SELECT field1, ';', field2, ';', field3 FROM '%%$$var_for_table_name' WHERE primary_key ='%%$$var_for_key'
With /print the entire result goes into $result.
You need the ';' or any other separator to see where one field ends and the next begins.
Depending on what database you use you might need to put the SQL rtrim around field1, field2 and field3 to prevent extra spaces.
Now you have everything in a big string that you can parse.
Maybe something like:
vResult = $result
while (vResult <> "")
vEOL = $scan(vResult, "%%^")
vLine = vResult[1:vEOL-1]
vResult = vResult[vEOL+1]
$1 = $replace(vLine,1,";","gold;",-1) ; I mean the gold character of course!
if (FIELD1.MYENTITY <> "")
creocc "MYENTITY", -1
setocc "MYENTITY", -1
endif
getitem "FIELD1.MYENTITY", vLine, 1
getitem "FIELD2.MYENTITY", vLine, 2
getitem "FIELD3.MYENTITY", vLine, 3
endwhile
Code not tested so you may need to improve it!
Author: Theo Neeskens (tneeskens@itblockz.nl)
Show all rows from a table with a SQL query, not retrieve.
Author: None (None)
Hello.
I want to execute a sql that have to do the query from different tables (24 tables with this format CC_01 to CC_24), all the tables have the same definition (the model was defined this way instead of making all in 1 table with an extra field for an implementation issue). The $$var_for_table_name is given by a dropdown list and the $$var_for_key is given by a normal var of the system.
For example:
sql "SELECT field1,field2,field3 FROM '%%$$var_for_table_name' WHERE primary_key ='%%$$var_for_key'
I need to show something like this (f.e. 3 rows returned):
field1 - field2 - field3
1a - 1b - 1c
2a - 2b - 2c
3a - 3b - 3c
If the result bring back several rows, I don't know how to show them all, like in a grid , cause when the result is only 1 row it can be show with $result.
If this can't be done or is too difficult, how can I implement 'read where' for a variable table? All of this have to be done in 1 form, my solution of openning 24 different forms depending on the dropdown list isn't a great solution.
Thanks for reading.
Hi Sergio,
try SQL/PRINT instead of simple SQL
you get a text where each datarecord is one line.
Line 1 holds the (truncated) Fieldnamnes
Line 2 holds underscores
Line 3+ holds the data records.
You can show this as a simple textfield "as is" (non-proportional font)
Or you separate the lines and sore them in separate occurences.
If you wan to get a first test, call the SQL-Wokbech with GOLD-W in your IDF
The output is generated with SQL/PRINT
Uli
Author: ulrich-merkel (ulrichmerkel@web.de)
Show all rows from a table with a SQL query, not retrieve.
Author: None (None)
Hello.
I want to execute a sql that have to do the query from different tables (24 tables with this format CC_01 to CC_24), all the tables have the same definition (the model was defined this way instead of making all in 1 table with an extra field for an implementation issue). The $$var_for_table_name is given by a dropdown list and the $$var_for_key is given by a normal var of the system.
For example:
sql "SELECT field1,field2,field3 FROM '%%$$var_for_table_name' WHERE primary_key ='%%$$var_for_key'
I need to show something like this (f.e. 3 rows returned):
field1 - field2 - field3
1a - 1b - 1c
2a - 2b - 2c
3a - 3b - 3c
If the result bring back several rows, I don't know how to show them all, like in a grid , cause when the result is only 1 row it can be show with $result.
If this can't be done or is too difficult, how can I implement 'read where' for a variable table? All of this have to be done in 1 form, my solution of openning 24 different forms depending on the dropdown list isn't a great solution.
Thanks for reading.
Alternatively, do the following.
Paint a dummy entity with the right fields on the form,
in the retrieve trigger or whichever triggeractivates the data fetch set the following code. (I am assuming your dropdown list field holds the table name to be used as a string.)
clear/e "%%dropdown_list%%%"
v_pk_name = "primary_key.%%dropdown_list%%%"
@v_pk_name = @$$var_for_key
retrieve/e "%%dropdown_list%%%"
setocc "%%dropdown_list%%%",1
while($status >=0 & !$empty("%%dropdown_list%%%"))
creocc "DUMMY_ENTITY",-1
setocc "DUMMY_ENTITY",-1
putlistitems/occ v_fields, "%%dropdown_list%%%"
getlistitems/occ/init v_fields, "DUMMY_ENTITY"
setocc "%%dropdown_list%%%",$curocc("%%dropdown_list%%%")+1
endwhile
You will need to paint all 24 of the entities as small as possible on the form somewhere and set the scroll bar property to 'None'.
Author: Iain Sharp (i.sharp@pcisystems.co.uk)
Show all rows from a table with a SQL query, not retrieve.
Author: None (None)
Hello.
I want to execute a sql that have to do the query from different tables (24 tables with this format CC_01 to CC_24), all the tables have the same definition (the model was defined this way instead of making all in 1 table with an extra field for an implementation issue). The $$var_for_table_name is given by a dropdown list and the $$var_for_key is given by a normal var of the system.
For example:
sql "SELECT field1,field2,field3 FROM '%%$$var_for_table_name' WHERE primary_key ='%%$$var_for_key'
I need to show something like this (f.e. 3 rows returned):
field1 - field2 - field3
1a - 1b - 1c
2a - 2b - 2c
3a - 3b - 3c
If the result bring back several rows, I don't know how to show them all, like in a grid , cause when the result is only 1 row it can be show with $result.
If this can't be done or is too difficult, how can I implement 'read where' for a variable table? All of this have to be done in 1 form, my solution of openning 24 different forms depending on the dropdown list isn't a great solution.
Thanks for reading.
Thank you for all your answers. Solved.
@ulrich-merkel
This can be a solution to see an SQL, but our end users are used to see the results in the forms, just the validations in the message board.
@istiller
We tried this one, but is such a large DB, the union view demand so much resources, maybe because it can´t take any parameters to delimit the number of rows to retrieve, it brought every row for every table. But I´m going to use views for smallest queries in the future.
The solution was something like the code FiresongKt posted; but instead of printing the 24 entities in the form we used a Service, and to a Case we passed the value of the dropdownlist. I know it also can be done without the service by printing the entities in the form.
Author: None (None)
Show all rows from a table with a SQL query, not retrieve.
Author: None (None)
Hello.
I want to execute a sql that have to do the query from different tables (24 tables with this format CC_01 to CC_24), all the tables have the same definition (the model was defined this way instead of making all in 1 table with an extra field for an implementation issue). The $$var_for_table_name is given by a dropdown list and the $$var_for_key is given by a normal var of the system.
For example:
sql "SELECT field1,field2,field3 FROM '%%$$var_for_table_name' WHERE primary_key ='%%$$var_for_key'
I need to show something like this (f.e. 3 rows returned):
field1 - field2 - field3
1a - 1b - 1c
2a - 2b - 2c
3a - 3b - 3c
If the result bring back several rows, I don't know how to show them all, like in a grid , cause when the result is only 1 row it can be show with $result.
If this can't be done or is too difficult, how can I implement 'read where' for a variable table? All of this have to be done in 1 form, my solution of openning 24 different forms depending on the dropdown list isn't a great solution.
Thanks for reading.
Hi Sergio, let me explain a bit more:
There is no need to show $result from SQL/PRINT directly to the users ("see the SQL").
In case you print it, there is no difference for the output doing it "the easy" way.
With a little additional work, you can cut the string (because its fixed-position)
and populate any data cells you like.
But: no need to define a database entity at first for each option etc.
Just one "template" definition is all you need.
Any source (including views, unions, ...) directly accessable and presentable.
Even with subselections and "ad hoc handling"
In a lot of situations it has proven to be fast, flexible and efficvient.
Uli
But if you have only a handful of database tables,
painting each of them in a form will do the job as well in a more "traditional" way.
Author: ulrich-merkel (ulrichmerkel@web.de)
