sql nulls slow: values

Showing posts with label values. Show all posts

Sunday, March 25, 2012

ASP...

Let say I pass values from first page to second page using a form, how can I
retain the value in the corresponding area (text, radio button, texarea, dro
p
down menu)in the first page when i press the back button on the second page.
--
regards seowhuaYou've posted to the wrong newsgroup - this is for SQL Server, not ASP
programming. I'd try posting your question to one of the
microsoft.public.dotnet.framework.aspnet.* newsgroups. (Ditto for your
"ASP Calendar..." post too.)
*mike hodgson*
blog: http://sqlnerd.blogspot.com
seowhua wrote:

>Let say I pass values from first page to second page using a form, how can
I
>retain the value in the corresponding area (text, radio button, texarea, dr
op
>down menu)in the first page when i press the back button on the second page
.
>sql

Monday, March 19, 2012

ascii values

hi guys,

i want to know hoe to get ascii values in a astored procedure.

the senario is i am reading a character and checking whether it is a special character or not.

if it is a special character the database does the further operations.

i have written the program in C#, but not able to use it in stored procedure

i do not know how to read the "ascii value" in Transact sql.

coding is all what i do, coding is all what i want to do.

Use the ascii function:

declare @.char char(1)

set @.char = 'A'

select ascii(@.char)

set @.char = ''

select ascii(@.char)

Returns:

--
65

--
233

Friday, February 24, 2012

ArrayList -> System.Object-Variable

Hi,

I have a custom task to execute a package. This task sets dynamically the values of the child package at runtime before execution of the child.

Everything works fine, as long as Im not trying to use an Object Variable in the child package, trying to fill with an ArrayList. Then, during package validation at runtime, the Properties of the custom task have NULL-Values. I don't know why all Properties got NULL or 0 (int), only adding an ArrayList-Property to the Task-Code.

The ArrayList gets initialized in the constructor of the Task. Values to add in execute(). But the process never gets to the execute()-Method, due to my check in validate() for null Values. So the List is initialized but empty.

Anyone an idea, maybe I'm doing something wrong at all. Anybody experiences with Object-Variables in Packages? A HowTo?

Thanks a lot

Thorsten

When you say you have an System.Object variable, are you referring to an SSIS variable of type Object, or a .NET variable/property of type System.Object?

Also, to get the picture correct. It sounds like this custom task is similar to an Execute Package Task, except that it sets the "values" of the child package at run-time. I assume you're familar with parent package configurations for non-object variables. Anyway, when you say "dynamically sets the values " are you referring literally to properties, as in pkg.SomeAttribute, or variables, as in pkg.Variables...?|||

Hi,

to get some light into the deep darkness I provided. Maybe, man should read over and over and over a post to provide all information :-). Sorry.

I refer to a SSIS Variable of Type Object. The task is similar to a execute package task, except of the UI, which provides a user friendly (non-technics :-)) GUI to set some properties of the task. These properties control the behavior of the package the task executes at runtime.

Yes I'm familiar with package configurations, but they need variables, one can change without the ability to know they link to a special task. Not so with properties, cos you have to explicitit mark the task to change them.

At runtime, I'm setting the variables of the package I wanna execute, by

Variables variables = null;
variableDispenser.LockOneForWrite(name, ref variables);
variables[0].Value = value;
variables.Unlock();

Everything works as expected, as long as I'm not trying to set a SSIS-Object Variable with an ArrayList. Then "NONE" of the vars gets setted.

Array of Value

I'm learning T-SQL, and I want to know what are the various ways to represent an array of values. For example, I might like to have an array of record IDs, which I feed to an "IN" clause. My guess is that such an "array" would be implemented by using a RecordSet. If yes, thats good - but what are the other ways (if any)? If no, then what other kinds of variables or parameters allow me to do something like that?You can use subquery for that like in:

