Hi All to Transact-SQL Community,
I had a problem with NULL selecting, actually i had two tables like,
Transaction Table
ID Image Link INTID(ForeignKey)
--------------------------------------------------------------------
1 test test 2
2 test1 test1 2
3 test2 test2 NULL
4 test3 test3 NULL
Master Table
INTID Path Link
-------------------------------------------------
1 a.gif test
2 b.gif test
When i am selecting from transaction table, if column INTID has a integer value the it should do innerjoin with Mastertable
on TransactionTable.INTID = MasterTable.INTID, and fetch rows TransactionTable and MasterTable, then result should be like this,
1 test test
2 test1 test1
2 b.gif test
and if NULL present then it should fetch only rows from transaction table. Can anyone help on how to do this.
i need to perform some searching on 10 columns. Since it will return >1 records/lines, i need to filter, if @param1 match with col1, then abandon those Null value in column1 set, and find match @param2 to col1. @param1='SS', @param2='' (if '' i set to %) til 10
problem is when i want to search only if colX is 'something', it returns null value,.. my where clause
WHERE (d.RecvUDF1 LIKE @UDF1 OR d.RecvUDF1 IS NULL) AND(d.RecvUDF2 LIKE @UDF2 OR d.RecvUDF2 IS NULL) AND(d.RecvUDF3 LIKE @UDF3 OR d.RecvUDF3 IS NULL) AND(d.RecvUDF4 LIKE @UDF4 OR d.RecvUDF4 IS NULL) AND(d.RecvUDF5 LIKE @UDF5 OR d.RecvUDF5 IS NULL ) AND(d.RecvUDF6 LIKE @UDF6 OR d.RecvUDF6 IS NULL) AND(d.RecvUDF7 LIKE @UDF7 OR d.RecvUDF7 IS NULL ) AND(d.RecvUDF8 LIKE @UDF8 OR d.RecvUDF8 IS NULL) AND(d.RecvUDF9 LIKE @UDF9 OR d.RecvUDF9 IS NULL) AND(d.RecvUDF10 LIKE @UDF10 OR d.RecvUDF10 IS NULL )
-i wrote the OR NULL is because i need those nulls value when im not searching for that column.. -i replace '' to '%' because i need to list any other remaining unmatched columns when i found the matching column.
maybe it's quite ridiculous to understand my Q ...coz i couldnt find any other better way to explain thanks in advance
I have a stored procedure that allows users to select addresses based on partially supplied information from the user. My procedure seems to work fine in all but a few cases. If a user decides to select all the rows for a particular country the procedure below does not return any rows even if the rows exist. I tracked this down to the fact that for Non US countries I set both the StateCode and Country Code to nulls. . Below is a partial version of my code. Can anyone show me how I can do a "Like" Search even if some of the fields in the row contain null values? Thanks 1 CREATE PROCEDURE dbo.Addresses_Like( @SendTo VarChar(50) = Null 2 , @AddressLine1 VarChar(50) = Null 3 , @AddressLine2 VarChar(50) = Null 4 , @City VarChar(50) = Null 5 , @StateCode VarChar(2) = Null 6 , @ZipCode VarChar(10) = Null 7 , @CountryCode VarChar(2) = Null) 8 9 10 Declare @SearchSendTo VarChar(50) 11 Declare @SearchAddressLine1 VarChar(50) 12 Declare @SearchAddressLine2 VarChar(50) 13 Declare @SearchCity VarChar(50) 14 Declare @SearchStateCode VarChar(2) 15 Declare @SearchZipCode VarChar(10) 16 Declare @SearchCountryCode VarChar(2) 17 18 If (@SendTo Is Null) 19 Set @SearchSendTo = "" 20 Else 21 Set @SearchSendTo = @SendTo 22 23 If (@AddressLine1 Is Null) 24 Set @SearchAddressLine1 = "" 25 Else 26 Set @SearchAddressLine1 = @AddressLine1 27 28 If (@AddressLine2 Is Null) 29 Set @SearchAddressLine2 = "" 30 Else 31 Set @SearchAddressLine2 = @AddressLine2 32 33 If (@City Is Null) 34 Set @SearchCity = "" 35 Else 36 Set @SearchCity = @City 37 38 If (@StateCode Is Null) 39 Set @SearchStateCode = "" 40 Else 41 Set @SearchStateCode = @StateCode 42 43 If (@ZipCode Is Null) 44 Set @SearchZipCode = "" 45 Else 46 Set @SearchZipCode = @ZipCode 47 48 If (@CountryCode Is Null) 49 Set @SearchCountryCode = "" 50 Else 51 Set @SearchCountryCode = @CountryCode 52 53 54 Select AddressID 55 , SendTo 56 , AddressLine1 57 , AddressLine2 58 , City 59 , StateCode 60 , ZipCode 61 , CountryCode 62 , RowVersion 63 , LastChangedDateTime 64 , OperID 65 From Addresses 66 Where SendTo Like RTrim(LTrim(@SearchSendTo)) + "%" 67 And AddressLine1 Like RTrim(LTrim(@SearchAddressLine1)) + "%" 68 And AddressLine2 Like RTrim(LTrim(@SearchAddressLine2)) + "%" 69 And City Like RTrim(LTrim(@SearchCity)) + "%" 70 And StateCode Like RTrim(LTrim(@SearchStateCode)) + "%" 71 And ZipCode Like RTrim(LTrim(@SearchZipCode)) + "%" 72 And CountryCode Like RTrim(LTrim(@SearchCountryCode)) + "%" 73 Order By CountryCode, City, AddressLine1, AddressLine2, SendTo
I want to run a query that selects rows from the table where a datetime column has null values; select * from Orders where IsNull(dClosedDate,'Null') = 'Null' However i get this error: Conversion failed when converting datetime from character string. Any help appreciated
I'm trying to construct code that will return the last non-NULL value in a column containing daily records.
For E.G. I want to know what the LAST value of Description field when it is not NULL, AND the Date is within the range t=1 to t=5 => i.e. "Dog" in the below example:
Hi, i have something like this: SELECT a.boardname, a.description, a.threadcount, a.answercount, a.lastthreadid, b.username, b.subject, b.created FROM forum_board AS a, forum_topics AS b WHERE (a.forumid = @ID) AND (b.id = a.lastthreadid) The problem here is that lastthreadid does not always have a post linked to it, so sometimes its null. I want it to still return all the boards and then just returnnull values or "" in b.username, b.subject, b.createdThis returns nothing. How can i force it to select these values?
I've got a big problem that I'm trying to figure out: I have an address table out-of-which I am trying to select mailing addresses for companies UNLESS a mailing address doesn't exist; then I want to select the physical addresses for that company. If I get multiple mailing or physical addresses returned I only want the most recently edited out of those.
I don't need this for an individual ID select, I need it applied to every record from the table.
My address table has some columns that look like: [AddressID] [int] [LocationID] [int]
AddressID is a primary-key non-null column to the address table and the LocationID is a foreign key value from a seperate Companies table. So there will be multiple addresses to one LocationID, but each address will have it's own AddressID.
How can I do this efficiently with perfomance in mind???
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)
Hi, I have one table (PermProportionalCosting) that contains employeecode, costcentre and proportionpercentage. An employee may have two or more records in this table, indicating that their salary has been split across multiple costcentres.
Another table (Employee) contains a default costcentre for each employee.
Another table (TransActualHistoricalMaster) contains the hours paid to each employee for each pay period.
My query so far selects all employees and the total hours worked from the TransMaster table, and then selects the costcentre and proportionpercentage from the PermProportionalCosting table. Multiple lines are created for all employees in the PermProportionalCosting table, with each line indicating the proportionpercentage and associated costcentre.
This is my query so far:
SELECT TransActualHistoricalMaster.EmployeeCode, Employee.PreferredName, Employee.LastName, CostCentre.Description, PermanentProportionalCosting.CostCentreCode,PermanentProportionalCosting.PercentageSplit, TransActualHistoricalMaster.OrdHours FROM TransActualHistoricalMaster INNER JOIN Employee ON TransActualHistoricalMaster.EmployeeCode = Employee.EmployeeCode INNER JOIN CostCentre ON Employee.CostCentreCode = CostCentre.CostCentreCode FULL OUTER JOIN PermanentProportionalCosting ON TransActualHistoricalMaster.EmployeeCode = PermanentProportionalCosting.EmployeeCode GROUP BY TransActualHistoricalMaster.EmployeeCode, Employee.PreferredName, Employee.LastName, CostCentre.Description, PermanentProportionalCosting.CostCentreCode, PermanentProportionalCosting.PercentageSplit, TransActualHistoricalMaster.OrdHours
This next bit is what I am unsure of.
I would like the CostCentre field from the Employee table to go into the PermanentProportionalCosting.CostCentreCode column where the employee doesn't have a record in the PermanentProportionalCosting table. In the above query, there is a null value for each employee that doesn't have a record in the PermanentProportionalCosting table, so I would like my query to replace the null value with the costcentre from the Employee table.
In my report i have CNAME parameter , which allows null value. I checked Allow null value check box in report parameter properties.
when i preview the report , it displays checked NULL check box beside CNAME parameter . I want to give some meaningful name(i.e.ALLCustomers) to this checkbox instead of NULL.
In the flat file SampleID and Product are populated in the first row only, rest of the rows only have values for Rep_Number, Protein, Fat, Solids.
SampleID and Product are blank for the rest of the rows. So my task is to fill those blank rows with the first row that has the sampleID and Product and load into the table.
I have a report that is run on a monthly basis with a default date of null. The stored procedure determines the month-end date that it should use should it be sent a null date.
The report works fine when I tell it to create a history entry; however, when I try to add a subscription it doesn't appear to like the null parameter value. Since I have told the report to have a default value of null it doesn't allow me to enter a value on the subscription page.
Now, I suppose I could remove the parameter altogether from the stored proc, but then the users would never be able to run the report for a previous time period. Can someone explain to me why default values aren't allowed to be used on subscriptions when they seem to work fine for ad hoc and scheduled reports? This is really quite frustrating as most of my reports require a date value and default to null so that the user doesn't have to enter them for the latest data.
An internal error occurred on the report server. See the error log for more details. (rsInternalError) Get Online Help
I run a stored procedure for which I have a return variable. The stored procedure returns the ID of a row in a table if it exists:
m_sqlCmd.ExecuteScalar();
The m_sqlCmd has been fed an SQLParameter with direction set to output. When the stored proc returns, I want to test it. Now when there IS a row it returns the ID ok. When the row doesn't exist, in my watch I have:
m_sqlParam.SqlValue with value {Null}
I can't seem to work out how to test this value out. I've tried several things but none seem to work.
This line compiles ok, but the following runs into the IF statement as if the SqlValue is null??
if (m_sqlParam.SqlValue != null).... {
// I'm here!! I thought the watch says this is null??? }
Sorry if this is obvious, but I can't work this one out!!
Looks like there was a fix and then I read this fix is not a fix. Does anyone know how this can be rectified? Does it mean that only Windows authentiation is the only way it works. The Software is over 2 years old, there are no excuses.
I am getting this error: "Cannot insert the value NULL into column 'OrderID', table 'outman.outman.Contact'; column does not allow nulls. INSERT fails." -- But my value is not null. I did a response.write on it and it show the value. Of course, it would be nice if I could do a breakpoint but that doesn't seem to be working. I'll attach a couple of images below of my code, the error, and the breakpoint error.
Server Error in '/' Application.
Cannot insert the value NULL into column 'OrderID', table 'outman.outman.Contact'; column does not allow nulls. INSERT fails. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Cannot insert the value NULL into column 'OrderID', table 'outman.outman.Contact'; column does not allow nulls. INSERT fails.Source Error:
Line 89: sContact.Phone = sPhone.Text.Trim Line 90: sContact.Email = sEmail.Text.Trim Line 91: sContact.Save() Line 92: Line 93: Dim bContact As Contact = New Contact()Source File: F:InetpubwwwrootOutman KnifeCheckout.aspx.vb Line: 91 Stack Trace:
hi,my structure table in database:Amount float(53) not null default 0when i try to run his script:alter table ABC alter column Amount float(53) nullit can only set the Amount to allow null, but can't set the defaultvalue to empty.anyone know how to set the field to allow null and default set toempty, no value.thanks
I've built a sample CLR function with the following declaration....
CREATE FUNCTION GetManager(@DeptCode nvarchar(3)) RETURNS nvarchar(1000) WITH RETURNS NULL ON NULL INPUT AS EXTERNAL NAME Assembly1.[ClassLibrary1.MyVBClass].MyManager
It returns the value "Unknown" as it would have for any unknown DeptCode, as-programmed.
I'm of the theory it should have returned NULL without actually firing the function? Or is this only for non-CLR items... or stored procedures, not functions?
We have a case where in we should show date based on conditions for e.g if we had a column defined as
col varchar(10) then we would show col as 'NULL' for some condition and actual value when no condition
Normaly date values are stored here e.g under col 20150901 .
Case when col>'20150901' then 'NULL' else col end as Derivedcol
Note this is an extract process and we are presenting data by pumping the data in a table .
Now there is another similar column -colz varchar(10) which stores date but doesnt have case condition so whenever date has no value its shows null which is database null.
So whats the difference between database null and string null ?
How can we show database null for the case condition instead of string "null"?
A colleague of mine has a view that returns approx 100000 rows in about 60 seconds.
He wants to use the data returned from that view in an OLE DB Source component.
When he selects the view from the drop-down list of available tables then SSIS seems to hang without any data being returned (he waited for about 15 mins).
He then changed the OLE DB Source component to use a SQL statement and the SQL statement was: SELECT * FROM <viewname>
In this instance all the data was returned in approx 60 seconds (as expected).
This makes no sense. One would think that selecting a view from the drop-down and doing a SELECT *... from that view would be exactly the same. Evidently that isn't the case.
I have two columns A (which allows nulls) and B( which does not allow nulls). How can I add the contents of columns A and B SO THAT I DO NOT GET A NULL RESULT WHEN A IS NULL.
The result of A+B concatanation will be stored in a column, C.
I've got a query on a particular table returning an odd result:
SELECT DISTINCT WorkStation FROM Invoice WHERE WorkStation Is Not Null ORDER BY WorkStation
This query returns the rows I'd expect plus a null row. This doesn't happen in databases at other sites, or in other tables at this site. The following query behaves as I'd expect returning only non-null AccountNumbers.
SELECT DISTINCT AccountNumber FROM Suppliers WHERE AccountNumber Is Not Null ORDER BY AccountNumber
I can't reproduce these results on another site on a table of the same structure, or on another table at this site.
Any suggestions as to what might be going on?
Pertinent info: --- select @@Version
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1) --- dbcc checkdb Abridged result: CHECKDB found 0 allocation errors and 0 consistency errors in database 'POS'. --- SELECT * INTO #Inv FROM Invoice
SELECT DISTINCT WorkStation FROM #Inv WHERE WorkStation Is Not Null ORDER BY WorkStation
Does not reproduce this problem (and so is a probable fix) but the questions remains, what causes this?
I have 595 default constraints in my database. I can return a list of them using the following:
select * from sys.default_constraints
Is there a way I can return a list of just the ones where NULL is still allowed? I want to update all of the columns with a default value to not allow NULLs.
I've NEVER EVER had this issue, I have always done this and it works great both locally and remotely.
I have a table, which is a "customers" table.
I have a stored procedure which takes in parameters (name, address, password etc...) and returns me back, via a parameter declared as output - the customer ID.
I've always done this, and works great.
Now, this works fine locally.
IF I try to run this/do the exact same execution of commands (create a customer) remotely (where the database is stored, so copying everything from local to "over there") it does not work.
I get a DBNULL value back from the parameter, declared as an int output.
IF I copy and paste this Stored procedure Query/command into Query Analyzer and execute it (on the remote connection) and run it, giving it the exact same values as I do, it works perfect and returns me the correct value.
I've even dropped and recreated the stored procedure but makes no difference.
Any ideas why?
here is the proc....
CREATE PROCEDURE [dbo].[DoCreateNewCustomer] ( @theFirstName nvarchar(25), @theLastName nvarchar(25), @theAddress nvarchar(50), @theCity nvarchar(15), @thePostCode nvarchar(9), @thePhoneNumber nvarchar(21), @theMobilePhoneNumber nvarchar(21), @theIPAddress nvarchar(25), @thePassword nvarchar(50), @theEmailAddress nvarchar(30), @result int output ) AS SET @result = (SELECT [ID] FROM Customers WHERE emailAddress = @theEmailAddress) IF @result IS NOT NULL SET @result = -1 ELSE BEGIN SET @result = @@IDENTITY INSERT INTO Customers (
Firstname, Lastname, Address, City, Postcode, PhoneNumber, MobileNumber, IPAddress, [Password], EmailAddress
Any ideas why it does not work when calling the stored proc from either a web app, or through QA remotely but works fine when I run that command in QA or locally?
Again, if I do:
EXEC DoCreateNewCustomer { params } - the output result it gives me is DBNULL (if on remote connection)
If I do this locally, perfect
If I copy and paste pretty much the SP into QA and execute it on the remote connection, works great, and also locally.
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?
Hello everyone,I have a table that stores data of a Person. For personal data, there are 2 different fields, for example... a personal address and a professional address, a peronal email and a professional email, a personal phone num and a professional phone num, etc...My question is, i would like to know if it's possible to make it for at least one of these fields to be NOT NULL, but not necessarily both.I'll have 2 fields: emailPersonalemailProfessional Can i make it so that, when inserting or updating data, sql checks to see if AT LEAST ONE of the emails is filled in (both can also be filled in)... I dont care which one is filled in, as long as ONE is filled in... Thanks in advance,SuperJB
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)
