I need create a field to store tax rate. I need only 2 decimal points. I defined the field as decimal, precision=5 and scale=2. Does it mean that it can hold value from 0.00 to 999.99?
View 12 RepliesI am designing a database. I want to define a automatic sequence on a table primary key field. what is the best solution for it?
I know I can enable identity property for a field, but it has some problems ( for example its seed jumps on restart and unsuccessful events)
I also can use some calculated sequences. for example I can calculate max of the filed values and after incrementing use it as key for new inserted record.
which one is better?
I want to insert attibute att1 in field F1 . Value for this attribute is content of another field in this table (F2).
My query :
update MyTable
set F1.modify
('insert attribute att1 {sql:column("F2")} into (/ROOT/Node1)[1]')
Where F1 Is not null
But I get this error :
XML well-formedness check: Duplicate attribute 'att1'. Rewrite your XQuery so it returns well-formed XML.
How do I check the douplicate attribute ?
Hi,
Let's say I have 1000 registered users in database table and each of them has numeric ranking value.
How can I get the position of each user in comparison to other users ranking value?
I need to replace Access Val() functions with similiar function in sql.
i.e. Return 123 from the statement: SELECT functionname(123mls)
Return 4.56 from the satement: SELECT functionname(4.56tonnes)
Any one with ideas please
Thanks
George
Hi everyone,
Primary platform is sql25k running with sp2.
I'd like to define for a concrete field which only could have three values:
"a","b","c"
TIA
Hi all,
I am trying to add a filter to the data of a chart as following:
Expression: =Fields!Time.Value
Operator: >
I tried to the following for the value:
12/28/2007
2007-12-28
'2007-12-18'
'12/28/2007'
...
I always get an error saying:
"... Cannot compare data for types System.DateTime and System.String. Please check the data type.."
Any tip will be greatly appreciated.
If I want to store day of the week (i.e. Mon), do I use datetime field or char? Also if I update #25/12/2004# to a datetime field. What is the value is the time port of the field? Thx.
View 10 Replies View RelatedHi! A question just struck me that I cannot test right now (don´t have vs2005 on this computer)...Is it possible to add a database-field value to an Xml-Source object attribute?
For instance <Object id=1 name="void" description="" /> is my souce xml... in this case I want to replace the description attribute value (string.Empty) with a value from my db... can this be done?
Regards
Erik B.
Hi guys,
I need to get a column with the sum of the field "SUF" from table "JurnalTransMoves_1" when that field ("SUF") is ordered by the field "REFERENCE" from table "Stock", and Show the value only once.
The desired result should by something like:
Stock.REFERENCE
JurnalTransMoves.SUF
SUM(JurnalTransMoves.SUF) Group By Stock.REFERENCE
5752
10
60
5752
20
5752
30
5753
400
3000
5753
500
5753
600
5753
700
5753
800
5754
7
15
5754
8
Is there any chance to do that?
Thanks in advance,
Aldo.
Code Snippet
SELECT
Accounts.FULLNAME AS 'ACCOUNTS.FULLNAME',
Accounts.ACCOUNTKEY AS 'ACCOUNTS.ACCOUNTKEY',
Accounts.FILTER AS 'ACCOUNTS.FILTER',
Accounts.SORTGROUP AS 'ACCOUNTS.SORTGROUP',
AccSortNames.SORTCODENAME AS 'AccSortNames.SORTCODENAME',
Accounts.CreditTermsCode AS 'Accounts.CreditTermsCode',
CreditTerms.DETAILS AS 'CreditTerms.DETAILS'
CreditTerms.CURRENF AS 'CreditTerms.CURRENF'
CreditTerms.MONTH AS 'CreditTerms.MONTH',
CreditTerms.DAYS AS 'CreditTerms.DAYS',
CreditTerms.SHAREPRC AS 'CreditTerms.SHAREPRC',
CreditTerms.TEMF AS 'CreditTerms.TEMF',
CASE
WHEN CAST(Accounts.VatExampt AS int) = 0 THEN 'x'
WHEN CAST(Accounts.VatExampt AS int) = 1 THEN 'y'
ELSE 'Undefined' END AS 'VAT',
Stock.DOCUMENTID AS 'Stock.DOCUMENTID',
DocumentsDef.DOCNAME As 'DocumentsDef.DOCNAME',
CASE
WHEN CAST(Stock.DOCUMENTID as int) = 1 THEN Stock.DOCNUMBER
WHEN CAST(Stock.DOCUMENTID as int) = 3 THEN Stock.DOCNUMBER
WHEN CAST(Stock.DOCUMENTID as int) = 35 THEN Stock.DOCNUMBER
WHEN CAST(Stock.DOCUMENTID as int) = 120 THEN Stock.DOCNUMBER
WHEN CAST(Stock.DOCUMENTID as int) = 31 THEN Stock.REFERENCE
WHEN CAST(Stock.DOCUMENTID as int) = 44 THEN Stock.REFERENCE
WHEN CAST(Stock.DOCUMENTID as int) = 34 THEN Stock.REFERENCE
WHEN CAST(Stock.DOCUMENTID as int) = 43 THEN Stock.REFERENCE
WHEN CAST(Stock.DOCUMENTID as int) = 40 THEN Stock.REFERENCE
ELSE '' END AS 'Invoice No',
Stock.VALUEDATE AS 'Stock.VALUEDATE',
JurnalTrans.DESCRIPTION AS 'JurnalTrans.DESCRIPTION',
JurnalTrans.REF2 AS 'JurnalTrans.REF2',
JurnalTransMoves.SUF AS 'JurnalTransMoves.SUF',
JurnalTransMoves_1.SUF AS 'JurnalTransMoves_1.SUF',
JurnalTransMoves.TRANSID AS 'JURNALTRANSMOVES.TRANSID'
FROM
JURNALTRANSMOVES AS JurnalTransMoves_1
INNER JOIN JURNALTRANSMOVES AS JurnalTransMoves
INNER JOIN (SELECT DISTINCT JURNALTRANSID, RECEIPTSTOCKID, FULLMATCH, TABLFNUM, CKCODE, RSORT, RUSEFID FROM RECEIPTJURNALMATCH) AS ReceiptJurnalMatch_1 ON ReceiptJurnalMatch_1.JURNALTRANSID = JurnalTransMoves.ID
INNER JOIN ACCOUNTS AS Accounts ON JurnalTransMoves.ACCOUNTKEY = Accounts.ACCOUNTKEY
INNER JOIN JURNALTRANS AS JurnalTrans ON JurnalTransMoves.TRANSID = JurnalTrans.TRANSID
INNER JOIN STOCK AS Stock ON JurnalTrans.STOCKID = Stock.ID ON JurnalTransMoves_1.TRANSID = JurnalTrans.TRANSID AND JurnalTransMoves_1.ACCOUNTKEY = Accounts.ACCOUNTKEY
LEFT OUTER JOIN ITEMS AS Items
INNER JOIN STOCKMOVES ON Items.ITEMKEY = STOCKMOVES.ITEMKEY
INNER JOIN ITEMSORTNAMES AS ItemSortNames ON Items.SORTGROUP = ItemSortNames.ITEMSORTCODE ON Stock.ID = STOCKMOVES.STOCKID
LEFT OUTER JOIN ACCSORTNAMES AS AccSortNames ON Accounts.SORTGROUP = AccSortNames.ACCSORTCODE
LEFT OUTER JOIN CREDITTERMS AS CreditTerms ON Accounts.CREDITTERMSCODE = CreditTerms.CREDITTERMSCODE
LEFT OUTER JOIN DOCUMENTSDEF AS DocumentsDef ON Stock.DOCUMENTID = DocumentsDef.DOCUMENTID
WHERE
Accounts.SORTGROUP Between '3001' And '3020'
AND Accounts.ACCOUNTKEY IN ('123456')
ORDER BY Accounts.ACCOUNTKEY
Hello, everyone!
What are the precision & the scale values in the numeric field for?(as also in int, etc).I need to have a field that is exactly 6 digits in length. However , when I enter the value , in the length column , it defaults to either 5 or 9 , depending on the precision values. Also when I enter the data , that field accepts not only 9(defined with precision of 10), but more than that, till about 15 digits!!…I think I am not clear on the use of precision…what do I need to define the field as so it accepts only 6 digits? Please enlighten me .
Thanks in advance!
HiI have a character field (char ot varchar) that I want to force only tocontain numeric characters.Can that be done by way of defining a constraint on the field ?or by any other way in the field/table definition ?What id the syntax ?Anyone have examples ?ThanksDavid Greenberg
View 1 Replies View Related
Hi,
I have source table , fact table and four dim. tables , I have to update a field in fact table from source table.
How can I do it?
thanks...
I need to insert a null valvue when the user does not impute any text.
here is my code
If cell_phone.Text = "" Then
cell_phone.Text = "dbnull.value"
End IfDim mySqlConnection As New SqlConnection
mySqlConnection.ConnectionString = ConfigurationManager.ConnectionStrings("Call_ListConnectionString").ConnectionString
Dim strSQL As String = "INSERT INTO Employees(Clock_ID, Last_Name, First_Name, Cell_Phone, Home_Phone, Work_Phone, Email, Primary_Day_Phone, Primary_Night_Phone, Blackberry_Number) VALUES ('" & clock_id.Text & "','" & last_name.Text & "','" & first_name.Text & "','" & work_phone.Text & "','" & home_phone.Text & "','" & cell_phone.Text & "','" & email.Text & "','" & prime_day_lst.SelectedValue & "','" & prime_night_lst.SelectedValue & "','" & blackberry.Text & "')"Dim mySqlCommand As New SqlCommand(strSQL, mySqlConnection)
mySqlConnection.Open()
mySqlCommand.ExecuteNonQuery()
mySqlConnection.Close()
THanks
Mike
Hi all,I've set up a page with a gridview, and I'm trying to create a query based on three parameters and to display all records when the page loads.I have created the first two parameters no problem, but I'm having problems with the last one.The parameter is to be populated by a dropdownlist, where the selectedvalue is numeric. I have done a search and found that I can't use the '%' wildcard as it is for a string data type.I have read somewhere that to get around this I can use the CHAR() function to convert to a character, I have tried this without success.When creating the ControlParameter the datatype gets set to string, which I think is not working becuase the input has to be an integer for it to conver to char, am I right?This is the query, and it is the @Application parameter that I'm having this problem with. SELECT dbo.Issue.IssueID, dbo.Issue.ReportedBy, dbo.Issue.ShortDescription, dbo.Issue.DateReported, dbo.Issue.Status, dbo.Priority.Description AS Priority,
dbo.Application.Application
FROM dbo.Issue INNER JOIN
dbo.Priority ON dbo.Issue.Priority = dbo.Priority.PriorityCode INNER JOIN
dbo.Application ON dbo.Issue.Application = dbo.Application.ApplicationID
WHERE (dbo.Issue.Status LIKE '%' + @Status) AND (dbo.Issue.AssignedTo = @AssignedTo) AND (CHAR(dbo.Application.ApplicationID) LIKE '%' + @Application)
ORDER BY dbo.Priority.PriorityCodeHas anyone managed to do this another way?Thanks
I have a field with State and Zip (ie; CA94526) which is a varchar field. I have lots of data that is invalid and need SELECT all records that the right(myfield,5) IS NOT Numeric. Can this be done?
Thanks!
I have one table and this field is character field with save data in below.
Bonus_table->bonus_amt_field. Char(20)
======================================
Record information
0
Null
Blank
3
4
Null
Blank
if i want to convert this character field => change to numeric field to display ,how to handle "Blank" and "null" record?
The result expect:
0
0
0
3
4
0
0
I try this query but wrong message :
select cast(convert(numeric,3)bonus_amt) as bonus_amt
from test
Hello All,I'm trying to parse for a numeric string from a column in a table. WhatI'm looking for is a numeric string of a fixed length of 8.The column is a comments field and can contain the numeric string inany positionHere's an example of the values in the column1) Fri KX 3-21-98 5:48 P.M. arrival Cxled ATRI #27068935 3-17-982) wed.kx10/26 Netrez 95860536Now I need to parse through these lines and return only the 8 digitnumbers in itThe result set should be2706893595860536This is what I've done so farDeclare @tmp table(Comments_Txt varchar(255))Insert into @tmpselect Comments_Txt from Reservationselect * FROM @tmp where Comments_Txtlike ('%[0-9][0-9][0-9][0-9][0-9][0**9]%')But it returns the entire comments field in the result set. What I needis a way to return just those 8 digits.Any Ideas??Thanks in advance!!!
View 2 Replies View RelatedI have a numeric data field called Price and this has a value of 0.000 in the db. when i create a package to extract this data to a flat file, the value is displayed as .000
what should i do to make it appear as 0.000 in the flat file.
I tried using a derived column expression where i check if the db value is 0.000 and display it as a string "0.000". this works fine if the OLEDB source is a sql command but fails if the OLEDB source is a sql command from variable.
any help would be appreciated.
Thanks.
How to get the max length of numeric field in a DataSet?
I have a DataSet bound to an Access database. Is it possible to get the maximum length of numeric field of a table in the DataSet? Many fields in the database tables have maximum length values set in ...
Hi,
I have a concern about adding a new field to a table with image field - which is huge.
Will there be a problem with some databases, where they have a hard time locating data correctly after such a large field?
Previously this happened to me, and what was advised to put all the big fields at the end of the table.
Thanks.
does anyone know if there is a way, or perhaps a custom toolbox control that is already developed that allows you to drag an entire dataset into a table, instead of pulling everything in field by field...?
was just curious.
-dk
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
how i convert varchar sal field to numeric in query
select sum(sal) from emp1
error:the sum or average aggregate operation cannot take a varchar data type as an argument.
I am not sure if this is the right place, but here's my question:
with a field being varchar, can only the rows that are numeric be selected? For example-
ID Data1
1 don
2 jerry
3 3030
4 1234
5 susan
6 4321
Does SQL have an IsNumeric type function that can be used in the where clause?
Don
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.
Hi All,
I'm migrating some SQL 2000 DTS to SSIS.
I am transfering data from a DB2 table to a SQL 2005 table using the OLE DB Source, Data Converstion then the OLE DB Destionation.
So, I have a numeric (Precision 3, Scale 2) field with NULL value in the DB2 table.
I'm trying to transfer these data to a SQL2005 table and I am receiving this error message below:
"[Destination Table TFACIL [18]] Error: There was an error with input column "COMB_OPPT_PRCT" (2865) on input "OLE DB Destination Input" (31). The column status returned was: "The value violated the integrity constraints for the column.". "
The field must accept null because of the APPLICATION ( i can't change it, im not the owner ).
Could someone help me?
Thanks in advance.
Regards,
Thiago
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?
I have a list of movies that show throughout the year. I would like to assign a unique numeric identifier to each text field.
I have provided some sample data with the output I would like. The Movie_ID in the sample data is just made up, feel free to assign any numeric identifier, preferably of the same length but not a necessity.
create table dbo.Movie_Pre_Fix
(
Report_Monthint,
IDint,
Movie_NameVarchar(50)
);
insert into dbo.Movie_Pre_Fix (Report_Month, ID,Movie_Name)
VALUES
(201406,0721312144,'SAW'),
[code]....
Hi there - I would like to share this strip of code with our SQL 2000DBA community. The code below strips all non-numeric characters from agiven string field and rebuilds the string. Very simple, but I had tobuild it from scratch due the lack of info on this specific matter. Iam sure there are better solutions out there, although I will be gladif this script can help anyone. Feel free to modify and comment itback.Regards,Rubem Linn JuniorMCSE, .NET developerWeb Apps Specialist------------------------------------------------------- BEGIN---------------------------------------------------DECLARE @String_Length AS INTEGER -- Length of the given stringDECLARE @Original_String as NVARCHAR(50) -- The field to stripnon-numeric charsDECLARE @counter as integer -- simple counter variableDECLARE @Stripped_String as nvarchar(50) -- The field after beenstripped-- Get the length of the field (string) to be parsedSELECT @String_Length = len(someStringField) FROM SomeTable WHEREFilterID = 001-- Get the field (string) to be parsedSELECT @Original_String = someStringField FROM SomeTable WHEREFilterID = 001-- Set counter variable to 1SELECT @counter = 1-- Reset this variableSELECT @Stripped_String = ''-- Initiate loop from 1 to the Length of the given stringWHILE (@counter) <= @String_LengthBEGIN-- Check if the char in the lap is numericif substring(@Original_String,@counter,1) LIKE '[0-9]'BEGIN-- Load this variable with the non-numeric-- data stripped from the original stringselect @Stripped_String = @Stripped_String +substring(@Original_String,@counter,1)END-- Increment the counter by oneselect @counter = @counter + 1END-- Print the original string with all charactersPRINT @Original_String-- Print the numeric data that was stripped outPRINT RTRIM(LTRIM(@Stripped_String))
View 1 Replies View RelatedHI everybody need help on this..
I have two tables below
table1
country countryid
africa ___
usa ___
italy ___
Spain ___
table2
countryid country name
1 africa
2 germany
3 italy
4 usa
I need to write the countryid of table 2 to the field countryid in table1 using the criteria of the correspoinding country name table 2 to country of table 1 if it write countryid else 0..
THE RESULT WOULD BE
country countryid
africa 1
usa 4
italy 3
spain 0
thanks
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.
Any assistance would really be appreciated.
Thanks.
I'm new to SQL and I'm trying to write a statement to satisfy the following:
If [Field1] contains text from [Field2] then return [Field3] as [Field4].
I had two tables where there were no matching keys. I did a cross apply and am now trying to parse out the description to build the key.