Solved

Problem with index on sql query

Posted on 2007-12-04
8
197 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
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 …
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

729 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