|
|
Posted by RodStrongo1 on 09/30/99 12:01
On Jan 15, 5:43 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> (RodStron...@gmail.com) writes:
> > The two queries are pertaining to domestic vs intl. It does a lookup
> > for a country code, and case 1 of domestic, case 2 of international.
> > The query I copied in here is the intl. Both queries run from the
> > same table, but searching the domestic entries runs smoothly.
>
> > Running the sp_spaceused returns:
> > name rows reserved
> > data index_size unused
> > distributors_detail 973 440 KB 368 KB 56 KB > 16 KB
>
> Less than a megabyte. The poor search conditions should not be an issue.
>
> But when I reviewed your original post, I notice that the SQL that
> is generated is not correct. There is this:
>
> > sql = sql& "isNull(cust_code_3,'') in('', 'CH') AND "
> > sql = sql & " or LEFT(cust_no, CHARINDEX('-', cust_no) - 1) = '"& trim(arrDist_id_physical(iIntern)) &"'"
> > sql = sql& "LEFT(distributors_detail.cust_no, CHARINDEX('-', distributors_detail.cust_no) - 1) IN ("
>
> The line in the middle does not fit in. AND can be followed by OR
> and a string literal cannot be followed by a call to a system function.
>
> Mind you, reading SQL code which is so entwined with client code is
> difficult. And difficult to maintain.
>
> In any case, it does not seem that you have posted the actual query
> you have problem with. Or at least, I would expect a completely
> different error than a timeout error for a query that does not
> compile. So that leaves me a bit in the dark.
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Thank you for your help Erland,
Sorry for the confusion in the last posting, I accidentally included a
line that is commented out. As I said, I kind of got tossed into this
one, and I'm trying to wade through and learn how everything works
here.
The actual statement that gets executed is:
select LEFT(distributors_detail.cust_no, CHARINDEX('-',
distributors_detail.cust_no) - 1) AS distributorNumber, * FROM
distributors_detail where right(distributors_detail.cust_no,
len(distributors_detail.cust_no) - (charindex('-',
distributors_detail.cust_no) - 1)) in('-0','-1','-X','-A', '-W') AND
isNull(cust_code_3,'') in('', 'CH') AND
LEFT(distributors_detail.cust_no, CHARINDEX('-',
distributors_detail.cust_no) - 1) IN ('001273','001075') AND
isNull(cust_code_2,'') = '' ORDER BY cust_no ASC
Having any of the '-0, -1, -X, -W' appended on the customer number is
used to indicate what type of location this is for the distributor
(office, warehouse, etc) .
I have a development version of this table, and things work fine in
there, with the same statement, so it leads me to believe there is
some inconsistency in the data,
as the number of records is not huge (~1000, ~300 international
records) and there appears to be no blocking going on.
Is there a way for me to examine the data or the progress of the query
to indicate where it is getting hung up?
Navigation:
[Reply to this message]
|