SQL Server 2012 :: Null Values With Joins?
Aug 14, 2015
CREATE TABLE A (ID INT IDENTITY (1,1))
CREATE TABLE B (ID INT, EMPID VARCHAR(10))
INSERT INTO A DEFAULT VALUES
GO 5
INSERT INTO B VALUES (1,'E23')
INSERT INTO B VALUES (1,'E24')
INSERT INTO B VALUES (2,'E23')
from the above code i would like to get output like
ID EMPID
1 23
2 23
3 null
4 null
5 null
1 24
2 null
3 null
4 null
5 null
I'm trying like
select a.id, b.empid from (
select * from a cross join (
select distinct empid from b) b
) a
left outer join b on a.id = b.id but i get repetitive rows.
View 7 Replies
ADVERTISEMENT
Aug 2, 2006
I would like to populate a grid with data from 2 different tables.
Table1: [PK]id(int), name(nvarchar), areaID(int)
Table2: [PK][FK]areaID(int), areaDescription(nvarchar)
My cerrent query is:
SELECT Table1.id, Table1.name, Table2.areaDescription FROM Table1 INNER JOIN Table2 ON Table1.areaID = Table2.areaID
However, sometimes the areaID in Table1 will only be populated at a later stage and therefore will be NULL in Table1. Table2 is used as a lookup table when inserting into Table1. This query therefore ommits any records in Table1 which do not have an areaID. I would like to view ALL records(ones without an areaID as well) as they would be populated in the grid and selected to be updated on web forms because they are incomplete and then subsequently assigned an areaID.
Any help with this query would be much appreciated...
View 2 Replies
View Related
Sep 25, 2015
I have tried all possible combinations of changing this. But was not able to make the results tally.I am giving you a part of the query, there are others queries involving 4 tables which are based on this same temporary table query.
SELECT c.juris_id, b.jrnl_mo_yrÂ
FROM a_trueup a,
#t_mths b, r_rj c  Â
WHERE a.rlzd_mo_yr
=* b.jrnl_mo_yr
ANDÂ
a.juris_id
=* c.juris_id
[code]....
I tried using left outer join as mentioned in blogs but got a different result (14 rows).I also used set null off/on options but no luck ..
View 15 Replies
View Related
Feb 10, 2014
I have two tables, a dates table and a values table. They are joined on the date column.The date table has a range, say from today as far as 20 days from now, incrementing by 1 day each row.The values table may have a row for a day, and may not. If the day has a value I want to display that value.If the day does not have a value in the values table I want to display the last known value.
I think this can be done with windowing functions in a set based manner but have not been able to work it out. I have done it procedurally but im not happy with that at all, and really want to see if this is possible in a set based manner.Below is some simplified code to allow testing with sample data.
create table DimDate
(
DateCol date
)
create table TotalsData
(
DateCol date
[code]....
View 6 Replies
View Related
Sep 20, 2014
The following t-sql 2012 works fine in sql management studio. However when I place it in a .net 2010 web form application, I am told the sql does not work when the parameter values are null. Thus can you tell me what I can change in the sql below that will accept null as 3 possible input values?
SELECT i.[lastName]
,i.[firstName]
,i.[middleName]
,i.[suffix]
,a.[userid]
[code]...
View 1 Replies
View Related
Sep 10, 2015
Question in review today is Creating a Report showing the FName, LName of all Employees not Specified in a region; I would assume "No Value to be Null" Correct?
Or is there another way for me to do this?
This is what I have so far...What am I missing that this is not showing me results?
Select Firstname, Lastname, Region
From Employees
WHERE Region LIKE 'null%';
View 3 Replies
View Related
Feb 4, 2014
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)
[code]...
View 8 Replies
View Related
Jan 21, 2014
How do I add column names as Total and SubTotal for NULL values.
SELECT DISTINCT
--[Group]
[Month]
,[Market]
,[Environment]
,[type]
, COUNT(*)
[code]....
View 9 Replies
View Related
May 27, 2014
I am working on SSIS Packages. I have raw files in which data is in text qualifier.
When I am trying to load files into database it's throwing me error when there is no values in text qualifier.
For Example. "Others","0","0"," "," "
If I have the value above in raw files. SSIS Package throwing me error in " " this column.
Saying Data Conversion failed.
View 5 Replies
View Related
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
Nov 13, 2013
I have select query with 10 Joins in place with different i want to improve the performance of the query how do i join tables with out joins.
View 9 Replies
View Related
Jan 22, 2014
Assume i have 3 tables
Person(personname,age)
Children(childname,personname)
car(carname,personname)
A persone can have multiple cars
A person can have multiple children
Its not possible to display the results in SQL rows and columns.
If i try to it will give
PersonName Carname Childname
Sachin Audi C1
Sachin Maruti C1
Sachin Audi C2
Sachin Maruti C2
Instead of writing seperate queries the application wants to receive an xml output as follows
<person>
<pname>sachin</pname>
<car>audi</car>
<car>bmw</car>
<cname>c1</cname>
<cname>c2</cname>
<person>
How to get this output ?
View 1 Replies
View Related
Jul 23, 2014
I have the following tables in my DB
Employee table - This table has EmployeeID, Name, DOB.
EmployeeDesignation table - 1 Employee can have many designations with each having an effective date. There is no flag to indicate which among multiples is the current entry. You can only figure it out by the newest/oldest EffectiveDate. I want to get the most recent and the oldest for each employee.
EmployeeSalaryHistory table - Structure/Design is similar to EmployeeDesignation table. I want to get the starting salary and current salary for each employee.
I want my query to output me the following fields...
EmployeeID
EmployeeName
EmployeeDOB
EmployeeStartingDesignation
EmployeeCurrentDesignation
EmployeeStartingSalary
EmployeeCurrentSalary
Here is a piece of code to generate sample data sets
DECLARE @Employee TABLE (EmployeeID INT, EmployeeName VARCHAR (100), EmployeeDOB DATE)
INSERT @Employee VALUES (101, 'James Bond', '07/07/1945'), (102, 'Tanned Tarzan', '12/13/1955'), (103, 'Dracula Transylvanian', '10/22/1967')
DECLARE @EmployeeDesignation TABLE (EmployeeID INT, Designation VARCHAR (100), EffectiveDate DATE)
INSERT @EmployeeDesignation VALUES (101, 'Bond Intern', '01/01/1970'), (101, 'Bond Trainee', '01/01/1975'), (101, 'Bond...James Bond', '01/01/1985')
[Code] ....
Currently, I have a query to get this done which looks as below. Since I have more than 8K employees with each having multiple Designation and Salary entries, my query is taking forever.
selecte.EmployeeID, e.EmployeeName, e.EmployeeDOB,
(select top 1 Designation from @EmployeeDesignation ed where ed.EmployeeID = e.EmployeeID Order By EffectiveDate) EmployeeStartingDesignation,
(select top 1 Designation from @EmployeeDesignation ed where ed.EmployeeID = e.EmployeeID Order By EffectiveDate Desc) EmployeeCurrentDesignation,
[Code] ....
View 5 Replies
View Related
Mar 19, 2014
I have a table that lists math Calculations with "User Friendly Names" that look like the following:
([Sales Units]*[AUR])
([Comp Sales Units]*[Comp AUR])
I need to replace all the "User Friendly Names" with "System Names" in the calculations, i.e., I need "Sales Units" to be replaced with "cSalesUnits", "AUR" replaced with "cAUR", "Comp Sales Units" with "cCompSalesUnits", and "Comp AUR" with "cCompAUR". (It isn't always as easy as removing spaces and added 'c' to the beginning of the string...)
The new formulas need to look like the following:
([cSalesUnits]*[cAUR])
([cCompSalesUnits]*[cCompAUR])
I have created a CTE of all the "Look-up" values, and have tried all kinds of joins, and other functions to achieve this, but so far nothing has quite worked.
How can I accomplish this?
Here is some SQL for set up. There are over 500 formulas that need updating with over 400 different "look up" possibilities, so hard coding something isn't really an option.
DECLARE @Synonyms TABLE
(
UserFriendlyName VARCHAR(128)
, SystemNames VARCHAR(128)
)
INSERT INTO @Synonyms
( UserFriendlyName, SystemNames )
[Code] .....
View 3 Replies
View Related
Jun 22, 1999
Following SQL Statement is from BOL 7.0 and it doesn't return any data. But when I change the where clause to "WHERE advance IS NULL" it works. I am trying to do something like this in my own stored procedure. It works fine in 6.5 both ways. Is there any parameter to set? or any other problem. Thanks in advance.
SELECT title_id, type, advance
FROM pubs.dbo.titles
WHERE advance = NULL
View 1 Replies
View Related
Mar 21, 2004
Hi
I am importing an excel spreadsheet into tables in a sql server database using dts. I have one row of information filled out in the excel spreadsheet, but when the dts runs, it imports the information plus 5 extra rows filled with nulls ino the table.
Does anyone know how to fix this?
Thanks
View 2 Replies
View Related
Jul 23, 2005
HiConsider two tablesid1 code1----------- -----1 a2 b3 cid2 code2 value----------- ----- -----------1 a 02 a 13 b 1They are joined on the code field.For each code, I want the maximum corresponding value. If the valuedoesn't exist (corresponding code in second table doesn't exist), I wanta NULL field returned.The result should look like this:code2 value----- -----------a 1b 1c NULLI can't get it to include the NULL row.While there are uniqe ID's in this example, the real life example uses ahorrible four field compound key.Any help would be appreciated.Ger.The above example can be recreated by the following script.DROP table #temp1DROP table #temp2SELECT 1 AS 'id1', 'a' AS 'code1'INTO #temp1UNIONSELECT 2, 'b'UNIONSELECT 3, 'c'SELECT 1 AS 'id2', 'a' AS 'code2', 0 AS valueINTO #temp2UNIONSELECT 2, 'a', 1UNIONSELECT 3, 'b', 1SELECT code2, valueFROM #temp1 t1LEFT JOIN #temp2 t2 ON t1.code1 = t2.code2WHERE CASEWHEN t2.value IS NULL THEN 1WHEN t2.value = 0 THEN 2WHEN t2.value = 1 THEN 3END = (SELECTMAX( CASEWHEN value IS NULL THEN 1WHEN value = 0 THEN 2WHEN value = 1 THEN 3END )FROM#temp2WHEREcode2 = t2.code2)
View 5 Replies
View Related
Oct 1, 2001
Hello, i´m inserting values in a table defined as above,
/********
create table SERVICE_TYPE (
PK_TYPE numeric(8) not null IDENTITY,
TYPE nvarchar(32) not null unique,
DESCRIPTION nvarchar(256) null default(''),
USER_VISIBLE numeric(1)not null,
constraint PK_SERVICE_TYPE primary key (PK_TYPE)
)
go
**********/
these values are inserted throug ODBC with VC++,
in the case of the DESCRIPTION Column, i´m sending null strings CString(""). this value will be filled after.
What happens is that, when i query the database to show the records in this table, the database shows, for example:
PK_TYPE TYPE DESCRIPTION USER_VISIBLE
--------- ------ ------------------- -------------
1 1 (null) 1
i dont want to show the "(null)" value in the column DESCRIPTION, but a null string like ""
can anyone help me , thanks
Cristovão
View 1 Replies
View Related
Aug 17, 2005
If myDateTimeColumn contains a <NULL> value. How do you handle that when reading into a DateTime object in your code?DateTime myDate = Convert.ToDateTime(dr["myDateTimeColumn"]);Does not work, it throws: System.InvalidCastException: Object cannot be cast from DBNull to other types.
I am curious as to what others are doing to handle this?
View 6 Replies
View Related
Nov 16, 2005
Hello,I am trying to import a CSV file into my SQL Server database, this file was originally generated by another database table (on another server) with the same structure, the table contains two columns of real datatype with Allow Null Value setto true for those columns, the CSV file contains the value NULL for theses columns, I am facing a problem when importing this file. This may be because DTS tries to represent values as strings then to convert them to real datatype which results in transforming the value "NULL" to real, I receive an error message saying.Error during Transformation 'DirectCopyXform' for Row number 1. Errors encountered so far in this task: 1.
TransformCopy 'DirectCopyXform' conversion error: Conversion invalid for datatypes on column pair 8 (source column 'Col008' (DBTYPE_STR), destination column 'zip_longitude' (DBTYPE_R4)).
TransformCopy 'DirectCopyXform' conversion error: Conversion invalid for datatypes on column pair 7 (source column 'Col007' (DBTYPE_STR), destination column 'zip_latitude' (DBTYPE_R4)).How can I work around this problem? Any help would be appreciable
View 2 Replies
View Related
May 13, 2015
I am stuck on a query where I need to display all the month year values even if the corresponding count_booking values are NULL. The requirement is to display the 13 month year period from current date.
For e.g. if the date exists in the current month then starting from May 15 go all the way back to Apr 14.
My current query works in terms of displaying all the 13 months if the count_booking values exist for all the months. However, if some months are missing the values then those months don't show up.how to display all the months even if the values are NULL ? Query below:
SELECT COUNT(m.BOOKING_ID) AS count_booking, LEFT(DATENAME(MONTH, m.CREATE_DT), 3) + ' ' + RIGHT('00' + CAST(YEAR(m.CREATE_DT) AS VARCHAR), 2)
AS month_name
FROM MG_BOOKING AS m
WHERE (m.CREATE_DT >= DATEADD(m, - 13, GETDATE()))
[code]...
View 8 Replies
View Related
Jun 11, 2015
Everytime I try to open up SSMS, I get this error:
"Values cannot be null. Parameter Name: ViewInfo"
When I press ok to login, It doesn't show me the database and system dbs aren't expandable.
View 4 Replies
View Related
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
Feb 5, 2008
Hi all,
I am using a SQL statement with a JOINs to display a summary list of Work Orders with some information from the related Purchase Order. I don't require that a Work Order be attached to a Purchase Order, so the JOIN field, POLineItemID, may not be present. My current problem is that my summary page does not show Work Orders that have a NULL value for POLineItemID -- ie, the JOINing field becomes required.
How can I continue to use this JOIN structure but still return rows that don't have this JOIN field?
Thanks in advance for your help. Here is my SQL statement for this...
SELECT tblWorkOrder.WorkOrderNumber, tblWorkOrder.ActualCompletionDate, tblWorkOrder.EstimatedCompletionDate, tblWorkOrder.AssignedTo, tblWorkOrder.DelegatedTo, tblWorkOrder.Due, tblPurchaseOrders.PONumber, tblWorkOrder.POLineItemID, tblPOLineItems.POLineItemID AS Expr1, tblPOLineItems.PurchaseOrderID, tblPurchaseOrders.PurchaseOrderID AS Expr2, tblProducts.ProductID, tblProducts.ProductCategory, tblWorkOrder.ProductID AS Expr3, tblWorkOrder.WorkOrderStatusFROM (((tblWorkOrder INNER JOIN tblPOLineItems ON tblWorkOrder.POLineItemID = tblPOLineItems.POLineItemID) INNER JOIN tblPurchaseOrders ON tblPOLineItems.PurchaseOrderID = tblPurchaseOrders.PurchaseOrderID) INNER JOIN tblProducts ON tblWorkOrder.ProductID = tblProducts.ProductID)ORDER BY tblWorkOrder.Due
View 5 Replies
View Related
Jun 2, 2014
Why we the Unique Constraint doesn't allow the multiple null values in Sql Server?
View 2 Replies
View Related
Mar 9, 2007
I can't figure this one out...
I have an Excel spreadsheet (Excel 2003 format) with a column of values. The column is formated to General (although I've tried it formated to text). The values are alphanumeric. However, there are a few values that are completely numeric.
Example:
_ITEMNUMBER_
1-528
K214-5
184PR
45678
As can be seen, the last value is completely numeric. I am importing into a table with one column that is formated as nvarchar(50).
I use dtswizard.exe and do a simple import into the table and the alphanumeric values import just fine but anytime a value is completely numeric it has a NULL value in the table instead of the value that should be there.
I've tried changing the format of the column to nchar, char, etc, etc. Always comes in NULL.
Any ideas?
View 7 Replies
View Related
Sep 17, 2015
I have three variables
DECLARE @QuantityID uniqueidentifier,
@LengthID uniqueidentifier,
@CostID uniqueidentifier
They are sent to the sproc as null. Since they could be null I need to exclude them from posting to a temp table
Example
DECLARE @QuantityID uniqueidentifier,
@LengthID uniqueidentifier,
@CostID uniqueidentifier
SET @CostID = NEWID()
SELECT @QuantityID as ID UNION ALL
SELECT @LengthID UNION ALL
SELECT @CostID
Two values are null. I want those excluded from this table
Here is the example of what I am trying to do:
DECLARE @QuantityID uniqueidentifier,
@LengthID uniqueidentifier,
@CostID uniqueidentifier
DECLARE @Temp as Table (id uniqueidentifier NOT NULL Primary key)
SET @CostID = NEWID()
INSERT INTO @Temp
SELECT @QuantityID as ID UNION ALL
SELECT @LengthID UNION ALL
SELECT @CostID
How do I insert into @Temp only non null values?
View 5 Replies
View Related
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
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
Aug 16, 2007
Hai Friends,
Actually i want to import excel sheet in to sql server tables. I am writing the following query SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:Book1.xls','SELECT * FROM [Sheet1$]') and able to retireve the data but the problem is the data present in the excel sheet is
Username
password
hello
12345
12345
hello
hai-123
haihai
haihello
hello-123
Once i run this query i am getting it as
Username Password hello NULLNULL hellohai-123 haihaihaihello hello-123can anybody please help me out in this case and how to solve this problem and if you have any piece of code please pass it on to me and i am in very urgent need waiting for your replies thanks in advance.
View 2 Replies
View Related
May 7, 2014
If I have a table
CREATE TABLE [dbo].[logg](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[Details] [varchar](MAX) NULL)
insert logg (Details) values('')
insert logg (Details) values(null)
Will both statements above access only a single page (as it fits into one page) or does the VARCHAR(MAX) always put its data on a separate page. If so, is the null insert treated differently from the '' insert?
View 2 Replies
View Related
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
May 1, 2014
I have the following view.
The column prod_Master.M2_Pct is defined as float.Yet for some of the rows, the value in [% of Total Target] comes up as null even though there is a number in prod.Measure column. There is valid non-null data in prod_Master.M2_Pct.
I tried LTRIM(RTRIM(prod.Measure)), but no change.
Use ProdDB
SELECT TOP (100) PERCENT dbo.prod.ProdNo AS [Prod No], dbo.prod.ProdName AS [Prod Name],
CASE WHEN dbo.prod.Measure = 'P1' THEN dbo.prod_Master.P1_Pct
WHEN dbo.prod.Measure = 'P2' THEN dbo.prod_Master.P2_Pct
WHEN dbo.prod.Measure = 'P3' THEN dbo.prod_Master.P3_Pct
WHEN dbo.prod.Measure = 'P4' THEN dbo.prod_Master.P4_Pct
[code]....
View 3 Replies
View Related