WHERE SomeColumn IN (SELECT SomeColumn From SomeTable)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Search the forums for "split function". You can then pass values into a stored procedure with a varchar like "2,34,67,98" and have that string split into a set that can be used in an IN clause. These split functions basically break the string up and puts the CSVs into a temporary table and then (similar to the above suggestion) selects the values from the table.

|||But what about doing a select that has results stored in a (single-column) RecordSet? I've not tried it, but can't that RecordSet be passed around as a parameter, and then fed "as is" into an "IN" clause? Is that not a common/popular thing to do? If not, why not?|||

>>Is that not a common/popular thing to do?<<

Funny way to put this. Common? Yes. Popular? Yes. Natively supported in SQL Server? Nope.

The "classic" paper on the subject is here: http://www.sommarskog.se/arrays-in-sql.html

You can do it with a table variable, but sadly, you cannot pass this around as a parameter.

Thursday, February 16, 2012

Arithmetic overflow error converting numeric to data type numeric

Guys

I'm getting the above when trying to populate a variable. The values in question are :
@.N = 21
@.SumXY = -1303765191530058.2251000000
@.SumXSumY = -5338556963168643.7875000000

When I run, SELECT (@.N * @.SumXY) - (@.SumXSumY * @.SumXSumY) in QA I get the result OK which is -28500190448996439680147097583285.072256 ie 32 places to left of decimal and 6 to the right
When I try the following ie to populate a variable with that value I get the error -
SELECT R2Top = (@.N * @.SumXY) - (@.SumXSumY * @.SumXSumY)@.R2Top is NUMERIC (38, 10)

Any ideas ??Sorry - did a typo - problem code is
SELECT @.R2Top = (@.N * @.SumXY) - (@.SumXSumY * @.SumXSumY)
not
SELECT R2Top = (@.N * @.SumXY) - (@.SumXSumY * @.SumXSumY)|||Try:
SELECT R2Top = CAST((@.N * @.SumXY) - (@.SumXSumY * @.SumXSumY) as NUMERIC (38, 10))|||Thanks blindman but still same error.

If I change data type of @.R2Top to FLOAT I get no error but aren't there accuracy issues with FLOAT ? I have further calculations to perform in the code and the accuracy is very important|||What are the datatypes for @.N, @.SumXY, and @.SumXSumY?|||OK. Here is your problem. numeric(38, 10) only leaves you 28 digits to the left of the decimal. Your problem requires 32 digits to the left of the decimal.

This code works:declare @.N int
declare @.SumXY decimal(26, 10)
declare @.SumXSumY decimal(26, 10)
declare @.R2Top numeric(38, 6)

set @.N = 21
set @.SumXY = -1303765191530058.2251000000
set @.SumXSumY = -5338556963168643.7875000000

select @.R2Top = (@.N * @.SumXY) - (@.SumXSumY * @.SumXSumY)
select @.R2Top|||Thanks blindman

Monday, February 13, 2012

Area Chart - urgent Help

Area Chart - Is there a bug?

