Solved

Problem with index on sql query

Posted on 2007-12-04
8
189 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

776 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