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.

7 Comments

  1. 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)
  2. 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)
  3. 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)
  4. 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)
  5. 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)
  6. 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)
  7. 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)