Return Null Values Using Coalesce And Nullif

Jan 9, 2008

Hello:

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)

View 12 Replies


ADVERTISEMENT

Coalesce And Nullif

Feb 9, 2007

hello,

from one of my solution, i havent had time untill now to ask detail what is this coalesce and nullif? Is nullif just like the isnull function? while coalesce is to replace null? can someone explain base on this eg??

d.LocID>= coalesce(nullif(@LocFrom, ''), d.LocID) and
d.LocID<= coalesce(nullif(@LocTo, ''), d.LocID) and

~~~Focus on problem, not solution~~~

View 9 Replies View Related

Return A Row With Null Values?

Apr 19, 2014

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?

View 1 Replies View Related

Check For Null Or Empty Values And Return 0

Mar 13, 2014

I am using the below query to calculate column values. But I need to return zero when a column values is empty or null.

select [Funding] [Fundings],
[Original] AS [Originals],
[Variance] = SUM([Previous_Year]-[Current_Year]),
[SumValue] = SUM([CurrentYear]/4),
[ActualValue] = SUM([Variance] * 0.75),
[FinanceYear],
[New Value] = SUM([Previous_Year]+[Current_Year])
from Finance
GROUP BY [Original], [FinanceYear]

View 1 Replies View Related

Return NULL Values In SELECT Statement With INNER JOIN ?

May 16, 2005

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")

        End While
    End Sub</code>

View 1 Replies View Related

Nested Join To Return Only Rows With Null Values From All Tables

Oct 17, 2007



Hello,

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.

Thanks

View 5 Replies View Related

How To Return Null Values For Non-exist Record On A Left Join Statement

Oct 16, 2004

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.

View 2 Replies View Related

Transact SQL :: Preserve And Return NULL For Non Matching Values From A Table Valued Function

Jun 29, 2015

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] ....

View 4 Replies View Related

Return Type Of COALESCE?

Jul 23, 2005

Hi everybody,VARCHAR has a higher precedence than CHAR. If you have a querySELECT COALESCE(c1,c2) FROM T1where c1 is CHAR(5) and c2 is VARCHAR(10), I would expect the returntype to be varchar(10) similiar to UNION. However it turns out tobe CHAR(10). Does anyone know why that is so?Thanks,Steffen

View 3 Replies View Related

Coalesce Vs NULL

Jun 19, 2008

Hi All
I have a problem in making out why Coalese is considered to be better than ISNULL .According to my investigation Coalesce Returns the data type of expression with the highest data type precedence.If for eg I have declared that I want to return the value only upto 3 char then why will I use Coalesce and override my requirement.Anyone with clear concept about this plz explain.
DECLARE @v1 VARCHAR(3)DECLARE @i1 INT
SELECT ISNULL(@i1, 15.00) /2,
COALESCE(@i1 , 15.00) /2,
ISNULL(@v1, 'Teaser #2'),
COALESCE(@v1, 'Teaser #2')
The result will be

7.500000 
Tea 
Teaser #2
 
thanks in advance

View 8 Replies View Related

What Is Th Difference Between Is Null & COALESCE

Feb 4, 2008

WHATS the basic diff. between Is Null and Coalesce can someone help me regarding this

Rahul Arora
07 Batch
NCCE Israna,


######################
IMPOSSIBLE = I+M+POSSIBLE

View 3 Replies View Related

COALESCE: Empty Instead Of NULL?

Mar 25, 2008

I've inherited a terribly designed database. When cells in the tables have nothing in them, rather than being NULL, they're just empty. So now I can't use COALESCE...

Is there a way for COALESCE to check if a cell is empty instead of NULL? And if not, is there a way to get around this?

View 1 Replies View Related

Problem With Isnull. Need To Substitute Null If A Var Is Null And Compare It To Null And Return True

Sep 20, 2006

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

View 3 Replies View Related

Transact SQL :: Return Set Of Values From SELECT As One Of Return Values From Stored Procedure

Aug 19, 2015

I have a stored procedure that selects the unique Name of an item from one table. 

SELECT DISTINCT ChainName from Chains

For each ChainName, there exists 0 or more StoreNames in the Stores. I want to return the result of this select as the second field in each row of the result set.

SELECT DISTINCT StoreName FROM Stores WHERE Stores.ChainName = ChainName

Each row of the result set returned by the stored procedure would contain:

ChainName, Array of StoreNames (or comma separated strings or whatever)

How can I code a stored procedure to do this?

View 17 Replies View Related

Compressing Multiple Rows With Null Values To One Row With Out Null Values After A Pivot Transform

Jan 25, 2008

I have a pivot transform that pivots a batch type. After the pivot, each batch type has its own row with null values for the other batch types that were pivoted. I want to group two fields and max() the remaining batch types so that the multiple rows are displayed on one row. I tried using the aggregate transform, but since the batch type field is a string, the max() function fails in the package. Is there another transform or can I use the aggragate transform another way so that the max() will work on a string?

-- Ryan

View 7 Replies View Related

NULL Values Returned When Reading Values From A Text File Using Data Reader.

Feb 23, 2007

I have a DTSX package which reads values from a fixed-length text file using a data reader and writes some of the column values from the file to an Oracle table. We have used this DTSX several times without incident but recently the process started inserting NULL values for some of the columns when there was a valid value in the source file. If we extract some of the rows from the source file into a smaller file (i.e 10 rows which incorrectly returned NULLs) and run them through the same package they write the correct values to the table, but running the complete file again results in the NULL values error. As well, if we rerun the same file multiple times the incidence of NULL values varies slightly and does not always seem to impact the same rows. I tried outputting data to a log file to see if I can determine what happens and no error messages are returned but it seems to be the case that the NULL values occur after pulling in the data via a Data Reader. Has anyone seen anything like this before or does anyone have a suggestion on how to try and get some additional debugging information around this error?

View 12 Replies View Related

Bug In Expression Evaluator Using NULLIF And RAND()

Jun 12, 2006

I think I might have found an (obscure) bug in the SQL Server expression evaluator.

I'm trying to create some dummy data for testing purposes.

I need to make an int column contain either 1, 2 or NULL. Here's my SQL:

SELECT nullif(floor(rand(checksum(newid())) * 3), 0)

from anytable

The rand(checksum(newid()) * 3 creates a random number between 0 and 2. I then use NULLIF to change zeros to nulls.

The problem is that the result data contains 1, 2, NULL *and* 0 !

It looks like the NULLIF function evaluates its first argument twice - the first time round to compare against its second argument, the second to return the result.

As the first argument is non-deterministic (because of the newid()), we get a different value on the second evaluation, hence the zeros appear in the resultset.

I can work around this problem, but is it a known bug?

Alan.

View 4 Replies View Related

Integration Services :: SSIS Reads Nvarchar Values As Null When Excel Column Includes Decimal And String Values

Dec 9, 2013

I have SQL Server 2012 SSIS. I have Excel source and OLE DB Destination.I have problem with importing CustomerSales column.CustomerSales values like 1000.00,2000.10,3000.30,NotAvailable.So I have decimal values and nvarchar mixed in on Excel column. This is requirement for solution.However SSIS reads only numeric values correctly and nvarchar values are set as Null. Why?

CREATE TABLE [dbo].[Import_CustomerSales](
 [CustomerId] [nvarchar](50) NULL,
 [CustomeName] [nvarchar](50) NULL,
 [CustomerSales] [nvarchar](50) NULL
) ON [PRIMARY]

View 5 Replies View Related

Null Values For Datetime Values Converted To '0001-01-01'

Mar 29, 2006

Hi

can somebody explain me how I can assign a NULL value to a datetime type field in the script transformation editor in a data flow task.
In the script hereunder, Row.Datum1_IsNull is true, but still Row.OutputDatum1 will be assigned a value '0001-01-01' which generates an error (not a valid datetime). All alternatives known to me (CDate("") or Convert.ToDateTime("") or Convert.ToDateTime(System.DBNull.Value)) were not successful.
Leaving out the ELSE clause generates following error: Error: Year, Month, and Day parameters describe an un-representable DateTime.




If Not Row.Datum1_IsNull Then

Row.OutputDatum1 = Row.Datum1

Else

Row.OutputDatum1 = CDate(System.Convert.DBNull)

End If



Any help welcome.

View 1 Replies View Related

Use Order By But Want NULL Values As High Values

Nov 9, 2000

Hi,

My query "select blah, blah, rank from tablewithscores" will return results that can legitimately hold nulls in the rank column. I want to order on the rank column, but those nulls should appear at the bottom of the list

e.g.

Rank Blah Blah
1 - -
2 - -
3 - -
NULL - -
NULL - -

At present the NULLs are at the top of the list, but I do not want my ranking in descending order. Any suggestions?

Thanks
Dan

View 1 Replies View Related

Return 0 Rather Than Null

Feb 2, 2007

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? 

View 9 Replies View Related

How To Return 0 Not Null

May 7, 2008

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
  

View 10 Replies View Related

Return NULL Value

Sep 6, 2005

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?

View 1 Replies View Related

Null, Not Null SQL Return

May 22, 2003

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)

View 1 Replies View Related

Sqldatasource Return Null !

Apr 12, 2008

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

View 2 Replies View Related

How To Return 0 Instead Of Null When Using A Sum Function?

May 10, 2005

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

View 5 Replies View Related

Stop Null Return

Apr 14, 2005

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

View 7 Replies View Related

Xp_cmdshell Always Return 'null'

Jun 14, 2006

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

View 4 Replies View Related

Return Null Results

Mar 24, 2004

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.

View 10 Replies View Related

Return Null When 0 Row Is Selected

Apr 1, 2008

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

View 5 Replies View Related

Return Only Columns That Are Not Null

Feb 20, 2006

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 Related

Can @@ROWCOUNT Return NULL?

Aug 29, 2005

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

View 3 Replies View Related

SQL XML :: Return NULL Value From OPENXML

Aug 24, 2015

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. 

View 5 Replies View Related







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