SQL 2012 :: Join Removing NULLS That Need To Be Displayed?
Apr 22, 2014
I have a table named item that looks like this:
PartNumberDescriptionI.DateCreated
1A1XXX ALPHA1 1/1/2014
2A2XXX ALPHA2 1/5/2014
3A3XXX ALPHA3 1/12/2014
1B1XXX BRAVO1 1/22/2014
2B2XXX BRAVO2 1/28/2014
3B3XXX BRAVO3 2/2/2014
And a second table named orders that looks like this:
OrderNoPartNumberQtyO.DateCreated
1CHXX1 1A1XXX 2 1/8/2014
1CHXX2 2A2XXX 3 1/8/2014
1CHXX3 2A2XXX 1 1/20/2014
2CHXX1 1B1XXX 4 2/10/2014
2CHXX2 2B2XXX 2 2/10/2014
2CHXX3 2B2XXX 1 2/22/2014
I need to pull all records from the Item table and then I need to populate the most recent OrderNo and O.DateCreated. I got this far but if there is a part in the item table that does not have an order against it, I do not get a value and my goal is to see any parts that have not been ordered in the last year. Something like this:
SELECT I.PartNumber, I.Description, I.DateCreated
FROM item I
CROSS APPLY (SELECT TOP 1 O.OrderNo, O.DateCreated
FROM Orders O
WHERE O.PartNumber = I.PartNumber ORDER BY O.DateCreated DESC) O
PartNumberOrderNoO.DateCreated
1A1XXX 1CHXX1 1/8/2014
2A2XXX 1CHXX3 1/20/2014
3A3XXX NULL NULL
1B1XXX 2CHXX1 2/10/2014
2B2XXX 2CHXX3 2/22/2014
3B3XXX NULL NULL
View 4 Replies
ADVERTISEMENT
Aug 4, 2006
Hi,
What sql would I use to remove all the nulls in a table?
thanks!
View 11 Replies
View Related
Oct 27, 2015
I have two tables one list changes of hospital ward and one lists changes of consultant doctor. These can change independently ie a ward change can occur without a consultant change and vice versa. I want to summarise these changes to give the status at each date_serial value.
create table #temp_ward_stay(date_serial bigint,ward_id varchar(10))
;
insert into #temp_ward_stay
values
(201501021200,'W1'),(201501031201,'W2'),(201501091200,'W3'),(201501161200,'W4'),(201501161201,'W5')(201501271200,'W3'),(201502101200,'W5')
;
create table #temp_consultant_episode(
date_serial bigint,
consultant_id varchar(10))
;
insert into #temp_consultant_episode values
(201501021200,'C1'),(201501031200,'C2'),(201501031201,'C3'),(201501091201,'C4'),
(201501121200,'C3'),(201501301200,'C6'),(201502111200,'C6'),(201502111201,'C7')
;
If I use:
select date_serial,consultant_id,null as ward_id from #temp_consultant_episode
union
select date_serial,null as consultant_id,ward_id from #temp_ward_stay
I get:
date_serial ward_id consultant_id
-------------------- ---------- -------------
201501021200 NULL C1
201501021200 W1 NULL
201501031200 NULL C2
201501031201 NULL C3
201501031201 W2 NULL
201501091200 W3 NULL
201501091201 NULL C4
201501121200 NULL C3
201501161200 W4 NULL
[code].....
whereas what I actually want is:
date_serialward_idconsultant_id
201501021200W1C1
201501031200W1C2
201501031201W2C3
201501091200W3C3
201501091201W3C4
201501121200W3C3
201501161200W4C3
201501161201W5C3
[code].....
I can see how I could remove the nulls where the date_serial values are the same:
select ce.date_serial,ward_id,consultant_id from #temp_consultant_episode ce
join #temp_ward_stay ws
on ce.date_serial=ws.date_serial
but I can't see how to move forward from here.
View 9 Replies
View Related
May 21, 2007
I am trying to utilize the left join to display all the links for a particular record by ID. When I test my code in query analyzer I get repeats of records and links for one ID. I dont want repeats, I want to see only the links associated with each ID. This is my query code below, I have tested it with several variations but still getting the same results.
Code Snippet
SELECT T_ProgramGuests.ProgramID, T_ProgramGuests.GuestName, T_ProgramGuests.GuestDescription, T_ProgramLinks.URL, T_ProgramLinks.Description FROM T_ProgramGuests LEFT OUTER JOIN T_ProgramLinks ON T_ProgramGuests.ProgramID = T_ProgramLinks.ProgramID WHERE (T_ProgramGuests.ProgramID = 9734)
I've been reading up on this in the forum but everyone does this differently which makes it more confusing.
So if 9734 looked like this:
John Smith - making of widgets
'John's Widgets Website' (this would be a link)
But if John Smith had three actual links why am I only seeing the first one?
What is the easiest way for me to get my data to display correctly and not have repeats?
View 8 Replies
View Related
Jun 17, 2008
hello friend
i have table1 and table2 and table3..
i have category and product number and name of colums for per table
i want to do this.. select category from table1 where category= textbox1.text and category from table2 where category= textbox1.text and category from table 3 where category= textbox1.text
and if there are category for per category colums of per table, gridview should displayed table1 and table2 and table2 in category and name and product number of colums:)
if there is category of coulums for table1, gridview should displayed just table1 in category and name and product number of colums:)
i want to 3 bind for gridview1 :) it should be without join because i dont need join.. columns name is same for tables but value of category is different but it can be same :)
cheers
View 6 Replies
View Related
Jul 23, 2015
All of a sudden my user variables are not displayed.
I know that they exist because the package run is successful.
For better, quicker answers on T-SQL questions, click on the following...
[URL]
For better answers on performance questions, click on the following...
[URL]
View 4 Replies
View Related
Dec 11, 1998
I am getting VERY poor performance on a join even though I have indexes. Instead of using the indexes, SQL Server does a table scan on one of the tables in the join. After I force the index in the query, I can see why. The index doesn't give any better results than a table scan. I suspect it may be because the table has a huge number of rows with null values for one column of a two-column key used in the join. However, the same tables use the indexes and give good performance when queried independently. It seems the problem is specific to join processing. I notice that the index I created appears to have pages referencing the null rows. I know that Oracle disregards nulls when creating indexes, but does SQL Server not do so? Is there any way to create a better index? And if I can't solve the join problem, any suggestions about workarounds?
The details:
-I have TABLE A with columns X, Y, and Z. In TABLE A there are 380,000 rows, and X and Y yield a unique key. The index is on X and Y. This table has 240,000 rows where X is null but there is a non-null value for Y.
-I have TABLE B (60,000 rows) with columns X,Y, and Q, where X,Y, and Q yield the unique key and have an index.
-Here is my query:
SELECT A.Z
FROM A, B
WHERE B.Q=1
AND B.X = A.X AND B.Y = A.Y
View 1 Replies
View Related
Jan 31, 2015
I have a small doubt regarding cross join and NULL values,
Table A has four rows (1,2,3,NULL)..Table B has 4 rows(1,2,3,NULL)..if i cross join table A and B..
How many rows will be there in result
View 5 Replies
View Related
Mar 29, 2006
I want to join 2 tables by a unique ID field, but the ID field also hasmultiple NULLS which I do not want to ignore and I fear they will causeduplication.Using TableA and TableB below i will demonstrate the problem.TableATableA.ID Field1 Field21 Paul 1Null John 12 John 1TableBTableB.ID Field3 Field41 25 1Null 32 1Null 23 12 26 1The Table I want isTableA.ID TableB.ID Field1 Field2 Field3 Field41 1 Paul 1 25 12 2 John 1 26 1Null Null John 1 Null NullNull Null Null Null 32 1Null Null Null Null 26 1I think a select distcinct statement with a full outer join may do whatI want, but I'm not certain so want to check.Regards,Ciarán
View 10 Replies
View Related
Nov 16, 2006
I have a query which is returning a different result set when it is run against identical tables in 2 different environments.
The query is like:
Select
F.LicenseeID, IsSpecialLicensee
from FactTable F
left join View_SpecialLicensee SL on F.LicenseeID = SL.LicenseeID
The Create Statement for the view is like
Create View [dbo].[View_SpecialLicensee]
as
Select LicenseeID, LicenseeName, IsSpecialLicensee = 1
from DimensionLicensee
where LicenseeName like '%ibm%'
or LicenseeName like '%cisco%'
or LicenseeName like '%hp%'
In my test environment, I get the query result I expected:
LicenseeID, IsSpecialLicensee
1 , 1 - (where LicenseeName = 'IBM')
2, null - (where LicenseeName = 'Juniper')
3, 1 - (where LicenseeName = 'Cisco')
4, null - (where LicenseeName = 'Microsoft')
5, null - (where LicenseeName = 'Oracle')
6, null - (where LicenseeName = 'Apple')
In my production environment, I get the following query result:
1 , 1 - (where LicenseeName = 'IBM')
2, 1 - (where LicenseeName = 'Juniper')
3, 1 - (where LicenseeName = 'Cisco')
4, 1 - (where LicenseeName = 'Microsoft')
5, 1 - (where LicenseeName = 'Oracle')
6, 1 - (where LicenseeName = 'Apple')
Ideas as to what changed gratefully received.
FYI the production environment which returned the 2nd dataset is SQL2000, I have got the result I expected in both SQL2000 and SQL2005 development environments.
View 6 Replies
View Related
Apr 11, 2008
When I use the object model to try to remove a path leading to a Merge Join component, I get an error. Anybody have any workarounds? (I'm sure there is a workaround because I'm able to delete that same path via BIDS without errors.)
Though I'm trying to remove a path from a package created with BIDS, the following is an easy way to reproduce the error. The last line in the following code is the one that generates the error.
Code Snippet
Application a = new Application();
Package p = new Package();
TaskHost th = p.Executables.Add("DTS.Pipeline") as TaskHost;
th.Name = "DataFlow";
MainPipe pipe = th.InnerObject as MainPipe;
//add a connection mgr
ConnectionManager adventureWorks = p.Connections.Add("OLEDB");
adventureWorks.Name = "OLEDBConnection";
adventureWorks.ConnectionString = "Provider=SQLNCLI;Integrated Security=SSPI;Initial Catalog=AdventureWorks;Data Source=(local)";
//add the OLEDB source component
IDTSComponentMetaData90 oledbSource = pipe.ComponentMetaDataCollection.New();
oledbSource.ComponentClassID = "DTSAdapter.OLEDBSource";
CManagedComponentWrapper instance = oledbSource.Instantiate();
instance.ProvideComponentProperties();
oledbSource.Name = "OLEDBSource";
oledbSource.RuntimeConnectionCollection[0].ConnectionManagerID
= p.Connections["OLEDBConnection"].ID;
oledbSource.RuntimeConnectionCollection[0].ConnectionManager
= DtsConvert.ToConnectionManager90(p.Connections["OLEDBConnection"]);
instance.SetComponentProperty("OpenRowset", "[Production].[Product]");
instance.SetComponentProperty("AccessMode", 0);
instance.AcquireConnections(null);
instance.ReinitializeMetaData();
instance.ReleaseConnections();
//add the merge join component
IDTSComponentMetaData90 mergeJoin = pipe.ComponentMetaDataCollection.New();
mergeJoin.ComponentClassID = "DTSTransform.MergeJoin";
mergeJoin.Name = "merge";
instance = mergeJoin.Instantiate();
instance.ProvideComponentProperties();
oledbSource.OutputCollection[0].IsSorted = true;
oledbSource.OutputCollection[0].OutputColumnCollection[0].SortKeyPosition = 1;
pipe.PathCollection.New().AttachPathAndPropagateNotifications(oledbSource.OutputCollection[0], mergeJoin.InputCollection[0]);
a.SaveToXml(@"c:projects est ssisSimplePipeline.dtsx", p, null);
//if I skipped the next 3 lines, I wouldn't get the error
p = a.LoadPackage(@"c:projects est ssisSimplePipeline.dtsx", null);
th = p.Executables[0] as TaskHost;
pipe = th.InnerObject as MainPipe;
int iPathToRemoveID = pipe.PathCollection[0].ID;
pipe.PathCollection.RemoveObjectByID(iPathToRemoveID); //Exception from HRESULT: 0xC0047072 (which is ErrorCode -1073450894 which is DTS_E_OBJECTIDNOTFOUND)
Some things I noticed:
1. Note the comment that says "if I skipped the next 3 lines, I wouldn't get the error". Apparently the error is somehow related to saving the package to disk then reloading it from disk. (By the way, I have to load it from disk because the package has been previously generated via BIDS, not by code. The above sample is just a simple way to repro the problem.)
2. I don't get the error with some other components (like union all). I haven't tried all the different stock components yet, but it appears to be something specific to merge join (or at least components like merge join).
I'm on SP2 proper... not any cumulative updates. (And I would like a workaround for SP2 proper.)
Thanks for any suggestions.
View 1 Replies
View Related
Jul 4, 2014
I'm trying to find the previous / next values of a field when it is having null values. I'm not able to use lag / lead as they don't ignore nulls.
declare @t table (v1 int , v int, v2 datetime)
insert into @t values(4,10, '2014-01-01 10:00:00')
insert into @t values(5, 20,'2014-01-01 10:05:00')
insert into @t values(6, null, '2014-01-01 10:10:00')
insert into @t values(7, null,'2014-01-01 10:15:00')
[code]....
The next values are having nulls. i'm trying to get next value of 'v' ignoring nulls.
View 6 Replies
View Related
Sep 23, 2014
Getting below error while ran EMPTYFILE.
DBCC SHRINKFILE: Page 10:6521 could not be moved because it is a work table page.
Msg 2555, Level 16, State 1, Line 1
Cannot move all contents of file "tempdata" to other places to complete the emptyfile operation.
View 3 Replies
View Related
Sep 5, 2014
I thought it's easy but somehow got lost, what is the good way to insert that Comma intelligently, depending if any of var value is NULL.
-- DECLARE @var1 varchar(10) = 'alpha1', @var2 varchar(10) = 'bravo2', @var3 varchar(10) = 'charlie3',@var4 varchar(10) = 'delta4'
DECLARE @var1 varchar(10) = 'alpha1', @var2 varchar(10) = NULL, @var3 varchar(10) = 'charlie3',@var4 varchar(10) = 'delta4'
SELECT ss= (CONCAT(@var1, + iif(COALESCE(@var1,@var2,@var3,@var4) IS NULL,'',', '),
@var2, + iif(COALESCE (@var2,@var3,@var4) IS NULL,'',', '),
@var3, + iif(COALESCE( @var3,@var4) IS NULL,'',', '),
@var4) )
View 3 Replies
View Related
Aug 5, 2015
I have a query that I'm filtering using Customer ID, CustomerID = '12345', even though I need the query to filter that data, I don't need to see that column in my results. I tried removing it from my Select Distinct group but I'm guessing it needs to be there or the filter won't work(like I said, very green). Is there something that I can add to hide this column?
SELECT DISTINCT
RG.ResNumber,
ResWithSupp.SupplierID,
ResWithSupp.ServiceType,
RG.CustomerID,
[code]......
View 4 Replies
View Related
Jun 13, 2014
I have a query which contains 12 left outer join. I remove some of the joins that don't have parameters. The result is coming same but usually when we remove joining it should take less exec time but for me it is taking more time. What could be the reason?
I am getting same result set.
View 6 Replies
View Related
Jul 6, 2015
I wanted to remove an extra transaction log file that was no longer required, and ran the following against the database...
DBCC Shrinkfile (DB_Name_log2, Emptyfile);
go
alter database [Db_Name]
remove file DB_Name_log2;
go
I got a successful removal message. But if I go into the properties of the database, and click on files, it still shows up. Why is this and how can I get rid of it?
It shows up in sys.master_files as offline.
View 4 Replies
View Related
Aug 21, 2014
When I create a table in sql server database name appears in prefix of table.
My database name: Digitall
My table name: Digitall.News
How can I remove Digitall prefix from my tables?
View 1 Replies
View Related
Dec 19, 2014
I need to build a query, or use a function to removing alfabetic Character from a string.
My string is
WI14000323-0003
The value i want to keep is : 14000323-0003.
I try using this SELECT:
SELECT STUFF(Upper(z.lote), PATINDEX('%[A-Z]%', Upper(z.lote)), 2, '') from mytable
but in this case i have only 2 letters WI, but in the future i dont know if is only 2 letters, 1 letter or more, then is not what i pretend.
View 2 Replies
View Related
Mar 11, 2015
I have found a bunch of duplicate records in our housing database that ideally I need to delete.There are two tables that I need to remove data from ih_cml_log_entry and ih_cml_log_notes. There is no unique identifier between the tables for a log entry. So I have had to join on the person_ref, log_seq and the date/time of entry.How do I go about deleting the data - I've used the script below to identify what I need to delete -
SELECT *
FROM
(
select cml.person_ref, cml.open_date + open_time as 'datetime',cml.open_user,cml.log_type
,ROW_NUMBER() OVER (PARTITION BY cml.person_ref, cml.open_date + cml.open_time,cml.open_user,cml.log_type ORDER BY (SELECT 0)) AS RowNo
,n.note
FROM ih_cml_log_entry cml
[code]...
View 2 Replies
View Related
Apr 14, 2015
I usually do this through Access so I'm not too familiar with the string functions in SQL. My question is, how do you remove characters from the middle of a string?
Ex:
String value is 10 characters long.
The string value is X000001250.
The end result should look like, X1250.
I've tried mixing/matching multiple string functions with no success. The only solution I have come up with removes ALL of the zeros, including the tailing zero. The goal is to only remove the consecutive zeroes in the middle of the string.
View 9 Replies
View Related
Sep 17, 2015
I have a varchar field which contains some Greek characters (α, β, γ, etc...) among the regular Latin characters. I need to replace these characters with a word (alpha, beta, gamma etc...). When I try to do this, I find that it is also replacing some of the Latin characters.
DECLARE @Letters TABLE (Letter NVARCHAR(10))
INSERT INTO @Letters VALUES ('a'), ('A'), ('b'), ('B'), ('α')
SELECTLetter, REPLACE(Letter,'α','alpha')
FROM@Letters
In this case, the "α" is being replaced, but so are "a" and "A".
I have tried changing the datatype from varchar to nvarchar and also changing the collation.
View 4 Replies
View Related
Oct 18, 2015
How to write a function to remove duplicated characters only if they come in sequence.
Examples
darrk should return dark
eeagle should return eagle
redd should return red
corner should corner as it is as the r's are not in sequence.
View 9 Replies
View Related
Apr 17, 2015
I have a sql server 2012 server and I need to prevent the users from creating new schemas by mistake. Is there any way to revoke that permission alone but still letting the user to create their own objects in dbo (yes I know that shouldn't be in dbo but that is another issue).
View 2 Replies
View Related
Oct 16, 2015
I need removing cursor in my table valued function with alternate code.
ALTER FUNCTION [dbo].[eufn_e5_eSM_SE_GetCurrentContentForContainer]
(
@containerSqlId SMALLINT,
@containerIncId INT
)
RETURNS @Results TABLE
[Code] ....
View 2 Replies
View Related
Aug 15, 2014
We have a multi-site AG and are demoting one of the remote sites out of the AG. In doing so, we've discovered that no logic exists to remove an IP address from the listener.
It seems that the ALTER AVAILABILITY GROUP MODIFY LISTENER logic lacks functionality other than ADD. [URL] ....
I'm afraid to just remove the IP address from the cluster object as that IP is also stored in the HADR systables.
select * from sys.availability_group_listener_ip_addresses
select * from sys.availability_group_listeners
View 1 Replies
View Related
May 16, 2006
When i do a select on my emplee table for rows with null idCompany i dont get any records
I then try to modify the table to not allow a null idCompany and i get this error message:
'Employee (aMgmt)' table
- Unable to modify table.
Cannot insert the value NULL into column 'idCompany', table 'D2.aMgmt.Tmp_Employee'; column does not allow nulls. INSERT fails.
The statement has been terminated.
This sux
View 4 Replies
View Related
Feb 11, 2014
I setup SQL Server 2012 on Windows Server 2012 with the service accounts in the local Administrator group, but now that I'd like to remove the accounts from this group I'm finding they don't have the appropriate access to the network storage. notes on setting the per-service SID's for SQL (SQL Engine, Analysis Services, Reporting Services, and Agent Service) so they can read the Data, Log, and TempDB mount points?
View 2 Replies
View Related
Feb 17, 2008
is there an elegant way to use one equals sign in a where clause that returns true when both arguments are null, and returns true when neither is null but both are equal and returns false when only one is null?
View 4 Replies
View Related
May 15, 2008
I have two SSIS packages that import from the same flat file into the same SQL 2005 table. I have one flat file connection (to a comma delimited file) and one OLE DB connection (to a SQL 2005 Database). Both packages use these same two Connection Managers. The SQL table allows NULL values for all fields. The flat file has "empty values" (i.e., ,"", ) for certain columns.
The first package uses the Data Flow Task with the "Keep nulls" property of the OLE DB Destination Editor unchecked. The columns in the source and destination are identically named thus the mapping is automatically assigned and is mapped based on ordinal position (which is equivalent to the mapping using Bulk Insert). When this task is executed no null values are inserted into the SQL table for the "empty values" from the flat file. Empty string values are inserted instead of NULL.
The second package uses the Bulk Insert Task with the "KeepNulls" property for the task (shown in the Properties pane when the task in selected in the Control Flow window) set to "False". When the task is executed NULL values are inserted into the SQL table for the "empty values" from the flat file.
So using the Data Flow Task " " (i.e., blank) is inserted. Using the Bulk Insert Task NULL is inserted (i.e., nothing is inserted, the field is skipped, the value for the record is omitted).
I want to have the exact same behavior on my data in the Bulk Insert Task as I do with the Data Flow Task.
Using the Bulk Insert Task, what must I do to have the Empty String values inserted into the SQL table where there is an "empty value" in the flat file? Why & how does this occur automatically in the Data Flow Task?
From a SQL Profile Trace comparison of the two methods I do not see where the syntax of the insert command nor the statements for the preceeding captured steps has dictated this change in the behavior of the inserted "" value for the recordset. Please help me understand what is going on here and how to accomplish this using the Bulk Insert Task.
View 2 Replies
View Related
Jan 13, 2014
These two T-SQL statements return the same results.
If Microsoft deemed it necessary to add the EXCEPT command, then what are its advantages over an INNER JOIN
-- LIST ONLY PRODUCTS THAT ARE ON A WORK ORDER
USE AdventureWorks2008R2;
GO
SELECT ProductID
FROM Production.Product
INTERSECT
SELECT ProductID
FROM Production.WorkOrder ;
USE AdventureWorks2008R2;
GO
SELECT DISTINCT Production.WorkOrder.ProductID
FROM Production.Product
INNER JOIN Production.WorkOrder ON Production.WorkOrder.ProductID = Production.Product.ProductID
View 9 Replies
View Related
Feb 7, 2014
A customer has messed up while moving their databases. After working for a week they found that data is missing in the database.I have two backups, one from the old server and one from the new server today, they have been working in the new one for a week.
I need to compare these two databases and then update the new database with all data that is in the old one but not in the new database. Join the data in the two databases so to say. Both databases are from the same application so they use the same users, schema and so on.
View 9 Replies
View Related
Apr 8, 2014
[URL]
I had a problem before of not been able to find the rows with 0 values. I've now managed this although it's brought up duplicate rows due to the discounts been different on the same Mfr_part_number. I tried using the max function on the isnull (Exhibit_Discount.Discount, 0.00) AS Discount instead but to no success. i think i maybe something to do with PK keys not been used in the set-up of the database.
Use Sales_Builder
Go
SELECT DISTINCT
GBPriceList.[Mfr_Part_Num],
[Code]....
View 7 Replies
View Related
