Solved

help with sql

Posted on 2013-06-11
4
146 Views
Last Modified: 2013-06-27
I have a table where some of the columns have the value 'NULL' instead of  null values so my query below fails and I need a way to fix the query, I have limited control on data:

select * from temp where

FY1= (YEAR(DATEADD(month, 6 + DATEDIFF(month, 0, getdate()), 0)))

and tempid=3145

here the 3145 has the FY1 value as 'NUll' where as others have the value either like 2013 or null and the column is varchar(4)

all I am doing is bring in data which matched this fiscal year

and with the above query I am getting the error :

Conversion failed when converting the varchar value 'NULL' to data type int.

where as the same works for null values
0
Comment
Question by:welcome 123
  • 2
4 Comments
 
LVL 23

Accepted Solution

by:
Ioannis Paraskevopoulos earned 200 total points
ID: 39239059
Well,

You could add
and FY1<>'NULL'

Open in new window

In your where clause.

Giannis
0
 
LVL 4

Expert Comment

by:BAKADY
ID: 39239063
select * from temp where case FY1 when NULL then 0 else case when FY1= (YEAR(DATEADD(month, 6 + DATEDIFF(month, 0, getdate()), 0))) then 1 else 0 end end = 1

Open in new window

0
 
LVL 23

Expert Comment

by:Ioannis Paraskevopoulos
ID: 39239068
Or even change your where to

CASE WHEN FY1 = 'null' THEN NULL ELSE FY1 END = (YEAR(DATEADD(month, 6 + DATEDIFF(month, 0, getdate()), 0)))

Open in new window


Giannis
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39239933
I'd strongly recommend avoiding the implied conversion of the data to int by converting the single date calculation to varchar
select
*
from temp
where FY1= convert(varchar,(YEAR(DATEADD(month, 6 + DATEDIFF(month, 0, getdate()), 0))))
or FY1 is null
or FY1 = 'NULL'

Open in new window

That solves the conversion issue, but what do you do with (true) nulls and the string 'NULL'?? i.e. how do you know what fiscal year those records belong to? If you are simply going to ignore those, just remove the last 2 lines above.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

747 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

12 Experts available now in Live!

Get 1:1 Help Now