Wednesday, March 13, 2013

[SQL Server] Creating my first UD

[SQL Server] Creating my first UD

Creating my first UD

Posted: 13 Mar 2013 10:04 AM PDT

I really like sql server, but one of the things that drives me crazy is formatting columns. The numbers in the databases that I pull from are usually numeric and I really need commas to help me read the numbers. I found the code below that will convert the numeric into numbers with columns. Since I use this all of the time, I would like to create a UDF. I have never done this before, but have found many examples. This is what I would like the UDF to do:REPLACE(CONVERT(varchar(20), (CAST(SUM(@numtocomma ) AS money)), 1), '.00', '')So far this:CREATE FUNCTION commainsert ( @numtocomma numeric(38,6)) RETURNS moneyASBEGINdeclare @monval moneyset @monval=REPLACE(CONVERT(varchar(20), (CAST(SUM(@numtocomma ) AS money)), 1), '.00', '')return @monvalENDselect dbo.commainsert(10201.21) as Numberyields this:Number10201.21Would someone please tell me where I went wrong?Thanks

outer reference error with group by

Posted: 13 Mar 2013 07:18 AM PDT

I get the following error: Each GROUP BY expression must contain at least one column that is not an outer referencewhen I try to run this query:INSERT INTO progsumm_dba.acc_task_sums (fiscal_year, plan_option, goal, objective_num, task_code, account_code, person_cost, added_cost, fte_prcnt) SELECT :fa_year, s.plan_option,s.goal, s.objective_num, s.task_code, l.account_code, sum(s.person_cost),sum(s.added_cost), sum(ISNULL(s.fte_prcnt,0)) FROM progsumm_dba.fund_task_sums s, account_funds_lookup l WHERE s.fiscal_year = :fa_year AND s.plan_option = :fa_plan AND s.fiscal_year = l.fiscal_year AND s.plan_option = l.plan_option AND s.fund_code = l.fund_code GROUP BY :fa_year, s.plan_option, s.goal, s.objective_num, s.task_code, l.account_codeI have no idea what is meant by 'outer reference'. Can anyone clear this up for me?

Microsoft Access 2010 & SQL Server 2008 Express

Posted: 20 Jan 2011 12:34 AM PST

What's the best way for clients to connect to an sql server? Should all clients have their own seperate Access data project or can one be shared on a network drive amongst all the clients that need access to sql server?Also, how can I share the reports created within the access data project?

SQL Query - Update varchar ID's...confused.com

Posted: 13 Mar 2013 02:20 AM PDT

Hi,So I have been replacing an old database of ours with a new one - it has all gone relatively well. The new database structure etc is far better, far more effecient and well awesome. unfortunately something I couldn't change was the ID structure ( the users wanted the same data presented to them a lot better). My problem - I have had to rename tables/queries/stored procedures/triggers to follow a new convention, which is fine, unfortunately I would "really" like to change 1 tables ID:Sample ID's:[code="sql"]AG001/1/P1 AG002/1/P1 AG003/1/P1 AG004/1/P1 AG006/1/P1 AG007/1/PO1 AG008/1/P01 AG009/1/P01 AG010/1/P01 AL001/1/P1 AL002/1/P1 AL003/1/P1 CC013/1/P01 [/code]As you can see they are a pain in the..It basically mirrors three tiers (each tier is a section between the slashes). I would like to change the ID's in the final tier to the following:[code="sql"]AG001/1/L1 AG002/1/L1 AG003/1/L1 AG004/1/L1 AG006/1/L1 AG007/1/LO1 AG008/1/L01 AG009/1/L01 AG010/1/L01 AL001/1/L1 AL002/1/L1 AL003/1/L1 CC013/1/L01 [/code]I find this quite difficult to explain, the first XX000 is the 1st tier, the /1/ is the second tier and the X01 or X1 or X001 is the final tier (the final tier is the messiest due to human error). Unfortauntely there are too many records to edit manually. I have tried looking into using CHARINDEX/REPLACE/SUBSTR which are helpful - the following provides me with the final tier which I then need to edit - however how can I go and edit to the new format.Used in a cursor:[code="sql"] RIGHT(@ID, CHARINDEX('/', REVERSE(ID)) - 1)[/code]I have also thought about simply recreating the final tier ID's from scratch, so they all follow Lnnn - this would be neater but again I unfortauntely don't know how to do this using SQL?Now I hold the 2nd tiers ID in another column[code="sql"]AG001/1 AG002/1 AG003/1 AG004/1 AG006/1 AG007/1 AG008/1 AG009/1 AG010/1 AL001/1 AL002/1 AL003/1 CC013/1 [/code]Perfect results:[code]AG001/1/L001AG001/2/L001AG001/2/L002AG001/2/L003CE001/1/L001CE002/1/L001[/code]

Dead lock not terminated

Posted: 13 Mar 2013 01:04 AM PDT

Dear AllI have read that sql takes care of dead locks on its own. But when i run following query through SSMS in 2 differnt query sessions it keeps on goingUSE AdventureWorksBEGIN TRANUPDATE Person.Address SET AddressLine1 = 'New address' WHERE AddressID = 25WAITFOR DELAY '0:0:10'SELECT * FROM Person.Address WHERE AddressID = 20Am i doing something wrong. I am using sql server 2008 R2Regards

Sum values in a select statement

Posted: 13 Mar 2013 12:15 AM PDT

I know this is a simple question. However, I'm not sure where to start. I'm new to SQL and would appreciate any help offered.How would I sum the following in example 1 and have it display as shown in example 2?--Example 1Expense_Id Expense Price Expense_By 1 Coffee 5.00 Jim 2 Coffee 3.00 Jim 3 Coffee 3.00 Mike 4 GAS 3.00 Jim 5 GAS 3.00 Jim 6 GAS 3.00 Mike 7 GAS 3.00 Mike--Example 2Expense_By Expense Total_Price Jim GAS 6.00 Mike GAS 6.00 Jim Coffee 8.00 Mike Coffee 3.00
Posted by at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog