Where Clause Changing Based On NULL Column(s)
Dec 30, 2003
Hello DBAs:
I am having difficulty constructing a where clause. I have 4 columns in a table. Col1 is never NULL. Col2,3,4 may or may not contain NULL values. Here is the situation.
1. Col2,3,4 are NULL
WHERE Col1 = condition
2. Col2 is NULL, 3 may or may not be NULL. When Col3 is NOT NULL, Col4 may or may not be NULL
where col1 and (col3(Not Null values) OR Col4(Not Null values)) = condition.
How do I construct this logic. Please help
View 6 Replies
ADVERTISEMENT
Apr 7, 2008
I have an optional param passed to my sproc @Pid if that is populated i want to have it part of where clause, if it is null then not in where
I know this is wrong, and very new to sqlsrver:
WHERE (Pos = 'T') AND (ofDate= @OfDate)
IF not @PID is null
AND (PID = @PID )
GROUP BY
bla bla bla
Thanks
View 12 Replies
View Related
Feb 28, 2008
Hi all,
One of my columns is the table has some Null values, and I Would like to stop having NULL values into that column any more.
I know, If I alter the column to NOT NULL will throw me an error, since it does a batch update.
Is there any way to achieve this...
Thanks...
View 3 Replies
View Related
Apr 19, 2008
Hi I have a table, which contains Char and NChar NOT NULL columns
Now I need to change it to NULL, when I use the following command, it fails for the following error,
The command I used,
ALTER TABLE <TableName>
ALTER COLUMN <ColName> CHAR NULL
ALTER TABLE <TableName>
ALTER COLUMN <ColName> NCHAR NULL
Msg 8152, Level 16, State 13, Line 1
String or binary data would be truncated.
The statement has been terminated.
But for the same table, the below command executes fine,
ALTER TABLE <TableName>
ALTER COLUMN <ColName> SMALLINT NULL
Also I can change the NULLABILITY from NOTNULL to NULL using Enterprise Manger, editing the table using Table Design and selecting Allow Nulls option.
I need a script to accomplish this task.
Any help would be greatly appreciated.
-Senthil
View 1 Replies
View Related
Jun 26, 2000
I'm using a Case statement to change an Order By clause on the fly, eg
ORDER BY case when @SortBy = 1 then s.ITEM_NAME
when @SortBy = 2 then s.ITEM_ID
when @SortBy = 3 then s.ITEM_SIZE
end
The numeric columns work fine but when @SortBy = 1, I get the following message when I try to run the sp:
Server: Msg 8114, Level 16, State 5, Procedure usp_CML_SAO_RptPresLvl, Line 95
Error converting data type varchar to numeric.
ITEM_NAME is a varchar(40) containing alphanumeric characters; ITEM_ID is a numeric(8,0) & ITEM_SIZE is a varchar(5) containing numeric characters.
Is there some rule preventing me to dynamically change the Order By if using a alphanumeric characters?
Thanks for any help you can offer
Jo
View 2 Replies
View Related
Oct 18, 2007
Hi!
I am designing a dimension table which will include a short name column based on the (full) name column. For example say Product dimension where I will have ProductName and ProductShortName. ProductShortName will be the first 6 characters of ProductName. I could populate ProductShortName using:
Substring in the select when I select from the original system, e.g. SUBSTR(PRODUCT_NAME, 1, 6) AS ProductShortName
Create a derived column in the SSIS flow which does the same thing
Create the ProductShortName column as a computed column which uses substring on ProductName
Create a trigger that populates ProductShortName based on ProductName when a row is inserted or updated
Create a named calculation in the table in the Analysis Services project's data source view
Create a named query in the Analysis Services project's data source view
I usually use 1, and 5 or 6 would only be used if I only will create reports against the cubes. 3 seems easiest to maintain, so I am thinking about using that one, but maybe it is slow for the data flow as I imagine it must be something like using 4, or when is the column "created" at runtime, i.e. when the table is queried?
Which approach(es) do or would you use? Pros and cons?
Thanks!
View 3 Replies
View Related
Jan 5, 2008
in the following procedure i want to pass in a value and as a result of the value I want to change the actual body of the query:
example, I pass in the value 'TS2' and I now want my query to see if TS2MIN < 70
i think I am doing it right, but it is throwing an error saying that "Conversion failed when converting the varchar value 'TS2MIN' to data type int."
any idea on what i am doing wrong?
ALTER proc SPU_YearlyTemp 905,'TS2',70,254,2007
@SITEID INT,
@SENSOR VARCHAR(10),
@MIN INT,
@MAX INT,
@YEAR INT
AS
DECLARE @S AS VARCHAR(12)
SET @S=(
SELECT
CASE @SENSOR
WHEN 'TS1' THEN 'TS1MIN'
WHEN 'TS2' THEN 'TS2MIN'
WHEN 'TS3' THEN 'TS3MIN'
WHEN 'TS4' THEN 'TS4MIN'
WHEN 'TS5' THEN 'TS5MIN'
WHEN 'TS6' THEN 'TS6MIN'
WHEN 'TS7' THEN 'TS7MIN'
WHEN 'TS8' THEN 'TS8MIN'
END AS SENSORVALUE)
select top(10)*
into #tempMin
from sitecontroldataarchive
where siteid = @SITEID
and @S < @MIN --this is where the error occurs,
SELECT * FROM #tempMin
View 11 Replies
View Related
Jul 20, 2005
Example, suppose you have these 2 tables(NOTE: My example is totally different, but I'm simply trying to setupthe a simpler version, so excuse the bad design; not the point here)CarsSold {CarsSoldID int (primary key)MonthID intDealershipID intNumberCarsSold int}Dealership {DealershipID int, (primary key)SalesTax decimal}so you may have many delearships selling cars the same month, and youwanted a report to sum up totals of all dealerships per month.select cs.MonthID,sum(cs.NumberCarsSold) as 'TotalCarsSoldInMonth',sum(cs.NumberCarsSold) * d.SalesTax as 'TotalRevenue'from CarsSold csjoin Dealership d on d.DealershipID = cs.DealershipIDgroup by cs.MonthIDMy question is, is there a way to achieve something like this:select cs.MonthID,sum(cs.NumberCarsSold) as 'TotalCarsSoldInMonth',TotalCarsSoldInMonth * d.SalesTax as 'TotalRevenue'from CarsSold csjoin Dealership d on d.DealershipID = cs.DealershipIDgroup by cs.MonthIDNotice the only difference is the 3rd column in the select. Myparticular query is performing some crazy math and the only way I knowof how to get it to work is to copy and past the logic which isgetting out way out of hand...Thanks,Dave
View 5 Replies
View Related
Mar 8, 2000
I have a field called Trainee1 and there are about 3 in every 10 records that the value is <NULL> - I want to change that value to 0.
I have tried to use this command but it changed EVERY record to <NULL>
update Trainee1 set RegId=replace(RegId,'NULL','0')
Has anyone any suggestions on how to just change the records where the value of traineeId is <NULL>???
Thanks in advance,
Anthony
View 2 Replies
View Related
Jul 8, 2006
Hi
I am generating report with my datasource to an OLAP Cube. I have scenario that there are 2 dimension tables pointing to a single fact table. According to a user input, i have to use one dimension table and not the other and vice versa. I tried using IIF statement in the MDX query designer., but was facing errors.
First of all i want to know if this is possible and if yes, how?
Also , Is it possible to open a window form on clicking any report data
(Similar to assigning hyperlink, but i want to open a window form instead!!!)
regard
Sai
View 2 Replies
View Related
Jul 28, 2015
I have a excel file which has a column called "Code" and their values are A,B,C,D,E,F,G,H. I want to create a new column called "status" based on the values of "Code".
Code:
A
B
C
D
E
F
G
H
If A,C,E,G then "status" = "Active" else if B,D,F,H then "Status" = "Inactive". I like to do it using "Derived Column".
View 4 Replies
View Related
Aug 15, 2013
Aim- the following columns “Cancel_Date”, “First_Post_Date”,“Last_Post_Date” can either be populated with figures or have a null value.
I want all Null values to be removed and be replaced with a blank
Final table is
Select *
from #test
left join #SF_AccountBuild on #test.FDMSAccountNo = #SF_AccountBuild.[External FDMSaccountno]
Results
[FDMSAccountNo],
External_ID ,
Parentsfid,
[DBA Name],
[Legal Name],
Street,
[MM3-DBA-ADDR2],
[Code] .....
View 5 Replies
View Related
Nov 10, 2015
When building stored procs, I typically have several nullable parameters. Then in the where clause I will either do this
WHERE (@MyParam IS NULL OR MyColumn = @MyParam)
or this:
WHERE MyColumn = COALESCE(@MyParam, MyColumn)
Lately, I've noticed that either of these imposes a performance penalty. In other words, if I change the first one to
WHERE MyColumn = @MyParam
without the IS NULL, it is noticeably faster. If I only have one or two nullable parameters, I can separate the logic using IF/ELSE, but it becomes huge if I have a bunch of nullable params.I've looked at the execution plans.
View 8 Replies
View Related
May 11, 2007
I'm new to SSIS so please be gentle...
I'm creating a package that needs to go to an FTP site (FTP Task), download a file, unzip it and then process a series of table loads for the 12 text files that will be unzipped. My problem is that the zip file is a date (yyyymmdd.zip) which is normally the previous day of execution EXCEPT on Mondays when it would be the previous Friday's date. My thought is that IF (magic question) I could determine the day of the week in the SSIS package, I know that Tuesday-Friday is just a formatting exercise of getdate()-1 and Monday would be getdate()-3 but I can't seem to find a way (function?) that will allow me to determine the day of the week?
Thanks in advance!
View 6 Replies
View Related
Jun 14, 2014
tblScore contains score for each problem
id problemID score
------------------------
1 1 10
2 2 30
tblSubmission contains problem submissions for each user
id user problemID accepted
-----------------------------------------------
1 UserA 1 0
2 UserA 1 0
3 UserA 1 1
4 UserA 2 1
5 UserB 1 0
6 UserB 1 1
7 UserB 2 1
For UserA :
- For problemID 1
-> submitted three times
-> rejected for first two submission and accepted on third submission.
- For problemID 2
-> submitted one time
-> accepted on first submission
For UserB :
- For problemID 1
-> submitted two times
-> rejected for first submission and accepted on second submission.
- For problemID 2
-> submitted one time
-> accepted on first submission
Now I would like to process the table and want to get the following result :
user Score
--------------------------
UserA 36 (6 + 30)
UserB 38 (8 + 30)
Explanation :
- For each rejected submission, a -2 point penalty.
- UserA have submitted probelmID 1
- > score of problemID 1 is 10.
- > first two times rejectd
- > third time accepted.
-> score = 10 - 4 = 6
- UserA have submitted problemID 2
- > score of problemID 2 is 30
- > first time accepted. No penalty will be counted
- > score = 30
so final score for UserA = 30 + 6 = 36
Similar for UserB.
View 2 Replies
View Related
Jun 21, 2008
Hello!
suppose i have two tables, table1 columns(empcode (pk), empDept) and table2 columns(empcode (FK),Date,Attendance) i wanted to write a query to get output like
DEPT ABSENT
-----------------------------
Accounts 10
EDP Section 0 **
Admin 2
Stationary 0**
if no employee is absent in the department it has to display Zero
View 2 Replies
View Related
Sep 13, 2007
I am writing a fairly simple sql, and I would like to write something like
Code Snippet
select
firstname as firstname,
case
when firstname = 'Peter' then 'yes'
else
'no'
end as whatever
from
MyTable
where
whatever = 'yes'
And this should then select out the rows where column number 2 is 'yes'.
It doesn't work, and I have to copy the firstname = 'Peter' into the where clause.
But why?
View 4 Replies
View Related
Mar 1, 2007
I’m working with a team of programmers who are in the process of upgrading an older ASP application to ASP.NET 2.0 (VB). The existing application connects to the SQL Server Database using different Logins, depending on the User logged into the ASP Application.
We are hoping to do the same using ASP.NET 2.0, although we haven’t been able to find a simple solution. The most feasible solution we have found includes programmatically trapping all the Selecting etc events and changing the SqlConnection at run time. This allow for ease during Designing of the Web Pages, but implementation requires adding code to each web page (>100 pages).
We are hoping there may be a simple solution which can be implemented once for the application where the event can be trapped at the application level, and the SqlConnection set at this point. Or possibly another type of solution such as creating a new class etc that can be used?
Does anyone have a simple solution where the username / password for the Database Connection can be set at run time?
View 2 Replies
View Related
Jan 8, 2008
Hi All,
I have a report that has a toggle item that toggles between showing and hiding report items specifically the detail rows, I am wondering can I also used this toggle item to change the font color of other report items. Exampl is I have a toggle item, EmployeeName that toggles the detail rows of the employee from visible to hidden. Can I also use this toggle to change the text color of other report items or highlighting different items.
Thanks & Regards
Fadzli
View 6 Replies
View Related
Nov 18, 2003
OK. I have this query, works on another box fine.
SELECT *
FROM bookkeep RIGHT OUTER JOIN
acraccts ON LEFT(bookkeep.accnum, 9) = acraccts.p_accnum
WHERE (bookkeep.busdate = '03/09/10') AND (bookkeep.tradetype = 'S')
on my sql box, if i run it, i get no data.
i figured out that if i change the where clause to (bookkeep.busdate='2003/09/10') it works
OR
if i simply put SET DATEFORMAT YMD on the first line before the SELECT * that it also works.
my problem is the basic query is hard coded and i really can't change it.
is there a global sql server setting that will make my sql 2000 sp3 box recognize '30/09/10' as 2003/09/10?
View 1 Replies
View Related
Jul 14, 2015
My source table has two columns... Policynum and PolicyStartdate and data looks like..
.
Policynum PolicyStartdate
123G 01/01/2012
456D 02/16/2012
789A 01/21/2012
163J 05/25/2012
Now my output should return based on 3 parameters..
First two parameters are date range... let say @fromdt and @todt
Third parameter is @policynum
Scenario-1: Enter dates in date range param and leave policynum param blank
Ex: policystartdate between '01/01/2012 and '01/31/2012'.... It returns 1st and 3rd rows from above in the output
Scenario-2: enter policy num in policynum param and don't select any dates
Ex: policynum ='456D' It returns 2nd row in the output
Scenario-3: Select dates in date range param and enter policynum in param
Ex: policystartdate between '01/01/2012 and '01/31/2012' and policynum
='163J'. it should return only 4th row even though dates were selected(Override date range when policynum is entered in param and just return specified policynum row in the output)
I need t-sql code to get above results.
View 12 Replies
View Related
Jun 3, 2008
I am working on a Function that takes multiple parameters. I have a query that populates a temporary table, and then it processes some logic. My question is, if the parameter is passed as null, I dont want the query to be affected by this null value. Rather, I would like to not pass it at all to the query. So if the parameter is NULL, dont pass it through the query. I have the following but its not compiling right:
SELECT bom.pEngr_BOM_ID , bom.fEngr_Item_ID, det.pEngr_BOM_Detail_ID, 1, bom.Bill_Type, bom.Rev_Ltr, bom.Series_Ltr
FROM dbo.Engr_BOM_Control bom WITH (nolock)
INNER JOIN dbo.Engr_BOM_Detail det WITH (nolock)
ON det.fEngr_BOM_ID=bom.pEngr_BOM_ID
WHERE bom.pEngr_BOM_ID=@v_pEngr_BOM_ID
AND det.fEngr_BOM_ID=@v_pEngr_BOM_ID
CASE WHEN @v_Bill_Type IS NOT NULL THEN
AND bom.Bill_Type=@v_Bill_Type
END
View 3 Replies
View Related
Apr 2, 2007
I am getting this error: "Cannot insert the value NULL into column 'OrderID', table 'outman.outman.Contact'; column does not allow nulls. INSERT fails." -- But my value is not null. I did a response.write on it and it show the value. Of course, it would be nice if I could do a breakpoint but that doesn't seem to be working. I'll attach a couple of images below of my code, the error, and the breakpoint error.
Server Error in '/' Application.
Cannot insert the value NULL into column 'OrderID', table 'outman.outman.Contact'; column does not allow nulls. INSERT fails.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Cannot insert the value NULL into column 'OrderID', table 'outman.outman.Contact'; column does not allow nulls. INSERT fails.Source Error:
Line 89: sContact.Phone = sPhone.Text.Trim
Line 90: sContact.Email = sEmail.Text.Trim
Line 91: sContact.Save()
Line 92:
Line 93: Dim bContact As Contact = New Contact()Source File: F:InetpubwwwrootOutman KnifeCheckout.aspx.vb Line: 91 Stack Trace:
[SqlException (0x80131904): Cannot insert the value NULL into column 'OrderID', table 'outman.outman.Contact'; column does not allow nulls. INSERT fails.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +857354
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734966
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838
System.Data.SqlClient.SqlDataReader.HasMoreRows() +150
System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout) +214
System.Data.SqlClient.SqlDataReader.Read() +9
System.Data.SqlClient.SqlCommand.CompleteExecuteScalar(SqlDataReader ds, Boolean returnSqlValue) +39
System.Data.SqlClient.SqlCommand.ExecuteScalar() +148
SubSonic.SqlDataProvider.ExecuteScalar(QueryCommand qry) +209
SubSonic.DataService.ExecuteScalar(QueryCommand cmd) +37
SubSonic.ActiveRecord`1.Save(String userName) +120
SubSonic.ActiveRecord`1.Save() +31
Checkout.btnCheckout_Click(Object sender, EventArgs e) in F:InetpubwwwrootOutman KnifeCheckout.aspx.vb:91
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42
View 8 Replies
View Related
May 13, 2015
It's giving me an error while I'm trying to change column value from not null to null..
'tblid' table
- Unable to modify table.
Cannot insert the value NULL into column 'ValidID', table 'Xe01.dbo.Tmp_tblid'; column does not allow nulls. INSERT fails.
The statement has been terminated.
View 4 Replies
View Related
Apr 19, 2007
All,
Please can someone advise how to use SQL select statement with where clasue which is based on a textBox.text value.
ex. below example I set the textbox.text value to a C# variable called TextBoxValue1 but I receive error this is not a valid
This is all done in Page_Load event backend code.
string strCommandtext = "Select Type.TypeName, Type.TypeDesc FROM Type Where Type.TypeName = TextBoxValue1";
View 2 Replies
View Related
Feb 18, 2014
I have written a merge Statement where i am facing trouble to delete the data basing on Where Clause Condition.
1) Case 1 : For example i have inserted Data from Source to Target based on Date Key Condition.Take an Instance 10 Records Inserted.
2) Case 2 : For example some changes in the records and it has been updated through the Merge Statement .
3) Case 3 : For the Same Date key based on Conditions now three records has came and need to be inserted and rest should be deleted for that Date Key.
How i need to proceed on this before 10 records are not getting deleted and new records adding for that one
My Example Code :
MERGE INTO TargetTable AS Target
USING (
SELECT DISTINCT Col1,
Col2
FROM Table1 AS cga
)
ON dad.AnchorDate = CASE
[Code] ....
View 1 Replies
View Related
Jul 9, 2015
I basically want to select all GRNID's from one table but they have to be between dates in another table.So I want all GRN's between two dates found in the ABSPeriodEndDate table. To find out the start date for the between clause I need to find the MAX Period then minus 1 and the max year. To find the end date of the between clause I want I need to find both the max period and year. But I want the DateStamp column to return the results for the between clause. My query is below:
SELECT tblGRNItem.GRNID
FROM tblGRNItem
INNER JOIN ABSPeriodEndDates ON tblGRNItem.DateCreated = ABSPeriodEndDates.DateStamp
WHERE tblGRNItem.DateCreated BETWEEN
(SELECT ABSPeriodEndDates.DateStamp FROM ABSPeriodEndDates WHERE ABSPeriodEndDates.DateStamp = (SELECT
[code]....
View 6 Replies
View Related
Mar 9, 2007
I can't figure this one out...
I have an Excel spreadsheet (Excel 2003 format) with a column of values. The column is formated to General (although I've tried it formated to text). The values are alphanumeric. However, there are a few values that are completely numeric.
Example:
_ITEMNUMBER_
1-528
K214-5
184PR
45678
As can be seen, the last value is completely numeric. I am importing into a table with one column that is formated as nvarchar(50).
I use dtswizard.exe and do a simple import into the table and the alphanumeric values import just fine but anytime a value is completely numeric it has a NULL value in the table instead of the value that should be there.
I've tried changing the format of the column to nchar, char, etc, etc. Always comes in NULL.
Any ideas?
View 7 Replies
View Related
May 14, 2008
2 examples:
1) Rows ordered using textual id rather than numeric id
Code Snippet
select
cast(v.id as nvarchar(2)) id
from
(
select 1 id
union select 2 id
union select 11 id
) v
order by
v.id
Result set is ordered as: 1, 11, 2
I expect: 1,2,11
if renamed or removed alias for "cast(v.id as nvarchar(2))" expression then all works fine.
2) SQL server reject query below with next message
Server: Msg 169, Level 15, State 3, Line 16
A column has been specified more than once in the order by list. Columns in the order by list must be unique.
Code Snippet
select
cast(v.id as nvarchar(2)) id
from
(
select 1 id
union select 2 id
union select 11 id
) v
cross join (
select 1 id
union select 2 id
union select 11 id
) u
order by
v.id
,u.id
Again, if renamed or removed alias for "cast(v.id as nvarchar(2))" expression then all works fine.
It reproducible on
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
and
Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
In both cases database collation is SQL_Latin1_General_CP1251_CS_AS
If I check quieries above on database with SQL_Latin1_General_CP1_CI_AS collation then it works fine again.
Could someone clarify - is it bug or expected behaviour?
View 12 Replies
View Related
Jul 19, 2006
Hi all
My query has some inner joins to some tables. And problem is when any ON clause get null as value, the correspondent record is not displayed.
SELECTTableA.A, TableB.AFROM TableAINNER JOIN TableB ON TableA.A = TableB.A
What I did try:
SELECTTableA.A, TableB.AFROM TableAINNER JOIN TableB ON TableA.A = TableB.A OR TableA.A IS NULL
(but It generates redundant values from TableB)
I need to show all values even that value from Tablea is null
Thank a lot for any help
View 1 Replies
View Related
Nov 10, 2014
I recently ran into an issue with an issue with a query against our Data Warehouse. When attempting to sum revenue from a table, and using a WHERE clause on a field that contains NULL values, the records with the NULL values are suppressed (in addition to whatever the WHERE clause specified). I believe this is because a NULL value is unknown so SQL doesn't know if it does or doesn't fit the criteria of there WHERE clause so it is suppressed.
That being said, is there a way to avoid this instead of having to add an ISNULL function in the WHERE clause which is going to kill performance?
Code:
create table #nullTest (
name varchar(50)
,revenue int)
INSERT INTO #nullTest
Values ('Tim',100)
,('Andrew', 50)
,(null, 200)
SELECT sum(revenue) as Revenue FROM #nulltest WHERE name <> 'tim'
Ideally, I would want the SELECT statement above to return 250, not 50. The only way I can think to accomplish this is with this query:
Code:
SELECT sum(revenue) as Revenue FROM #nullTest WHERE isnull(name,'') <> 'tim'
View 4 Replies
View Related
Jun 17, 2007
The following is a simplified version of my SQL statement. I am attempting to do a simple count(*) with two groupings and a where clause. This is Select command for a GridView. However, I am unable to display zeros. The rows are completely missing and I would greatly appreciate someone's help. I have already tried Group By All, but that, unfortunately, does not work. Here is my SQL statement:
"SELECT [GENDER], [RACE], COUNT(*) FROM [TABLE] WHERE ([COLUMNNAME] ='SOMETHING') GROUP BY [GENDER], [RACE]"
Thanks for the help in advance!
View 1 Replies
View Related
Jan 6, 2005
Hi
I have stupid users... who doesn't?! They have entered carriage returns as a whole value in some fields, that is, the field contains nothing more than a carriage return.
I really need to treat these cases as nulls and have successfully removed whole fields of nothing but spaces by using the LTRIM(RTRIM()) construct. Unfortunately, this doesn't deal with carraige returns. Even CASTing and CONVERTing to varchar and then using LTRIM(RTRIM()) doesn't work.
Does anyone know how I can elegantly get around this problem other than my best guess below:
Best guess pseudo code:
IF count of field is greater than 1 THEN probably a full sentence so ignore ELSE SUBSTRING first character and if CHAR(10, etc) then treat as NULL.
Here's some code that reconstructs the problem:
select datalength(char(13)) CarriageReturnVisible
, datalength(ltrim(rtrim(cast(char(13) as varchar)))) [This Don't Work]
Cheers - Andy
View 4 Replies
View Related