Querying Through NUll
Aug 10, 2006
hello ,
I have 2 tables that are linked and i am querying sencond table from 1st table here is how my query looks. It works fine when i enter the parameter value from 1st table , it gives me the assoaciated values in 2nd table, But i also want it to display without filtering the second table when the parameter value is NULL ie when there are no associated values in 1st table. I tried putting "IS NULL" but it doest work. Can any one please help me out with this
Thx
SELECT DISTINCT dbo.group_primary.site_group_primary
FROM dbo.group_primary INNER JOIN
dbo.group_conversion ON dbo.group_primary.id = dbo.group_conversion.primary_group_id INNER JOIN
dbo.group_secondary ON dbo.group_conversion.secondary_group_id = dbo.group_secondary.id
WHERE (dbo.group_secondary.site_group_secondary = @secondary) OR
(dbo.group_secondary.site_group_secondary IS NULL)
View 5 Replies
ADVERTISEMENT
Mar 5, 2007
I wonder if someone out there can help me. I am writing an ASP application to query a MSSQL database. The users will be able to use one or all of 4 columns. There may be time when the columns are empty (null). How can I write a select query to ignore null values? A rough example of what I am talking about it below.
select * from table where value1='something' value2=<null> value3='something' value4=<null>
I would like to ignore the null values so that in effect the statement would just do the following.
select * from table where value1='something' and value3='something'
I realize my syntax is wrong but I think you get the idea. Any thoughts?
View 5 Replies
View Related
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
Oct 20, 2015
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.
Is it possible through SSRS designer?
View 5 Replies
View Related
Jul 15, 2015
I have a flat file with the following columns
SampleID Rep_Number Product Protein Fat Solids
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.
View 7 Replies
View Related
May 3, 2007
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
Key cannot be null. Parameter name: key
View 1 Replies
View Related
Nov 5, 2007
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!!
View 3 Replies
View Related
Mar 26, 2007
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.
View 1 Replies
View Related
Apr 2, 2007
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:
[SqlException (0x80131904): Cannot insert the value NULL into column 'OrderID', table 'outman.outman.Contact'; column does not allow nulls. INSERT fails.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +857354
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734966
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838
System.Data.SqlClient.SqlDataReader.HasMoreRows() +150
System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout) +214
System.Data.SqlClient.SqlDataReader.Read() +9
System.Data.SqlClient.SqlCommand.CompleteExecuteScalar(SqlDataReader ds, Boolean returnSqlValue) +39
System.Data.SqlClient.SqlCommand.ExecuteScalar() +148
SubSonic.SqlDataProvider.ExecuteScalar(QueryCommand qry) +209
SubSonic.DataService.ExecuteScalar(QueryCommand cmd) +37
SubSonic.ActiveRecord`1.Save(String userName) +120
SubSonic.ActiveRecord`1.Save() +31
Checkout.btnCheckout_Click(Object sender, EventArgs e) in F:InetpubwwwrootOutman KnifeCheckout.aspx.vb:91
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42
View 8 Replies
View Related
Jun 28, 2004
I would drop and add the table but the data can't be deleted. So if anyone could help with the statement it would be greatly appreciated. Thanks
View 7 Replies
View Related
Feb 26, 2004
I'm trying to set the concat... option to OFF
all the time and in all my databases
I tried this command
USE master
EXEC sp_dboption 'DatabaseName', 'concat null yields null', 'FALSE'
but it doesn't change anything
Select NULL + 'TOTO'
----> NULL
(it should be 'TOTO')
View 2 Replies
View Related
Aug 3, 2005
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
View 5 Replies
View Related
May 13, 2015
It's giving me an error while I'm trying to change column value from not null to null..
'tblid' table
- Unable to modify table.
Cannot insert the value NULL into column 'ValidID', table 'Xe01.dbo.Tmp_tblid'; column does not allow nulls. INSERT fails.
The statement has been terminated.
View 4 Replies
View Related
May 3, 2006
Hello.
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
And it works as expected, except when I use NULL:
DECLARE @MyManager nvarchar(1000)
EXEC @MyManager = dbo.GetManager NULL
PRINT @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?
View 3 Replies
View Related
Oct 27, 2015
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"?
View 10 Replies
View Related
Sep 9, 2007
I'm a newbie to SQL and wanted to see if someone could help me write a query. I store historical options data in a SQL data base and I'm trying to create a query that will retrieve all ATM (at the money options).
For each unique stock symbol (STOCK_SYMBOL) in my database find the strike price (STRIKE) which is closest to the stock price (UND_LAST). This will be refined by the EXPIRATION and OPT_TYPE
The following query returns all options not just ATM options.
SELECT STOCK_SYMBOL, UND_LAST, STRIKE, EXPIRATION FROM DATA
WHERE EXPIRATION > '2007-02-01 00:00:00'
AND EXPIRATION < '2007-02-28 00:00:00'
AND OPT_TYPE = 'CALL'
Related DB fields.
STOCK_SYMBOL
STRIKE
UND_LAST
OPT_TYPE
EXPIRATION
Any help would be appreciated.
View 2 Replies
View Related
Nov 8, 2007
I am using MS SQL Server 2005 Import and Export Wizard (.net framework Data Provider for mySAP Business Suite. )
as a interface between SAP and MS SQL 2005.
Once the connection is created i can use basic query to fetch the data from SAP tables
ie., SELECT * FROM AFPO
But say I want to EXTRACT data just for month of July from AFPO.
AFPO does not have date field so you have to join AFPO to AUFK and AUFK to COEP to fetch the date as a criteria to restrict data for the month of July .
This can be done in ABAP by view or Interim Table, if you have Developer Key.
Can anyone please tell me if aforesaid is possible with MS SQL Server 2005 Import and Export Wizard query ?
View 3 Replies
View Related
Nov 9, 2006
Hello,
I want to find all the entries where the date field is a specific date; so I may have 5 entries with the date 1/1/2006, and I want to find all of those. However, datetime fields in SQL Server also have the time, so how do you handle that? Can you just say where requestdate = getdate() to get all of the entries that have records for today?
How does time affect this?
View 5 Replies
View Related
Sep 28, 2007
Hi,I am new to sql and was wondering if someone could help with this select statement. please don't laugh! SELECT * FROM product_detailsWHERE MemberId = @MemberIdINNER JOIN Member_Ratings ON product_details.MemberId = member_ratings.MemberIdSELECT COUNT(*) FROM member_ratings AS FinalHighRating WHERE member_ratings.MemberId = product_details.MemberId AND member_ratings.Rating = 5 Any questions? Thanks!
View 7 Replies
View Related
Oct 31, 2007
Hey,
What is the best way to query two datetime values to see if they are alike? I need to determine if an [AccountEndDate] is today (both values are datetime). Using "Like" does not work.
Thanks!
View 4 Replies
View Related
Jan 27, 2008
If you create a view such as CREATE VIEW TestView AS Select * FROM customers_table WHERE CustomerID between 213 AND 3443 Each time you want to select from the view - is the View going to perform the WHERE clause on the customers_table, or not? Or does the view already have the virtual rows?, meaning it does not have to perform a where clause (WHERE CustomerID between 213 AND 3443) on the customers_table, and just performs an inner join with the rows it already has?
View 5 Replies
View Related
Aug 10, 2005
I have a usage table Aaccessdate (datetime)useridpageidI need to write a sql that would do a report on a monthly basis. ( how many pagehits per month)All is fine - and my SQL looks likes thisselect count(pageid)from A.The twist here is , I do not want to count those records if the same user visits the page on the same day.For Eg02/03/2005 10:09:09.000 user1 page102/03/2005 15:09:09.000 user1 page102/03/2005 11:09:09.000 user2 page202/03/2005 16:09:09.000 user3 page3I want my count to give me back 3 instead of 4.Can this be done?ThanksRajini
View 4 Replies
View Related
Mar 20, 2006
I have a table containing prices. This table will be queried very often to provide quotes for clients.So to ease the burden on the server I want to cache the table and then just query the cached version.However it seems that I can only cache the table as a datatable. This means I have to query the datatable to get the prices for each quote.I'm not sure how to query a datatable. Is ther syntax similar to querying a SQL table?In fact is this best way to go about things?Any help would be appreciated.G
View 4 Replies
View Related
Apr 20, 2006
Hi guys
im struggling to see what im doing wrong with the following SQL, wondering if it has something to do with the subquery being a view...cant think why though
select value from table
where type = 'Extra Mailbox'
and active = 1
and value not in
(select login from view)
order value
The view returns a list of logins and the type field is also a list of logins. I want to filter the list of logins that do not appear in the subquery....
Any ideas?
Thanks in advance for any responses.
Ging
View 2 Replies
View Related
Oct 1, 2004
I have 2 different database's on the same server. I'm trying to create a stored proc that resides in Reporting database but queries against the Call database. 4 part naming convention gives me an error of 'invalid object name' What am I forgetting here?
View 5 Replies
View Related
Apr 30, 2008
Hello all,
I have a table (tbl_a) with 2 columns: itemNames | Date .
ItemNames can have duplicates.
I have another table (tbl_b) with date ranges in 2 columns: startDate | endDate.
what I would like to do is: query tbl_a where date between startDate and endDate of tbl_b.
does anyone have an idea?
thanks in advance.
View 2 Replies
View Related
May 23, 2008
This is my problem
Table A Columns:
OriginatingNumber,
TerminatingNumber
Both of these columns contain npanxx numbers which would be the first 6 digits of a phone number, example 217345 which is in illinois.
Table B Columns:
npanxx,
state
table B hold all npanxx numbers in the united states and what state it pertains to.
what i need to do is run a query to check and see what state the OriginatingNumber and the TerminatingNumber are in and decide if both numbers passed are in the same state or not.
so for example in Table A Row 1: OriginatingNumber is 217345 and the TerminatingNumber is 309454. I need to check both of these numbers against table B and it would return that both numbers are in IL. Basically I need to return a result of yes or no. yes they are in the same state or no they are not in the same state.
Any help would be great
Thanks for reading,
Nick
View 3 Replies
View Related
Jun 12, 2008
how to write a query to find the no of tables present in that database.
Any suggestions.
View 4 Replies
View Related
Jun 12, 2008
Hi All
Thx in advanced for any help anyone can offer me.
I have 2 tables Staff and Class which are as follows
Staff
ID (Primary Key)
FirstName
MiddleName
Surname
Etc. . .
Class
ID (Primary key)
LessonTemplateID
TeacherID
TermID
AssistantID
LessonTemplateID and TermID link to other tables which i don't need right now.
The Class.TeacherID and Class.AssistantID both link to the Staff.ID
I am trying to create a query where i can say i want to select a class.ID of lets say 12 and display both the Teachers name and the Assistants Name.
If i do just one join between Staff.ID and lets say Class.TeacherID all is fine and i can display the teacher.FirstName for a particular class.ID. However my problem is when im trying to display both teacher and assistant. Every time i try to create a query to do this the query comes back blank.
Im really stuck on this and just can't figure it out (is it even possible?).
Thx for any help Scott.
View 2 Replies
View Related
Jan 23, 2007
Hi there,
Is there a way where I can run a query or a store procedure where i can retrieve all the contraints attached to the tables in the database?
thanks
View 1 Replies
View Related
Apr 21, 2007
Hi All,
I am very new to sql and I am not really sure of what I am doing.
I have a bit of a complicated stored procedure that I am trying out. There is 3 parts to it to search for new articles,updates and discontinued articles for a particular Supplier. Right now I am just trying to get the first part working - I am trying to query another database that has 4 tables, 2 are joined ActiveArticle & ActiveArticlePrice and the other 2 are also joined UpdateArticle & UpdateArticlePrice. But all tables also hold data for ALL Suppliers, they are recognised by a GUID - SupplierGuid.
I want to compare them to each other to see if there are any new articles added to the Update tables. This is what I have so far, I was hoping someone could tell me if they see some obvious mistakes(I'm sure theres many!). For the first select, I tried adding a couple of parameters Flag and Text to use so that I can order it all once i have the 3 parts working. But I don't know the syntax is right, because then I also want to select all fields in both tables?.
CREATE PROCEDURE Portal_GetPriceListUpdates
(
@SupplierGUID uniqueidentifier
)
AS
SELECT 'A' AS FLAG, 'New Product' AS TEXT, * FROM SupplierUnits.UpdateArticle UA, SupplierUnits.UpdateArticlePrice UAP WHERE EXISTS
(SELECT * FROM UA, UAP WHERE UA.SupplierGuid=@SupplierGUID AND UA.SupplierArticleNumber NOT IN (SELECT SupplierArticleNumber FROM SupplierUnits.ActiveArticle.SupplierArticleNumber))
GO
I would appreciate any help!
Thanks,
pp
View 2 Replies
View Related
Sep 8, 2007
Guys ...
I have a SQL SERVER 2005 running on my local machine. My local machine is also configure for web applications through IIS.
There is a remote oracle db, which is maintained by another department. They gave us read only access but they discourage web access over their oracle database.
Even if you try to access it through web ... they come to know that certain logins are trying to access it through web ... How ... i dont know.
Now what i did is linked that oracle database with my SQL server 2005 and developed an ASP.NET application which is indirectly accessing oracle database though my SQL Server 2005.
Whenever a user visits my web application on the backend the authentication done on my sql server but the query runs on linked oracle server... as I open connection to my sql server 2005
do you guys think that Oracle database administrator can ever know that his oracle db is access through a web application. whereas they allow us to copy required data on our machines through SQL Server 2005?
View 4 Replies
View Related
Jul 23, 2005
Hello all,I'm trying to run a query to make a report. My database is a incidentreporting database. I'm tryng to make a monthy report for incidents.The field I need to query in the date field which is a nvarchar in theform of 01/01/04 and 01/01/2004. I ran a query that looks like this:SELECT incident, doccur, IDFROM dbo.IncidentWHERE (doccur between '01/01/2004' and '01/31/2004')I get some results that look like this:Unsecured doors01/19/0492INTOXICATION 01/17/0477Bill Door entry door 01/28/03130Hit & Run01/21/04105Customer complaint01/02/0370Customer complaint01/02/0491PRINTER MALFUNCTION01/22/04111Customer complaint01/30/042322Trash Smoldering01/15/0451LOST01/02/0380BROKEN GLASS PANEL01/13/0442B.I.A. Assist01/04/03189GAS LEAK01/06/048UNCHANGED CASH BOX01/11/0440Intoxication01/17/0469Intoxication01/02/0471Intoxication01/17/0472Employee accident01/17/0473GREASE FIRE01/18/0474Verbal Dispute01/17/0475PANHANDLING01/17/0476Near Miss/Water backup01/18/0478Unsecured Arcade Door01/19/0493Intoxication01/18/0479Intoxication01/02/0481SUSPECT/WANTED01/18/0482Intoxication01/18/0483Property Damage01/20/0384Unsecured Bingo Snack Bar01/18/0485PANHANDLING01/18/0486Employee accident01/19/0487Unauthorize of proper exit01/19/0488Safety Hazard01/19/0489Key control violation01/02/0390Cracked keno ball01/23/04116Employee accident01/19/0494delay in drop01/27/2003128test01/01/20053763As you can see, the querey will give me the month and day I ask for,butnot the right year. Some to the data has 2 digit years and some have 4digits. How do I design the query to give me the year I ask for.Any assistance will be greatly appreciated
View 7 Replies
View Related