|
When I try to run this query, I get a message saying "Syntax error near '='" on the 2nd line (actually, any line that has the ISNULL check in it):
SELECT o.ID
,o.[Name] = ISNULL([Name], 'NO VALUE')
,o.Abbreviation = ISNULL(Abbreviation, 'NO VALUE')
,o.OrgTypeID = ISNULL(OrgTypeID, 'NO VALUE')
,o.DisplayOrder = ISNULL(DisplayOrder, 999)
,s.MetricNumber
,s.Status = ISNULL(Status, 'U')
,s.Command
FROM [dashboard].[tblSLOrganizations] o
RIGHT OUTER JOIN [dashboard].[tblMetricStatus] s
ON (s.Base = o.Abbreviation)
ORDER BY DisplayOrder, MetricNumber
If I comment out all of the "= ISNULL..." instances, the query works.
What am I doing wrong?
.45 ACP - because shooting twice is just silly
-----
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001
|
|
|
|
|
SELECT o.ID
,ISNULL(o.[Name], 'NO VALUE') AS Name
,ISNULL(o.Abbreviation, 'NO VALUE') AS abbreviation
,ISNULL(o.OrgTypeID, 'NO VALUE') AS OrgTypeID
,ISNULL(o.DisplayOrder, 999) AS DisplayOrder
,s.MetricNumber
,ISNULL(s.Status, 'U') AS Status
,s.Command
FROM [dashboard].[tblSLOrganizations] o
RIGHT OUTER JOIN [dashboard].[tblMetricStatus] s
ON (s.Base = o.Abbreviation)
ORDER BY DisplayOrder, MetricNumber
|
|
|
|
|
you beat me to it.
|
|
|
|
|
Sorry  been doing queries like that most of the week
|
|
|
|
|
Could you please clarify on why the column alias is required?
Thanks
Md. Marufuzzaman
I will not say I have failed 1000 times; I will say that I have discovered 1000 ways that can cause failure – Thomas Edison.
|
|
|
|
|
While it is not required you end up with a result set with no relevant column headers and you cannot use such a query in a view.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Yep..I agreed Thanks.
Thanks
Md. Marufuzzaman
I will not say I have failed 1000 times; I will say that I have discovered 1000 ways that can cause failure – Thomas Edison.
|
|
|
|
|
after reading up a bit, I think you need an AS keyword:
SELECT o.ID
,ISNULL(o.[Name], 'NO VALUE') as [Name]
...
|
|
|
|
|
If T-SQL it is not mandatory.
|
|
|
|
|
I have a table in my DB containing a column of type Datetime, which allows null values. I created DataSet on which I drag&droped this table. Everything was fine, DataTable with TableAdapter have been successfully created and are both working. When I select this column in this DataTable (on designer surface) it shows in properties that this column is indeed nullable (AllowDBNull: True).
However ADO.NET generated false code for this example. Look at the following code:
var t= new ABCTableAdapter().GetData();<br />
ticket[0].MyNullableColumn -> this return 'Datetime' instead of 'Datetime?'.
If a given record in DB has indeed null value in this column, then such code generates "StrongTypingException". So this is quite clear that ADO.NET made some mistakes in code generation.
I tried deleting all DataSet and recreating it from scratch. Also I`m sure that my column really allows null values as some records have such value in this column.
Could you please help me and tell how am I supposed to force ADO.NET to generate this code properly? I want the following code to compile:
if (t[0].MyNullableColumn != DBNull.Value) {...}
|
|
|
|
|
This is why you don't use the drag and drop crap.
It generates a method .IsMyNullableColunNull()
|
|
|
|
|
Jamie is right, learn to use a proper DAL and get control of your application, don't rely on MS generated code!
This may be a better option for you
t[0].MyNullableColumn ?? DBNull.Value
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Sure sure, its the best to write everything in assembler or even machine code. Such approach is senseless. ADO's designer saved me a lot of time during many years, this is first time it fails. Still a very good result.
|
|
|
|
|
Yoyosch wrote: Sure sure, its the best to write everything in assembler or even machine code.
Actually thats a bloody silly statement, I'm talking about the framework you use every day.
Yoyosch wrote: ADO's designer saved me a lot of time during many years, this is first time it fails
This would seem to indicate that you are doing fairly straight forward CRUD operations. We see this type of problem in the forums regularly, and yes it is generally something slightly different.
IMHO it is not the productive issues, the designer/wizard stuff does make it quick and simple, it is the lack of any deep knowledge about your data and tools.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I implementd CLR stored procedure on 'Database1'. I would like to perform some UPDATE statements inside this CLR stored procedure in 'Database2'. Is is possible? If so, then how?
|
|
|
|
|
You can have a use database statement in your proc.
I have not idea how you do that in the clr, I have never turned it on, I use TSQL. You will find there are very few devs use the clr, most will use TSQL and sql servers SSMS tool. You do not need another layer of abstraction between you and your data.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Is there any chance I make the SQL server think Sunday is the last day of the week (not the first day of the next week) with out changing the regional settings?
This is messing up the weekly reports queries...
I have no smart signature yet...
|
|
|
|
|
Solved my own problem!
SET DATEFIRST 1; -- set's the first day of the week to monday
SET DATEFIRST 7; -- set's the first day of the week to sunday
I have no smart signature yet...
|
|
|
|
|
Hi All,
I have a SQL database column, name "Closing Date" with datatype as Datetime. For example, the entries are like this:
1-Nov-2009
21-Dec-2009
10-Jan-2010
21-Mar-2010
11-Apr-2010
Now What I want is to select a date which is less than my requested date from a text box.
e.g. If I want to select a date which is less that 31-Mar-2010, then I should get 21-Mar-2010.
or, If I want to select a date which is less that 9-Jan-2010, then I should get 21-Dec-2009.
Presently I am using following in VB.net.
cmd = New SqlCommand("SELECT [Closing Date] FROM client_details WHERE [Closing Date] <= textbox.text", con)
But I think I need to use something else. Hope My query is clear. Please suggest me how to go ahead?
Regards
R.S.
|
|
|
|
|
Try
cmd = New SqlCommand("SELECT [Closing Date] FROM client_details WHERE [Closing Date] <= @selectedDate", con);
Dim param As New SqlParameter("selectedDate", DateTime.Parse(textbox.Text, CultureInfo.InvariantCulture));
cmd.Parameters.Add(param);
|
|
|
|
|
Hi,
try
"SELECT MAX([Closing Date]) FROM client_details WHERE [Closing Date] <= '"+textbox.text+"'"
(assuming C# is what you need). With some comments:
1. it is much easier when database field names don't contain special characters and spaces.
2. such SQL statement is vulnerable to SQL injection: users could enter parts of an SQL command, which your code then executes. Solution: verify the textbox content, or better yet, use SQLparameter.
3. the user must enter a string representing a date, according to the format your database is expecting. It is better to have the user enter data according to the system's regional settings, then to convert it to what the database needs. Again SQLParameter is the better approach: use DateTime.Parse(textbox.text) (or TryParse), then set the SQLParameter.
|
|
|
|
|
Here is another alternative - no better, no worse just different.
"SELECT TOP 1 [Closing Date]) FROM client_details
WHERE [Closing Date] <= '"+textbox.text+"'
order by [Closing Date] DESC"
As Luc suggested use parameterized queries - this is IMPORTANT.
There are usually a number of ways to sole a problem, try both and look at the execution plans to decide the better solution. Then throw an index on the close date field and see the difference!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
This is for SQL Server 2008:
Will the stored procedure be rolled back if the db server gets an error (server crash or performance problem) in the middle of it's operation?
This is the idea:
- stored proc spTest contains 2 insert statements
- exec spTest
- spTest executes 1st insert statement
- stored procedure still running... but server gets some problem that's unrelated to this stored procedure, which causes the stored proc to stop (e.g. server crash)
Will the data in the 1st insert statement be rolled back?
Thanks in advance!
Rafferty
|
|
|
|
|
|
What do you mean?
Currently, these 2 insert statements are not enclosed in try catch blocks, but I didn't think that I need to because the error is caused by something else that is external to this stored procedure.
Or is there some DB configuration-wide that I should set?
Rafferty
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
