Solved

query not pulling in ID column correctly on query

Posted on 2016-09-23
4
46 Views
Last Modified: 2016-09-23
sql server 2008 r2

I have an id_column that is varchar(50)

I have this query where I'm trying to pullout data in sequence piece by piece. into a text file.

[code][/
SELECT [Id_Column]
      ,[MfrnumCond]
     ,[Sku]
     ,[LongDesc]
      ,[DateCreated]
      ,[DateUpdated]
  FROM [WeeklyFile].[dbo].[FuncExt]
  where FuncExt.[Id_Column] <= '2000000'
  --where FuncExt.[ID_COLUMN] between  '2000000'  and '5000000'
  --where FuncExt.[ID_COLUMN] between  '5000000'  and '8000000'
  --where FuncExt.[ID_COLUMN] between  '8000000'  and '10000000'
  --where FuncExt.[ID_COLUMN] > '10000000'
code]

The query is pulling out records not in sequence ???

Thanks
fordraiders
0
Comment
Question by:fordraiders
  • 2
4 Comments
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 41813067
You are trying to treat strings as numbers.  You will need to cast the ID_COLUMN to an integer if you want to use the BETWEEN operator as you are.

Here is a short example to demonstrate the issue with strings as numbers:

WITH cteNumber AS
(
	SELECT 1 AS Value
	UNION ALL
	SELECT Value + 1 AS Value
	FROM cteNumber
	WHERE Value < 100000
)
SELECT CAST(Value AS VARCHAR)
FROM cteNumber
ORDER BY CAST(Value AS VARCHAR)
OPTION (MAXRECURSION 0)

Open in new window

0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 41813071
A varchar comparison works fundamentally different from a numeric comparison, since it is a simple left-to-right character comparison and not a whole value comparison.

For example:

SELECT *
FROM (
    values('2000000'),('214')
    ) AS FuncExt(Id_Column)
  where FuncExt.[ID_COLUMN] between  '2000000'  and '5000000'

You could add a check for the length of the column:

  where LEN(FuncExt.[Id_Column]) < 7
  --where LEN(FuncExt.[Id_Column]) = 7 and FuncExt.[Id_Column] <= '2000000'
  --where LEN(FuncExt.[Id_Column]) = 7 and FuncExt.[ID_COLUMN] between  '2000000'  and '5000000'
  --where LEN(FuncExt.[Id_Column]) = 7 and FuncExt.[ID_COLUMN] between  '5000000'  and '8000000'
  --where LEN(FuncExt.[Id_Column]) = 7 and FuncExt.[ID_COLUMN] between  '8000000'  and '9999999'
  --where LEN(FuncExt.[Id_Column]) >= 8
0
 
LVL 34

Accepted Solution

by:
Brian Crowe earned 500 total points
ID: 41813074
Try this...

SELECT [Id_Column]
      ,[MfrnumCond]
     ,[Sku]
     ,[LongDesc]
      ,[DateCreated]
      ,[DateUpdated]
FROM [WeeklyFile].[dbo].[FuncExt]
WHERE CAST(FuncExt.[Id_Column] AS INT) <= 2000000

Open in new window


Keep in mind that this is a non-sargable query that will require a table scan to execute so performance will be dependent on the size of the table.
0
 
LVL 3

Author Closing Comment

by:fordraiders
ID: 41813091
Thanks Brian, worked great !!
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.

733 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