T-SQL (SS2K8) :: Using IsNull In Where Filter

Sep 23, 2014

I remember in the past using IsNull in a where filter of a stored proc that was used for a search form. I typically go about this in the following way. Is threre a better way to handle this?

sproc parm
@LName varchar(50) = null
@FName varchar(50) = null

Select * from LargeTable
where Lastname = ISNULL(@LName, Lastname)
Or Firstname = ISNULL(@FName, Firstname)

I do it this way to ensure that if the user doesn't enter a value for one of the parameters that it won't be used in the filter.

View 3 Replies


ADVERTISEMENT

T-SQL (SS2K8) :: Filter Records In Table?

Mar 6, 2014

I have a table that has multiple records as illustrated in the simple list below. The real list is much longer.

MachineA 1/1/2008
MachineA 1/3/2008
MachineB 1/7/2008
MachineB 1/8/2009
MachineB 5/5/2010
MachineA 5/7/2011
MachineA 4/2/2013

I need to query to return a result for each unique machine with the latest date. The example result below would be returned because they have the latest date.

MachineA 5/7/2011
MachineB 5/5/2010

Select Distinct would almost do it, but I need each unique machine that has the latest date.

View 9 Replies View Related

T-SQL (SS2K8) :: Add String WHERE Filter That Will Match Everything

Nov 19, 2014

I need to add a filter clause like
WHERE username = '%%%'

I know you will say 'why add a filter if you're not going to use it?' but I need to for a certain application which will use the parent query for child queries in which I select the specificity required for the child query's data set.I've tried '%*%' and '%_%' but always it returns nothing. I need the filter to exist yet not really filter.

View 6 Replies View Related

T-SQL (SS2K8) :: Using Not Like To Filter Out Non-numeric Values From Varchar Field

Mar 26, 2014

I am putting a SELECT statement together where I need to evaluate a results field, to determine how the color indicator will show on a SSRS report. I am running into a problem when I try to filter out any non-numeric values from a varchar field, using a nested CASE statement.

For example, this results field may contain values of '<1', '>=1', '1', '100', '500', '5000', etc. For one type of test, I need a value of 500 or less to be shown as a green indicator in a report, and any value over that would be flagged as a red. Another test might only allow a value of 10 or less before being flagged with a red.

This is why I setup a CASE statement for an IndicatorValue that will pass over to the report to determine the indicator color. Using CASE statements for this is easier to work with, and less taxing on the report server, if done in SQL Server instead of nested SSRS expressions, especially since a variety of tests have different result values that would be flagged as green or red.

I have a separate nested CASE statement that will handle any of the values that contain ">" or "<", so I am using the following to filter those out, and then convert it to an int value, to determine what the indicator value should be. Here is the line of the script that is erring out"

case when (RESULT not like '%<%') or (RESULT not like '%>%') then
CASE WHEN (CONVERT(int, RESULT) between 0 and 500) THEN '2'
ELSE '0'

The message I am getting is: Conversion failed when converting the varchar value '<1' to data type int.

I thought a "not like" statement would not include those values for converting to an int, but that does not seem to be working correctly. I did also try moving the not to show as "not RESULT like", and that did not change the message.

How I can filter out non-numeric values before converting the rest of the varchar field (RESULT) to int, so that it is only converting actual numbers?

View 6 Replies View Related

Automatic Select Filter (something Like Global Table Filter)

Apr 15, 2008

Hello,

Here is my problem:


I use SQL Server 2005. I have approx. 50 tables in my database and 30 of them have a filed named "CompanyID". Example:
create table A (ID int identity, NAME varchar(100), COMPANYID int)create table A (ID int identity, REF_ID int, FIELD1 varchar(100), FIELD2 varchar(100), COMPANYID int)

Also there are nearly 200 stored procedures that read data from these tables. Example:
create procedure ABCasbegin /* some checks and expressions here ... */ select ... from A inner join B on (A.ID = B.REF_ID) where ... /* ... */end;

