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.
Thank you very much.
I have two tables:
1. tblProducts
columns: ProductID, ProductName
2. tblProductCodes
columns: ProductID, CustomerID, ProductCode
The 2nd table is for storing product codes for customers, in other words, one product can have different ProductCode for different customers. But some customers do not have ProductCode for a ProductID.
I want to create a query to return all the Products and its ProductCode (null is valid) for a specific customer.
I tried:
SELECT dbo.tblProductCodes.ProductCode, dbo.tblProductCodes.CustomerID,
dbo.tblProducts.ProductName,
dbo.tblProducts.ProductID
FROM dbo.tblProducts LEFT OUTER JOIN
dbo.tblProductCodes ON dbo.tblProducts.ProductID = dbo.tblProductCodes.ProductID
WHERE dbo.tblProductCodes.CustomerID = 2
But the query left out all products that does not have ProductCode value in tblProductCodes table for CustomerID = 2. I want all the ProductName returned from query and render null or empty string for ProductCode value if the code does not exist in tblProductCodes table for the customer.
Any help is highly appreciated.
I have table Products and Orders that has the following columns:
table Products: ProductID, ProductName
table Orders: OrderID, ProductID, OrderDate, Quantity, Price
The Orders table contains orders placed on all the dates. I want to obtain a list of orders for a particular date, if there is no order for a product on the requested date, I want to return null values for the Quantity and Price fields.
I tried the following select statement:
select Products.ProductName, Orders.Quantity, Orders.Price from Products left join Orders on Products.ProductID = Orders.ProductID where Orders.OrderDate = '10/16/2004'
Where, there are a total of three products (A,B,C) in table Products. Product-C has no order on 10/16/2004, but I want it to return :
ProductName / Quantity / Price
Product-A 5 1.89
Product-B 6 2.43
Product-C null null
Obviously, my sql statement won't work becaue the where clause will filter out Product-C.
Could anyone help me figure out how to modify my sql code to get the resultset I want?
Thanks in advance.
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
Hi,
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.
Thanks,
Jan.
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'
I have tables and a function as representated by the code below. The names for objects here are just for representation and not the actual names of objects. Table RDTEST may have one or multiple values for RD for each PID. So the function GIVERD will return one or multiple values of RD for each value of PID passed to it.
When I run the following query, I get the required result except the rows for CID 500 for which PID is NULL in table T1. I want the rows for CID 500 as well with PID values as NULL.
SELECT A.CID,
A.ANI,
A.PID,
B.RD
FROM T1 AS A CROSS APPLY GIVERD(A.PID) B
CREATE TABLE [DBO].[RDTEST](
[PID] [INT] NULL,
[RD] [INT] NULL
)
[Code] ....
I want to write a function, which accept 3 parameters, 1 TableName 2 ColumnName 3 DateValue, and returns number of records in that table for that particular date(in parameter date), I have written below function but it is not returning the desired result.
CREATE FUNCTION dbo.[f_Rec_cnt]
(@InTableName NVARCHAR(100),
@InDtColName NVARCHAR(50),
@InDate NVARCHAR(50)
)
RETURNS INT
[Code] .....
My overarching goal is to generate sets of random Symptom records for each Enrollee in a drug study, so that for each cycle (period of time), the code will insert a random number of random records for each enrollee.
I'm trying to return a number of random records from a table, but inside a table-valued function... (which could be my problem).
CREATE FUNCTION dbo.ufn_GetTopSymptoms (
@enrollID INT
, @CTCVersion VARCHAR(20)
, @NumRecords INT
)
RETURNS TABLE
[Code] ....
But that ORDER BY NEWID() clause is illegal apparently, because here's the error it throws:
Msg 443, Level 16, State 1, Procedure ufn_GetTopSymptoms, Line 13
Invalid use of a side-effecting operator 'newid' within a function.
I was hoping I could return a set of enrollmentIDs and then use CROSS APPLY to generate a random set of records for each enrollmentID... is this not possible with APPLY? I was trying to avoid using a cursor...
The idea is basically to create all the Symptom records for all the patients in treatment cycle at once by using Enrollee OUTER APPLY dbo.ufn_GetTopSymtoms(dbo.Enrollment.EnrolleeID)
but that's clearly not working. Is there a way to do this without resorting to a cursor?
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)
AND DR.languageid = isnull(@inlanguageid,null)
AND DR.[ID]= @ID
)
go
set ansi_nulls on
I'm a Reporting Services New-Be.
I'm trying to create a report that's based on a SQL-2005 Stored Procedure.
I added the Report Designer, a Report dataset ( based on a shared datasource).
When I try to build the project in BIDS, I get an error. The error occurs three times, once for each parameter on the stored procedure.
I'll only reproduce one instance of the error for the sake of brevity.
[rsCompilerErrorInExpression] The Value expression for the query parameter 'UserID' contains an error : [BC30654] 'Return' statement in a Function, Get, or Operator must return a value.
I've searched on this error and it looks like it's a Visual Basic error :
http://msdn2.microsoft.com/en-us/library/8x403818(VS.80).aspx
My guess is that BIDS is creating some VB code behind the scenes for the report.
I can't find any other information that seems to fit this error.
The other reports in the BIDS project built successfully before I added this report, so it must be something specific to the report.
BTW, the Stored Procedure this report is based on a global temp table. The other reports do not use temp tables.
Can anyone help ?
Thanks,
Okay, I have the following statement for my store procedure:
Code Snippet
IF exists (SELECT [ID], [title], [name], [issueID] FROM [Outlook] WHERE ([issueID] = @OLissueID))
SELECT [ID], [title], [name], [issueID] FROM [Outlook] WHERE ([issueID] = @OLissueID)
ELSE
SELECT null ID, 'No parent page yet' title FROM Outlook
I wanted to put an ELSE IF statement right before the ELSE statement to check if the value is null. Something like this:
IF exists (SELECT [ID], [title], [name], [issueID] FROM [Outlook] WHERE ([issueID] = @OLissueID))
SELECT [ID], [title], [name], [issueID] FROM [Outlook] WHERE ([issueID] = @OLissueID)
ELSE IF (SELECT [ID], [title], [name], [issueID] FROM [Outlook] WHERE ([issueID] = @OLissueID) != null
SELECT [ID], [title], [name], [issueID] FROM [Outlook] WHERE ([issueID] = @OLissueID)
ELSE
SELECT null ID, 'No parent page yet' title FROM Outlook
Or if this is possible, it's much better:
IF exists (SELECT [ID], [title], [name], [issueID] FROM [Outlook] WHERE ([issueID] = @OLissueID) AND (SELECT [ID], [title], [name], [issueID] FROM [Outlook] WHERE ([issueID] = @OLissueID) != null)
SELECT [ID], [title], [name], [issueID] FROM [Outlook] WHERE ([issueID] = @OLissueID)
ELSE
SELECT null ID, 'No parent page yet' title FROM Outlook
I don't quite get how to write the SQL statement that I wanted so any help is much appreciated.
What SQL statement can i run to return a list of new tables or columns that exist in the Sales Database on Server A, which do not exist in the Sales Database in Server B.
Server A and Server B are linked servers.
I am using SQL Server 2005. Thanks.
I'm just learning to join tables and I'm trying to construct a query to tell me the following:
Table names and related columns:
Table1.HdrSys
Table2.HdrSysNum and HdrSys
Table3.HdrSysNum
Table3 contains the detail info for Table2. Table1 is the header table for Table2. So.. If I want all records in Table3 that don't have a related record in Table1, is this even possible?
I started with:
select * from Table3 a
join Table2 b on a.HdrSysNum = b.HdrSysNum
join Table1 c on b.HdrSys= c.HdrSys
-- Now.. how do I qualify the statement.. or can I with just this. Thx! Hope I wasn't too confusing.. because I tend to get that way when I'm confused!
basically IF multiple records with the same start date exist, i have to select the one with the max(note_id)
Example
note_id/start_date
88888/08-01-2001
98788/08-01-2001 <===
55555/08-01-2001
54856/08-01-2001
in the example above, i would need to pick the one with the arrow
note_id-98788 and start_date-08-01-2001
I am trying to write a script that will only insert records that do not exist to a local table. Whenever a form is changed, it keeps the same name, but a new record is created with a new ID. I need to be able to pull the new record by checking for ID's that are not on the local table, but using a form name to isolate. The nested select I am attempting is
select distinct qf.[eform_id]
, qf.[name]
, qf.[description]
from [qfiniti].[qfiniti_platform].[dbo].[eval_forms] qf
inner join FORMS_REF fr on qf.[name] collate database_default = fr.form_name collate database_default
where not exists (select fr.eform_id
from forms_ref fr
inner join [qfiniti].[qfiniti_platform].[dbo].[eval_forms] qf on fr.eform_id = qf.eform_id);
It is outputting nothing, but I have intentionally deleted one form record to force it to show up.
Hi all,
I am developing an ETL wherein the requirement is to do an incremental load and at the same time, if there is a record that got deleted in the Source delete it from the destination too, makes sense.
The approach am doing is, pick data from the SRC and Destination, pass it onto a Merge join component, do a Full Outer join, then pass the rows to a conditional split. Newly Added records and updated records I can handle, how do I handle the Deleted records?
Am I correct in the way I am doing or there is something better to handle this?
Thanks in advance
When I run the Microsoft tutorial for data mining I get this error when I get to the decision tree part.
I get a similar error for clustering in the same tutorial.
However, The Naive Bayes demo seems fine.
The messages said the project was built and deployed without errors.
Does anyone know how to fix the error:
TITLE: Microsoft Visual Studio
------------------------------
The tree graph cannot be created because of the following error:
'Query (1, 6) The '[System].[Microsoft].[AnalysisServices].[System].[DataMining].[DecisionTrees].[GetTreeScores]' function does not exist.'.
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.762&EvtSrc=Microsoft.AnalysisServices.Viewers.SR&EvtID=ErrorCreateGraphFailed&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
Query (1, 6) The '[System].[Microsoft].[AnalysisServices].[System].[DataMining].[DecisionTrees].[GetTreeScores]' function does not exist. (Microsoft OLE DB Provider for Analysis Services 2005)
------------------------------
BUTTONS:
OK
------------------------------
I am trying to put together a Stored Procedure that runs other SP's based on a records found condition. Sometimes my update table either by deleting duplicates or for other reasons is empty. Running a SP to update records on a table is not needed.
IF EXISTS (SELECT TOP 1 * FROM Work_Tables.dbo.Trace_Return_Updates WHERE TR_RecType = 'CT1')
BEGIN
EXEC IMB_Trace_Data.dbo.Update_CT1_Trace_Data
END
IF EXISTS (SELECT TOP 1 * FROM Work_Tables.dbo.Trace_Return_Updates WHERE TR_RecType = 'CT2')
BEGIN
EXEC IMB_Trace_Data.dbo.Update_CT2_Trace_Data
END
IF EXISTS (SELECT TOP 1 * FROM Work_Tables.dbo.Trace_Return_Updates WHERE TR_RecType = 'FULF')
BEGIN
EXEC IMB_Trace_Data.dbo.Update_FULF_Trace_Data
END
Hi guys,i have a little problem here.im attempting to write a stored procedure that compares two tables ofthe same data structure and adds (inserts) extra records that exist intable1 to table2.My problem is that i dont have a unique identifier between the tables.i think someone said that i needed to build up a keyany ideas greatly appreciated ??C
View 1 Replies View Relatedi 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?
View 9 Replies View RelatedHi 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?
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)
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>
thanks for help
hi,
can anyone tell me how i can replicate the Access function NZ please?
i want to use it with charindex when parsing text strings
many thanks
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
----
DECLARE @command varchar(500)
SET @command = 'c:ServerClientabc.exe BBB/XX B,AAAA'
EXEC master..xp_cmdshell @command
Hi,
I have this problem with MS SQL Server.
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.
Any help is appreciated.
thanks a million.
Hi all,
I have the situation below. I can't use dual. Thank you for your time.
Example Query:
select name
from employee
where id=1;
Result:
0 rows selected
Wanted Result:
1 row selected
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."
View 2 Replies View RelatedSQL 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
View 3 Replies View RelatedI 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.
View 5 Replies View Related