Link to home
Start Free TrialLog in
Avatar of lnshop
lnshop

asked on

Problem with index on sql query

I have a sql query which runs fine in Foxpro 6.0.   When I run the code from vb.net 2003, no results are returned but if I remove the index, I get the expected results but its way too slow.  Any help in getting this query to work is appreciated.   Thank-you.
premsql = "Select policy, txtype from premsht where policy + str(sheet_no, 4) = padr(""" & CasPolicyNo & """, 15)"
        Response.Write(premsql)
        Dim premCmd As New Odbc.OdbcCommand
        Dim premDataAdapter As New Odbc.OdbcDataAdapter
        Dim premDataView As DataView
        Dim premDataSet As New DataSet
        premDataAdapter = New OdbcDataAdapter(premsql, connIFIS)
        premDataAdapter.Fill(premDataSet, "premsht")
        premDataView = premDataSet.Tables("premsht").DefaultView
        For Each dr As DataRow In premDataSet.Tables(0).Rows
            CasPolicyNo = dr(0)
            Response.Write(CasPolicyNo)
            txtype = dr(1)
            Response.Write("<br>")
            Response.Write(txtype)
        Next

Open in new window

Avatar of randomjames
randomjames
Flag of United States of America image

Should you padr(policy + str(sheet_no, 4)) like you did with CasPolicyNo?
Since the problem seems to be the index. what expression you indexing on and what are you calling the index tag within VFP6 where it does work?
Avatar of lnshop
lnshop

ASKER

This is what I ran in Foxpro.  The index is on policy.  The only difference I could see in foxpro is that the expression was policy+STR(sheet_no,4).  I tried using this exact syntax and didn't get any results. but it must be a problem with the index.
Foxpro query
select policy, txtype from premsht where policy+str(sheet_no, 4)=padr("CRX53328", 15)
.net query
premsql = "Select policy from premsht where policy + str(sheet_no, 4) = padr(""" & CasPolicyNo & """, 15)"
I can't tell from what I've seen what is the Policy part and what is the sheet # part.

However, if that sheet # is NOT exactly four characters long, you will have leading spaces in that portion which will make an unusual concatentation of those strings.

Is policy # "CRX5" and sheet # is "3328" ?
Avatar of lnshop

ASKER

No the policy number is CRX53328, the sheet_no is 4
This is exactly what I entered into the Foxpro command window and it retured 6 records.  I have used the str(sheet_no, 4) index in many other queries and didn't have this problem.  I don't what else to try at this point.
select policy from premsht where policy+str(sheet_no, 4)=padr("CRX53328", 15)
ASKER CERTIFIED SOLUTION
Avatar of CarlWarner
CarlWarner
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of lnshop

ASKER

Thank-you very much for your help with this, you put me on the right track and its now working.  The query is
 premsql = "Select premsht.policy, premsht.sheet_no from premsht where premsht.policy + str(sheet_no, 4) between padr(""" & CasPolicyNo & """, 12) and padr(""" & CasPolicyNo & """, 15) +""9999"""
Fantastic!  I wasn't sure where we were headed.  Now I see it, too.