All my queries in the Stored procedure does not filter the tables by CompanyID, so they process the entire data.

However, now we have a requirement to separate the data for each company. That means that we have to put a filter by CompanyID to each of those 20 tables in each query where the tables appear.

Firstly, I put the CompanyID in the context so now its value is accessible through the context_info() function. Thus I do not need now to pass it as a parameter to the stored procedures.

However, I don't know what is the easiest and fastest way to filter the tables. Example:

I modified the above mentioned procedure in the following way:
create procedure ABCasbegin /* some checks and expressions here ... */
-- gets the CompanyID from the context: DECLARE @CompanyID int; SELECT @CompanyID = CONVERT(float, CONVERT(varchar(128), context_info()))
select ... from A inner join B on (A.ID = B.REF_ID) where ...
and A.COMPANYID = @CompanyID and B.COMPANYID = @CompanyID /* ... */end;

Now I have the desired filter by CompanyID. However, modifying over 200 stored procedures is rather tedious work and I don't think that this is the best approach. Is there any functionality in SQL Server that can provide the possibility to put an automatic filter to the tables.
For example: when I wrote "SELECT * FROM A", the actual statements to be executed would be "SELECT * FROM A WHERE CompanyID = CONVERT(float, CONVERT(varchar(128), context_info()))".

I was looking for something like "INSTEAD OF SELECT" triggers but I didn't manage to find any answer.

I would very grateful is someone suggests a solution for something like "global table filter" (that will help me make an easy refactoring)?


Thanks in advance.

Best regards,
Beroetz

View 5 Replies View Related

And/or Filter Field Not Enabled In The Group Filter Tab

Jan 26, 2006

Howdy,

I have a table that has a group. In this group, I want to filter by 2 different expressions, concatenated with an OR. BUT I can't change the "And/Or" column value for the first entry because it is grayed out. The column will automatically change to an OR value if both my expression column fields are the same (which I don€™t want) but if I put any other value in to the expression field of the second row, the "And/Or" field of the first row automatically changes to an AND.

PLEASE! How do I get the And/Or field "ungrayed" so I can change it to what I want?

The 2 filters I and using check the UserID = to the user, and the other is checking a count to get the Top N 1. (So just showing the current user and the top producer)

View 14 Replies View Related

Isnull

Aug 22, 2000

I am trying to delete a row of data that is all nulls. Help in BOL was a little help but it is not working.

View 2 Replies View Related

ISNULL With LIKE?

Apr 30, 2008

I create a stored procedure like such:

"create procedure tmptable_query (@lname varchar(20)) as select * from #temp_table where lname like ISNULL(@lname+'%',lname)"

Unfortunately, it only returns exact matches, as if it were written as "where lname = ISNULL(@lname, lname)"

However, if I query from a static table the code works correctly.Any ideas on what could be wrong?

Thank you.

View 3 Replies View Related

Using Isnull

Feb 28, 2008

for the query i created i need zeros where ever the filed is blank. i have used count(acc) for selecting the count . can any one help me out with sample query. Thanks in advance

View 5 Replies View Related

Where To Put ISNULL

Jul 20, 2005

I have two tables, tblMTO and tblIMPORT_MTO. If I import an entire MTOinto the import table I want to create a delta from it (i.e. leave onlythe changed items). I have a view (simplified)SELECT dbo.tblIMPORT_MTO.ImportID, dbo.tblIMPORT_MTO.MTONo,dbo.tblIMPORT_MTO.Rev AS New_Rev, dbo.tblMTO.Rev AS Old_RevFROM dbo.tblIMPORT_MTO LEFT OUTER JOINdbo.tblMTO ON dbo.tblIMPORT_MTO.MTONo = dbo.tblMTO.MTONoNow to get all rows where old_rev = new_rev I also want all rows whereboth are null, is the best method to put ISNULL() in the view or toselect from the view using ISNULL in the criteria, e.g.select * from view1 where ISNULL(Old_Rev,0)=ISNULL(new_rev,0)or in the viewCREATE VIEW view1 asSELECT dbo.tblIMPORT_MTO.ImportID, dbo.tblIMPORT_MTO.MTONo,ISNULL(dbo.tblIMPORT_MTO.Rev,0) AS New_Rev, ISNULL(dbo.tblMTO.Rev ASOld_Rev,0)FROM dbo.tblIMPORT_MTO LEFT OUTER JOINdbo.tblMTO ON dbo.tblIMPORT_MTO.MTONo = dbo.tblMTO.MTONo;select * from view1 where Old_Rev=new_rev;

