Solved

Problem with index on sql query

Posted on 2007-12-04
8
192 Views
Last Modified: 2013-11-26
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

0
Comment
Question by:lnshop
  • 4
  • 3
8 Comments
 
LVL 3

Expert Comment

by:randomjames
ID: 20407651
Should you padr(policy + str(sheet_no, 4)) like you did with CasPolicyNo?
0
 
LVL 33

Expert Comment

by:CarlWarner
ID: 20407658
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?
0
 

Author Comment

by:lnshop
ID: 20407931
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)"
0
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
LVL 33

Expert Comment

by:CarlWarner
ID: 20408010
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" ?
0
 

Author Comment

by:lnshop
ID: 20413585
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)
0
 
LVL 33

Accepted Solution

by:
CarlWarner earned 250 total points
ID: 20414227
>>>No the policy number is CRX53328, the sheet_no is 4
>>>select policy from premsht where policy+str(sheet_no, 4)=padr("CRX53328", 15)

When you pad the right side of the equation, you are padding it with spaces.
So, it ends up being "CRX53328       " or "CRX53328xxxxxxx" where x=<space>, right?

On the left side, you show a policy no, which is eight characters, right?  And then you toss in the concatention of the sheet_no, which is four more characters.  That concatenation of 8 chars and 4 chars is 12 characters.  Are you sure you want to compare a string of 12 chars on the left to 15 chars on the right, where the right side seems to believe the last seven chars are spaces?
0
 

Author Comment

by:lnshop
ID: 20414494
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"""
0
 
LVL 33

Expert Comment

by:CarlWarner
ID: 20414781
Fantastic!  I wasn't sure where we were headed.  Now I see it, too.
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question