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
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?
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)"
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" ?
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" ?
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)
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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"""
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.