View 2 Replies View Related

Isnull()

Apr 24, 2006

does sql server mobile 2005 support the isnull function? I'm getting an error when I try to use it and I don't know if it is becuase of using the isnull function or not, but when I run the same query on Sql Server 2005 it works fine.

View 5 Replies View Related

Using ISNULL In WHERE Clauses

Apr 29, 2008

I've seen lots of entries recommending the use of ISNULL in SQL WHERE clauses, e.g. in a search sproc where users can enter some or all parameters to search a table. Previously I would have used something like:SELECT * FROM MyTableWHERE (FName = @fname OR @fname IS NULL) AND(MName = @mname OR @mname IS NULL) AND(LName = @lname OR @lname IS NULL)So using the neat ISNULL syntax it could be updated to:SELECT * FROM MyTableWHERE (FName = ISNULL(@fname, FName)) AND(MName = ISNULL(@mname, MName)) AND(LName = ISNULL(@lname, LName))Having played around with this I stumbled upon a problem. If one of the fields, e.g. MName, is NULL then that clause will return false since MName = NULL isn't true and you have to use MName IS NULL. Did I miss all the caveats with using ISNULL in this way on fields that can contain NULL or have I missed something else? 

View 4 Replies View Related

My ISNULL Confusion

May 1, 2008

I've got this query and it use to work or at least I thought it did. Could someone please help me with it.
Thank you
SELECT CID, CompletionDate, MarkedExport, CustomerName, EditUser, RouteID, WorkOrder FROM RouteCustomer WHERE (CompletionDate IS NOT NULL) AND (ExportDate IS NULL) AND (RouteID LIKE '%' + ISNULL(RouteID,@RouteID) + '%') AND (EditUser IS NULL OR EditUser = '' OR EditUser = @EmployeeID) AND (MONTH(CompletionDate) = ISNULL(MONTH(CompletionDate),@Month))
The problem comes with in the WHERE clause. What I wanted it to do is if the user did want to use a RouteID critera then the user would speified one else it wouldn't, and it was my belief that the ISNULL feature in SQL was the answer for that. same for the Month. I believe the EditUser is fine the way it is written.
thanks to anyone that can help me with this.
 Rex

View 9 Replies View Related

ISNull Question

Jun 7, 2005

I have to display string "not assigined" when a datefield is null in a table.
I am using like ISNULL(datefiled, "not assigned"), but I am getting following error
Syntax error converting character string to smalldatetime data type.
Is there any way, I can acheive desired result.
Please help

View 1 Replies View Related

CASE Vs ISNULL?

Feb 1, 2015

To give you a little background, there is a CRM system with SQL server as its back-end. The CRM uses a view in SQL Server to list all the communications a user has had with his client over any given interval of time. Now there is a requirement to add a new column in the view that tells a user if the communication was filed in automatically or if it happened overnight via an automated archive manager process. I have achieved this using an expression field which is based on the comm_url field in the communications table in database.

example:

create view vCommunications
as
select col1, col2,...,case when comm_url is null then 'Manually filed' else 'Automatically Filed' as Filing
from Communications

alternatively, this can also be achieved by the following:

create view vCommunications
as
select col1, col2,...,isnull(comm_url, 'Manually Filed') as Filing
from Communications

Now my question is, given that there are many rows in the communications table, which of the above two expression fields will be more efficient in performance i.e. CASE versus ISNULL. I've checked a lot on google but I haven't been able to come up with a concrete answer.

View 3 Replies View Related

IsNull Question

Apr 9, 2008

Hi All,

Quick question...

why doesn't this work....
select IsNULL(select null),'TallOne')

But this does...
select IsNull((Select null),0)

How does it know what datatype I'm trying to get?

View 16 Replies View Related

Help Using ISNULL Function

Apr 18, 2008

Hey, I'm taking an intro SQL Server class, and I have a pretty simple homework assignment. We were provided with a DB and asked to write several SELECT statements. However, I'm stuck up one of the questions. Here is the question: 12.Create a SELECT statement that displays all employees and their Qualifications. Display that individuals with no Qualifications as having ‘NoQual’. Hint: Use a function to determine this ‘empty’ field using ISNULL.

Here is what I have:

SELECT
FNAME + ' ' + LNAME AS 'Employee Name', ISNULL(QUALID, 'NoQual') AS 'Qualifications'
FROM
EMPLOYEE, QUALIFICATION
WHERE
EMPLOYEE.QUALID = QUALIFICATION.QUALID;

However, I do not get any results that have a NULL value in the QUALID column. Here is the code for the DB:

CREATE TABLE emplevel
(LevelNoint,
LowSalaryint,
HighSalaryint,
CONSTRAINT emplevel_levelno_pk PRIMARY KEY (LevelNo));
GO

CREATE TABLE position
(PositionIdint,
PosDescVARCHAR (10),
CONSTRAINT position_positionid_pk PRIMARY KEY (PositionId));
GO

CREATE TABLE qualification
(QualIdint,
QualDescVARCHAR (11),
CONSTRAINT qualification_qualid_pk PRIMARY KEY (QualId)
);
GO

CREATE TABLE dept
(DeptIdint,
DeptNameVARCHAR (12) ,
LocationVARCHAR (15),
EmployeeIdint,
CONSTRAINT dept_deptid_pk PRIMARY KEY (DeptId)
);
GO

CREATE TABLE employee
(EmployeeId int,
LnameVARCHAR (15) CONSTRAINT employee_lname_nn NOT NULL,
Fname VARCHAR (15) CONSTRAINT employee_fname_nn NOT NULL,
PositionId int,
Supervisorint,
HireDate DATETIME,
Salaryint,
Commissionint,
DeptIdint,
QualIdint,
CONSTRAINT employee_employeeid_pk
PRIMARY KEY (EmployeeId)
);
GO

CREATE TABLE dependent
(EmployeeId int,
DependentIdint,
DepDOBDATETIME,
RelationVARCHAR (8),
CONSTRAINT dependent_empiddepid_pk PRIMARY KEY (EmployeeId, DependentId)
);
GO

INSERT INTO position VALUES (1, 'President');
INSERT INTO position VALUES (2, 'Manager');
INSERT INTO position VALUES (3, 'Programmer');
INSERT INTO position VALUES (4, 'Accountant');
INSERT INTO position VALUES (5, 'Salesman');

INSERT INTO emplevel VALUES (1, 1, 25000);
INSERT INTO emplevel VALUES (2, 25001, 50000);
INSERT INTO emplevel VALUES (3, 50001, 100000);
INSERT INTO emplevel VALUES (4, 100001, 500000);

INSERT INTO qualification VALUES (1, 'Doctorate');
INSERT INTO qualification VALUES (2, 'Masters');
INSERT INTO qualification VALUES (3, 'Bachelors');
INSERT INTO qualification VALUES (4, 'Associates');
INSERT INTO qualification VALUES (5, 'High School');

INSERT INTO dept VALUES (10, 'Finance', 'Charlotte', 123);
INSERT INTO dept VALUES (20, 'InfoSys', 'New York', 543);
INSERT INTO dept VALUES (30, 'Sales', 'Woodbridge', 135);
INSERT INTO dept VALUES (40, 'Marketing', 'Los Angeles', 246);

