RPGLE DSPF filtering based on multiple columns

670 pts.
Tags:
#AS400 #RPGLE #as400
Looking for suggestions on one issue.
I have a DSPF with filtering allowed for multiple columns like - Column1, Column2, Column3. User can input values for any of these columns or all columns at once or even can leave those as blank.
My requirement is, initially when i display the screen, I load all records from the database file and show it on screen. The moment user hits enter key, i need to validate if value for any of these 3 columns are input. If yes, I need to dynamically query the database according to the selected columns only.
Example: User input value for column2 only. So, my program will run query like - "Select * from table where column2 = XXX'.
I can handle this by writing 8 possible queries (2^3 possible combinations) in the program, but i feel there must be a better way to do this.
Is there a way to do this efficiently? Or should the design be changed?


Software/Hardware used:
RPGLE
0

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

Discuss This Question: 16  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • ToddN2000
    We need a few more details please. You said your display has columns. How many columns and how are they defined? Is this a subfile or have you coded each column as separate fields? If there is a need to run your SELECT,  is this based on a READC or are you filed testing for a change in value? What are you doing with the data returned from the select? I'm not sure where you are coming up with 8 possible queries based on the information provided.

    136,290 pointsBadges:
    report
  • sri8707
    Hi ToddN2000, let me provide more details on this. 

    It is a RECORD and not a SUBFILE. When you call the program, the record is displayed with 3 fields pertaining to column1, column2 and column3 of a file. Assume the screen is as below -


    Filter By: Column1 ______     Column2 ________ Column3 ______

    ID     COLUMN1       COLUMN2       COLUMN3       DATE      TIME
     1      A  B   C   12-Dec   9AM
     2   A  D   E   10-Dec     8AM

    In the above screen, user is allowed to filter records based on the values input in any of the below 3 fields - 
    Column1
    Column2
    Column3

    Say, user input values for column1 and column2 fields as A and B respectively. When he hits Enter, I am expected to display only record1 (because it points to A and B)

    The problem I face is, how can i dynamically pick the records from database file based on the input filters? Because, as in above case, i cannot query the DB file with column1 = A and column2 = B and column3 = Blanks. That will provide me with no records. 

    Is that clear?
    670 pointsBadges:
    report
  • ToddN2000
    It could be done a few ways. Do you have to use SQL? You could simply build a KLIST with each of the columns being a KFLD in the RPGLE then SETLL and do a READE to get all matching data, IT can also be done using embedded SQL. Is this a class assignment and has to be done using a certain method ? 
    136,290 pointsBadges:
    report
  • sri8707
    I am fine with using any method that is efficient. (I generally avoid SQL and use RPGLE only). 

    The problem with KLIST here is, i have to create multiple logical files here. Because, none of the 3 fields are mandatory. If user doesnt input anything, no filter is applied. When user inputs value for any one field, I have to read from the file based on that particular field only, and other 2 fields cannot be considered as blanks, rather should be ignored. As these are dynamic, how can i perform SETLL/READE appropriately here? 

    The main problem here is, when filter is applied, I cannot treat the empty fields as blanks while fetching data. I have to avoid using that field for fetching data, and thats my issue.

    One method i can think of is -
    1. check if column1 is not empty. If not empty, read all records having that column1 value and store it as a list (list will contain all the values - column1, column2, column3)
    2. check if column2 is not empty. If not empty, read all records having that column2 value and store it in the same list (if record already exists in the list, avoid rewriting)
    3. do same with column3 as well.
    670 pointsBadges:
    report
  • ToddN2000
    Lets say column 1 and 2 both have values. You enter col1 = A and col2 = B. Do you want only data coming back with both A & B?  or do you want all A records as well as all B records in one returned table ?

    136,290 pointsBadges:
    report
  • ToddN2000
    Try something like this, just expand it for your other conditions/tests
    C                   IF        col1 <> *BLANKS AND                 
    C                             col2 = *BLANKS AND                    
    C                             col3 = *BLANKS                     
    C/EXEC SQL                                                          
    C+    DECLARE myfile1 CURSOR FOR                                        
    C+       SELECT fld2,fld2,fld3,fld4  
    C+       FROM  myfile                                                   
    C+       WHERE fld1 = :col1   
    C/END-EXEC                                                          
    C/EXEC SQL                                                          
    C+    OPEN myfile1 
    C/END-EXEC                                                          
    C                   ENDIF
    136,290 pointsBadges:
    report
  • sri8707
    Hi Todd,

    The above code is the exact issue that i mentioned. You wrote an IF clause with Col1 <> Blanks, and Col2,3 as blanks. If i write this way, i should be writing 8 IF clauses as below conditions are possible -

    Column1 Column2 Column3
    blank blank valid
    valid blank valid
    blank valid blank
    valid valid blank
    blank blank blank
    valid valid valid
    valid blank blank
    blank valid valid
    670 pointsBadges:
    report
  • ToddN2000
    You could put the 3 values in an array, sort the array and then read it back in. Unless there is a reason that Valid/Blank/Blank is truly different than Blank/Blank/Valid. How many different values can be in each column? Can the same value appear in more than one column ?
    136,290 pointsBadges:
    report
  • sri8707
    Scenario1: 

    Column1 - valid (user has input 100), column2 - blank, column3 - blank.

    The backend query is supposed to be:

    select * from table where column1 = 100

    Scenario2: 

    Column1 - blank, column2 - blank, column3 - valid (user has input 100).

    The backend query is supposed to be:

    select * from table where column3 = 100


    These columns1,2,3 are present in a file. So, I am trying to retrieve values from the file based on the filter user has applied and display on screen. 
    670 pointsBadges:
    report
  • ToddN2000
    I get it now, Screen 1 has to match column 1, screen 2 has to match column 2 and the same for the last one 3. In your example of a Valid / Blank / Valid condition, how do you see the querey working ? Have you looked at the possibility of using the UNION feature of SQL to combine the individual tests. You would just need  to test 3 conditions. Something like this maybe ?
    EXEC SQL
    SELECT fields 
    INTO :SLCT1
    FROM X
    UNION
    SELECT fields 
    INTO :SLCT2
    FROM Y
    UNION
    SELECT fields 
    INTO :SLCT3
    FROM Z
    END-EXEC



    136,290 pointsBadges:
    report
  • sri8707
    I havent thought about UNION condition, but i expect blank to be ignored.

    Think of it this way. The file has 1000 records. I am displaying all 1000 records on screen initially. User then filters based on values input on column1 and column3 only. Column2 is left as blanks.

    Now, when i query the file, I have to write SELECT * FROM FILE WHERE COLUMN1 = value AND COLUMN3 = value. 

    I cannot add COLUMN2 = blank condition, because that will basically return 0 records from the file. I am not even sure if this design is a valid case
    670 pointsBadges:
    report
  • ToddN2000
    Try adding a where test and check for blanks.
    It should only return a table for the union if it passes.

    EXEC SQL
    SELECT fields 
    INTO :SLCT1
    FROM X
    where screen1 = col1 and screen1 <> ' '
    UNION
    SELECT fields 
    INTO :SLCT2
    FROM Y
    where screen2 = col2 and screen2 <> ' '
    UNION
    SELECT fields 
    INTO :SLCT3
    FROM Z
    where screen3 = col3 and screen3 <> ' '
    END-EXEC
    136,290 pointsBadges:
    report
  • ToddN2000
    or try a series of SQL statements

    Build a work table and flag the ones you want that pass your filter test.
    Select ' ' as flag, * into work_table
     from starting_table
    
    update work_table
    set flag = 'X' where screen1 = col1 
    and screen1 <> ' '
    
    update work_table
    set flag = 'X' where screen2 = col2 
    and screen2 <> ' '
    
    update work_table
    set flag = 'X' where screen3 = col3 
    and screen3 <> ' '
    
    select * from work_table
    where flag = 'X'
    136,290 pointsBadges:
    report
  • sri8707
    Thanks Todd. You think any other approach possible? Adding multiple SQL statements affect performance i feel. Thats why i was hesitant to do this approach in the first place. 
    670 pointsBadges:
    report
  • TheRealRaven
    Consider this:
    select * from myfile
       where
          (dspval1 = ' ' or column1 = dspval1) and
          (dspval2 = ' ' or column2 = dspval2) and
          (dspval3 = ' ' or column3 = dspval3)

    36,880 pointsBadges:
    report
  • sri8707
    thank you all for your suggestions.
    670 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: