cursor insert with like %

Hi I have a weird problem I want to cursor thru the values in a
temporary table and use the values to do a select statement to insert
into another temporary table...This select statement uses a like clause

something like where...when I take off the insert still nothing comes
back from the select...when I hardcode values it works...I get
results...is there something wrong with appending a +'%' to a value
read from a cursor?

DECLARE @.DEPT VARCHAR(65)
SET @.DEPT = "00201,00203"

DECLARE @.TB_ABSENCES TABLE(DeptOrEmpId VARCHAR(65))
DECLARE @.TB_DEPT TABLE ( V_DEPARTMENT_CODE VARCHAR(128) )

INSERT INTO @.TB_DEPT (V_DEPARTMENT_CODE)
SELECT V_DEPT FROM [ISIS].[dbo].[FU_GET_DEPTS_FROM_STRING](',', @.DEPT)

DECLARE DEPTS CURSOR FAST_FORWARD FOR
SELECT V_DEPARTMENT_CODE+'%' FROM @.TB_DEPT

OPEN DEPTS
FETCH NEXT FROM DEPTS INTO @.DEPT_CODE

WHILE @.@.FETCH_STATUS = 0
BEGIN

--INSERT INTO @.TB_ABSENCES TABLE
SELECT Code from TB_EMPLOYEE_DEPARTMENT T2
WHERE T2.V_HIERARCHY_CODE LIKE @.DEPT_CODE + '%'

FETCH NEXT FROM DEPTS INTO @.DEPT_CODE

END

CLOSE DEPTS
DEALLOCATE DEPTSyurps (yurps@.yahoo.co.uk) writes:
> Hi I have a weird problem I want to cursor thru the values in a
> temporary table and use the values to do a select statement to insert
> into another temporary table...This select statement uses a like clause
> something like where...when I take off the insert still nothing comes
> back from the select...when I hardcode values it works...I get
> results...is there something wrong with appending a +'%' to a value
> read from a cursor?

I would guess that there are trailing spaces. Rewrite as:

DECLARE @.DEPT VARCHAR(65)
SET @.DEPT = "00201,00203"

DECLARE @.TB_ABSENCES TABLE(DeptOrEmpId VARCHAR(65))

INSERT INTO @.TB_ABSENCES TABLE
SELECT Code
from TB_EMPLOYEE_DEPARTMENT T2
JOIN [ISIS].[dbo].[FU_GET_DEPTS_FROM_STRING](',', @.DEPT) D
ON T2.V_HIERARCHY_CODE LIKE rtrim(V_DEPT) + '%'

Yeah, that's right. No cursor. There is no need for it, and it could
be costly in terms of performance.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Oh gosh !! a couple hours wasted on something that I didn't need
to...

thanks!

Cursor Fetch Problem

I'm using a cursor to get a rowset and then update that rowset with certain new values. So I have a basic while loop to check until @.@.fetch_status is not zero. However, this means that it tries to fetch one time a row that does not exist before it exists the loop. This gives out an error message that the query had an error in it. I know it's not an error, but is there any way way to avoid that? Is there a way to peek ahead to see if I'm currently fetching the last row and exit before trying to fetch another one? Maybe there is a way to see how many rows were returned when the cursor was opened? This is a dynamic cursor so @.@.CURSOR_ROWS won't work as far as I know.

Thank you.What was the exact error?
For more information on cursors refer to books online.|||How about using the dreaded GOTO

WHILE 1 = 1

BEGIN

FETCH NEXT..............

IF (@.@.FETCH_STATUS <> 0) GOTO CloseCursor

......update rowset.......

END

CloseCursor:

CLOSE .........
DEALLOCATE ..........

GWsql

Cursor