INSERT INTO employee VALUES (111, 'Smith', 'John', 1, NULL,'04/15/1960', 265000, 35000, 10, 1);
INSERT INTO employee VALUES (246, 'Houston', 'Larry', 2, 111,'05/19/1967', 150000, 10000, 40, 2);
INSERT INTO employee VALUES (123, 'Roberts', 'Sandi', 2, 111,'12/02/1991',75000, NULL, 10, 2);
INSERT INTO employee VALUES (433, 'McCall', 'Alex', 3, 543,'05/10/1997',66500, NULL, 20, 4);
INSERT INTO employee VALUES (543, 'Dev', 'Derek', 2, 111,'03/15/1995',80000, 20000, 20, 1);
INSERT INTO employee VALUES (200, 'Shaw', 'Jinku', 5, 135,'01/03/00',24500, 3000, 30, NULL);
INSERT INTO employee VALUES (135, 'Garner', 'Stanley', 2, 111,'02/29/1996',45000, 5000, 30, 5);
INSERT INTO employee VALUES (222, 'Chen', 'Sunny', 4, 123,'08/15/1999',35000, NULL, 10, 3);

INSERT INTO dependent VALUES (543, 1,'09/28/1958','Spouse');
INSERT INTO dependent VALUES (543, 2,'10/14/1988','Son');
INSERT INTO dependent VALUES (200, 1,'06/10/1976','Spouse');
INSERT INTO dependent VALUES (222, 1,'02/04/1975','Spouse');
INSERT INTO dependent VALUES (222, 2,'08/23/1997','Son');
INSERT INTO dependent VALUES (222, 3,'07/10/1999','Daughter');
INSERT INTO dependent VALUES (111, 1,'12/12/1945','Spouse');

ALTER TABLE dept
ADD CONSTRAINT dept_employeeid_fk FOREIGN KEY(EmployeeId)
REFERENCES employee(EmployeeId);
GO
--ALTER TABLE employee
--ADD CONSTRAINT employee_supervisor_fk FOREIGN KEY(Supervisor)
--REFERENCES employee(EmployeeId);

ALTER TABLE employee ADD CONSTRAINT employee_positionid_fk FOREIGN KEY (PositionId)
REFERENCES position (PositionId);
GO

ALTER TABLE employee ADD CONSTRAINT employee_deptid_fk FOREIGN KEY (DeptId)
REFERENCES dept (DeptId);
GO

ALTER TABLE employee ADD CONSTRAINT employee_qualid_fk FOREIGN KEY (QualId)
REFERENCES qualification (QualId);
GO

ALTER TABLE dependent ADD CONSTRAINT dependent_employeeid_fk FOREIGN KEY (EmployeeId)
REFERENCES employee (EmployeeId);
GO

View 3 Replies View Related

ISNull Troubles

Jun 17, 2008

I'm having a problem creating a isnull statement.
I want to do two things I want to list all of the accounts that have a null value in a numeric field. And I want to update those accounts to 0.00.
I tried:
select Account_Num, isnull(TotalDDMFEE, 0)
FROM Addr_20080402
But it returned all records

For the update I tried:
update Addr_20080402
CASE
when TotalDDMFEE = ' ' then TotalDDMFEE = 0.00
AND
update Addr_20080402
CASE
when TotalDDMFEE = isnull(TotalDDMFEE, 0) then TotalDDMFEE = 0.00

Any ideas how I should have written these two queries?
Thanx,
Trudye

View 3 Replies View Related

Isnull Function

Dec 29, 2006

okay, using isnull function we could replace null value..
but i want to do opposite, i want to replace if it's NOT null..
i tried notisnull also cannot..

Note : this is for select statement
SELECT isnull(d.ClientID,'-') FROM blabla

How to replace something if it's not null
SELECT isNOTnull(d.ClientID, '-')

View 1 Replies View Related

ISNULL Function

Jun 18, 2007

