Problem-Adding Numeric Values In The Database
Dec 15, 2007
i have a table in the below shown format
Values Items.......................................................TABLE1
Customer1 20 Fuses
Customer2 30 Screws
Customer3 40 Fuses
Customer1 45 Fuses
Customer3 78 Batteries
Customer1 40 Screws
now i want to add the values of screw of customer1 and insert in a table in one field as shown below
Screws Fuses Screws .................................TABLE2
Customer1 65 (total of above) 115 40
Custoerm2 ------------------------------------
NOW ADD VALUES IN THE ABOVE WAY AND INSERT IN THE TABLE 2
Thanks
Navi
View 2 Replies
ADVERTISEMENT
Dec 11, 2007
hi folks...now m in serious trouble...i hve a very complicated problem now...
let me explain
i have a table in SQL...I have to select values from this table depending on 2 where conditions.....and add these values......and insert them in another table......
now can somebody tell me how can i add the values from the database and store them in a session and update them to another table...
if i am not clear then ask me i will explain u in more detail
Navi
View 11 Replies
View Related
Apr 15, 2008
Hi guys, I need some help again please.
On my page load, I need the following done...
I have a label control that must display a certain numeric value. This value must be the sum of an array of values that must be retreived from a table ("Details") with the column name "DealValue" but only where the column "ConsultantID" = txtConsultant.text
I understand the select statement, but I don't know how to "run" through the table to add all the values. Can someone please help me with this and how I would construct the MSSql select statement for this.
Thanks again
View 4 Replies
View Related
Jul 9, 2007
hi everyone, I have a form in in that 3columns and 8rows default.. I have to store values for 8 rows at a time in to database..I am working with vs2003, asp.net & C# behind the code.. so pls send me a code for implementing this.. asap vijai
View 2 Replies
View Related
Jun 9, 2006
Hi there,
This seems a bug to me. Or does anyone has a logical explanation that escapes me?
When in SSIS Designer Version 9.00.1399.00 I add output columns (numeric 4,0 ) to a scriptcomponent and fill them with valid numeric data in thescript I get a database error 'invalid number' when I use these columns in an OLE db Command-transformation . This errormessage disappears when I replaces those columns by a dataconversion to the datatype they originally have.
Derived Column Name Derived Column Expression
STATUS_DEF Replace 'STATUS_DEF' (DT_NUMERIC,4,0)STATUS_DEF
Maybethis info is usefull for somebody else who can't figure out wathever he's doing wrong.
Paul Baudouin
View 1 Replies
View Related
Feb 9, 2007
Hi,
Is there a way to search for numeric values in the field.
For eample my table is
create table test (sector varchar(20))
insertinto test
values ('Hybrid 3/1')
values ('ARM')
values ('20yr')
is it possible to display
3/1
NULL
20
for the above?
Thanks.
View 3 Replies
View Related
Dec 11, 2005
I have a varchar field that contains both numeric and text data. I need to pull only numeric, non-null values.
View 1 Replies
View Related
Jun 6, 2007
If I have a Select statement like this in my C# code:
Select * From foods Where foodgroup In (@foodgroup)
And I want @foodgroup to have these values ... "meat", "dairy", fruit", what is the correct way to add the parameter?
I tried
meat, dairy, fruit
'meat', 'dairy', 'fruit'
but neither worked. Is this possible?
View 2 Replies
View Related
Apr 2, 2008
Hi,
I am using SQL Server 2000. In database i am having one column named Address which contains full address of the customer. While searching i want to ignore starting numeric or alphanumeric values. Kinly guide how I can ignore numeric or alphanumeric values while searching the data.
View 8 Replies
View Related
Oct 31, 2005
Not sure how I can convert varchar to numeric values in SQL Server? Trying to do so in Design and get error:
Unable to modify table.
ODBC error: [Microsoft][ODBC Server Driver][SQL Server] Error converting data type varchar to numeric.
Any suggestions?
View 5 Replies
View Related
Jun 9, 2015
I want to convert every numeric values into textstring like abc in sql or vb.net
My encoding is as under
0=alz
1=bmw
2=cn
3=dox
4=ep
5=fqy
6=gr
7=hsv
8=it
9=kuj
How can i covert these all numeric values equally above coding in sql server or vb.net...
View 5 Replies
View Related
Feb 14, 2008
Hello,
I forget. How can PATINDEX be used to find column values where the data is all numeric? I've tried, the following:
select distinct acct from tbl_CYProcessedSales
where PatIndex('%[0-9]%',Acct) > 0
order by acct
Acct is varchar(8). What am I doing wrong?
Thank you for your help!
CSDunn
View 12 Replies
View Related
Dec 18, 2007
Is it possible to write a sql statement to skip aplpha numeric values? I got a field containing these values; 20, 70, 150, 140, 100, KORT, 90, 180, 160. And I'm trying to check if any value is bigger than 175 (@Limit), but I want to skip the value 'KORT'. So is it possible to check if a value is numeric or not? ISNULL( CONVERT(int, ProductVariant.Size), 0) > @Limit Regards, Sigurd
View 2 Replies
View Related
Feb 28, 2008
HI,
Thanks in advance for taking your time to read this post.
I am trying to write a SQL query using MS SQL 2005 that will read the value of a field and tell if it is alpha or numeric. I have tried the following but it does not work:
select field1 from table1 where left(field1,2)='[0-9]'
select field1 from table1 where isnumber(left(field1,2) tried with a =1 at the end and without and =1 at the end
the goal is to read through a field and format it so if a field looks like this 12xxx111xx I can change it to look like 12-xxx-111-xx.
Any help is greatly apprecaited
View 4 Replies
View Related
Feb 11, 2003
Hi all,
I have a field defined as varchar(8) but this field should not contain any letters, needs to be only numbers. How can I validate the data if it contains only numbers? Any ideas?
Thanks,
Jannat.
View 5 Replies
View Related
Jul 29, 2015
Why can i get the numeric values from this string?
{_cpn}=1743; {_cpnll}=4511
Result: 1743, 4511
Position and len of the value can be different...
View 6 Replies
View Related
Sep 13, 2007
Hi,
I have a little bit of a problem I cannot seem to figure it out. Is it possible to write a Select statement that contains a WHERE column_name > desired_numeric_value
The tricky part it that the column is of CHAR type and can contain numeric grades ranging from 0-100 or the letter I for Incomplete.
My SQL was working perfect when this column contained only numbers as soon as a record with I was added I get the following error:
Character to numeric conversion error
This report will be used to find students who have failing grades. Thanks for any help!
View 5 Replies
View Related
Mar 26, 2014
I am putting a SELECT statement together where I need to evaluate a results field, to determine how the color indicator will show on a SSRS report. I am running into a problem when I try to filter out any non-numeric values from a varchar field, using a nested CASE statement.
For example, this results field may contain values of '<1', '>=1', '1', '100', '500', '5000', etc. For one type of test, I need a value of 500 or less to be shown as a green indicator in a report, and any value over that would be flagged as a red. Another test might only allow a value of 10 or less before being flagged with a red.
This is why I setup a CASE statement for an IndicatorValue that will pass over to the report to determine the indicator color. Using CASE statements for this is easier to work with, and less taxing on the report server, if done in SQL Server instead of nested SSRS expressions, especially since a variety of tests have different result values that would be flagged as green or red.
I have a separate nested CASE statement that will handle any of the values that contain ">" or "<", so I am using the following to filter those out, and then convert it to an int value, to determine what the indicator value should be. Here is the line of the script that is erring out"
case when (RESULT not like '%<%') or (RESULT not like '%>%') then
CASE WHEN (CONVERT(int, RESULT) between 0 and 500) THEN '2'
ELSE '0'
The message I am getting is: Conversion failed when converting the varchar value '<1' to data type int.
I thought a "not like" statement would not include those values for converting to an int, but that does not seem to be working correctly. I did also try moving the not to show as "not RESULT like", and that did not change the message.
How I can filter out non-numeric values before converting the rest of the varchar field (RESULT) to int, so that it is only converting actual numbers?
View 6 Replies
View Related
Dec 9, 2014
We have an address table with a house_num field which is a nvarchar.
Most of house numbers are numbers like 1234, 0989
But some of them have a letter behind it like 678 B, 8909 F, even some like this 123/B
We would like to remove any non-numeric letter for this column.
Is there a way to do it?
View 4 Replies
View Related
Aug 29, 2006
It appears that the VFPOLEDB driver tries to convert numeric types in VFP to decimal types in .NET. In this case, it's a Numeric 9,3 in VFP and it's trying to put it into a decimal 8,3 in .NET.
I know that VFP has this funny quirk where if you don't use all of the decimal places after the decimal point, it allows you to use extras in front of the decimal point. In our case, the number is 104895.83 and yes, VFP is happy storing this in a Numeric 9,3 column.
When trying to read this in .NET, you get this message:
System.InvalidOperationException
The provider could not determine the decimal value. blah, blah, blah...
The question then is - is there a work-around? I can find no way to force .NET to use greater precision. About all I can come up with is to catch the exception and return a nice error message asking the user to make the column larger in VFP.
Here's the .NET code, thanks in advance
Mike
string sqlString = "SELECT * FROM " + Path.GetFileName(dbfFullPath);
OleDbConnection oleConn = new OleDbConnection("Provider=VFPOLEDB.1;Data Source=" +
folderName + ";Collating Sequence=MACHINE;");
OleDbCommand oleCmd = new OleDbCommand(sqlString, oleConn);
OleDbDataReader oleReader;
try
{
oleConn.Open();
oleReader = oleCmd.ExecuteReader();
View 3 Replies
View Related
Jun 3, 2015
I have SSAS cube with Fact that include values in kg (e.g. 25.3, 32.5, 18,3...).What kind of attribute or other solution should I create If I want to filter those kg's in browser with integer values e.g.:weight between 10 and 25
View 3 Replies
View Related
Jan 10, 2014
I need to split NUMERIC & ALPHABETICAL values from string.
for eg :-
1234heaven56-guy
output
123456 heaven-guy
View 3 Replies
View Related
May 29, 2015
I have a report which is redirecting to a subreport. The main report is having multi value parameter. I need to pass these multi values to sub report. Passing parameters from MDX report to T-sql report. So, I'm using the below exp.
=SPLIT(REPLACE(TRIM(Join(Parameters!Grade.Label,",")),", ",","),",")
The value will look like this
01-Manger
02-Senior Mange
21-Associate
25-Associate Trainee
This is working for me in all the cases except one. In all other cases, the parameter's Label and Value field has same data in the sub report. But, in a specific parameter I'm getting Label and Value data are different. I'm getting an alpha numeric string value from MDX report , but I need to pass only the numeric values to the sub report since its value field contains only numeric value. The numeric value is coming at the starting of the string data. So I have used Mid()
=SPLIT(Mid(REPLACE(TRIM(Join(Parameters!Grade.Label,",")),",
",","),1,2),",")
Result will be 01
But, mid() will give only the first value. It is working for single value. But I need to extract multiple values.
View 5 Replies
View Related
Mar 30, 2008
Hi All,
I have developed few charts - line graphs - and have checked the Numeric or time-scale values on the X-Axis. Now, when the graphs come out, they appear to have two datasets. For example, if the X axis has values from 1st march to 14th March, then there will be a line from 1st March to 14th March, but additionally there will be another one, pointing to different datapoint, and again starts from 1st March and ends at 14th March. Whereas I think it is because of the Numeric or time-scale values selection, but I am not sure. Also, is there a particular way to interpret such graphs??
Thanks a lot, I would have loved to give a picture, but I think I cannot give it.. I did not find an option..
Let me know if the question is not clear enough..
Manoj Deshpande.
View 4 Replies
View Related
Feb 15, 2008
I have a line chart which works great except when the x axis displays duplicate months. I have tried format codes of MM-YYYY and MMM and have the "numeric or time-scale values" checkbox checked. The data displays fine but the X axis in the case of the MMM format code displays as:
Sep Oct Oct Nov Nov
leaving the user to wonder where exactly does October and November start...
I've googled around but don't see off-hand what I am doing wrong. I am display 3 simultaneous lines if that matters...
Thanks!
Craig
View 4 Replies
View Related
May 8, 2009
I'm using SSIS 2005 Enterprise edition, I'm creating a package that reads an excel (xls) file using the "excel source" component, and it dumps the data into an OLEDB destination (a sql server). When I drag the excel source component and create the excel connection to my file the component automatically reads the columns and their datatypes.
The problem is that I have a column which has numeric data and the package uploads as NULL every number that starts with a zero. (note: in excel this column is formatted as "text", despite it has only numbers, because it's the only way excel maintains the left sided zeros).
So I checked the data types by right clicking the excel source component -> show advanced editor and my surprise is that this column's data type is detected as double-precision float, and it doesn't let me change it. URL... but it only works when the first row of data has a number beginning with zero on this column. How to get the data imported correctly?
View 15 Replies
View Related
Apr 5, 2007
I have 21 Boolean fields that I would like to calculate a percentage from. If the boolean value is true I would like to be able to return a percentage of Outcomes available from a possible of 21. So If 7 of my boolean fileds are true then i would like to be able to calculate that 33% of Outcomes are present.
Does anyone have anything that will get me started?
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Client]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Client]
GO
CREATE TABLE [dbo].[Client] (
[RecordID] [int] IDENTITY (1, 1) NOT NULL ,
[FacilityNumber] [int] NOT NULL ,
[AdmitNumber] [int] NOT NULL ,
[LastName] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FirstName] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MiddleName] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Suffix] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BirthDate] [datetime] NULL ,
[AdmitDate] [datetime] NULL ,
[AdmitWeight] [real] NULL ,
[IdealWeightLow] [real] NULL ,
[IdealWeightHigh] [real] NULL ,
[Unit] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Bed] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MedicaidNumber] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[GuardianName] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[GuardianAddress1] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[GuardianAddress2] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[GuardianCity] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Outcomeb01] [bit] NULL ,
[Outcomeb02] [bit] NULL ,
[Outcomeb03] [bit] NULL ,
[Outcomeb04] [bit] NULL ,
[Outcomeb05] [bit] NULL ,
[Outcomeb06] [bit] NULL ,
[Outcomeb07] [bit] NULL ,
[Outcomeb08] [bit] NULL ,
[Outcomeb09] [bit] NULL ,
[Outcomeb10] [bit] NULL ,
[Outcomeb11] [bit] NULL ,
[Outcomeb12] [bit] NULL ,
[Outcomeb13] [bit] NULL ,
[Outcomeb14] [bit] NULL ,
[Outcomeb15] [bit] NULL ,
[Outcomeb16] [bit] NULL ,
[Outcomeb17] [bit] NULL ,
[Outcomeb18] [bit] NULL ,
[Outcomeb19] [bit] NULL ,
[Outcomeb20] [bit] NULL ,
[Outcomeb21] [bit] NULL ,
[GuardianState] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Outcomea21] [bit] NULL ,
[Outcomea20] [bit] NULL ,
[Outcomea19] [bit] NULL ,
[Outcomea18] [bit] NULL ,
[Outcomea17] [bit] NULL ,
[Outcomea16] [bit] NULL ,
[Outcomea15] [bit] NULL ,
[Outcomea14] [bit] NULL ,
[Outcomea13] [bit] NULL ,
[Outcomea12] [bit] NULL ,
[Outcomea11] [bit] NULL ,
[Outcomea10] [bit] NULL ,
[Outcomea09] [bit] NULL ,
[Outcomea08] [bit] NULL ,
[Outcomea07] [bit] NULL ,
[Outcomea06] [bit] NULL ,
[GuardianZipCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Outcomea05] [bit] NULL ,
[Outcomea04] [bit] NULL ,
[Outcomea03] [bit] NULL ,
[Outcomea02] [bit] NULL ,
[Outcomea01] [bit] NULL ,
[GuardianPhoneNumber] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SSN] [nvarchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Gender] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Race] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AdmittingDiagnosis] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CurrentDiagnosis] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Active] [bit] NULL ,
[CAPMRDDWaiverServices] [bit] NULL ,
[upsize_ts] [timestamp] NULL ,
[EventTriggerCount] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[Client] ADD
CONSTRAINT [DF__Client__Facility__117F9D94] DEFAULT (0) FOR [FacilityNumber],
CONSTRAINT [DF_Client_AdmitNumber] DEFAULT (0) FOR [AdmitNumber],
CONSTRAINT [DF__Client__AdmitWei__1367E606] DEFAULT (0) FOR [AdmitWeight],
CONSTRAINT [DF__Client__IdealWei__145C0A3F] DEFAULT (0) FOR [IdealWeightLow],
CONSTRAINT [DF__Client__IdealWei__15502E78] DEFAULT (0) FOR [IdealWeightHigh],
CONSTRAINT [DF__Client__CAPMRDDW__164452B1] DEFAULT (0) FOR [CAPMRDDWaiverServices],
CONSTRAINT [aaaaaClient_PK] PRIMARY KEY NONCLUSTERED
(
[RecordID]
) ON [PRIMARY]
GO
View 2 Replies
View Related
Feb 15, 2007
Hello all, i have 2 sql tables, and they each contain a column with bigint values. What i want to do is add up the values from both table, and then display it as 1 number. Supposing i have the following
table name: DownloadTable
fileName | DownloadSize |
file1 | 45
file2 | 50
file3 | 20
---------------------------------------------
second table
table name: VideoTable
fileName | VideoSize |
file1 | 40
file2 | 60
file3 | 20
----------------------------------------
Now, i want this to output 120, wich is the sum of the sum of the values of table 1 and 2 I've already tried the following:
SELECT SUM(DownloadTable.DownloadSize) + SUM(VideoTable.VideoSize) FROM DownloadTable, VideoTable
However, when i ran this, it gave me a huge number, that was far from accurate from what it was suppose to output. Can anyone help me?
Thanks in advance
Regards,
View 8 Replies
View Related
Oct 13, 2005
I posed this problem a few days ago, but havent been able to generate the results i need. Suppose my resultset from an sql query gathering totalsales for a given day by a salesrep looks like this:Lastname totalsales orderID-----------------------doe 1403 510doe 500 680 doe 200 701using SUM(Accounts.totalsales) is not adding up the totalsales. What do I need to do to add up the totalsales, and then reassign it to a newfield?netsports
View 8 Replies
View Related
Jul 8, 2002
Hi,
I want to perform the following select statement
select (sum(ColA) + sum(ColB) + sum(ColC)) / 3
from tableA
Where ....
I want to add the sum of the columns and then divide it by a constant. The problem I am having is there are a lot of nulls in the database that I am working on (i.e. one of the values in the ColB is Null, which makes the result of the entire expession null).
How do I get around this. I need to add the values of the columns?
Any help would be appreciated.
Thanks,
Bill
View 3 Replies
View Related
Jan 17, 2012
I have a table with 6 columns. which we can call a, b, c, d, e, f. What I want to achieve is to put data in column d and e and then split this result in column f.
The data I want to put in column d and e is already exported and executed from a table called exp_data, which is from a period of november.
So this is what i have so far but is not working:
update split_table set d =
select amount from exp_data
where period = '1111'
and exp_data.account = split_table.b
and exp_data.company = split_table.a
The error I get is incorrect syntax near select. Fixed the issue by adding a parenthesis before the select until the end...
View 6 Replies
View Related
Sep 27, 2007
Hi!
I have one table with thousands of records. Now, I have to add one more column to that table. And this new column will contain concatination of two columns in that same table. Is there any command to do this?
My table has two columns as 'file', 'extn', now i want to add 'fullname' column to that table, and it will contain the concatinated value of 'file' and 'extn'. If the 'file' field has one record as "myfile", 'extn' has one record as ".doc", I want to insert "myfile.doc" in the new column 'fullname'. Like this my table has thousands of records. How can i do this? Please help me! Thanks in advance!
View 9 Replies
View Related
Mar 6, 2008
i insert the names of the collumns in the design of the sql server and in some of the names it add the brackets.
for example [LabsRadiologyBiochemAmylase>125mMolPLitre].
i try to remove them but when i save it, it write them again.
can someone explain me how to remove the brackets???
View 6 Replies
View Related