Custom search - not searching

[neil.interactit]

Hey guys,

I put together a custom search, using viewtopic.php?f=27&t=184 and viewtopic.php?f=26&t=280 ...

Code: Select all

    Private Sub Search_Showing(sender As Object, e As System.EventArgs)
        Dim jiwaSearch As JiwaApplication.JiwaSearch.clsSearch = DirectCast(sender, JiwaApplication.JiwaSearch.clsSearch)
        With JiwaApplication.Manager.Instance.Search
            If .CurrentSearchMode = JiwaApplication.JiwaSearch.clsSearch.SearchModes.jswDebtors Then
                ' Add Description to display
                Dim aOption As New JiwaApplication.JiwaSearch.SearchOption
                With aOption
                    .Title = "External Systems"
                    .SQLStr = "SELECT tbl.DebtorID,AccountNo,Name,ComcareClientId,JobNo,BpayCrn FROM ( SELECT s.DebtorID,AccountNo,Name,ComcareClientId,HCPNo,BpayCrn FROM DB_Main s, ( SELECT * FROM ( SELECT p.DebtorID, p.Contents, pd.SettingName FROM DB_CustomSettingValues p LEFT OUTER JOIN DB_CustomSetting pd ON pd.SettingID = p.SettingID ) tbl PIVOT ( MIN(tbl.Contents) FOR tbl.SettingName in (ComcareClientId,HCPNo,BpayCrn) ) piv ) p WHERE s.DebtorID = p.DebtorID) tbl LEFT JOIN JB_Main ON tbl.HCPNo = JB_Main.JobID "
                    .OrderBy = "ORDER BY AccountNo"
                    .AddColumn("DebtorID", vbString, "", 0, 1)
                    .AddColumn("Account No", vbString, "", 20, 2)
                    .AddColumn("Name", vbString, "", 29, 3)
                    .AddColumn("Comcare Client Id", vbString, "", 17, 4)
                    .AddColumn("HCP No", vbString, "", 17, 5)
                End With
                .Options.Add(aOption)
            End If
        End With
    End Sub


All good, the new search displays perfectly ...

The trouble is that it doesn't filter results. Entering "3TIER" for example, doesn't change the result set at all.

Is this because of the the table joins in the query maybe? How to I accommodate this query?

Cheers,
Neil

[Mike.Sheen]

I couldn't use your exact snippet, as I don't have the tables - but I did create a test sample and did not find the same behaviour.

Attached is the sample which worked fine on demo data - does it also work for you?

[Scott.Pearce]

I wonder if the pivot is causing an issue with the way we process the user search strings? A full sample of this along with required tables etc. would be most helpful for this one Neil.

[neil.interactit]

Hi Scott,

I agree, it must be the pivot ... I changed the SQL to "SELECT DebtorID,AccountNo,Name FROM DB_Main" (and commented out the extra addcolumns) and it worked fine.

Attached is the working version. If you just create ComcareClientId(text),JobNo(text),BpayCrn(integer) as debtor maintenance custom fields, and uncomment the PIVOT, you should be able to replicate.

Thanks,
Neil

[Scott.Pearce]

Interesting. Run your search, type in a search string, and then click the "SQL" button on the search screen ribbon to see the raw SQL used.

[Scott.Pearce]

Here is what is happening. When a user search string is entered, we build a WHERE clause and tack it on to the end of the query. If the query already contains a WHERE, we tack it on the end using an AND.

Your sub-query contains the keyword WHERE, and this is stuffing up the way we tack on our WHERE clause. I actually cannot see a solution to this problem as far as our code is concerned. HOWEVER, if you can avoid using WHERE in sub-queries, there is no issue. To that end, I recommend you change your query. Perhaps you could wrap your pivoting, etc. into a view?

[neil.interactit]

Makes sense. Thanks Scott. I'll have a play.

Cheers,
Neil

[neil.interactit]

Hi Scott,

Turned out an easy fix worked ... I just added "WHERE 1=1" to the end! That way your appended "AND" works fine. All sweet!

Thanks,
Neil