SELECT JS_ID = ISNULL(ID.JS_ID,'-')
FROM
dbo.FM_INVOICE I,
dbo.FM_INVOICE_DETAILS ID
WHERE
I.INVOICE_ID = ID.INVOICE_ID
AND WO_ID = '-'--ISNULL(@GetLatest, '-')

is there any mistake i made? because it still return NULL when no data is found. how do i make it return '-' if a null is found

View 3 Replies View Related

Getting An Error Around The Isnull

Sep 21, 2007

I have a null in my column IV2.Inventory. So im trying the case statement at the bottom and keeping getting errors. Where am I going Wrong?

Select StoreGroupID, Sum(SnapShotQuantity * SnapShotPrice) as Inventory,
Convert(DateTime, Convert(Char, RecordDate, 101)) as [Date]
Into #ttIV
From Delsol.dbo.ItemSnapshotStore

Where DateDiff(mm,Convert(DateTime, Convert(Char, GetDate(), 101)),Convert(DateTime, Convert(Char, RecordDate, 101))) >= -24
and Day(RecordDate) = 1
and StoreGroupID = 1

Group By Convert(DateTime, Convert(Char, RecordDate, 101)), StoreGroupID
Order By Convert(DateTime, Convert(Char, RecordDate, 101)) desc

Select *, IV1.Inventory-IV2.Inventory/IV2.Inventory as Trend,
Case When IV2.Inventory (isnull(0,IV1.Inventory-IV2.Inventory/IV2.Inventory))
From #ttIV IV1
Left Join #ttIV IV2 on IV1.[Date] = DateAdd(mm,1,IV2.[Date])
--Drop Table #ttIV

View 5 Replies View Related

Default Value: ISNULL()

Jan 23, 2006

Hi!I'm wondering whether it's possible to set up the MS SQL functionISNULL() as a default value to avoid NULL entries when importing datainto a table?!For example, I want the column1, to have a 0 (zero) as default value,when entering/importing data: isnull("column1",0)I remember that it is possible to set up with a date function likenow(), having for each record the current time as default value. Isthat also with isnull() somehow possible?Thx a lot!Peter

View 1 Replies View Related

ISNULL() With Performance

Aug 14, 2007





isnull(startweight) = case startWeight when NULL then 0 else startWeight end ?

I have read some where that using function on column while selecting a bunch of data makes the query slower because the function needs to work on each row of the table..


SO I was just thinking like

is the value of above 2 would be same perfrmance wise and value wise ?

Which one will execute faster?

View 5 Replies View Related

ISNULL In SSIS

Aug 30, 2007



Hi All,

I want to accomplish something like this , I want to sum two of my columns and pass them as input into a third column, looks pretty simple but I think the way ISNULL is handled in SSIS is different from the way it is handled in TSQL.

Basically I wanna write an expression for the following TSQL


(ISNULL(Column1 , 0) + ISNULL(Column2 ,0)) as Column3

Thanks

View 3 Replies View Related

IsNull Question

Jun 19, 2007

I have a some currency fields that are sometimes null and I want to display them as '--' or something similar when they are such. I can do this with IIF statements, but I have a lot of them and would like a more manageable solution. I thought of the IsNull SQL Command, but the problem is that it won't let me insert char values into a currency field. Any other solutions? Thanks.

View 10 Replies View Related

Isnull Problem

Aug 24, 2007



hello,
I have a stored procedure that inserts or updates data depending of the value of one select that uses isnull finction. Here is the code:


Declare @id1 int
Select @id1= isnull(id,0) from tablename where name=@name

if @id=0
begin
insert.....
end
else
begin
update
end
the problem is that even i use isnull function the select statement does not return 0 in the @id when it does not find the record. ID column is of type Bigint and it is autoincremented identity

View 4 Replies View Related

When Is ISNULL Not An Allowed To Be Used

Aug 17, 2007

Hi everyone,
I was browsing and came across this code with this result set
CREATE TABLE dbo.SalesByQuarter

