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 MyTable
WHERE (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 MyTable
WHERE (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


ADVERTISEMENT

IF THEN CLAUSES

Feb 23, 2006

I'm importing a text field that is called advertiserTYPE. It will be one of four options in the parenthesis below:

advertiserTYPE (FSBO,BROKER,DEVELOPER,REALTOR) VarChar(50)

I need to be able to construct a statement that says something like:

If advertiserTYPE = FSBO
Then INSERT INTO [COLUMN1]
If advertiserTYPE = BROKER
Then INSERT INTO [COLUMN2
If advertiserTYPE = DEVELOPER
Then INSERT INTO [COLUMN3]

Am I on the right track??

View 8 Replies View Related

Multiple Where Clauses

Feb 21, 2007

Hello helpful people :)
 
I have this sproc.
ALTER PROCEDURE dbo.cis_UpdateCourseUserWithGrade
@Grade nvarchar,
@UaaStudentId nchar,
@CourseId int
AS
UPDATE cis_CourseUser
SET Grade =@Grade
WHERE UaaStudentId = @UaaStudentId
AND WHERE CourseID = @CourseId
RETURN
 the ANE WHERE isn't much liked.  How can I update the field with the grade where both of the conditions are true?  I'm getting an invalid syntax near @UaaStudentId
 
Thanks.
 

View 4 Replies View Related

Dynamic WHERE Clauses

May 19, 2008

So how do you do dynamic WHERE clauses with asp.net?
 I have a QueryString parameter with a value that will look like this.  |1|  or  |1||2|  or  |1||2||3|  or etc.  For each number in the value I want to add an EXISTS function to the WHERE clause.  I have accomplished this in T-SQL.  But I can't figure out how to bind asp.net control to it.  It gives me 'Invalid syntax near the keyword 'ORDER'.  I know this has something to do with .net not being able to exaluate the schema properly.
So I tried Linq to Sql.  Well, so happens that my WHERE clause is pointing to the existance of records in another table which the default install of Linq to Sql doesn't seam to support.  I came across this post (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2979081&SiteID=1) that explains how to dynamically query across multiple tables.  I tried this and got errors when trying to plug in the additional code.  And this still does not let me dynamically add where clauses.
 I tried the who Predicate thing and ran into the same problem where I can't reference the existance of values in foreign tables.
 Here is my sproc.  Can anyone tell me how to get this into a asp.net environment.  I also do NOT want to auto-generate fields in my GridView.ALTER PROCEDURE dbo.Item_SelectByFilters      @Item_Category_Id int = NULL,       @ItemSpecificValueIdList varchar(100) = NULL,       @debug bit = 0 AS DECLARE @sql        nvarchar(4000),        @paramlist  nvarchar(4000) SELECT @sql = 'SELECT     Item_Id, Number, Primary_Item_Category_Id, Secondary_Item_Category_Id, Engineering_Document_Id, CategoryIdList              FROM         Item              WHERE     (1 = 1)' IF @Item_Category_Id IS NOT NULL SELECT @sql = @sql + ' AND                                           Item.CategoryIdList Like ''|'' + CONVERT(varchar, (@xItem_Category_Id) + ''|''' IF @ItemSpecificValueIdList IS NOT NULL       BEGIN            WHILE @ItemSpecificValueIdList <> ''            BEGIN                  DECLARE @StartLocation int                  SELECT @StartLocation = CHARINDEX('-', @ItemSpecificValueIdList, 1)                  IF @StartLocation <> 0                        BEGIN                              DECLARE     @EndLocation int                              SELECT @EndLocation = CHARINDEX('|', @ItemSpecificValueIdList, @StartLocation + 1)                              IF @EndLocation <> 0                                     BEGIN                                          DECLARE @ValueLength int                                          SELECT @ValueLength = @EndLocation - @StartLocation - 1                                          DECLARE @ValueBetween varchar(10)                                          SELECT @ValueBetween = SUBSTRING(@ItemSpecificValueIdList, @StartLocation + 1, @ValueLength)                                          IF @ValueBetween IS NOT NULL SELECT @sql = @sql + ' AND                                           EXISTS(SELECT Item_Specific_Value_Id FROM Item_Specific_Value WHERE Item_Id = Item.Item_Id AND Item_Specific_Value.Item_Specific_Value_Id = ' + @ValueBetween + ')'                                    END                        END                  SET @ItemSpecificValueIdList = SUBSTRING(@ItemSpecificValueIdList, @EndLocation + 1, len(@ItemSpecificValueIdList) - @EndLocation)            END      END SELECT @sql = @sql + '                     ORDER BY Item.Number' IF @debug = 1   PRINT @sql SELECT @paramlist = '@xItem_Category_Id int' 
EXEC sp_executesql @sql, @paramlist, @Item_Category_Id
 

View 20 Replies View Related

Dynamic WHERE Clauses

May 27, 2008

Here is another project that seams to be the same as my previous post but a little simpler.  http://forums.asp.net/t/1263330.aspx 
My Item_Category table is self referencing.  I have a list of category Ids in a particular order representing the tree of categories from the current category to the top.  For example: 1 - Fasteners, 4 - Screws, 12 - Sheet Metal Screws  or the reverse.
I need to translate this so I can bind a cookie crumb type control to it.  ie.  Fasteners > Screws > Sheet Metal ScrewsI originally accomplished this like so;
Dim q = From ic In itemdc.Item_Category _Where valueId.Contains(ic.Item_Category_Id) _ Select Id = ic.Item_Category_Id, ic.Name But found that this does not order them properly.  Remember that the list of Ids is in a particular order.  I need a query to result to the below.  I was using a horizontal DataList control as the cookie crumb control.  Again, I am trying to do this with Linq to Sql without using Stored Procedures even though I am the DBA as well.  Or is there some other better way to do this?








Id
Name
Sort

1
Fastener
1

4
Screws
2

12
Sheet Metal Screws
3
Finally, as I mentioned in my previous post, I accomplished this concept with a stored procedure but then the asp.net controls could not recognize the schema, hence I could not bind the controls to the data source control.

View 2 Replies View Related

CASE And Where Clauses

Oct 28, 2014

I have an existing SPROC which works the way it should do and was difficult to construct. But now I need to add 1 more condition and hopefully that should be it however I am struggling how to do this. Here is a snip of the existing SPROC.

quote:
IF @columnName = 'Rating - Fire' OR @columnName = 'Rating - PPE' OR @columnName = 'Rating - Reactivity' OR @columnName = 'Rating - Health'
BEGIN
SELECT @totalRecords = (SELECT COUNT(p.[SID]) FROM S_Summary p INNER JOIN S_Detail detail ON detail.SID = p.SID
WHERE
CASE @columnName

[code]...

so this works fine but now I need to add 1 more thing..if a new parameter is supplied (lets call it @stringBranch), then I want to join another table and also match the param value to a field in that table along with any existing WHERE conditions being applied to this:

quote:
WHERE (@columnName IS NULL AND @columnValue IS NULL)
OR
CASE @columnName
WHEN 'Rating - Fire' THEN detail.F
WHEN 'Rating - PPE' THEN detail.P

[code]...

View 9 Replies View Related

Multiple Where Clauses

Feb 23, 2006

I'm trying to use multiple where clauses but its not working. I want it to look something like this:

INSERT INTO [USCondex_Development].[dbo].[miamiheraldExceptions]([InvalidEmails], [InvalidAdPrintID], [InvalidPropertyStreetAddress], [InvalidPropertyPrice])
SELECT [AdvertiserEmail],[AdPrintId],[AdvertiserAddress], [PropertyPrice]
FROM [Development].[dbo].[table2]
WHERE advertiseremail is NULL
and WHERE adPrintID is NULL
and WHERE firstinsertdate is NOT NULL
and WHERE propertystreetaddress is NOT NULL
and WHERE propertyprice < 100

But it's not working.

View 7 Replies View Related

IF/CASE For WHERE Clauses

Nov 28, 2007

I am trying to wrap my WHERE clause with an IF or a CASE but cannot seem to get it to work. This is what I am trying:

WHERE
CASE
WHEN EVENT_TYPE='d' THEN
(link_inc.incident_id = 10000005) AND (B.incident_id <> 10000005) AND link_rsn.link_rsn_sc = 'CHANGE' AND B.incident_id > 10000000
ELSE
(link_inc.incident_id = 10000005) AND (B.incident_id <> 10000005) AND link_rsn.link_rsn_sc = 'CHANGE' AND B.incident_id > 10000000 AND act_type.act_type_sc <> 'CLOSURE'
END
ORDER BY B.incident_id DESC, act_reg.act_reg_id DESC

Basically I want to run a different WHERE clause based on a value (EVENT_TYPE). The error message I am getting is:
Incorrect syntax near '='.

View 5 Replies View Related

Complex 'order By' Clauses

Apr 11, 2007

I have a table, basically consisting of products and their prices. I want to select some products, then sort them by price in ascending order BUT putting prices of zero at the bottom. (e.g. 5.99, 8.99, 10.99, 0.00, 0.00)I thought I'd be able to do something like:ORDER BY (price != 0), pricethinking that it would sort rows according to whether the condition was true or not, and then by price, but MSSQL doesn't seem to allow this. should this work, or is there another way around this? One solution would be to load the values into a table object and sort them using that, but I'd rather do all of this in SQL if possible, for speed.any suggestions?thanks! 

View 1 Replies View Related

Is Is Possible To Have Multiple WHERE Clauses In SELECT?

Dec 4, 2007

If I have a table called "content_hits_tbl" and want to pull information, can't i write something like this:


SELECT COUNT(visitor_id) AS HITS, COUNT(DISTINCT visitor_id) AS VISITORS, COUNT(DISTINCT visitor_id) WHERE visit_type = 0 AS NEW, COUNT(DISTINCT visitor_id) WHERE visit_type = 1 AS RETURNING
FROM content_hits_tbl

Can't you have multiple WHERE clauses in the SELECT statement?

Any suggestions would be great. I have been wrestling with it and SQL queries arent my strong area ...

I'm using VS05 connecting to a SQL database.

Sincerely,
Tommy


View 3 Replies View Related

SQL Server 2012 :: Counting With Where Clauses

Sep 18, 2014

Below is a script that count the amount of Void properties we had in a specific month.

SELECT COUNT(C.[Place Referance]) AS [April Total Voids]
FROM
(
SELECT DISTINCTTOP 100 PERCENT
HIST.[PLACE-REF] AS 'Place Referance'

[Code] ....

It tells me I have 53 total voids.

What I also want is a column next to this to say how many of those voids back in April are STILL Void.

So basically the WHERE clause would still be the same -

WHERE [Void Start Date YEAR] = '2014'
AND [Void Start Date MONTH] = '4'

but with the added -
AND HIST.[END-DATE] IS NULL

So ideally I'm after two columns with figures in them and then going forward I can then calculate other months as well.

View 5 Replies View Related

Subquery Totals Based On Clauses

Aug 1, 2013

I have a Master table with a OrderNbr which is also contained in the Detail table.

It's a 1 to Many relationship, respectively.

I want to update the MASTER.FinalizedDate using a "select top 1 FinalizedDate order by FinalizedDate DESC" from the Detail table but the clause is ALL the Status have to be "F". So OrderNbr 12345 should not get updated because it contains a 'O'. OrderNbr 67899 should get updated in the Master table to 2/26/2013 because all have a 'F' and the last date to post is the official finalized date.

Here is what I came up with.......so far, but not sure how to work in the Status piece on 1 to M.

The rub here is that even if one row has the 'O' status I want to ignore the update. If all have the 'F' then I want the udpate to happen.

Update MASTER
Set FinalizedDate = (select top 1 d.FinazliedDate from Detail d
where m.OrderNbr = d.OrderNbr
and d.Status not in ('O')
Order by FinalizedDate DESC)
From MASTER m

How do I not include all 3 rows for OrderNbr 12345 because one row has the Status "O" in the DETAIL table?

Here are the table looks........

MASTER
OrderNbr Ytotals Ztotals Xtotals Finalized Date
12345$1,500$1,500$1,200
67899$1,200$1,100$900

DETAIL
OrderNbrItemNbr PriceStatusFinalized Date
1234563453453 $1,400F1/2/2013
12345554444 $1,500F1/2/2013
12345545444 $2,200O NULL
67899333334 $899F2/24/2013
678993434344 $659F2/24/2013
67899434676 $499F2/26/2013
6789978888 $599F2/24/2013

View 3 Replies View Related

Error In Group By And Distinct Clauses

May 2, 2008



hai,
i'm using a table in sql.the name of register. the table datas are

FirstName LastName UserName Pwd dob
krishna murthy ckm0006 asdfg 1985-04-01 00:00:00.000
krishna dfgd ckm0006 cxbcv 1985-05-01 00:00:00.000
raja fdd ddd ddd 1985-01-01 00:00:00.000
raja hgff fgrgf fgf 1985-02-01 00:00:00.000

i want the result as:[group by or distinct by FirstName && order by dob]

FirstName LastName UserName Pwd dob

krishna dfgd ckm0006 cxbcv 1985-05-01 00:00:00.000
raja hgff fgrgf fgf 1985-02-01 00:00:00.000

i tried many queries i'm getting error Like this


Column 'register.LastName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

can anyone resolve my problem...

View 5 Replies View Related

Conditionally Extend Query With Clauses

Jan 14, 2008

Howdy folks, first time poster.

In a UDF, how is the best way to extend a query with additional clauses based on expressions? The user input here is used to refine the basic query by introducing additional clauses. Is there something like the following?


-- The basic query
SELECT column
FROM table
WHERE clause

-- Additional clause, only appended to query
-- if expression evaluates to true
IF @parameter <> default_value
BEGIN
AND additional_clause
END


Right now I'm using CASE like the following, but it necessarily makes the query longer. Is there a more efficient way?


-- The basic query

SELECT column
FROM table
WHERE clause

-- Additional clause, should only effect result
-- set when the parameter is not default_value
AND table.column =
CASE @parameter
WHEN default_value THEN
-- identity, table.column=table.column,
-- should have no effect other than just a long query
table.column
ELSE
@parameter
END

View 8 Replies View Related

Multiple Clauses In A CASE Statement

Jan 12, 2008



Hi
I'm not sure if I have stated my subject line correctly for what I want to achieve, but I will attempt to explain it below.

In addition to what I have in my script below, I also need to include the following clauses:

1. where TransPerPaySequence.FinancialYTDCode like '2007', and e.EmployeeStatusCode like 'CASUAL' and p.PositionGroupCode like 'AC', then instead of using the divisor of 72, it needs to be 35; and

2. where TransPerPaySequence.FinancialYTDCode like '2008', and e.EmployeeStatusCode like 'CASUAL' and p.PositionGroupCode like 'AC', then instead of using the divisor of 72 or 35, it needs to be 31.

I would really appreciate any assistance that can be provided.

Thanks


SELECT DISTINCT
pc.PositionClassificationCode, pc.Description AS positionclass, pg.PositionGroupCode, pg.Description AS positiongroup, p.Description AS position,
e.PreferredName + ' ' + e.LastName AS employeename, SUM(ha.Quantity)
/ ((CASE p2.PositionGroupCode WHEN 'AC' THEN 72 WHEN 'AL' THEN 75 WHEN 'EX' THEN 80 WHEN 'MG' THEN 80 WHEN 'SM' THEN 80 END) *
(SELECT COUNT(DISTINCT PaySequence) AS Expr1
FROM TransPerPaySequence
WHERE (PayPeriodCode LIKE 'EIT') AND (Closed = '1') AND (Description LIKE 'St%'))) AS FTE,
(SELECT COUNT(DISTINCT PaySequence) AS Expr1
FROM TransPerPaySequence AS TransPerPaySequence_1
WHERE (PayPeriodCode LIKE 'EIT') AND (Closed = '1') AND (Description LIKE 'St%')) AS payseq
FROM HistoricalAllowance AS ha LEFT OUTER JOIN
Position AS p ON ha.PositionCode = p.PositionCode LEFT OUTER JOIN
PositionGroup AS pg ON p.PositionGroupCode = pg.PositionGroupCode LEFT OUTER JOIN
PositionClassification AS pc ON p.PositionClassificationCode = pc.PositionClassificationCode LEFT OUTER JOIN
WAP ON ha.WAPCode = WAP.WAPCode LEFT OUTER JOIN
Employee AS e ON ha.EmployeeCode = e.EmployeeCode LEFT OUTER JOIN
Position AS p2 ON e.PositionCode = p2.PositionCode LEFT OUTER JOIN
TransPerPaySequence AS tpps ON ha.PaySequence = tpps.PaySequence
WHERE (e.EmployeeCode IN ('83', '739')) AND (ha.AllowanceCode IN ('005', '201', '203', '101')) AND (tpps.FinancialYTDCode LIKE '2007%')
GROUP BY pc.PositionClassificationCode, pg.PositionGroupCode, pc.Description, pg.Description, p.Description, e.PreferredName, e.LastName,
p2.PositionGroupCode

View 8 Replies View Related

Join 2 Selects And Distinguish 2 Where Clauses

May 8, 2008

hi, i'm using Access 2007 and i'm trying to join two selects and create two new columns[complete and not complete] where 'x' denotes a hit was made. i will use this later for grouping. here is my code so far. thanks.

SELECT tblOutlookTask.TaskSubject, tblOutlookTask.PercentComplete, tblOutlookTask.ID
FROM tblOutlookTask
WHERE (((tblOutlookTask.PercentComplete)=100))

SELECT tblOutlookTask.TaskSubject, tblOutlookTask.PercentComplete, tblOutlookTask.IDFROM tblOutlookTask
WHERE (((tblOutlookTask.PercentComplete)<>100))

View 3 Replies View Related

Dynamic Sql Where And Order By Clauses In Stored Procedure

Feb 17, 2008

Hi, I hope some one can help me. I have a stored procedure (Microsoft SQL 2005 Express Edition) that I want users to be able to dynamically set the, group by, order by (@orderby) and where clause (@where). I have managed to get the group by to work but can't seem to get the where and order by to work. Here's my stored procedure. Any idea how this can be done? ALTER PROCEDURE [dbo].[sp_aggregate]     -- Add the parameters for the stored procedure here @finfileid int,     @phaseid int, @supplierid int, @measurementid int, @roleid int, @groupby int, @orderby int, @where int AS BEGIN     -- SET NOCOUNT ON added to prevent extra result sets from     -- interfering with SELECT statements.     SET NOCOUNT ON;     -- Insert statements for procedure here     SELECT     MAX(ProjectFinFileMonthItems.ProjFinFileMonthItemsMonthId) AS ProjFinFileMonthItemsMonthId,                       SUM(ProjectFinFileMonthItems.ProjFinFileMonthItemsValue * ProjectFinFileMonthItems.ProjFinFileMonthItemsRate * ProjectFinFileMonthItems.ProjFinFileMonthItemsAvail                        / 100) AS total, MAX(ProjectFinFileItems.ProjPhaseId) AS phaseid, MAX(ProjectFinFileMonthItems.ProjDeliveId) AS deliveid, MAX(ProjectFinFileMonthItems.SupplierId)                       AS supplierid, MAX(ProjectFinFileMonthItems.ProjFinFileItemsId) AS ProjFinFileItemsId, MAX(ProjectFinFileMonthItems.ProjFinFileMonthItemsId)                       AS ProjFinFileMonthItemsId, MAX(ProjectFinFileMonthItems.ProjDeliveId) AS ProjDeliveId, MAX(ProjectPhases.ProjectPhaseName) AS ProjectPhaseName,                       MAX(Suppliers.SupplierName) AS SupplierName, MAX(ProjectFinFileMonthItems.RoleId) AS RoleId, MAX(Measurements.MeasurementName) AS MeasurementName,                       MAX(ProjectFinFileMonthItems.MeasurementId) AS MeasurementId, MAX(ProjectFinFileMonthItems.FinDataTypeId) AS FinDataTypeId,                       MAX(FinDataTypes.FinDataTypeName) AS FinDataTypeName, max(ProjectFinFileItems.FinFileId) as finfileid FROM         ProjectFinFileItems INNER JOIN                       ProjectFinFileMonthItems ON ProjectFinFileItems.ProjFinFileItemsId = ProjectFinFileMonthItems.ProjFinFileItemsId LEFT OUTER JOIN                       FinDataTypes ON ProjectFinFileMonthItems.FinDataTypeId = FinDataTypes.FinDataTypeId LEFT OUTER JOIN                       Measurements ON ProjectFinFileMonthItems.MeasurementId = Measurements.MeasurementId LEFT OUTER JOIN                       Roles ON ProjectFinFileMonthItems.RoleId = Roles.RoleId LEFT OUTER JOIN                       ProjectPhases ON ProjectFinFileItems.ProjPhaseId = ProjectPhases.ProjectPhaseId LEFT OUTER JOIN                       Suppliers ON ProjectFinFileMonthItems.SupplierId = Suppliers.SupplierId /*dynamic where clause needs to go here */ /*dynamic group by clause */ GROUP BY CASE when @groupby=1 then ProjectFinFileItems.projphaseid  --phaseid when @groupby=2 then ProjectFinFileMonthItems.supplierid -- supplierid when @groupby=3 then ProjectFinFileMonthItems.measurementid -- measurment when @groupby=4 then ProjectFinFileMonthItems.roleid --role else ProjectFinFileMonthItems.ProjFinFileMonthItemsId END /*dynamic order clause needs to go here */ END cheers Mark :)

View 2 Replies View Related

Variable Number Of Where Clauses In A Stored Procedure

Jun 20, 2001

I have a web page which passes back parameters to a stored procedure.
From the web page the user selects different clauses for the 'where' criteria.
Based upon the number of clause items in the parameters sent back, a select statement is built and executed.
In the stored procedure I have many if statements to chose the correct sql statement.
As the no of clauses in the where statement can vary, it can become messy script.
Has anyone dealt with this scenario. What is the best strategy ?

A simple illustation of this is as follows
A statement with two clauses :-

Select * from Sales where
user = 'John' and country = 'England'
A statement with three clauses :-
Select * from Sales where
user = 'John' and country = 'England' and County = 'Staffordshire'

The stored procedure would except three parameters and would build a string based on the number of actual where clases sent back

View 2 Replies View Related

Passing SQL Clauses As Parameters To A Stored Procedure

Feb 26, 2004

Hi,

I have a complex SQL query against multiple tables that ideally would be best used as a stored procedure.

I know of and have read about creating named parameters and setting the parameters' values in ASP just before issuing an execute command via the Command object. What I'd like to do is assign the text of the filter and sort clauses to the parameters. Not the values, but the entire string, such as

"WHERE lastname LIKE 'A%' "

"ORDER BY lastname "

I need to assign the entire clause because the query may or may not use a particular clause.

I have experimented. However SQL treats the parameter as a literal string rather than part of the SQL query itself. Is there another technique that I may use to accomplish my goal?

Thanks in advance for your opinion, suggestion, criticism, etc.

--Tom.

View 2 Replies View Related

SQL Server 2012 :: Multiple ON Clauses In One Join?

Jun 18, 2014

I came across this structure today and haven't seen it before:

SELECT blablabla
FROM T1
FULL OUTER JOIN T2 ON
T1.Col1 = T2.Col1
AND T1.Col2 = T2.Col2 ON
T3.Col1 = T1.Col1
AND T3.Col2 = T1.Col2 ON
T4.Col1 = T1.Col1
AND T4.Col2 = T1.Col2

I can follow along until the second "ON".

View 9 Replies View Related

MSSQL Management Studio View Editor Destroys Where-Clauses With Date-Functions

Nov 28, 2007



Hello,

i've written the following query:



SELECT dbo.KALENDER.KALENDER_ID, dbo.KALENDER.JAHR_BEZ, dbo.KALENDER.JAHR_WERT, dbo.KALENDER.HALBJAHR_WERT,

dbo.KALENDER.HALBJAHR_BEZ1, dbo.KALENDER.HALBJAHR_BEZ2, dbo.KALENDER.QUARTAL_WERT, dbo.KALENDER.QUARTAL_BEZ1,

dbo.KALENDER.QUARTAL_BEZ2, dbo.KALENDER.MONAT_BEZ, dbo.KALENDER.MONAT_WERT, dbo.KALENDER.TAGE_IM_MONAT,

dbo.TAG.KALENDERWOCHE, dbo.TAG.WOCHENTAG, dbo.TAG.TAG, s.STUNDE_ID, s.DATUM_ZEIT

FROM dbo.KALENDER INNER JOIN

dbo.TAG ON dbo.KALENDER.KALENDER_ID = dbo.TAG.KALENDER_ID INNER JOIN

dbo.STUNDE AS s ON dbo.TAG.TAG_ID = s.TAG_ID

WHERE (SELECT MONTH(s.datum_zeit)) = ((SELECT MONTH(GETDATE()))-2)and

(SELECT year(s.datum_zeit)) = (SELECT year(GETDATE()))

order by s.stunde_id



when copying that query to the view editor and executing it, it trys to fix it somehow to:


SELECT TOP (100) PERCENT dbo.KALENDER.KALENDER_ID, dbo.KALENDER.JAHR_BEZ, dbo.KALENDER.JAHR_WERT, dbo.KALENDER.HALBJAHR_WERT,

dbo.KALENDER.HALBJAHR_BEZ1, dbo.KALENDER.HALBJAHR_BEZ2, dbo.KALENDER.QUARTAL_WERT, dbo.KALENDER.QUARTAL_BEZ1,

dbo.KALENDER.QUARTAL_BEZ2, dbo.KALENDER.MONAT_BEZ, dbo.KALENDER.MONAT_WERT, dbo.KALENDER.TAGE_IM_MONAT,

dbo.TAG.KALENDERWOCHE, dbo.TAG.WOCHENTAG, dbo.TAG.TAG, s.STUNDE_ID, s.DATUM_ZEIT

FROM dbo.KALENDER INNER JOIN

dbo.TAG ON dbo.KALENDER.KALENDER_ID = dbo.TAG.KALENDER_ID INNER JOIN

dbo.STUNDE AS s ON dbo.TAG.TAG_ID = s.TAG_ID

WHERE ((SELECT MONTH(s.datum_zeit) AS Expr1

FROM ) =

(SELECT MONTH(GETDATE()) AS Expr1) - 2) AND

((SELECT YEAR(s.datum_zeit) AS Expr1

FROM ) =

(SELECT YEAR(GETDATE()) AS Expr1))

ORDER BY s.STUNDE_ID

... but this causes syntax-errors. I don't understand why this query works fine in the query editor but then gets automatically "destroyed" by the view editor. Do i have to use more statements to get the working query to run inside a view?

Thanks alot for reading.

View 1 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

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







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