I have an Area Chart with four different values plotted on it, as 25th
Percentile, Median value, 75th Percentile and the Customer data.
Now that with SP1, we can specify FILL color, it has become very useful on
this Area chart. (mind you this is Simple Area chart).
However, in certain cases where the customer data completely falls above say
25th Percentile, the whole FILL area of 25th Percentile is overlapped with
the Customer data (and overtaken by Customer FIll color)
SO, I used for customer data, the background color as <Transparent>.
While chossing color and borders etc, the image that is brought up in the
<Layout> window matches with what selection I've made. However, in the
<Preview> window, the Chart takes GREEN fill-color (mind you, I've chosen
Transaparent color) and thus washes away the area for 25th Percentile
(considering the situation wher the customer data values fall above the 25th
Percentile)
jfyi: The palette I've chosen in this case is <Default> However, I've
already tried with all supported palettes..! They all result into some FILL
color for my customer data.
I do not understand, why is the display in <Preview Window> any different
from the <Layout window> '
The other strategy I've tried is, to plot Customer data as a solid black
line, but seems that feature is not supported (<Plot data as a line>), as
well.
Pls. guide.Several comments:
* "why is the display in <Preview Window> any different from the <Layout
window>"?
The layout window will not execute your queries to retrieve the datasets. It
will use random data to give you an approximation of how the chart will look
at runtime. In addition in layout mode expressions cannot be executed (e.g.
the expression result might depend on the dataset).
* Black line:
You need to use an expression (i.e. ="Black") in this particular case. Due
to a bug, the report designer does not write a constant chart color of Black
into the RDL-file.
* Combining area charts with line charts by using a "Transparent" fill
color:
Based on the RDL specification, Transparent is not a valid color. Therefore
you cannot use Transparent right now for the color property. Please try this
for now:
Color: e.g. White
Gradient: TopBottom
EndColor: ="Transparent"
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kam" <Kam@.discussions.microsoft.com> wrote in message
news:A8AF6608-E6B1-4B44-851E-3F43E0FBCD62@.microsoft.com...
> I have an Area Chart with four different values plotted on it, as 25th
> Percentile, Median value, 75th Percentile and the Customer data.
> Now that with SP1, we can specify FILL color, it has become very useful on
> this Area chart. (mind you this is Simple Area chart).
> However, in certain cases where the customer data completely falls above
say
> 25th Percentile, the whole FILL area of 25th Percentile is overlapped with
> the Customer data (and overtaken by Customer FIll color)
> SO, I used for customer data, the background color as <Transparent>.
> While chossing color and borders etc, the image that is brought up in the
> <Layout> window matches with what selection I've made. However, in the
> <Preview> window, the Chart takes GREEN fill-color (mind you, I've chosen
> Transaparent color) and thus washes away the area for 25th Percentile
> (considering the situation wher the customer data values fall above the
25th
> Percentile)
> jfyi: The palette I've chosen in this case is <Default> However, I've
> already tried with all supported palettes..! They all result into some
FILL
> color for my customer data.
> I do not understand, why is the display in <Preview Window> any different
> from the <Layout window> '
> The other strategy I've tried is, to plot Customer data as a solid black
> line, but seems that feature is not supported (<Plot data as a line>), as
> well.
> Pls. guide.
>

Sunday, February 12, 2012

Are these two statements equivalent?

Both return the same records in the same order, with the same values, and I
think I'm right, but I just wanted to get some other eyeballs on this so I
can win the argument we're having here, because mine (the second one) is
more than twice as fast.
-- Uses a WHERE...IN(...) and subquery on one of the joined tables
SELECT DISTINCT ss.SampleSourceKey, dbo.ParseFilename(ss.Filename) AS
Filename
FROM SampleSource ss WITH (NOLOCK)
INNER JOIN CLMR WITH (NOLOCK)
ON clmr.SampleSourceKey = ss.SampleSourceKey
WHERE ss.SampleSourceKey IN (
SELECT clmr.SampleSourceKey
FROM CLMR WITH (NOLOCK)
WHERE CountryKey IS NULL
)
ORDER BY ss.SampleSourceKey
-- Uses a column from one of the joined tables.
SELECT DISTINCT ss.SampleSourceKey, dbo.ParseFilename(ss.Filename) AS
Filename
FROM SampleSource ss WITH (NOLOCK)
INNER JOIN CLMR WITH (NOLOCK)
ON clmr.SampleSourceKey = ss.SampleSourceKey
WHERE clmr.CountryKey IS NULL
ORDER BY ss.SampleSourceKey
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave Mustanethe usual: if this query
SELECT clmr.SampleSourceKey
FROM CLMR WITH (NOLOCK)
WHERE CountryKey IS NULL
can ever return a null, then the first query will return nothing...|||> the usual: if this query
> SELECT clmr.SampleSourceKey
> FROM CLMR WITH (NOLOCK)
> WHERE CountryKey IS NULL
> can ever return a null, then the first query will return nothing...
Ooo I had forgotten that, but we're safe here, because clmr.SampleSourceKey
is required.
Thanks for the brainpick!
--
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave Mustane