(

Y INT,

Q INT,

sales INT,

PRIMARY KEY (Y,Q)

)

GO



INSERT dbo.SalesByQuarter(Y,Q,Sales)

SELECT 2003, 2, 479000

UNION SELECT 2003, 3, 321000

UNION SELECT 2003, 4, 324000

UNION SELECT 2004, 1, 612000

UNION SELECT 2004, 2, 524000

UNION SELECT 2004, 3, 342000

UNION SELECT 2004, 4, 357000

UNION SELECT 2005, 1, 734000

GO



SELECT Y,

[1] AS Q1,

[2] AS Q2,

[3] AS Q3,

[4] AS Q4

FROM

(SELECT Y, Q, Sales

FROM SalesByQuarter) s

PIVOT

(

SUM(Sales )

FOR Q IN ([1],[2],[3],[4])

) p

ORDER BY

GO



DROP TABLE dbo.SalesByQuarter

GO

Y Q1 Q2 Q3 Q4
2003 NULL 479000 321000 324000
2004 612000 524000 342000 357000
2005 734000 NULL NULL NULL

I tried to modify it to remove the nulls by changing this line of code

SUM( ISNULL(Sales,0))


I got this error


Msg 102, Level 15, State 1, Line 1

Incorrect syntax near 'ISNULL'.


so i tried it like this


and got this error

Msg 195, Level 15, State 1, Line 12

'ISNULL' is not a recognized aggregate function.


My question is why can't i use isnull to change NULL TO 0

Thanx
Slimshim

View 8 Replies View Related

I Need Help Understadning--&> Isnull(max(PermissionValue),0)

Feb 22, 2008

I am trying to understand the block of sql code below. What I do not understand the second line, when they have isnull(max(PermissionValue),0)Can someone help me to understand what the purpose of this might be?
declare     @ThePermissionValue as intSELECT  @ThePermissionValue = isnull(max(PermissionValue),0)FROM Permission,WHERE Permission.EncryptedId = @TheId
 
Bill

View 2 Replies View Related

Isnull Not Working In Sql Server

Mar 12, 2008

select isnull(comments,'-') as comments from drawing_version where drawing_code='ACC    -Arch -IS-2008-54642' the comment value value is empty i want to display as "-" 

View 3 Replies View Related

Function Similar To ISNULL()

Oct 6, 2005

I'm constructing a single string of several counts with concatenated labels using SQL and want to not show zeros (or their labels). Is there a function within an SQL statement that will let me do this? ISNULL() sort of does this, but I really need to test for zero instead of NULL to eliminate noise data from the string.

View 2 Replies View Related

IIF() And IsNull() Equivalents In Views

Sep 25, 2000

Question 1:
In a view, I want to create some columns whose values depend on the contents of the rows. Here's an example in classic IIF() syntax:

Select Iif( IsNull(LastName), FirstName, LastName + ', ' + FirstName )

I have looked as ISNULL() and cannot figure it out. I have read about CASE/WHEN/THEN/END, but I don't think I understand it either.

Some example statements would be much appreciated.

Question 2:
If a stored procedure returns a value, can you call the SP from a SELECT statement?

TIA,
Arthur

View 2 Replies View Related

IsNull() Equivalent In MS SQL 2000

Mar 7, 2005

what is the ISNull () equivalent in MS SQL 2000?
Syntax please

View 2 Replies View Related

IsNull For Decimal Acting Up

Sep 22, 2006

When I run this code on a column of the type float or real it's ok, but not if the column is decimal, why?

this produces an error:

SELECT IsNull(column1,'') as column1
FROM mytable

---

this code works:

SELECT IsNull(column1,0) as column1
FROM mytable

---

The problem is that this query is used in a C++ environment to build an insert into another table. The query is build together in a CString.
C++ considers a CString to have ended if it encounters a NULL, therefore I need the check on all columns.

The error message I get is:

"Error converting data type varchar to numeric."

Why is it ok to use float or rel, but not decimal??

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved