ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Query about SQL Search problem

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Query about SQL Search problem

    Hi All,

    Hoping someone can help me out on the problem i have.

    I am designing a search utility using DSPF (with SQL logic in the backend). The purpose of this search utility is to identify all records from the file matching the condition.

    Example:
    File1 has 10 fields in it - field1 to field10
    DSPF will have all 10 fields displayed
    User can input any of those 10 values and the embedded SQL is supposed to find matching records according to the fields input by the user

    For this purpose, I hardcode SQL keywords in my SQLRPGLE program as -
    C Field1 Const('field1 = ?')
    C Field2 Const('field2 = ?')
    etc

    Now, if user has input field1 and field5 values, I build my SQL statement in program as -

    Select * from file1 where field1 = ? and field5 = ?

    Then i run,
    Prepare SqlStmt from :Sqlstr
    Declare c1 cursor for SqlStmt
    Open c1 cursor using :field1,:field5

    This logic works fine, but as u can see, the problem is that there are 10! (10 factorial) possible combinations that can be input by user. And i cannot write OPEN CURSOR statement with each of the combinations.

    What is the ideal way to work on scenarios like this?


  • #2
    I'd not use dynamic SQL, but Static instead. The following statement will consider all compositions.

    Code:
    Exec SQL Declare CsrC01 Cursor For
    Select ... From ...
    Where Field1 = Case When :HostField1 > '' Then :HostField1 Else Field1 End
            and Field2 = Case When :HostField2 <> 0 Then :HostField2 Else Field2 End
            and Field3 = Case When :HostField3 > '' Then :HostField3 Else Field3 End
    ....
    Order By ...
    You can also use the case clause in dynamci SQL, if you want, but in this case dynamic SQL is not necessary.

    Birgitta
    Last edited by B.Hauser; May 17, 2020, 07:57 AM.

    Comment


    • #3
      Hi Birgitta,

      Thanks for replying. Can you provide me one additional detail on this?

      My query goes like below -

      Select * from lib/file where
      Field1 = CASE When Field1 = ? Then FldLen End

      I had to use it dynamically as, apart from this Field1 value, there are few more values that will be built while opening the cursor only.

      Now, my doubt is that, say, user inputs value of 100 for field1. So, this query fetches all records from the table that has field1 = 100. However, if user does not input any value for field1, this query does not produce any result.

      Rather, i want the query to ignore FIELD1 condition on the WHERE clause and fetch remaining results. (In our case, the query should produce result of select * from lib/file).

      Can you tell me what should be corrected?

      Comment


      • #4
        ... because you used only a single place holder instead of 2 for a single field. Look again at my the Example which differs from yours:

        Where Field1 = Case When :HostField1 > 1 Then :HostField1 Else Fiel1 ...

        If you insist to use dynamic SQL with your syntax you have to change it as follows:

        Code:
        Where Field1 = Case When ? > '''' Then ? Else Field1
                 and Field2 = Case When ? <> 0 Then ? Else Field2 ...
        In your Open Statement you have then have to specify each of your variables twice:

        Code:
        Exec SQL Open using :HostVar1, :HostVar1, :HostVar2, :HostVar2 ...

        Comment


        • #5
          Thanks again for your assistance. Yes, i have to use dynamic SQL due to the scenario i have and the solution you provided works perfectly fine..

          Comment

          Working...
          X