hi, I have a problem with a flat file and a table
I have a flat file with 5 columns and in the table I have 30 columns and they all are (not null)
I cannot leave other columns in target for which it throws mistake, have to insert something, since as I insert information in the demas columns???
Since I can solve this problem???
helps please
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.
Appreciate your help
Ziggy
ColumnA has existing data in it some of the data are Nulls
what would be the best way to have new as well as existing
data change to 0, using a constraint?
If I had a basic table with 3 VARCHAR fields; let's say A, B, C
How could I write a query that returns the count of the number of NULL columns for every record in a table?
Ideally, it would be something like:
SELECT
CAST (A IS NULL) AS INTEGER
+ CAST (B IS NULL) AS INTEGER
+ CAST (C IS NULL) AS INTEGER
FROM MyTable
That doesn't work at all. I can't seem to do "IS NULL" in the SELECT area. Should I write a T-SQL user-defined function that takes all three columns as parameters? Would that be performance friendly for large data sets?
Content table
deleteddate revokeddate
1/1/2001 null
null 2/2/2003
4/5/2004 null
I am trying to create a stored procedure where.I need to loop through every row of the table and check if there exists a date in either of the column. If date exists in either of the column for every row, I need to update some other stuff.For the above table, it meets my requirement, so I need to update. However if the table is as below, it doesn't meet the requirement, so I don't need to update
deleteddate revokeddate
1/1/2001 null
null null
4/5/2004 null
I started off as below, But I am getting incorrect values.
Declare @deleterevoke bit
Declare @DelRevId int
Declare @DelRevnumrows int
Declare @tempDeletedDate datetime
Declare @tempRevokedDate datetime
[code]....
johnny writes "I am trying to return only the columns from multiple tables that are NOT NULL for a specific ID. Once I have the tables JOINED is there a way to only get those columns that are populated. Thanks."
View 2 Replies View RelatedThis procedure doesnt increment the "Views" column like it should when the value is NULL (not zero)
CREATE procedure [dbo].[sp_theDb_image_addView]
@imageId int
AS
SELECT [views],
CASE WHEN [views] is null then 1
END
FROM theDb_image WHERE imageId=@imageI
UPDATE theDb_image SET [views] = [views] + 1 WHERE imageId=@imageI
The CASE statement apparently isn't working.... any advice?
Thanks.
Using SQL2000. I want to return the # of columns with non-nullvalues. Here's my query so far:selectcase when Dx1 is not null then 0 else 1 end +case when Dx2 is not null then 0 else 1 end +case when Dx3 is not null then 0 else 1 end +case when Dx4 is not null then 0 else 1 end as DxCountfrom tblClerkshipDataCleanwhere PalmName = @PalmNameThere are 7 rows for the particular PalmName I'm using. The queryreturns a result of 7. However, there are 25 values in Dx1 thru Dx4so the query should be returning 25.What am I doing wrong here? Thanks in advance.
View 4 Replies View RelatedDoes anyone have a suggestion for how to efficiently determine what columns in a table are NULL for all rows? - Shean
View 5 Replies View RelatedHi,
This question comes without the wish for any philosofical debates concerning NULL
Normally SQL Server 2005 uses 13 bytes to store a NUMERIC(22,7) value. Does it use the full 13 bytes when the column contains NULL as well? And what about DATETIME and (BIG)INT? Most of the info on storage of NULL values is about varchar/char/nvarchar/varchar...
Thnx, Jeroen.
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
Ive created a Data Access Layer (xsd file) and some of the tables have DateTime columns. I need to allow NULL values to be saved, but when I try to do that I get an error
In my code (c#) ive used Nullable DateTime, but when I try to assign a nullable Datetime to a DateTime column, I get this error
cannot implicitly convert type 'System.DateTime?' to 'System.DateTime'
I understand the reasons why, so how do I save null DateTime values to the underlying database through a schema ?
In the schema file, the DateTime columns have <DBNULL> as the default value, AllowBDNull is true, but I am only allowed 'Throw exception' on the NULL value property
I could probably do this another way (by not using a schema and writing the code myself) but Id prefer to keep the strongly typed code.
I dont want to use MAx or MIn values for the default datetime values, so how can this be done ?
How to find out that there is a null value in a column rather than a valid integer, DateTime or bool value, for strings I use 'as' operator to cast the column value and it returns null when column value is null, but for value types using 'as' operator causes compile error and using simple casting causes runtime error, for example:
int count = (int)row["Count"];
and
int count = row["Count"] as int;
the first one throws an exception when Count is null and the second doesn't compile at all since 'as' applies to reference types, so what is the way other than exception handling to determine null value in a column?
Hello,
Is then any type of statement I could run to drop all the columns of a table that have a NULL value thoughout or must this be done manually?
Thanks,
Bryan
I am trying to divide these two columns...
parsename('$'+ Convert(varchar,Convert(money,Q.[Product Cost]-R.[Shared Cost]),1),2) as [Pre Cost],
parsename('$'+ Convert(varchar,Convert(money,R.[New Product Cost]-R.[Shared Cost]),1),2) as [Post Cost]
Formula:
[Post Cost] / [Pre Cost]
Expected:
If [Post Cost] is null then I would need to assign 1 and divide by [Pre Cost] and similarly if [Pre Cost] is null then I would need to assign 1 so that I can divide [Post Cost] / [Pre Cost]
Table DDL below:The tables I have contain Timesheet information. Each row in thetblTSCollected table contains an entry for an employee into thetimesheet system, specifically by scanning the barcode on their badge.A whole bunch of business logic periodically attempts to "pair" theseinto logically matched scans. For example, some employees will scan inand out of a single place of work. For these there will be a rowwritten to the tblTSRuleApplied table which contains, inter alia andsome redundant data, the fldCollectedID for the two rows. The earlierwill be put into the fldStartTimeCollectedID, and the later into thefldEndTimeCollectedID. Some employees will clock on at their base,then perform sub-duties at different locations during the day, andclock off at their home base at the end of their shift. For these, thesystem would identify the outer records as a matching pair, and thenpair up inner records by location.However, if the employee fails to enter a valid "clocking in and out"pair (for example, if they clock in at the wrong location) the systemneeds to generate a "dummy" "clocking in and out" record for thepayroll department. Ideally, this would have NULL values in thefldStartTimeCollectedID and fldEndTimeCollectedID columns. This wouldalert a user in a different part of the system, where missingtimesheets were being arbitrated, that an employee appeared to havefailed to clock in for that day. Of course, the user could seeon-screen that they had clocked in, but at an incorrect location.Unfortunately, the database designer is not here for the moment (he wasknocked off his bicycle recently), but he put a unique index on thetblTSRuleApplied table that prevents the same value being entered intothe fldStartTimeCollectedID and fldEndTimeCollectedID columns. This isgenerally A Good Thing, since we don't want the same timesheet scan toform both a "clocking on" event and a "clocking off" event.So, is there any way of retaining the requirement that thefldStartTimeCollectedID and the fldEndTimeCollectedID columns may notcontain the same value in a single row, UNLESS that value is NULL inwhich case all is hunky dory. I should add that the clients don't muchcare for Triggers (and neither do I for that matter).Many thanks if you are able to help.Edwardif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[FK_tblTSRuleApplied_tblTSCollected]') andOBJECTPROPERTY(id, N'IsForeignKey') = 1)ALTER TABLE [dbo].[tblTSRuleApplied] DROP CONSTRAINTFK_tblTSRuleApplied_tblTSCollectedGOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[FK_tblTSRuleApplied_tblTSCollected1]') andOBJECTPROPERTY(id, N'IsForeignKey') = 1)ALTER TABLE [dbo].[tblTSRuleApplied] DROP CONSTRAINTFK_tblTSRuleApplied_tblTSCollected1GOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[FK_tblTSArbAccept_tblTSRuleApplied]') andOBJECTPROPERTY(id, N'IsForeignKey') = 1)ALTER TABLE [dbo].[tblTSArbAccept] DROP CONSTRAINTFK_tblTSArbAccept_tblTSRuleAppliedGOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[FK_tblTSCollected_tblTSRuleApplied]') andOBJECTPROPERTY(id, N'IsForeignKey') = 1)ALTER TABLE [dbo].[tblTSCollected] DROP CONSTRAINTFK_tblTSCollected_tblTSRuleAppliedGOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[tblTSCollected]') and OBJECTPROPERTY(id,N'IsUserTable') = 1)drop table [dbo].[tblTSCollected]GOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[tblTSRuleApplied]') and OBJECTPROPERTY(id,N'IsUserTable') = 1)drop table [dbo].[tblTSRuleApplied]GOCREATE TABLE [dbo].[tblTSCollected] ([fldCollectedID] [int] IDENTITY (1, 1) NOT NULL ,[fldEmployeeID] [int] NULL ,[fldLocationCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[fldTimeStamp] [datetime] NULL ,[fldRuleAppliedID] [int] NULL ,[fldBarCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[fldProcessed] [smallint] NOT NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[tblTSRuleApplied] ([fldEmpRuleID] [int] NOT NULL ,[fldRuleAppliedID] [int] IDENTITY (1, 1) NOT NULL ,[fldStartTime] [datetime] NULL ,[fldEndTime] [datetime] NULL ,[fldStartTimeCollectedID] [int] NULL ,[fldEndTimeCollectedID] [int] NULL ,[fldStartArbStatus] [smallint] NULL ,[fldEndArbStatus] [smallint] NULL ,[fldDurationArbStatus] [smallint] NULL ,[fldPrimary] [smallint] NOT NULL ,[fldDateEntered] [datetime] NULL ,[fldEnteredBy] [int] NULL) ON [PRIMARY]GOALTER TABLE [dbo].[tblTSCollected] WITH NOCHECK ADDCONSTRAINT [DF_tblTSCollected_fldProcessed] DEFAULT (0) FOR[fldProcessed],CONSTRAINT [PK_tblTimesheetCollected] PRIMARY KEY CLUSTERED([fldCollectedID]) WITH FILLFACTOR = 90 ON [PRIMARY]GOALTER TABLE [dbo].[tblTSRuleApplied] WITH NOCHECK ADDCONSTRAINT [DF_tblTSRuleApplied_fldPrimary] DEFAULT (1) FOR[fldPrimary],CONSTRAINT [PK_tblTSRuleApplied] PRIMARY KEY CLUSTERED([fldRuleAppliedID]) WITH FILLFACTOR = 90 ON [PRIMARY] ,CONSTRAINT [IX_tblTSRuleApplied_1] UNIQUE NONCLUSTERED([fldStartTimeCollectedID],[fldEndTimeCollectedID]) WITH FILLFACTOR = 90 ON [PRIMARY]GOALTER TABLE [dbo].[tblTSCollected] ADDCONSTRAINT [FK_tblTSCollected_tblEmployee1] FOREIGN KEY([fldEmployeeID]) REFERENCES [dbo].[tblEmployee] ([fldEmployeeID]),CONSTRAINT [FK_tblTSCollected_tblLocation] FOREIGN KEY([fldLocationCode]) REFERENCES [dbo].[tblLocation] ([fldLocationCode]),CONSTRAINT [FK_tblTSCollected_tblTSRuleApplied] FOREIGN KEY([fldRuleAppliedID]) REFERENCES [dbo].[tblTSRuleApplied] ([fldRuleAppliedID])GOALTER TABLE [dbo].[tblTSRuleApplied] ADDCONSTRAINT [FK_tblTSRuleApplied_tblTSCollected] FOREIGN KEY([fldStartTimeCollectedID]) REFERENCES [dbo].[tblTSCollected] ([fldCollectedID]),CONSTRAINT [FK_tblTSRuleApplied_tblTSCollected1] FOREIGN KEY([fldEndTimeCollectedID]) REFERENCES [dbo].[tblTSCollected] ([fldCollectedID]),CONSTRAINT [FK_tblTSRuleApplied_tblTSDurationStatus] FOREIGN KEY([fldDurationArbStatus]) REFERENCES [dbo].[tblTSDurationStatus] ([fldStatus]),CONSTRAINT [FK_tblTSRuleApplied_tblTSEmpRules] FOREIGN KEY([fldEmpRuleID]) REFERENCES [dbo].[tblTSEmpRules] ([fldEmpRuleID]),CONSTRAINT [FK_tblTSRuleApplied_tblTSTimeStatus] FOREIGN KEY([fldStartArbStatus]) REFERENCES [dbo].[tblTSTimeStatus] ([fldStatus]),CONSTRAINT [FK_tblTSRuleApplied_tblTSTimeStatus1] FOREIGN KEY([fldEndArbStatus]) REFERENCES [dbo].[tblTSTimeStatus] ([fldStatus])GO
View 7 Replies View RelatedHi,
I would like to ask how can I get count of null values in each column together?
I know I can use COUNT(*) - COUNT(<col>), but I need to explicitly name every column in the query.... is it possible without it?
Hello guys I have a very simple query that is to be used to verify what users have access to what servers. Here it is:
/* SQL Server & Windows NT login name, security access */
USE master
go
SELECT name, loginname, sysadmin, securityadmin, serveradmin, setupadmin, @@SERVERNAME
FROM syslogins
WHERE status != 0
AND ((sysadmin = 1) OR (securityadmin =1) OR (serveradmin =1) OR (setupadmin = 1))
I would like to know how to stop the columns that have a null or 0 value from being returned to me. I don't want to see a bunch of 0s since almost no users do have these privaleges, I'd rather have that be hidden. Do I need to create a view for this? Thank You!
Hi All.
I'm having some issues with what seems to be a simple update statement but is giving me grief when one or both of the columns I'm comparing are null. My statement (simplified) is as follows:-
UPDATE
TAB_A
SET
TAB_A.TRADCODE = TAB_B.TRADCODE
FROM
TADS_STAGE.DBO.UNCLBRAMDEPT TAB_B
JOIN
TADS.DBO.UNCLBRAMDEPT TAB_A
ON
TAB_B.BRANCODE = TAB_A.BRANCODE
AND
TAB_B.MERDCODE = TAB_A.MERDCODE
AND
(
TAB_B.TRADCODE <> TAB_A.TRADCODE
)
If either of the TRADCODE fields (or both) are null then the comparison fails to return the row to update. I've tried setting the ANSI_NULLS setting to off, this has no effect, presumably because it will only work when comparing a column to a variable or evaluating if the column is null itself.
I've considered using ISNULL, but if one of the columns happens to contain the value that I specify as the replacement value then the comparison will result true and not include the row.
I'd be grateful for any pointers!
Thanks in advance
In a query in SQL server 2005 I have a column LastName + ', ' + FirstName.All works fine except when a LastName or FirstName = NULL then the result is NULL.How do I get around this?ThanksA Newbie to SQL.
View 1 Replies View RelatedHi,
I need help to build a query that shows me how many columns inside a range on columns are null.
Example: quantity1;quantity2;quantity3;quantity4;quantity5; quantity6;quantity7;
Which columns are null?
Thanks in advance
Hi, I need to build a query that just includes certain columns if they haven't NULL row values.For example:SELECT NAME_COLUMN IF not NULL, SELECT GENDER_COLUMN if not NULL, otherwise won't select these 2 columns i used as example.Any help, will be greatly appreciated,Best regards,Teixeira
View 3 Replies View RelatedIf i have a table with Col1,Col2,Col4, and Col5, how can I create and add a Col3 with null values? The format would be varchar.
View 13 Replies View RelatedI have a table with 4 column in below
Total amount = 1000
salemancode1 = space
salemancode2 = Staff-99
salemancode3 = space
salemancode4 = staff-88
How I can write a one query statement to do this, we expect to count how many salemancode is not space and count the number of salesman to over the total amount.
total amount / (no_of_saleman) as commission
the result is 1000/ 2 the commission is $500.
Christine the Pharmacist writes "Please pardon as I am not horribly advanced with SQL...
Using SQL Server 2000, Windows 2000, Service pack 4
I'm trying to figure out marketshares for "preferred" products. I have a table put together by hospital and month of medication use with the totals of each drug dispensed in columns as well.
(example table below)
Hospital Month Drug A Drug B Drug C Drug D
A 7 5 10 58 73
B 7 NULL 26 98 43
etc.
** Drug C and Drug D are the preferred drugs
I'm getting correct preferred % in all cases except when a value is NULL (result is NULL). I'm using an aggregate (sum), which should ignore NULLs, but it ignores NULLs within a column, and not ignoring across columns. I've tried setting concat_null_yields_null off and I'm still getting NULL (since I'm using the plus sign).
This is my admittedly confused select statement, suspect this is where my problem lies (problems with saying "sum", but using "+" as well?):
select a.Hospital,a.month,convert(float,sum(c.Drug_C+d.Drug_D))/convert(float,sum(a.Drug_A+b.Drug_B+
c.Drug_C+d.Drug_D)) as preferred_share
Thanks for any advice!"
Hi,I have some tables where I import data in, lots of field have gotten aNULL value which the application can not handle.Now can I replace each NULL value with '' in a columns with:update <tableset [<column>] = '' where [<column>] IS NULLBut because there are lots of columns this is pretty much work, alsothere are multiple tables.Is there an easy way to replace all NULL values in all columns in atable?Thanks in AdvanceBob
View 2 Replies View RelatedHello everyone,
I have a little problem here. I need to select data from multiple columns and multiple tables, some of those columns can have NULL value. I have tried few things, and havent find the right way so far. Problem that i am incountering is that when i run SELECT statment as it is right now, i dont get back results which match search parameters but have NULL value in one or more column in select statment. How can i fix this problem?
SELECT Person2role.person2roleID,Person.firstname, Person.lastname, Person.sex
FROM Person LEFT OUTER JOIN
Person2Role ON Person.personID = Person2Role.personID
WHERE (Person.firstname LIKE '%' + ISNULL(@firstname + '%', ''))
AND (Person.lastname LIKE '%' + ISNULL(@lastname + '%', ''))
AND (Person.sex = ISNULL(@sex, Person.sex))
This is a part of SELECT statment..just so you can get the idea of what i am doing..i have few more parameters
I am working with SQL Server 2005 Express Version
Thank you,
Alex
I have a table called Appointment, for storing (you guessed it) appointments at a medical practice. If an appointment is cancelled, I want to collect a cancellation reason, so let's say that I create a second table called Cancellation which has a foreign key relating to the Appointment table's primary key, AppointmentID, and another column, Reason. Now, in order to indicate that an appointment was cancelled, I could include a Cancelled column in the Appointment table with a bit datatype, or instead I could infer that an appointment must be cancelled if it has a corresponding record in the Cancellation table.
It may be that it'd be better to store the cancellation reason in the Appointment table - But regardless, let's say I stick with the two-table solution described above, and I subsequently want to write a query to list all appointments which have been cancelled. If I had the Cancelled column in the Appointment table, I could simply query for all records in that table where that column's value was FALSE. If I went the other way and DIDN'T have a Cancelled column, I could instead write a query joining the Appointment and Cancellation tables to return all records in Appointment with a corresponding record in Cancellation.
That latter method, whilst slightly more complicated because it involves joining two tables, seems to me to be the most normalised. Instead of storing the fact that an appointment is cancelled in two different tables, that fact is only stored in the Cancellation table. Would there be a performance hit in using the two-table, 'inferred cancellation' method rather than just having a bit column in the Appointment table? Would that performance hit be enough to persuade you to use a Cancellation column in the Appointment table instead? And what about if I were to apply that method to other things associated with each appointment, e.g. Is it completed? Is it chargeable to the client or an insurance company? Is the client and in-patient or out-patient?
I am looking for the best way to check to see if any columns are still NULL in a record. I have a form that gets filled out by users and the values entered into TableA. There are 6 columns in the table, 5 are responses and column6 indicates if the record is complete. So I want a way to see if all of the first 5 columns are NOT NULL and, if so, mark column6 with a 1.
I am thinking this would be a good thing for a trigger on INSERT or UPDATE to check to see if the first 5 columns are filled in and then mark the record as complete.
We are writing a C application that is using ODBC to insert recordsinto a database. We have a NOT NULL column that can legitimately havean empty value, i.e. we know the value and it is empty (i.e. a zerolength string).We are using SQLBindParameter() to bind a variable to theparameterized insert statement <<in the form: INSERT INTO table VALUES(?, ?, ?)>>. We are using SQLExecDirect() to process the SQL.We are running into the problem where ODBC is converts the empty (zerolength) string into a NULL value and this errors due to the fact thatthe column is defined as NOT NULL.We do not want to redefine the column as NULL, becasue myunderstanding of the correct usage of a NULL column is to indicatethat a value is unknown or meaningless... in our case we know thevalue (it is empty) and an empty value has meaning within ourapplication.I'm sure that this issue has been seen and address thousands(millions?) of times... any guidance would be appreciated.
View 2 Replies View Related
I have a timestamp column and a username column with default values of getdate and system user in my table and they are defined as not null.
even though i donot use these columns in scd type 2 in wizard
if the columns are not null the scd deosnot work and If the columns are defined null they work
Can anyone please explain or help me with what might be the problem associated with this
We have two tables:
Table1
Servers|Databases|Users|Names
Server1|Database1|User1|Name1
Server2|Database2|User2|Name2
Server3|Database3|User3|Name3
Server4|Database4|User4|Name4
Server5|Database5|User5|Name5
Table2
Servers|Databases|Users|Names
NULL |Database1|NULL |Name1
NULL |NULL |User1|Name2
NULL |NULL |NULL |Name3
Server5|Database5|NULL |NULL
I need to check Table1 by Table2 only on NOT NULL cells and if all of them in the row match do not return that row as the result. In this case it will be:
Results:
Servers|Databases |Users |Names
Server2|Database2|User2|Name2
Server4|Database4|User4|Name4
I used query like this:
SELECT a.Server, a.Databases, a.Users, a.Names FROM Table1
EXCEPT
SELECT ISNULL(b.Server,c.Server), ISNULL(b.Databases,c.Databases), ISNULL(b.Users,c.Users), ISNULL(b.Names,c.Names) FROM Table2 AS a, Table1 AS c
But for many rows (like 100 000) it takes ages to get results, any better way to work on this?
Hi all,
I am facing a problem on validating the data from a flat file while inserting the data into the destination table of sql server 2005 database. In my package, i have to validate the input data whether the values are coming as null or not, before inserting into the destination database. The flat file may not contain data for all NOT NULL columns. I have to find out that row(s) and reject the record. If the rows are coming as Null for the Not Null columns, the OLEDB Destination throws OLEDB exception for the constraint.
To resolve this, i have an script component in data flow, to check whether the input data is coming as null. I have added the output column of boolean type to the script component, it will be assigned to TRUE when there is null for the Not Null column in the script.
And in the follwing conditional split, i am checking the flag for TRUE to reject the record.
Is there any other way to handle this validation?
Regards
Madhav