Solved

query not pulling in ID column correctly on query

Posted on 2016-09-23
4
47 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
[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
  • 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
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.

691 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