Hello all
I am trying to run a sql statement, (without having to run stored procedures), that will lookup a value stored in a Record.field., otherwise ignore the value if it is blank. I have a field tied to a Microsoft Access form - ItemLocation. If the user selects a unique ItemLocation the report will attempt to locate all values within that ItemLocation. If the user decides to leave the field blank, I would like for it to return all values for every ItemLocation. If anyone can help, I would really appreciate it. I will attach code. My problem is the last line of this SQL statement. If F.ITemLocation is null, I get no data. I would like for it to return everything. Thank you.
Code:
SELECT DISTINCT
B.BarCodeID,
A.ItemDescription,
A.ItemCategory,
A.TypeOfItem,
A.SerialNumber,
B.ItemLocation,
B.LocationID,
B.LastUpdate,
B.TrackItID,
B.UserID
FROM
tblMISFixedAssetTable A,
tblMISFixedAssetTable2 B
WHERE A.BarCodeID = B.BarCodeID
AND A.DisposalDate is null
AND B.LastUpdate = (Select Max(C.LastUpdate)
from tblMISFixedAssetTable2 C
Where B.BarCodeID = C.BarCodeID)
AND B.BarCodeID in (Select D.BarCodeID
from tblMISFixedAssetTable3 D
Where D.FiscalYear = (Select E.FiscalYear
from tblReportFY E))
AND B.ItemLocation = (Select F.ItemLocation
from tblReportFY F)
Hey. I need to substitute a value from a table if the input var is null. This is fine if the value coming from table is not null. But, it the table value is also null, it doesn't work. The problem I'm getting is in the isnull line which is in Dark green color because @inFileVersion is set to null explicitly and when the isnull function evaluates, value returned from DR.FileVersion is also null which is correct. I want the null=null to return true which is why i set ansi_nulls off. But it doesn't return anything. And the select statement should return something but in my case it returns null. If I comment the isnull statements in the where clause, everything works fine. Please tell me what am I doing wrong. Is it possible to do this without setting the ansi_nulls to off??? Thank you
set ansi_nulls off
go
declare
@inFileName VARCHAR (100),
@inFileSize INT,
@Id int,
@inlanguageid INT,
@inFileVersion VARCHAR (100),
@ExeState int
set @inFileName = 'A0006337.EXE'
set @inFileSize = 28796
set @Id= 1
set @inlanguageid =null
set @inFileVersion =NULL
set @ExeState =0
select Dr.StateID from table1 dR
where
DR.[FileName] = @inFileName
AND DR.FileSize =@inFileSize
AND DR.FileVersion = isnull(@inFileVersion,DR.FileVersion)
i have query which does the following select x from y where t = "House"x is an integer. If no record is found, how do i get it to return 0 rather than null?
Hi I have an sql statement that when i put through the query builder and specify NULL it returns all the rows. if i put 0 it returns nothing.how can i change the "IS Null" part of the statement to work with 0. so when i specify 0 for all values it returns all the rows.so i want 0 to work like NULL does 1 SELECT LocationID, TypeID, Title, Description, PropertyID, Price, 2 (SELECT Location 3 FROM Location_Table 4 WHERE (Property_Table.LocationID = LocationID)) AS Location, 5 (SELECT TypeOfProperty 6 FROM Type_Table 7 WHERE (Property_Table.LocationID = TypeID)) AS TypeOfProperty 8 FROM Property_Table 9 WHERE (@MinPrice IS NULL) AND (@MaxPrice IS NULL) AND (@TypeID IS NULL OR 10 TypeID = @TypeID) AND (@LocationID IS NULL OR 11 LocationID = @LocationID) OR 12 (@MinPrice IS NULL) AND (@TypeID IS NULL OR 13 TypeID = @TypeID) AND (@LocationID IS NULL OR 14 LocationID = @LocationID) AND (Price <= @MaxPrice) OR 15 (@MaxPrice IS NULL) AND (@TypeID IS NULL OR 16 TypeID = @TypeID) AND (@LocationID IS NULL OR
I am working on a web application for a company that uses an existing company database. I just found out before the long weekend that a couple hundred of the items in the table aren't completely related.
I am mostly using 2 tables for the project: ItemVersion, ItemVersionRev
ItemVersion has fields: ItemVersion, ItemNumber, RecordID ItemVersionRev has fields: RecordID, RevNumber
All of the items in the database have ItemNumbers (obviously) and most have revision numbers (RevNumber). One of the functions that I created for my application finds the MaxOfRevNumber. I ran a query on the database and saw that dreaded error screen that read "There is no row at position 0"
The query that I have now simply returns MaxOfRevNumber. Is there a way to program a query to check and see if an ItemNumber has a Revision and depending on the result, return a certain value. For example, if there is not a corresponding RevNumber, then return 0 as the RevNumber?
hi i am trying to get the output of the select statements of sqldatasource : protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { DataView dv; dv = (DataView)(this.SqlDataSourcePictures.Select(DataSourceSelectArguments.Empty)); } } the problem is that dv returns null ? and here is the sqldatasource definition in .aspx page <asp:SqlDataSource ID="SqlDataSourcePictures" runat="server" ConnectionString="<%$ ConnectionString:con1%>" SelectCommand="SELECT URL FROM SchoolPictures WHERE (School_Code = @School_Code) AND (SchoolPictureCategory = @SchoolPictureCategory)" OnSelecting="SqlDataSourcePictures_Selecting" OnSelected="SqlDataSourcePictures_Selected"> <SelectParameters> <asp:QueryStringParameter Name="School_Code" QueryStringField="bid" Type="Int16" /> <asp:ControlParameter ControlID="ddlCat" Name="SchoolPictureCategory" PropertyName="SelectedValue" Type="Int16" /> </SelectParameters> </asp:SqlDataSource>
Hi, I basically do not want to return a null value as a result of using a sum function (using sum against 0 rows). Is there a common way to avoid this? Thanx
I have this program that I want to invoke from SQLServer funny thing is I can invoke this from command line but could not get it to work via 'xp_cmdshell' on this particular server. When I invoke run the following from SQLServer Query Analyser I always get 'NULL' returned. I tried just running EXEC master..xp_cmdshell 'dir ' and it worked, so I am certain it is not a SQL rights problem.
FYI, I executed the same code on another machine and it worked. I just cannot figure out why it is not working on this one server? Can anyone help? thanks
I have this table 'Request' in which there's 2 fields 'RequestName' and 'DateSubmitted'. The datatype for 'DateSubmitted' is datetimn.
I have to write a query to extract all the 'RequestName' that falls under each month based on the 'DateSubmitted'.
For example, the table 'Request' has 12 rows, each row ('DateSubmitted') with the month of the year. Like row1 = January, row2 = February etc.
For each month, I have to extract all the 'RequestName' for that month based on 'DateSubmitted' field. My query is like this:
select RequestName, datename(month, DateSubmitted)as month from Request where datename(month, DateSubmitted) = 'March'.
By right it should return just one row right?
Instead, it returned me all 12 rows, only one row with value 'March' and the other 11 rows with value 'NULL'. Even if the other 11 rows have value of other months, when i run the above query, it will return me null.
I am trying to return all the names of employees and their managers
this query returns all the employees except for 1
SELECT E.FNAME,E.LNAME,M.FNAME,M.LNAME FROM EMPLOYEE E,EMPLOYEE M WHERE E.SUPERSSN=M.SSN
the one that isn't returned has a null SUPERSSN, but when I add in:
OR E.SUPERSSN IS NULL
it returns a row with the name of the employee whose SUPERSSN is null 8 times (where each time the M.FNAME,M.LNAME are other employee names)
How do I ammend the first query to return each employee and their respective manager once, the employee without a manager having null values for the manager name columns?
johnny writes "I am trying to return only the columns from multiple tables that are NOT NULL for a specific ID. Once I have the tables JOINED is there a way to only get those columns that are populated. Thanks."
SQL Server 2000 SP3.Is it possible for the @@ROWCOUNT function to return NULL after astatement? I am troubleshooting a relatively large stored procedure withmultiple SELECT statements and a couple of INSERTs into table variables.Immediately after each statement I save the value returned by @@ROWCOUNT toa local variable. That information eventually is passed back to the clientvia one output parameter, for all statements in the procedure.Occasionally, the value returned via that parameter is NULL. This cannot bereproduced by re-running the SP with the same input parameters.Before doing any further troubleshooting, I would like to rule out thepossibility that @@ROWCOUNT can actually return a NULL under somecircumstances. From searching the archives, it appears that in SQL Server7.0 this could happen in the context of a DML query on a table withtriggers. This is not the case here - the only DML queries are INSERTs intotable variables, all other queries in the SP are SELECTs.Any related information would be appreciated.--remove a 9 to reply by email
I am new to XML file to SQL table update. I use sp_xml_preparedocument and openxml to extract the fields from xml file but it returns NULL highlight below. I also notice both sp_xml_preparedocument and sp_xml_removedocument store procedures are not in SQL system store procedure.
Hello, I have a stored procedure that accepts a number of different input parameters that populate some variables in my stored procedure. I want to have this stored procedure return nothing if some of these variables aren't filled out (they are populated by a search page the user fills out). I'm not very familiar with writing stored procedures, so any help you can give me is appreciated. Thanks!
I'm trying to ensure that only of the parameters is passed to my stored procedure.
BOL says that the IS [NOT] NULL operator (language construct?) will return a boolean. An IF statement takes an expression which results in a boolean so I was surprised to find that the below code doesn't parse.
CREATE PROC sp_OneParm( @NumericVal float = null, @StringVal nvarchar(200) = null, @DateVal datetime = null, @BitVal bit = null) AS DECLARE @ValCount tinyint SET @ValCount = 0
-- Ensure we've only got one update value specified IF @NumericVal IS NOT NULL @ValCount = @ValCount + 1 IF @StringVal IS NOT NULL @ValCount = @ValCount + 1 IF @DateVal IS NOT NULL @ValCount = @ValCount + 1 IF @BitVal IS NOT NULL @ValCount = @ValCount + 1 IF @ValCount > 1 RAISERROR ('Only one @*Val paramater may be specified when calling sp_OneParm()', 16, 1)
-- Other Stuff GO
Am I missing something simple or do I need to restructure my code to achieve the logic I want?
In a new instalation of SQL Server 2005. I've made a restore from SQL Server 2000 database.
Solve some problem with the user, and it looks ok.
I'm using a asp web aplication, with vb dll, and change the sql provider from "SQL Server" to "SQL Native Client". It works, but in some recordsets the value a recive for the table primary key is null?????
If execute the querie in the database, the result has values in the primary key.
the same appends when i connect to an SQL Server 2000 using "SQL Native Client".
I'm creating a 'dynamic where clause' with 15 parameters. I was using coalesce with the example below and it's working fine until it encounters a null value. I was trying to use nullif with coalesce to no avail. Can someone show me based on my example below how I can incorporate nullif with coalesce. Thanks in advance for you help.
Code Block WHERE cs.DirID = Coalesce(@DirNum, cs.DirID)
I have an assembly that contains the following function:
Public Class Lookup
<SqlFunction()> _
Public Shared Function MyTest() As Integer
Return System.Data.SqlTypes.SqlInt64.Null
End Function
End Class
Then in SSMS:
CREATE ASSEMBLY IRS_MyTest
FROM '\machine empmyAssembly.dll'
GO
CREATE FUNCTION dbo.MyTest() RETURNS INT
AS EXTERNAL NAME IRS_MyTest.[MyClass].MyTest
GO
when I run:
SELECT dbo.MyTest()
the following is returned:
Msg 6522, Level 16, State 2, Line 1
A .NET Framework error occurred during execution of user defined routine or aggregate 'MyTest':
System.Data.SqlTypes.SqlNullValueException: Data is Null. This method or property cannot be called on Null values.
System.Data.SqlTypes.SqlNullValueException:
at System.Data.SqlTypes.SqlInt64.get_Value()
at System.Data.SqlTypes.SqlInt64.op_Explicit(SqlInt64 x)
at Informed.DCLG.IRS.SQL.IncidentTransform.Lookup.MyTest()
Can anyone please advise on how to return back a null value. Currently, my only other option is to return nothing (actually returns 0) and then wrap this up to convert the value to null - not ideal.
In my report i have CNAME parameter , which allows null value. I checked Allow null value check box in report parameter properties.
when i preview the report , it displays checked NULL check box beside CNAME parameter . I want to give some meaningful name(i.e.ALLCustomers) to this checkbox instead of NULL.
If 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")
I recently ran into an issue with an issue with a query against our Data Warehouse. When attempting to sum revenue from a table, and using a WHERE clause on a field that contains NULL values, the records with the NULL values are suppressed (in addition to whatever the WHERE clause specified). I believe this is because a NULL value is unknown so SQL doesn't know if it does or doesn't fit the criteria of there WHERE clause so it is suppressed.
That being said, is there a way to avoid this instead of having to add an ISNULL function in the WHERE clause which is going to kill performance?
Code: create table #nullTest ( name varchar(50) ,revenue int)
INSERT INTO #nullTest Values ('Tim',100) ,('Andrew', 50) ,(null, 200)
SELECT sum(revenue) as Revenue FROM #nulltest WHERE name <> 'tim'
Ideally, I would want the SELECT statement above to return 250, not 50. The only way I can think to accomplish this is with this query:
Code: SELECT sum(revenue) as Revenue FROM #nullTest WHERE isnull(name,'') <> 'tim'
Using TSQL, I have a table that holds filenames of Pictures for products. Different products can be using the same picture. I need to select the filenames for a single product only if it does not exists for a different product.I have tried Where Exists (select FileName From Tbl where
Prod_Id = @var) AND NOT EXISTS(select FileName From Tbl where Prod_Id != @var) In the Select Statement.
In the flat file SampleID and Product are populated in the first row only, rest of the rows only have values for Rep_Number, Protein, Fat, Solids.
SampleID and Product are blank for the rest of the rows. So my task is to fill those blank rows with the first row that has the sampleID and Product and load into the table.
I have a report that is run on a monthly basis with a default date of null. The stored procedure determines the month-end date that it should use should it be sent a null date.
The report works fine when I tell it to create a history entry; however, when I try to add a subscription it doesn't appear to like the null parameter value. Since I have told the report to have a default value of null it doesn't allow me to enter a value on the subscription page.
Now, I suppose I could remove the parameter altogether from the stored proc, but then the users would never be able to run the report for a previous time period. Can someone explain to me why default values aren't allowed to be used on subscriptions when they seem to work fine for ad hoc and scheduled reports? This is really quite frustrating as most of my reports require a date value and default to null so that the user doesn't have to enter them for the latest data.
An internal error occurred on the report server. See the error log for more details. (rsInternalError) Get Online Help
I run a stored procedure for which I have a return variable. The stored procedure returns the ID of a row in a table if it exists:
m_sqlCmd.ExecuteScalar();
The m_sqlCmd has been fed an SQLParameter with direction set to output. When the stored proc returns, I want to test it. Now when there IS a row it returns the ID ok. When the row doesn't exist, in my watch I have:
m_sqlParam.SqlValue with value {Null}
I can't seem to work out how to test this value out. I've tried several things but none seem to work.
This line compiles ok, but the following runs into the IF statement as if the SqlValue is null??
if (m_sqlParam.SqlValue != null).... {
// I'm here!! I thought the watch says this is null??? }
Sorry if this is obvious, but I can't work this one out!!
Looks like there was a fix and then I read this fix is not a fix. Does anyone know how this can be rectified? Does it mean that only Windows authentiation is the only way it works. The Software is over 2 years old, there are no excuses.
Does SQL have a function that return "null" for records which don't exist? Per example in a FK relation ship, that not all records in the first table have a "child" in the second table, so it returns null records.
I have this INNER JOIN that is fine to show all possible combinations. But I need to show only rows that have one or more Null values in tbIntersect.
Should I use nested LEFT JOINT? How?
This is the SQL statement: sSQL = "SELECT DISTINCT tbCar100.Car100_ID, tbCar100.Description100 AS [Caractéristique 100], " & _ "tbCar200.Car200_ID, tbCar200.Description200 AS [Caractéristique 200], " & _ "tbCar300.Car300_ID, tbCar300.Description300 AS [Caractéristique 300], " & _ "tbCar400.Car400_ID, tbCar400.Description400 AS [Caractéristique 400], " & _ "tbCar500.Car500_ID, tbCar500.Description500 AS [Caractéristique 500], " & _ "tbCar600.Car600_ID, tbCar600.Description600 AS [Caractéristique 600], " & _ "tbCar700.Car700_ID, tbCar700.Description700 AS [Caractéristique 700], " & _ "tbProducts.Prod_ID, tbProducts.PartNumber AS [Part Number] , tbProducts.Description AS [Description] , tbProducts.DateAdded AS [Date] " & _ "FROM tbProducts INNER JOIN (tbCar700 INNER JOIN (tbCar600 INNER JOIN (tbCar500 INNER JOIN (tbCar400 INNER JOIN (tbCar300 INNER JOIN (tbCar100 INNER JOIN " & _ "(tbCar200 INNER JOIN tbIntersect ON tbCar200.Car200_ID = tbIntersect.Car200_ID) " & _ "ON tbCar100.Car100_ID = tbIntersect.Car100_ID) ON tbCar300.Car300_ID = tbIntersect.Car300_ID) ON tbCar400.Car400_ID = tbIntersect.Car400_ID) ON tbCar500.Car500_ID = tbIntersect.Car500_ID) ON tbCar600.Car600_ID = tbIntersect.Car600_ID) ON tbCar700.Car700_ID = tbIntersect.Car700_ID) ON tbProducts.Prod_ID = tbIntersect.Prod_ID " & _ ";"
Here is the content of the tbIntersect table: Car100_ID Car200_ID Car300_ID Car400_ID Car500_ID Car600_ID Car700_ID Prod_ID ID 1 1 1 1 1 1 1 1 1 1 2 1 1 1 1 1 19 1 3 1 1 1 1 1 20
I need to return the rows that have null data, ex: second row because Prod_ID is NULL and third row because Car300_ID is NULL. In fact I need the data from the other joint tables that correspond to these ID fields.
