Null To Zero In Select Statement?
Mar 11, 2004How can I do a null to 0 in a select statement? I tried the NZ() function but it is not part of SQL.
Thanks very much,
How can I do a null to 0 in a select statement? I tried the NZ() function but it is not part of SQL.
Thanks very much,
Hi.
I have an sql table which contains a number and a name. I would like to create a select statement that will display these two fields in the format :
"number | name", but if there is a null value in the number it will display only the name and vice versa.
How can I do it ?
Any help is appreciated.
I have a stored procedure with a SELECT statement, that retrieves 1 row.
SELECT name FROM tblNames WHERE nameID = "1"
I want all the NULL values in that row to be change in some default values.
How do I do this?
Hello! I have a field "End" in my database that is mapped as DateTime and allows nulls. Now I want to do a SQL-Select (in a SqlDataSource) like SELECT * FROM My_Table Where (([End] = @EndDate) OR ([End] = null)) @EndDate is a valid DateTime, but the second OR condition doesn't work. What is the best way to check if the [End]-field is empty or null? Thank you very much!
View 1 Replies View RelatedIf I try to run the code below, and even one of the values in the INNER
JOIN statements is NULL, the DataReader ends up with zero rows.
What I need is to see the results even if one or more of INNER JOIN
statements has a NULL value. For example, if I want info on
asset# 2104, and there's no value in the DriverID field, I need the
rest of the data to display and just have the lblDriverName by
blank. Is that possible?
<code>
Sub BindSearchGrid()
Dim searchUnitID As String
Dim searchQuery As String
searchUnitID = tbSearchUnitID.Text
lblIDNum.Text = searchUnitID
searchQuery = "SELECT * FROM Assets " & _
"INNER JOIN Condition ON Condition.ConditionID = Assets.ConditionID " & _
"INNER JOIN Drivers ON Drivers.DriverID = Assets.DriverID " & _
"INNER JOIN Departments ON Departments.DepartmentID = Assets.DepartmentID " & _
"INNER JOIN AssetCategories
ON AssetCategories.AssetCategoryID = Assets.AssetCategoryID " & _
"INNER JOIN Store ON
Store.[Store ID] = Assets.StoreID WHERE RTRIM(Assets.[Unit ID]) = '"
& searchUnitID & "'"
Dim myReader As SqlDataReader
myReader = Data.queryDB(searchQuery)
While myReader.Read
If
Not IsDBNull(myReader("Store Name")) Then lblStrID.Text =
myReader("Store Name")
If
Not IsDBNull(myReader("AssetCategory")) Then lblAsstCat.Text =
myReader("AssetCategory")
If
Not IsDBNull(myReader("Condition Description")) Then lblCondID.Text =
myReader("Condition Description")
If
Not IsDBNull(myReader("DepartmentName")) Then lblDepID.Text =
myReader("DepartmentName")
If
Not IsDBNull(myReader("Unit ID")) Then lblUnID.Text = myReader("Unit
ID")
If
Not IsDBNull(myReader("Year")) Then lblYr.Text = myReader("Year")
If
Not IsDBNull(myReader("Make")) Then lblMk.Text = myReader("Make")
If
Not IsDBNull(myReader("Model")) Then lblMod.Text = myReader("Model")
If
Not IsDBNull(myReader("Mileage")) Then lblMile.Text =
myReader("Mileage")
If
Not IsDBNull(myReader("Vin Number")) Then lblVinNum.Text =
myReader("Vin Number")
If
Not IsDBNull(myReader("License Number")) Then lblLicNum.Text =
myReader("License Number")
If
Not IsDBNull(myReader("Name")) Then lblDriverName.Text =
myReader("Name")
If
Not IsDBNull(myReader("DateAcquired")) Then lblDateAcq.Text =
myReader("DateAcquired")
If
Not IsDBNull(myReader("DateSold")) Then lblDtSld.Text =
myReader("DateSold")
If
Not IsDBNull(myReader("PurchasePrice")) Then lblPrPrice.Text =
myReader("PurchasePrice")
If
Not IsDBNull(myReader("NextSchedMaint")) Then lblNSM.Text =
myReader("NextSchedMaint")
If
Not IsDBNull(myReader("GVWR")) Then lblGrVWR.Text = myReader("GVWR")
If
Not IsDBNull(myReader("GVW")) Then lblGrVW.Text = myReader("GVW")
If
Not IsDBNull(myReader("Crane Capacity")) Then lblCrCap.Text =
myReader("Crane Capacity")
If
Not IsDBNull(myReader("Crane Certification")) Then lblCrCert.Text =
myReader("Crane Certification")
If
Not IsDBNull(myReader("Repair Cost")) Then lblRepCost.Text =
myReader("Repair Cost")
If
Not IsDBNull(myReader("Estimate Replacement")) Then lblEstRep.Text =
myReader("Estimate Replacement")
If
Not IsDBNull(myReader("SalvageValue")) Then lblSalVal.Text =
myReader("SalvageValue")
If
Not IsDBNull(myReader("CurrentValue")) Then lblCurVal.Text =
myReader("CurrentValue")
If
Not IsDBNull(myReader("Comments")) Then lblCom.Text =
myReader("Comments")
If
Not IsDBNull(myReader("Description")) Then lblDesc.Text =
myReader("Description")
End While
End Sub</code>
If I run this statement in Query Analyzer, it properly returns 1for my testing table. But if I put the statement into a storedprocedure, the stored procedure returns NULL. What am I doingwrong? I suspect it may be related to how I defined the parametersfor the stored procedure. Perhaps my definition of TableName andColumnName don't match what COLUMNPROPERTY and OBJECT_ID expect toreceive, but I don't know where to look for the function declarationsfor those. Any pointers would be appreciated.Select statement:SELECT COLUMNPROPERTY(OBJECT_ID('Table1'), 'TestID', 'IsIdentity') ASIsIdentityTable definition:CREATE TABLE [dbo].[Table1] ([TestID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,[Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL) ON [PRIMARY]Stored Procedure definition:CREATE PROCEDURE spTest(@TableName varchar,@ColumnName varchar)AS SELECT COLUMNPROPERTY(OBJECT_ID(@TableName), @ColumnName,'IsIdentity') AS IsIdentity
View 2 Replies View Related I wrote a select statement, I only want to see orders with max lastUpdatedOn date of 14 days and older. Now my results show dates with all orders of 14 days and older (which is OK), but all others are displayed in the "Uitgifte" column as "NULL". But those orders should not be displayed at all.
selectdistinct ProductionHeader.ProdHeaderOrdNr,
ProductionHeader.PartCode,
ProductionHeader.Description,
ProductionHeader.Qty,
(select max (ProdStatusLog.ProdStatusCode)
[code]...
Hello... im having a problem with my query optimization....
I have a query that looks like this:
SELECT * FROM table1
WHERE location_id IN (SELECT location_id from location_table WHERE account_id = 998)
it produces my desired data but it takes 3 minutes to run the query... is there any way to make this faster?... thank you so much...
I am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly. My problem is that the table I am pulling data from is mainly foreign keys. So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys. I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit. I run the "test query" and everything I need shows up as I want it. I then go back to the gridview and change the fields which are foreign keys to templates. When I edit the templates I bind the field that contains the string value of the given foreign key to the template. This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value. So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors. I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode. I make my changes and then select "update." When the page refreshes, and the gridview returns, the data is not updated and the original data is shown. I am sorry for so much typing, but I want to be as clear as possible with what I am doing. The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work. When I remove all of my JOIN's and go back to foreign keys and one table the update works again. Below is what I have for my SQL statements:------------------------------------------------------------------------------------------------------------------------------------- SELECT:SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) WHERE (People.PersonID = ?)UPDATE:UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ? ---------------------------------------------------------------------------------------------------------------------------------------The only fields I want to update are in [People]. My WHERE is based on a control that I use to select a person from a drop down list. If I run the test query for the update while setting up my data source the query will update the record in the database. It is when I try to make the update from the gridview that the data is not changed. If anything is not clear please let me know and I will clarify as much as I can. This is my first project using ASP and working with databases so I am completely learning as I go. I took some database courses in college but I have never interacted with them with a web based front end. Any help will be greatly appreciated.Thank you in advance for any time, help, and/or advice you can give.Brian
View 5 Replies View RelatedOk I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".
Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.
hiI need to write a stored procedure that takes input parameters,andaccording to these parameters the retrieved fields in a selectstatement are chosen.what i need to know is how to make the fields of the select statementconditional,taking in consideration that it is more than one fieldaddedfor exampleSQLStmt="select"if param1 thenSQLStmt=SQLStmt+ field1end ifif param2 thenSQLStmt=SQLStmt+ field2end if
View 2 Replies View RelatedHi guys,
I have the query below (running okay):
Code Block
SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02'
FROM myTables
WHERE Conditions are true
ORDER BY Field01
The results are just as I need:
Field01 Field02
------------- ----------------------
192473 8461760
192474 22810
Because other reasons. I need to modify that query to:
Code Block
SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02'
INTO AuxiliaryTable
FROM myTables
WHERE Conditions are true
ORDER BY Field01
SELECT DISTINCT [Field02] FROM AuxTable
The the results are:
Field02
----------------------
22810
8461760
And what I need is (without showing any other field):
Field02
----------------------
8461760
22810
Is there any good suggestion?
Thanks in advance for any help,
Aldo.
Hello friends,
I want to use select statement in a CASE inside procedure.
can I do it? of yes then how can i do it ?
following part of the procedure clears my requirement.
SELECT E.EmployeeID,
CASE E.EmployeeType
WHEN 1 THEN
select * from Tbl1
WHEN 2 THEN
select * from Tbl2
WHEN 3 THEN
select * from Tbl3
END
FROM EMPLOYEE E
can any one help me in this?
please give me a sample query.
Thanks and Regards,
Kiran Suthar
I am attempting to run update statements within a SELECT CASE statement.
Select case x.field
WHEN 'XXX' THEN
UPDATE TABLE1
SET TABLE1.FIELD2 = 1
ELSE
UPDATE TABLE2
SET TABLE2.FIELD1 = 2
END
FROM OuterTable x
I get incorrect syntax near the keyword 'update'.
Hi,I have a query like this :SELECTx1,x2,( SELECT ... FROM ... WHERE ...UNIONSELECT ... FROM ... WHERE ...) as x3FROM ...WHERE ...The problem is that I don't want to return the results where x3 isNULL.Writing :SELECTx1,x2,( SELECT ... FROM ... WHERE ...UNIONSELECT ... FROM ... WHERE ...) as x3FROM ...WHERE ... AND x3 IS NOT NULLdoesn't work.The only solution I found is to write :SELECT * FROM((SELECTx1,x2,( SELECT ... FROM ... WHERE ...UNIONSELECT ... FROM ... WHERE ...) as x3FROM ...WHERE ...) AS R1)WHERE R1.x3 IS NOT NULLIs there a better solution? Can I use an EXISTS clause somewhere totest if x3 is null without having to have a 3rd SELECT statement?There's probably a very simple solution to do this, but I didn't findit.Thanks
View 7 Replies View RelatedIn the below code i want to use select statement for getting customer
address1,customeraddress2,customerphone,customercity,customerstate,customercountry,customerfirstname,customerlastname
from customer table.Rest of the things will be as it is in the following code.How do i do this?
INSERT INTO EMImportListing ("
sql += " CustId,Title,Description,JobCity,JobState,JobPostalCode,JobCountry,URL,Requirements, "
sql += " IsDraft,IsFeatured,IsApproved,"
sql += " Email,OrgName,customerAddress1,customerAddress2,customerCity,customerState,customerPostalCode,
[code]....
I have 3 tables, with this relation:
tblChats.WebsiteID = tblWebsite.ID
tblWebsite.AccountID = tblAccount.ID
I need to delete rows within tblChats where tblChats.StartTime - GETDATE() < 180 and where they are apart of @AccountID. I have this select statement that works fine, but I am having trouble converting it to a delete statement:
SELECT * FROM tblChats c
LEFT JOIN tblWebsites sites ON sites.ID = c.WebsiteID
LEFT JOIN tblAccounts accounts on accounts.ID = sites.AccountID
WHERE accounts.ID = 16 AND GETDATE() - c.StartTime > 180
Hey guys i have a stock table and a stock type table and what i would like to do is say for every different piece of stock find out how many are available The two tables are like thisstockIDconsumableIDstockAvailableconsumableIDconsumableName So i want to,Select every consumableName in my table and then group all the stock by the consumable ID with some form of total where stockavailable = 1I should then end up with a table like thisEpson T001 - Available 6Epson T002 - Available 0Epson T003 - Available 4If anyone can help me i would be very appreciative. If you want excact table names etc then i can put that here but for now i thought i would ask how you would do it and then give it a go myself.ThanksMatt
View 2 Replies View RelatedHi Everyone,
I have a query
select name, address, city from table1
I want to append address +'-' + city in this query . I want to show hyphen only if both address and city are not null. If name is null then I don't want to show hyphen. How can I get around this problem.
Any help will be appreciated.
Thanks,
Vinki
I searched through the forums thinking this may have been brought up before, but I cannot find a post the directs to my problem.
I have a lookup table (lookup.os) which defines various operating systems. It has a "OSID" field which is just a primary key numbering, a "OSInstr" field which is used in a 'like statement' in a cursor used later on and "OSName" which is the full name of the OS.
ID| BROWSERNAME | INSTR
*********************************
1 | Windows XP | Windows+NT+5.1
2 | Windows 2003 | Windows+NT+5.2
3 | Windows Vists | Windows+NT+6.0
.....
19 | Unknown Operating System | <blank>
I have a cursor loop that goes through another table (tblIISLog_ALL_OS) and searches through the instr of the (lookup.os) table to add to a stats of a third table (statistics.os). It works fine going through the loop, except there is a problem with the last value in the (lookup.os) table. If the value does not match the rest, it will select value "19 - Unknown Operating System" which is correct, but if the value is null it will not be selected. I take it 'NULL' >< a blank like statement. Here is what I have now. I am open to suggestions of what I should do. I was thinking maybe removing the "19 - Unknown Operating System" from the (lookup.os) table and after the first cursor loop has run, run it again and everything remaining it will just put in as "19 - Unknown Operating System". What do you think?
DECLARE @TempID int,
@TempOSInStr varchar(50),
@TempOSName varchar(100),
@TempCount int
DECLARE loopc CURSOR FOR
SELECT [OSID],
[OSInStr],
[OSName]
FROM [IISLOG_REPORTS].[dbo].[lookup.os]
OPEN loopc
FETCH NEXT FROM loopc INTO @TempID,
@TempOSInStr,
@TempOSName
SET @TempID = 0
SET @TempCount = 0
WHILE @@fetch_status = 0
BEGIN
---------------------
SELECT [date], @TempOSName, count(1)
FROM [IISLOG_REPORTS].[dbo].[tblIISLog_ALL_OS]
WHERE '%' + [csUserAgent] + '%' like '%' + @TempOSInStr + '%'
GROUP BY [date]
INSERT INTO [IISLOG_REPORTS].[statistics].[os]
([TotalDate]
,[OSID]
,[OSTotal])
SELECT [date], @TempID, count(1)
FROM [IISLOG_REPORTS].[dbo].[tblIISLog_ALL_OS]
WHERE '%' + [csUserAgent] + '%' like '%' + @TempOSInStr + '%'
GROUP BY [date]
DELETE FROM [IISLOG_REPORTS].[dbo].[tblIISLog_ALL_OS]
WHERE '%' + [csUserAgent] + '%' like '%' + @TempOSInStr + '%'
----------------------
FETCH NEXT FROM loopc INTO @TempID,
@TempOSInStr,
@TempOSName
---------------------
END
CLOSE loopc
DEALLOCATE loopc
Thanks for the assistance. I hope I provided all the necessary information and explained it ok.
Hello,I have a table which contains some nullable columns. I want to write asingle query, which can be prepared (ie. prepared statement), that canhandle null or non-null values for the where clause. Is this possiblein a standard-conforming manner?The simple for of the query is this:SELECT * FROM <table> WHERE <column> = <value>But when the value to be matched on is NULL, the syntax of the querymust change to be:SELECT * FROM <table> WHERE <column> IS <value>In the second case <value> is NULL.I know one option might be to change the ANSI NULL handling option,but I am loathe to do this (I have five RDBMS's to support, not justMSSSQL).I thought I might have been able to cheat using an IN clause to makethe SQL consistent, but no luck.ThanksKevin
View 6 Replies View RelatedCode below:
SELECT
NAME,
CASE WHEN PHONE IS NULL THEN '-' ELSE PHONE END AS PHONE
FROM
PATIENT
WHERE PHONE = '-'
If I am switching the NULL values to hyphens (-), why am I not getting any records when I look for hyphens in the WHERE clause? The script is not as simple as this or else I would just check for NULL values. I really need to switch NULL values to something I can pass on to a variable and query on it from Reporting Services.
Hello,Thanks for helping me with this... I really appreciate it.I have a table called tblPatientDemographics with a number of columns.I would like to count the number of NULL values per record within mytable.tblPatientDemographicsPatientID Age Weight Height Race1234567 20 155 <NULL> Caucasian8912345 21 <NULL> <NULL> <NULL>In the first example above I want to display '1'In the second example above I want to display '3'Any help would be very much appreciated.Thanks !Chad*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 3 Replies View RelatedExample code:
declare @somesql nvarchar(20)
execute sp_executesql @somesql, N'@lnCurrRow int', @lnCurrRow = 1
PRINT @@ERROR
@@Error returns 0, which is seems incorrect since @somesql is null. If you substitute @somesql for the constant NULL then an error is fired. The above also executes successfully with @@error = 0 if you set @somesql = '' (empty string).
EXEC behaves the same way.
It makes sense that executing an empty string should be fine, but NULL? Is there a SET option available that will cause an error to be raised if sp_executesql attempts to execute a nvarchar variable that's set to NULL?
Thanks.
In ssrs, I want to display a column "Actual Value", which is a currency field.
If the field is null, I want to display a "0.00" value, otherwise I want to display the actual value with 2 decimals as well
Not sure how to do this. Below is what I have so far....
=IIf(IsNothing(Fields!Actual_Value.Value),0,Fields!Actual_Value.Value)
SELECT Top 10 Name, Contact AS DCC, DateAdded AS DateTimeFROM NameTaORDER BY DateAdded DESC
I'm trying to right a sql statement for a gridview, I want to see the last ten records added to the to the database. As you know each day someone could add one or two records, how can I write it show the last 10 records entered.
On my aspx page I have a basic sqldatasource and gridview - both were set up using the wizards. The SQL Server 2000 database table that the sqldatasource is querying has some fields in it that are set to not allow nulls, however, the gridview control is not displaying all of the fields in the table - including some of the non-null fields.
My problem is, when I run an insert on the table from my aspx page, I get an error that says: "Cannot insert the value NULL into column 'ColumnName', table 'TableName'; column does not allow nulls. INSERT fails. The statement has been terminated."
The 'ColumnName', as you may have guessed, is one of the aforementioned columns that doesn't allow nulls, but isn't in the GridView.
How can I do an insert on this table without messing with the non-null fields. Those fields are relevant to the purposes of the gridview, so I don't want to display them, let alone allow editing of them.
Any suggestions on my options would be greatly appreciated!
Thanks
Capella07
Can someone help me with this :
Case WHEN ISNULL (dbo.BLH.n1name,'NOTHING')= 'NOTHING' then dbo.BLH.coname else dbo.BLH.n1name End as CustName
as the strored proc is not returning value when the fileds is blank
cheers
I am attempting to update a table that drives a report. The report is at the order level. An order can have several types of demand (revenue $): Ship and/or Backordered. When I update one of these demand fields on the report table, I would expect those orders that have that type of demand to update, and those orders that don't have that type of demand to remain at their current value ($0 in this example which is the table default). But what is happening is that orders that don't have that type of demand are changing to NULL. What am i doing wrong with my update statement that is causing this?
Code:
CREATE TABLE #sales(
OrdNo int,
StatusGrp varchar(10),
Demand decimal(10,2)
)
INSERT INTO #sales
VALUES (1,'Ship',100.00)
[Code] .....
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
How would amend the following so that if one or both variables were NULL the result would default of ALL (everything)for that variable
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[InLnFncSchool]
(
@School nvarchar(100) =NULL,
[Code] ....
What does it mean "select null"?
Is null meant nothing?
Thanks.
Code Snippet
select null
from(
select e.empno,e.ename,e.job,e.mgr,e.hiredate,
e.sal,e.comm,e.deptno,count(*) as cnt
from emp e
Hello
How can i say this I would like my if statement to say: if what the client types in Form1.Cust is = to the Select Statement which should be running off form1.Cust then show the Cust otherwise INVALID CUSTOMER NUMBER .here is my if statement.
<% If Request.Form("Form1.Cust") = Request.QueryString("RsCustNo") Then%> <%=Request.Params("Cust") %> <% Else %> <p>INVALID CUSTOMER NUMBER</p> <% End If%>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:RsCustNo %>"
ProviderName="<%$ ConnectionStrings:RsCustNo.ProviderName %>" SelectCommand="SELECT [CU_CUST_NUM] FROM [CUSTOMER] WHERE ([CU_CUST_NUM] = ?)">
<SelectParameters>
<asp:FormParameter FormField="Cust" Name="CU_CUST_NUM" Type="String" />
</SelectParameters>
</asp:SqlDataSource>any help would be appreciated