[T-SQL] Changing a varchar column with money values in, into a decimal data type |
- Changing a varchar column with money values in, into a decimal data type
- Implicit Update in TRY..CATCH can it become uncommitable
- Count Number Of Investors in fund
- Unknown Syntax for me ;With
- Limit count of user work with my DB (by C#)
- convert varchar to date
- need help with trigger
- SELECT TOP record
- RIGHT 4 unless RIGHT 5th digit > 0
- Avoid exporting "enter character" on the footer
- Help with complex code
- valiate each parent intermediary is also a parent to itself
- Data Manipulation on Read Only subscribers
| Changing a varchar column with money values in, into a decimal data type Posted: 15 May 2013 12:47 AM PDT Ive been given a data set to work with with a column for money.This column is basically a mess but its a varchar value.Some of the examples are 12,312.00, 17,160.96, 21,519.00there are lots of issues, thre are even spaces in there which need to be trimmedIm trying to convert this to a decimal value that I can work with for further calculations. Ive triedSELECT convert(decimal(19,4),NULLIF(REPLACE(LTRIM(RTRIM(Full_Time_Equivalent_Salary)),',',''),0))Bu I get the error messageConversion failed when converting the varchar value '999.96' to data type int.Ive tried a few decimal types but Im having no joy at all. If anyone has any idea how to do this I would be very gratefulDebbie |
| Implicit Update in TRY..CATCH can it become uncommitable Posted: 15 May 2013 12:53 AM PDT Hi All,My question is could this statement cause an uncommitable transaction and subsequent leave transaction in an open state where the locks it had acquired would persist. I ask the question because I've taken over administration responsibility for a database and a stored procedure that calls this statement is occasionally causing blocking on the database but in activity monitor the state is blank and it sits there inactive until killed. My first instinct from looking at the statement is that the update has failed and the transaction has entered an uncommitable state but what I don't understand is why wouldn't the implicit transaction be rolled back on hitting the catch unless for some reason the catch has been bypassed.[code="sql"]BEGIN Try UPDATE Table1 SET Col1 = @State, Col2 = Getdate(), Col3 = @Error WHERE Col4 = @MessageID SELECT 'SUCCESS' as Response END TRYBEGIN CATCH SELECT ERROR_MESSAGE() as ResponseEND CATCH[/code]I have now set XACT_ABORT on and wrapped the update in a transaction and check the XACT_STATE in the catch and rollback if appropriate.I hope this makes sense, any help would be much appreciated so I can better understand what is going on.Thanks |
| Count Number Of Investors in fund Posted: 14 May 2013 07:03 PM PDT Hi,Please help me with this :I need to take count of investors invested and having balance in Funds:1,2,3,4,5,6,7,8 by date 12-03-2010I have following tables:aao_Fund_AJ ,Its Columns are : FId,FNameaao_Balance_Aj ,its columns are :balance,holder,date |
| Posted: 14 May 2013 11:15 PM PDT Dear all,I got following code in one procedure and did not get why its use and what is it for :;WITH L0 AS (SELECT 1 AS c UNION ALL SELECT 1), L1 AS (SELECT 1 AS c FROM L0 A CROSS JOIN L0 B), L2 AS (SELECT 1 AS c FROM L1 A CROSS JOIN L1 B), L3 AS (SELECT 1 AS c FROM L2 A CROSS JOIN L2 B), L4 AS (SELECT 1 AS c FROM L3 A CROSS JOIN L3 B), Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS i FROM L4), FilteredNums AS (SELECT i FROM Nums WHERE i<= 400)Please explain.Thanks in advance :) |
| Limit count of user work with my DB (by C#) Posted: 14 May 2013 05:07 PM PDT HiI have sql server DB and C# Application.How can I restricted count of user who use from my DB?How can I do this by c# or by SQL SERVER ?Thanks very much |
| Posted: 14 May 2013 11:12 AM PDT I would like to convert a varchar column to datetime.The varchar column are supposed are be like a date : 2013-05-12But there may be a wrong date due to user error: like this one [u]0210-05-12.[/u]How can I use a statement to convert all them to datetime and if it is a invalid one like above, make it null.Thanks |
| Posted: 14 May 2013 03:25 AM PDT I have a strange requirement. This is a code issue but i am looking for a work around from daatabase perspective. I have a table that has a foreign key relation ship and it has duplicate values. Now I need a trigger to suppress inserts if a couple of column values already exists. I dont want to raise errors, i just dont want anything to get inserted if those values exist in the table.Here is the table definition:CREATE TABLE [dbo].[DMV1]( [DMVID] [uniqueidentifier] NOT NULL, [FDID] [uniqueidentifier] NOT NULL, [Value] [nvarchar](50) NOT NULL, [LastModified] [datetime] NOT NULL, [IsNew] [bit] NOT NULL, CONSTRAINT [PK_DMV1] PRIMARY KEY NONCLUSTERED ( [DMVID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOALTER TABLE [dbo].[DMV1] WITH CHECK ADD CONSTRAINT [FK_DMV1_FDEFs] FOREIGN KEY([FDID])REFERENCES [dbo].[FDEFs] ([FDID])ON DELETE CASCADEGOALTER TABLE [dbo].[DMV1] CHECK CONSTRAINT [FK_DMV1_FDEFs]GOALTER TABLE [dbo].[DMV1] ADD DEFAULT ((0)) FOR [IsNew]This can be prevented if i had unique constraints on the columns but unfortunately we dont and cant add now. Now the columns I am looking at are FDID, value where both are not null and FDID is a foreign key. If these columns values already exisit then i dont want their duplicates. I started writing the trigger but stuck at the logic to prevent insert:Create Trigger [dbo].[Unique_FDID_Value] on [dbo].[DMV1] After InsertasBeginif exists (select FDid,value from inserted)EndGONeed your help experts. Thanks. |
| Posted: 14 May 2013 05:07 AM PDT Hi, I have a query where it returns exact same value for all fields except 1 field.ExampleCREATE TABLE #Temp1 ( recid int identity (1,1),SiteID INt,TankID INT,TNo INT,PDate DateTime,Gallons INT,Status1 VarCHAR(10))INSERt INTO #Temp1 (SiteID ,TankID ,TNo ,PDate ,Gallons ,Status1)SELECT 1,2,3,'12-08-2012',2,'On'UNIONSELECT 1,2,3,'12-08-2012',2,''SELECT * FROM #Temp1DROP TABLE #Temp1The above selects are results from different tables. Is there any way I can eliminate Status1 ='' while inserting in the table ? |
| RIGHT 4 unless RIGHT 5th digit > 0 Posted: 14 May 2013 05:38 AM PDT In the current data set I'm working with in production, the values are only 4 digits, but the CSV file is dropping the data in my staging table with a leading two zeros. So the actual value is 0100 but I'm getting 000100. So I wrote a query (below: SQL SELECT) that captures the RIGHT 4 on the INCOMING_VALUE column. If you run the code below, you'll see the first problem I have. The third and fourth rows are incorrect since I need the RIGHT 5, so the leading 1 & 2 are dropped. Is there a way to dynamically create a process that says RIGHT 4 unless the RIGHT 5th digit >= 1? Ultimately I need to update RIGHT_GREATER_THAN_FIVE with the correct RIGHT 4 or 5 values in the CONVERTED_VALUE column. SQL SELECT[code="sql"]SELECT [rgtf].[INCOMING_VALUE] , ( SELECT RIGHT([rgtf].[INCOMING_VALUE] , 4) ) AS CONVERTED_DATA , [rgtf].[DB_VALUE] , [rgtf].[CAPTURE_VALUE] FROM [dbo].[RIGHT_GREATER_THAN_FIVE] AS rgtf[/code]Create table[code="sql"]CREATE TABLE [dbo].[RIGHT_GREATER_THAN_FIVE]( [INCOMING_VALUE] [varchar](15) NULL, [CONVERTED_VALUE] [varchar](15) NULL, [DB_VALUE] [varchar](15) NULL, [CAPTURE_VALUE] [varchar](32) NULL)[/code]Insert Scripts[code="sql"]INSERT INTO [dbo].[RIGHT_GREATER_THAN_FIVE] ( [INCOMING_VALUE] , [CONVERTED_VALUE] , [DB_VALUE] , [CAPTURE_VALUE] ) VALUES ( '001000' , -- INCOMING_VALUE - varchar(15) '' , -- CONVERTED_VALUE - varchar(15) '1000' , -- DB_VALUE - varchar(15) '8045265CC6AA4671AF6062B1E3C3E6E9' -- CAPTURE_VALUE - varchar(32) )INSERT INTO [dbo].[RIGHT_GREATER_THAN_FIVE] ( [INCOMING_VALUE] , [CONVERTED_VALUE] , [DB_VALUE] , [CAPTURE_VALUE] ) VALUES ( '000200' , -- INCOMING_VALUE - varchar(15) '' , -- CONVERTED_VALUE - varchar(15) '0200' , -- DB_VALUE - varchar(15) '7E10E630820042EBB7896D9A560FD998' -- CAPTURE_VALUE - varchar(32) )INSERT INTO [dbo].[RIGHT_GREATER_THAN_FIVE] ( [INCOMING_VALUE] , [CONVERTED_VALUE] , [DB_VALUE] , [CAPTURE_VALUE] ) VALUES ( '010200' , -- INCOMING_VALUE - varchar(15) '' , -- CONVERTED_VALUE - varchar(15) '10200' , -- DB_VALUE - varchar(15) 'B7EC34B8F60D4EF690BDEA95B2B36C15' -- CAPTURE_VALUE - varchar(32) ) INSERT INTO [dbo].[RIGHT_GREATER_THAN_FIVE] ( [INCOMING_VALUE] , [CONVERTED_VALUE] , [DB_VALUE] , [CAPTURE_VALUE] ) VALUES ( '022405' , -- INCOMING_VALUE - varchar(15) '' , -- CONVERTED_VALUE - varchar(15) '22405' , -- DB_VALUE - varchar(15) 'A774DFA5095F45DE99B2B5EAA68FABAD' -- CAPTURE_VALUE - varchar(32) ) [/code] |
| Avoid exporting "enter character" on the footer Posted: 14 May 2013 04:07 AM PDT Hi peopleI have a problem. I need to export a file using bcp command, however I must avoid putting enter character only on the footer. Is it possible? Best Regards.Ulisses |
| Posted: 14 May 2013 02:11 AM PDT Hello. I'd appreciate help with a bit of code that can help me retreive data from one table in my DB.The issue here is that on the same table I have a bill of materials that contains the part numbers of the parent part and the child parts. One same part number can have several child parts which in turn can each have more child parts.I managed to get the information in an 'indented' form, but I need a 'summarized' form, meaning that instead of having the information horizontally, I need to retreive it vertically, so I can see the whole bill of materials in a simple manner: Parent part first (with quantities), child parts after and 'grandchilds' after that (if any).I hope someone can help me out with this.Thank you very much in advance.Lenar C. |
| valiate each parent intermediary is also a parent to itself Posted: 13 May 2013 05:20 PM PDT I have a table for which I want to validate where each parent intermediary is also a parent to itself. I want to write a query to validate this... COLUMN DATATYPE DESCRIPTIONIntermediaryPK int SourceSystemID tinyint Intermediary sourceIntermediaryID nvarchar(20) Intermediary IDIntermediaryName nvarchar(200) Intermediary nameIntermediaryTypeID nvarchar(1) Intermediary typeParentIntermediaryID nvarchar(20) Intermediary parentIntermediaryTypePK tinyint Intermediary typeIntermediaryCategoryPK smallint Intermediary categoryParentIntermediaryPK int Parent intermediary PKGrandParentIntermediaryPK int Grand parent intermediary PK |
| Data Manipulation on Read Only subscribers Posted: 14 May 2013 01:11 AM PDT Hi Friends,Although the situation is not too tricky on its face but would request some feedback for implementing a correct approach.I have a transactional replication for reporting purpose and subscriber are meant only for read-only operations. The distribution agent is the only login that should write the data. However, I have witnessed that at times somehow some data goes missing on subscriber side and then replication alerts give an error message: ' row not found on subscriber'At one instance, I could find that one of my colleagues ran a DML script meant for publisher(OLTP side).Is there a way to track 'who, what and when' on subscriber side to catch this. There are some people who has got access(authorized and un-authorized) and to make the approach more disciplined, I need to implement some sort of tracking on my databases.ThanksChandan Jha |
| You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / T-SQL (SS2K8) To stop receiving these emails, you may unsubscribe now. | Email delivery powered by Google |
| Google Inc., 20 West Kinzie, Chicago IL USA 60610 | |
No comments:
Post a Comment