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
ADVERTISEMENT
Jan 16, 2003
I am trying to find the equivalent to MS Access's TRANSFORM and PIVOT in T-SQL. I've tried using GROUP BY with CUBE, and I can't seem to get the data in the correct format. Can someone help? And please explain things to me like I am an idiot, because I am.
Here is the current table and the desired results that I want.
Current Table
MonthTypeSubtypeTotalTime
1TaskASubA5
1TaskASubB10
1TaskASubC8
1TaskBSubX5
2TaskASubA4
2TaskBSubX5
Desired Result:
TypeSubTypeJanuaryFebruary
TaskASubA54
TaskASubB100
TaskASubC80
TaskBSubX55
Thanks.
Colleen
View 5 Replies
View Related
Apr 3, 2006
Fellow database developers,I would like to draw on your experience with views. I have a databasethat includes many views. Sometimes, views contains other views, andthose views in turn may contain views. In fact, I have some views inmy database that are a product of nested views of up to 6 levels deep!The reason we did this was.1. Object-oriented in nature. Makes it easy to work with them.2. Changing an underlying view (adding new fields, removing etc),automatically the higher up views inherit this new information. Thismake maintenance very easy.3. These nested views are only ever used for the reporting side of ourapplication, not for the day-to-day database use by the application.We use Crystal Reports and Crystal is smart enough (can't believe Ijust said that about Crystal) to only pull back the fields that arebeing accessed by the report. In other words, Crystal will issue aSelect field1, field2, field3 from ReportingView Where .... eventhough "ReportingView" contains a long list of fields.Problems I can see.1. Parent views generally use "Select * From childview". This meansthat we have to execute a "sp_refreshview" command against all viewswhenever child views are altered.2. Parent views return a lot of information that isn't necessarilyused.3. Makes it harder to track down exactly where the information iscoming from. You have to drill right through to the child view to seethe raw table joins etc.Does anyone have any comments on this database design? I would love tohear your opinions and tales from the trenches.Best regards,Rod.
View 15 Replies
View Related
Sep 6, 2007
Which is more efficient? One large view that joins >=10 tables, or a few smaller views that join only the tables needed for individual pages?
View 1 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
Jun 28, 2007
Hello.
Newbie here. I've only been using SQL for about a year now and have some minor questions about sql objects that reference other objects.
We have some views which reference other views in the joins. I will call one the primary view and the one being referenced in the joins as the secondary view.
Recently we made changes to the secondary view.
After which the primary views which referenced it would not work because of this change and had to be 'refreshed' by using drop/create scripts which essentially just dropped it and recreated the exact same view. I do not recall the exact error message that was returned other than it seemed to suggest that it could no longer see the secondary view since it had been changed. Nothing in the primary view was changed in any way, just the secondary.
Some here where I work have suggested off hand that this was a recompile of the primary view because the contents of the secondary changed.
My questions are:
1. Exactly why did this happen and is there a proper name for it when it does?
2. The same problem does not seem to occur when we have stored procedures referencing views in the joins which had just been changed. Why is that?
Thanks for any help on the matter. I greatly appreciate it.
View 3 Replies
View Related
Feb 22, 2007
Hello,
to make a report easier I'm developing it using a view of joined views of joined views.
Is there any significant performance penalty as opposed to just having one big select?
Cheers.
View 1 Replies
View Related
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
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
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
Mar 7, 2005
what is the ISNull () equivalent in MS SQL 2000?
Syntax please
View 2 Replies
View Related
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
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
View Related
