T-SQL (SS2K8) :: Select One Of Three Values That Are Not NULL?
May 6, 2014
I am working on some data that is JOINing to another table. Not a big thing. In the child table, there are different values for a single ID. I want to be able to select the Max ColorID that is Not Null, for each distinct CarID. The CarID is what I am joining the other table with. I need selecting the distinct row with the Max ColorID that is not Null. All this data is made up, so nothing looks logical in the design.
DECLARE @ColorList TABLE
(
CarID float
, ColorID int
)
INSERT INTO @ColorList
SELECT 1.55948815793043E+15, 9 UNION ALL
SELECT 1.55948815793043E+15, 27 UNION ALL
I need to list all students who have both parents phone number is null. The parent relationship values 1 and 2 indicates the person is either Mom (value 2) or dad (value 1) of the student. Note: I am using student parent as an example to write my query.
I use the merge statement in a sproc to insert, update and delete records from a staging table to a production table.
In the long sql, here is a part of it,
When Matched and
((Student.SchoolID <> esis.SchoolID OR Student.GradeLevel <> esis.GradeLevel OR Student.LegalName <> esis.LegalName OR Student.WithdrawDate <> esis.WithdrawDate Student.SPEDFlag <> esis.SPEDFlag OR Student.MailingAddress <> esis.MailingAddress)
Then update
Set Student.Schoolid=esis.schoolid, .....
My question is how about if the column has null values in it.for example
if schoolID is null in production table is null, but in staging table is not null, will the <> return true.or if either side of <> has a null value, will it return true.
I don't want it to omit some records and causing the students records not get updated.If not return true, how to fix this?
I have a table with data in two columns, item and system. I am trying to accomplish is we want to compare if an item exists in 1, 2 or all systems. If it exists, show item under that system's column, and display NULL in the other columns.
I have aSQL Fiddle that will allow you to visualize the schema.
The closest I've come to solving this is a SQL pivot, however, this is dependent on me knowing the name of the items, which I won't in a real life scenario.
select [system 1], [system 2], [system 3] from ( SELECT distinct system, item FROM test where item = 'item 1' ) x pivot ( max(item)
I'd like to be able to offer the option of selecting a project by the ProjectID number, and if a projectID is not supplied then the default result set would be select * from tbl_Projects.
ALTER PROCEDURE [USP_SelectProject] -- Add the parameters for the stored procedure here @ProjectNumber as int AS BEGIN
set @bcpcommand = 'bcp "select med + replicate('' '',10-datalength(med)),ml+ replicate('' '',20-datalength(ml)),iname + replicate('' '',10-datalength(iname)) from copy_tbl" queryout "'+ @filename + '" -U -P -c'
exec master..xp_cmdshell @bcpCommand
Some of the values in the select statement are Null values and are getting skipped in the text file .My output looks like At A02 At1E AtE A03 At2E c100 c1230 I want them to allign but the third row has a null in the middle so it skips it and put the third value in the seconds place
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 use a parameter for product type in my report so that end user can select different product types from a drop down menu. Now my problem is that for some fields product type is blank(NULL). But end user can not see these NULL value when he click drop down menu. I can not use Allow Null value option in report paramter , because I have used multi value option. Is there any other way to select these NULL values?
How to include row values as columns in my select query. I have a table that stores comments for different sections in a web application. In the table below, I would like display each comment as a new column. I only want one row for each record_ID.
Existing table layout
table name - tblcomments Record_ID Comment_Section_ID Comment 1 5 Test 5 comment 1 7 Test 7 comment 2 5 New comment 2 7 Old comment 3 5 Stop 3 7 Go
Desired table layout table name - #tempComment Record_ID Comment_Section_5 Comment_Section_7 1 Test 5 comment Test 7 comment 2 New comment old comment 3 Stop Go
Once I figure out how to get the data in the layout above, I will need to join the table with my record table.
table name - tblRecord Record_ID Record_Type_ID Record_Status 1 23 Closed 2 56 Open 3 67 Open 4 09 Closed 5 43 In progress
I would like to be able to join the tables in the query below for the final output.
Select r.Record_ID, r.Record_Type_ID, r.Record_Status, c.Comment_Section_5, c.Comment_Section_7 from tblRecord r left outer join #tempComment c on r.record_ID = c.record_ID
How I can get the data in the desired #tempComment table layout mentioned above?
I have a little problem here. I need to select data from multiple columns and multiple tables, some of those columns can have NULL value. I have tried few things, and havent find the right way so far. Problem that i am incountering is that when i run SELECT statment as it is right now, i dont get back results which match search parameters but have NULL value in one or more column in select statment. How can i fix this problem?
I cannot in the life of me understand what goes wrong here... In a webapplication (C#.NET) I traced an inability to retrieve existing records to SQL Server, where I cannot do the same either. The problem is that in the parameterized query, some fields can be null, and thus when the corresponding fields in the database record are null also, they should be selected. But this won't happen for some reason.
I wrote a test SQL statement that gives me the same bogus:
DECLARE @institution int, @collection int, @serialnr int, @subnr nvarchar(50)SET @institution = 1 SET @collection = 1SET @serialnr = 240 SET @subnr = NULLSELECT ID, Institution, Collection, SerialNumber, SubNumber, AccessionYear, Sagsnummer, DanekraeNr, TaxonIdentified, Stratigraphy, TypeStatus, PlacementRoom, PlacementCabinet, PlacementDrawer, UnitNotesFROM SpecimensWHERE (Institution = @institution) AND (Collection = @collection) AND (SerialNumber = @serialnr) AND (SubNumber = @subnr) Now there is at least one row with corresponding fields values (1, 1, 240, null), but it won't be selected! What is wrong!?
I am creating a view and want to select records where the value of a Customer field (Klant Test Plan) is NULL or has exact the same value as for example customer field 2 (Klant Schedule).
I have already the code below:
SELECT DISTINCT TOP (100) PERCENT dbo.Product.ERPKey, dbo.TestPlan.CoA, dbo.TestMethod.WorkInstruction, dbo.Customer.Name AS [Klant Testplan], Customer_1.Name AS [Klant Schedule], LEFT(dbo.ShopFloor.ShopFloorNumber, 7) AS Schedule, CASE WHEN Customer_1.Name = 'ItsMe BV' THEN Customer.Name ELSE Customer_1.Name END AS Customer FROM dbo.Customer AS Customer_1 INNER JOIN
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
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?
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")
is it possible to exclude empty records from an ADSI QuerySelect?
I got a query like
Code SnippetSELECT objectGUID FROM OpenQuery(ADSI, 'SELECT objectGUID FROM ''LDAP://DC=whatever,DC=domain,DC=org'' where objectClass = ''User'' AND objectCategory = ''Person''')
and would like to have it like
Code Snippet
SELECT objectGUID FROM OpenQuery(ADSI, 'SELECT objectGUID FROM ''LDAP://DC=whatever,DC=domain,DC=org'' where objectClass = ''User'' AND objectCategory = ''Person'' AND homeDirectory IS NOT NULL')
or
Code SnippetSELECT objectGUID FROM OpenQuery(ADSI, 'SELECT objectGUID FROM ''LDAP://DC=whatever,DC=domain,DC=org'' where objectClass = ''User'' AND objectCategory = ''Person'' AND NOT(homeDirectory = NULL) ')
The problem is that I can only perform a query like
Code SnippetSELECT objectGUID FROM OpenQuery(ADSI, 'SELECT objectGUID FROM ''LDAP://DC=whatever,DC=domain,DC=org'' where objectClass = ''User'' AND objectCategory = ''Person'' AND NOT(homeDirectory = '''')')
which results in the error
"Could not fetch a row from OLE DB provider 'ADSDSOObject'."
There are many objects which do have a homedirectory, though, and therefore should be fetched.
Any comments or hints on where my error hides?
I know I could go on fetching all objects and exclude the unwanted ones later on but I'd simply run into the limitation of data records LDAP would provide me with.
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?
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)
How to count the number of values that exist in a row based on the values from an array of numbers. Basically the the array of numbers I want to look for are in row 1 of table [test 1] and I want to search for them and count the "out of" in table [test 2]. Excuse me for not using the easiest way to convey my question below. I guess in short I have 10 numbers and like to find how many of those numbers exist in each row. short example:
I have a script that I use after some amount of data massaging (not shown). I would like to be able to change the
1) denominator value (the value 8 in line 32 of my code) based on how many columns are selected by the where clause:
where left(CapNumber,charindex('_', CapNumber)-1) = 1where capNumber is a value like [1_1], [1_4], [1_6]...[1_9] capNumber can be any values from [1_1]...[14_10] depending upon the specialty value (example: Allergy) and the final number after the equal sign is a number from 1 to 14)
2) I'd like to dynamically determine the series depending upon which values correspond to the specialty and run for each where: left(CapNumber,charindex('_', CapNumber)-1) = n. n is a number between 1 and 14.
3) finally I'd like to dynamically determine the columns in line 31 (4th line from the bottom)
If I do it by hand it's 23 * 14 separate runs to get separate results for each CapNumber series within specialty. The capNumber series is like [1_1], [1_2], [1_3],[1_4], [1_5], [1_6], [1_7], [1_8],[1_9] ... [8_4],[8_7] ... [14_1], [14_2],...[14_10] etc.
Again, the series are usually discontinuous and specific to each specialty.
Here's the portion of the script (it's at the end) that I'm talking about:
--change values in square brackets below for each specialty as needed and change the denom number in the very last query.
if object_id('tempdb..#tempAllergy') is not null drop table #tempAllergy select * into #tempAllergy from dbo.#temp2 T
[Code] ....
If I were to do it manually I'd uncomment each series line in turn and comment the one I just ran.
I want to aggregate to monthly values for the reading. I want to display Reading value for Oct 2010, November 2010 likewise My question is simple and I have tried to follow the etiquette.
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL DROP TABLE #mytable
--===== Create the test table with
CREATE TABLE #mytable ( meterID INT PRIMARY KEY, Readingdate DATETIME, reading real )
--===== Setup any special required conditions especially where dates are concerned
SET DATEFORMAT DMY SELECT '4','Oct 17 2013 12:00AM','5.1709' UNION ALL SELECT '4','Oct 17 2013 12:15AM','5.5319' UNION ALL SELECT '4','Nov 17 2013 12:00AM','5.5793' UNION ALL SELECT '4','Nov 17 2013 14:00AM','5.2471' UNION ALL SELECT '5','Nov 17 2013 12:00AM','5.1177' UNION ALL SELECT '5','Nov 17 2013 14:00AM','5.5510' UNION ALL SELECT '5','Dec 17 2013 15:00AM','5.5128', UNION ALL SELECT '5','Dec 17 2013 16:00AM','5.5758' UNION ALL
Output should display as
MeterId Period Reading
4 Oct 13 10.20 4 Nov 13 10.40 5 Oct 13 10.20 5 Nov 13 10.40 4 Dec 13 11.15
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?
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?
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.
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
I have 2 identical tables one contains current settings, the other contains all historical settings.I could create a union view to display the current values from table A and all historical values from table B, butthat would also require a Variable to hold the tblid for both select statements.
Q. Can this be done with one joined or conditional select statement?
DECLARE @tblid int = 501 SELECT 1,2,3,4,'CurrentSetting' FROM TableA ta WHERE tblid = @tblid UNION SELECT 1,2,3,4,'PreviosSetting' FROM Tableb tb WHERE tblid = @tblid
I would like to force a column to be null all the time. I cannot alter the table structure or alter the code that inserts data.
create table dbo.tblCustomer ( CID int IDENTITY(1,1) not null, Fnamevarchar(20) not null, Lnamevarchar(20) not null, Extravarchar(20) null
[Code] ....
So when this is executed the field Extra is always NULL
INSERT INTO tblCustomer (Fname, Lname, Extra) VALUES ('bob', 'smith', 'ignore'), ('jane', 'doe', 'empty') update dbo.tblCustomer set Extra = 'something'
If I've understood After triggers correctly the data will be written and the trigger will fire and overwrite. To avoid 2 writes
I could create an INSTEAD OF trigger
CREATE TRIGGER TR_I_Customer ON tblCustomer INSTEAD OF INSERT AS BEGIN SET NOCOUNT ON INSERT INTO tblCustomer (Fname, Lname, Extra) SELECT Fname, Lname, NULL FROM Inserted END
This will not write the "extra" field twice. However if a new Nullable column were added; it would be v.easy to forget to update the Instead Of trigger. Everything would still work OK but I would be effectively ignoring the new column as well.
What I would like in the instead of trigger is do something like this...
UPDATE INSERTED SET Extra=NULL
Continue with insert without supplying column /value list.
What would be the best way of achieving this, trigger is the only way I could think of?
I have to populate a table of exchange rates which is easy enough however because rates are held on Fridays but I need to make calculations on weekends or holidays I need to populate the Friday rate on non weekends and holidays. I have a sample table below with null values on the weekends for the last 90 days but need a script that will show the Friday exchange rate on Saturday and Sunday
Here was my latest attempt
;with cte as ( select currxdate, [from], [TO], CurrXRate from dbo.CurrXchange ) select a.CurrXDate, a.[From],a.[To] , isnull(a.CurrXRate, b.currxrate) as 'CurrXRate'
I have an SSIS package that imports data from an Excel file, replaces any value in Excel that reads "NULL" to "", then writes the data to a couple of databases.
What I have discovered today, is I have two columns of dates, an admit date and discharge date column, and what I need to do is anywhere I have a null value in the discharge date column, I have to replace it with the value in the admit date column.
I have searched around online and tried a few things using the Replace funtion in Derived columns but no dice so far.
I have an instance where a process is trying to insert a Customer into the Customers table and CustomerLastName is a non nullable field. Customer's last name is Null. Should this be the reason this Customer is never in the end table?