I have to perform a number of calculations on a table but these
calculations need intermediate values ( like for example I have to
first calculate one value before I can use it in the next step of the
calcualtion). There are two ways of doing this:
1. Have columns for intermediate values ( it is possible as my
calculations are on a temp tbl) and use update statements. One update
statement for every step in the calculation.
2. Have a cursor, loop through the resultset and do the calculations
like we would in a programming language like C#.
Which one is better? Are updates ( a number of them) faster than a
cursor?
Thanks.John
A set based solution is much faster that cursors, however since you have
not posted DDL+ sample data it is hard to suggest something
"John Smith" <postmaster@.sumanthcp.plus.com> wrote in message
news:1139492787.074798.293960@.g14g2000cwa.googlegroups.com...
> I have to perform a number of calculations on a table but these
> calculations need intermediate values ( like for example I have to
> first calculate one value before I can use it in the next step of the
> calcualtion). There are two ways of doing this:
>
> 1. Have columns for intermediate values ( it is possible as my
> calculations are on a temp tbl) and use update statements. One update
> statement for every step in the calculation.
>
> 2. Have a cursor, loop through the resultset and do the calculations
> like we would in a programming language like C#.
>
> Which one is better? Are updates ( a number of them) faster than a
> cursor?
>
> Thanks.
>|||Cursors are usually slower than an SQL operation which updates all the rows
in
one go.
I would only use a cursor for this sort of operation if there were any
locking issues.
Are Riksaasen
"John Smith" wrote:

> I have to perform a number of calculations on a table but these
> calculations need intermediate values ( like for example I have to
> first calculate one value before I can use it in the next step of the
> calcualtion). There are two ways of doing this:
>
> 1. Have columns for intermediate values ( it is possible as my
> calculations are on a temp tbl) and use update statements. One update
> statement for every step in the calculation.
>
> 2. Have a cursor, loop through the resultset and do the calculations
> like we would in a programming language like C#.
>
> Which one is better? Are updates ( a number of them) faster than a
> cursor?
>
> Thanks.
>|||John Smith wrote:
> I have to perform a number of calculations on a table but these
> calculations need intermediate values ( like for example I have to
> first calculate one value before I can use it in the next step of the
> calcualtion). There are two ways of doing this:
>
> 1. Have columns for intermediate values ( it is possible as my
> calculations are on a temp tbl) and use update statements. One update
> statement for every step in the calculation.
>
> 2. Have a cursor, loop through the resultset and do the calculations
> like we would in a programming language like C#.
>
> Which one is better? Are updates ( a number of them) faster than a
> cursor?
>
> Thanks.
Neither of the above if you can avoid them. Create derived columns in
your queries or updates rather than using temp tables. Cursors are the
method of last resort for anything in SQL.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Currency or Double preferred?

I'm wondering whether it's better to choose Currency or Double for measure datatypes if the aggregated values will fit inside either datatype. Thoughts? Which would be smaller in terms of storage? If the number gets real big will either Currency or Double lose precision? Are rounding errors more prevalent with one rather than the other?

I have read the following pages but can't seem to make heads or tails of it in terms of best practices:

http://msdn2.microsoft.com/en-us/library/ms129408.aspx

http://msdn2.microsoft.com/en-us/library/system.data.oledb.oledbtype.aspx

http://msdn2.microsoft.com/en-us/library/678hzkk9(VS.80).aspx

http://msdn2.microsoft.com/en-us/library/364x0z75(VS.80).aspx

There is no Double in SQL Server so you have to use either Currency or Decimal or you may have to do conversion before sending the value to SQL Server. Try the link below for the SQL Server Types, ADO.NET types and .NET types. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms131092.aspx

|||

Caddre, thanks for the reply. That was a good link if you're doing SQLCLR stuff.

The Double SSAS datatype is equivalent to the float datatype in SQL Server. The Currency SSAS datatype is equivalent to the money datatype in SQL Server. Regardless, I'm not interested in SQL Server datatypes, just Analysis Services.

The question still remains, which datatype do you choose in SSAS if either will work: Currency or Double?

|||

Float is not a data type for persistence in any layer of SQL Server because of known precision issues in all programming languages. And my reply was related to your posted links which are clr related.

|||

Hi Furmangg,

If you have to store financial information in your cube and your database stores it as money(currency) you should definitely use currency data type for your mesures. Operations with the currency data type take less CPU power (no FPU calculations) and moreover will be better comressed by the storage engine.

Also if your information could be stored in currency data type (from -922,337,203,685,477.5808 to 922,337,203,685,477.5807) you schould take this type for your measures.

Best regards,

Vladimir Chtepa

|||

Thanks Vladimir. That's the answer I was looking for. I appreciate it.

Currency Fromatting

