Solved

query not pulling in ID column correctly on query

Posted on 2016-09-23
4
36 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
Comment Utility
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:ScottPletcher
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks Brian, worked great !!
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
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 …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now