Hi Everyone,
I am a bit of a newbie to SQL, I have created a table to hold supplier
invoice details which has three columns to hold currency values i.e.
Nett Value
VAT Value
Gross Value
The columns for VAT and gross calculate automatically from the Nett value,
however the values in the fields have more than 2 digits after the decimal
place i.e.
Nett Value = 275.00
VAT Value = 48.125
Gross Value = 323.125
How do I force the two calculating fields to format the contents to standard
currency format i.e. ￡xxx.xx with only 2 digits after the decimal point?
Hope that makes sense. Any help would be greatly appreciatedNote that in the UK (AFAIK same as the rest of the EU), accounting
regulations require that VAT be calculated on an invoice sub-total, not
on individual items (there may be a difference due to rounding) so what
you are doing will not represent the correct total for accounting
purposes.
Storing both Net and Gross columns in a table is poor design. This is
redundant data and most systems will store just the Net value at detail
level, plus a VAT code.
Formatting is probably best left to your client app or middle tier. If
you must do it in SQL then look up the style parameter of the CONVERT
function. Avoid using MONEY / SMALLMONEY columns in your tables though
as these have some problems with precision in division operations.
David Portas
SQL Server MVP
--|||Thanks for your reply David. I am aware that for official accounting it
would need to be line by line calculated, but my DB if purely for reporting
supplier turnover and not for accounting so I am not overly bothered about
the roundings, but thanks for the pointers I'll go and have aplay and see
what I get.
Cheers
Jonathan
"David Portas" wrote:

> Note that in the UK (AFAIK same as the rest of the EU), accounting
> regulations require that VAT be calculated on an invoice sub-total, not
> on individual items (there may be a difference due to rounding) so what
> you are doing will not represent the correct total for accounting
> purposes.
> Storing both Net and Gross columns in a table is poor design. This is
> redundant data and most systems will store just the Net value at detail
> level, plus a VAT code.
> Formatting is probably best left to your client app or middle tier. If
> you must do it in SQL then look up the style parameter of the CONVERT
> function. Avoid using MONEY / SMALLMONEY columns in your tables though
> as these have some problems with precision in division operations.
> --
> David Portas
> SQL Server MVP
> --
>

Currency Format

I'd like currency format of $#,###
but when i specify that, I dont get any values for 0 I just get a $
which is somewhat expected.
If I specify C, I get the cents, which I dont care about.
Anyone knowhow I might achieve C currency format, that doesnt include
cents, but displays 0 when the value is 0?
Thanks in advance
Weston WeemsWeston,
Check out the FORMATCURRENCY function.
HTH
Jerry
"Weston Weems" <wweems@.nospam_itsucksGMAIL.COM> wrote in message
news:uFR4buHxFHA.664@.tk2msftngp13.phx.gbl...
> I'd like currency format of $#,###
> but when i specify that, I dont get any values for 0 I just get a $ which
> is somewhat expected.
> If I specify C, I get the cents, which I dont care about.
> Anyone knowhow I might achieve C currency format, that doesnt include
> cents, but displays 0 when the value is 0?
> Thanks in advance
> Weston Weems

Currency Conversions

Hi!

I am having trouble wrapping my brain around currency conversions.

Here is my situation:

My fact table contains values in local currency with a reference to the currency dimension.

These local currencies need to be converted to two reporting currencies: Actual Rates and Budget Rates.

I have rates on a daily basis for both reporting currencies.

How should my table structure look, and how do i set it up in AS so this works? Ive been fiddeling with this for AGES, getting M:1 conversions to work but not this.

Any help appreciated.

Maybe this thread on currency conversions can help you?

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=151917&SiteID=1

>>

11-29-2005, 8:29 PM UTC

Michael Barrett Jensen

Posts 41

Measure expressions and currency conversion
Was this post helpful ?

OK - here is my scenario... I have a fact table containing one measure: Sales. The values of the measure are stored in the same currency (DKK) for all records. In another fact table I have exchange rates for four different currencies per day for a 15 year period (approx. 22000 records). I have built a cube around these fact tables containing two measure groups.

Measure Group 1
Measure: Sales
Dimensions: Time, Product, Company

Measure Group 2
Measure: Exchange rate
Dimensions: Time, Currency

The only shared dimension between the two measure group is thus Time... The currency dimension contains one attribute hierarchy (CurrencyCode) with IsAggregatable set to False and DefaultMember set to DKK.

For the measure Sales, I have created a measure expression like this:

[Measures].[Sales] / [Measures].[Currency]

I thought this should work - i.e. give me the opportunity to select any of the four available currencies and have the value of Sales displayed accordingly. However - it does not. Instead my Sales measures is multiplied by 4, which corresponds to the number of currencies for which I have exchange rates in my exchange rate fact table. Why is this? Am I not modelling the scenario correctly?

I hope someone can help, since this really puzzles me!

---

Michael

11-30-2005, 12:06 PM UTC

Chris Webb

Posts 31

Answer Re: Measure expressions and currency conversion
Was this post helpful ?

Hi Michael,

I think I might know what's going on here: you need to give your Currency dimension a many-to-many relationship with Measure Group 1 using Measure Group 2 as the intermediate measure group. Does this work?

Regards,

Chris

---

Blog at http://spaces.msn.com/members/cwebbbi/

>>

Currency conversion

Hi all,
I have an application based in Germany where the currency is the following
format: #.###,00
Users enter values in the above format but SQL Server requires the standard
format: #,###.00
Is there a way that I can tell SQL Server to convert formats automatically
or should I do the conversion in my code?
Regards,
IvanIvan Debono wrote:

> Hi all,
> I have an application based in Germany where the currency is the following
> format: #.###,00
> Users enter values in the above format but SQL Server requires the standar
d
> format: #,###.00
> Is there a way that I can tell SQL Server to convert formats automatically
> or should I do the conversion in my code?
> Regards,
> Ivan
Applications can do better formating of data than SQL Server.
Regards
Amish Shah

Cube throws an error while browsing

Hi,
One of cubes when browsed through proclarity showed
nothing. To debug the same, I browsed through analysis
services and the values were "#ERR". There was no
change to the structure of the cube. The cube
processing log shows that the cube was refreshed
successfully.
I then again reprocessed the cube in "Refresh" mode and
it worked fine.
Any clue of why had this happen to just one of the cube?
I was fortunate that this happenned to a smaller cube,
otherwise I would have been to a toss?
Any advice?
Thanks,
Lakshman.
if double click on #ERR, what error you see?
Wei Zhang
Microsoft OLAP Support

Cube throws an error while browsing

Hi,
One of cubes when browsed through proclarity showed
nothing. To debug the same, I browsed through analysis
services and the values were "#ERR". There was no
change to the structure of the cube. The cube
processing log shows that the cube was refreshed
successfully.
I then again reprocessed the cube in "Refresh" mode and
it worked fine.
Any clue of why had this happen to just one of the cube?
I was fortunate that this happenned to a smaller cube,
otherwise I would have been to a toss?
Any advice?
Thanks,
Lakshman.if double click on #ERR, what error you see?
Wei Zhang
Microsoft OLAP Support

csv import which runs stored procedure

Are there any tools which allow you to take a comma delimted file and run a stored procedure for each line in the file, passing the values in the file as parameters? i.e.

CREATE PROCEDURE add_user
@.name varchar(20),
@.first_name varchar(20),
@.last_name varchar(20)
AS
/* do some lookups */
/* validate input */
insert into users (name, first_name, last_name) values (@.name, @.first_name, @.last_name)

Then a comma delimited file such as
bpeikes,Benjamin,Peikes
rbobly,Robert,Bobly
trichard,Tom,Richards

I need a tool which can execute the stored procedure once for each line in the file. I could write one myself, but I'd like to know if any of the tools which come with SQL Server will handle it.

My preferred method would be to first load the csv file data into a 'staging' table, and then have a procedure that validates, cleanses, massages, and transfers the data to the working table(s).

And that can easily be accomplished using a SQL Agent Job, scheduled to run 'on schedule', or available to run 'as needed'.

|||

This isn't a scheduled job, it's something that I'd like to have as a tool for cases where I need to "quickly" run a stored procedure in batches. It's not necessarily about just importing data.

For instance, one might have two procedures remove_user and add_user which do more than just add the records to a single table. I don't want to have a separate staging tables for each type of "batch" work that I want to do.

I was hoping that there was a utility like bcp that would allow you to execute a SP for each line.

|||

You could add a trigger to your target table that calls your stored procedure, bcp the data into the target table and it should trigger storedproc calls for each insert.

Hope that helps,

John

|||

bpeikes wrote:

This isn't a scheduled job, it's something that I'd like to have as a tool for cases where I need to "quickly" run a stored procedure in batches. It's not necessarily about just importing data.

For instance, one might have two procedures remove_user and add_user which do more than just add the records to a single table. I don't want to have a separate staging tables for each type of "batch" work that I want to do.

I was hoping that there was a utility like bcp that would allow you to execute a SP for each line.

Maybe you should use the sql server integration services SSIS

instead