Help with COUNT in SELECT Statement

Could someone assist with getting the count function working correctly
in this example please. I know the count function will return all rows
that do not have null values, but in this case I want to count all the
rows except those with a zero sale price, (which are unsold).

The table shows works offered for sale by an artist, with a positive
figure under SalePrice indicating a sale, and I want to count the
number sold by each auction house, and sum the sale price by auction
house. The table is as follows:

NameSalePriceAuction
Dowling12000Christies
Dowling 0Christies
Dowling10000Christies
Dowling 0Christies
Dowling 0Christies
Dowling 6000Sothebys
Dowling 0Sothebys
Dowling 0Sothebys
Dowling 8000Sothebys
Dowling 0Sothebys
Dowling 0Sothebys
Dowling 0Sothebys

When I run this query:

SELECT MyTable.Name, Count(MyTable.Name) AS [Number],
Sum(MyTable.SalePrice) AS TotalSales, MyTable.Auction
FROM MyTable
GROUP BY MyTable.Name, MyTable.Auction
HAVING (((MyTable.Name)="Dowling") AND ((Sum(MyTable.SalePrice))>0));

The results are:

NameNumberTotalSalesAuction
Dowling 5 22000 Christies
Dowling 7 14000 Sothebys

The TotalSales is correct, but the Number (Count) is incorrect, as the
rows with zero were also included. The results should be:

NameNumberTotalSalesAuction
Dowling 2 22000 Christies
Dowling 2 14000 Sothebys

How do I prevent the unsolds (zeros) being counted?

Thanks in advance,

John FurphyAssuming you also want to exclude NULL saleprices (if any):

SELECT name, COUNT(NULLIF(saleprice,0)) AS number,
SUM(saleprice) AS totalsales, auction
FROM MyTable
GROUP BY name, auction
HAVING name='Dowling' AND SUM(saleprice)>0;

--
David Portas
----
Please reply only to the newsgroup
--|||"John Furphy" <johnfurphy@.a1.com.au> wrote in message
news:4ce579e8.0312010326.115691db@.posting.google.c om...
> Could someone assist with getting the count function working correctly
> in this example please. I know the count function will return all rows
> that do not have null values, but in this case I want to count all the
> rows except those with a zero sale price, (which are unsold).
> The table shows works offered for sale by an artist, with a positive
> figure under SalePrice indicating a sale, and I want to count the
> number sold by each auction house, and sum the sale price by auction
> house. The table is as follows:
> Name SalePrice Auction
> Dowling 12000 Christies
> Dowling 0 Christies
> Dowling 10000 Christies
> Dowling 0 Christies
> Dowling 0 Christies
> Dowling 6000 Sothebys
> Dowling 0 Sothebys
> Dowling 0 Sothebys
> Dowling 8000 Sothebys
> Dowling 0 Sothebys
> Dowling 0 Sothebys
> Dowling 0 Sothebys
> When I run this query:
> SELECT MyTable.Name, Count(MyTable.Name) AS [Number],
> Sum(MyTable.SalePrice) AS TotalSales, MyTable.Auction
> FROM MyTable
> GROUP BY MyTable.Name, MyTable.Auction
> HAVING (((MyTable.Name)="Dowling") AND ((Sum(MyTable.SalePrice))>0));
> The results are:
> Name Number TotalSales Auction
> Dowling 5 22000 Christies
> Dowling 7 14000 Sothebys
> The TotalSales is correct, but the Number (Count) is incorrect, as the
> rows with zero were also included. The results should be:
> Name Number TotalSales Auction
> Dowling 2 22000 Christies
> Dowling 2 14000 Sothebys
> How do I prevent the unsolds (zeros) being counted?
>
> Thanks in advance,
> John Furphy

SELECT "Name",
COUNT(*) AS Number,
SUM(SalesPrice) AS TotalSales,
Auction
FROM MyTable
WHERE SalesPrice > 0
GROUP BY "Name", Auction

Regards,
jag

Help with converting code: VB code in SQL Server 2000->Visual Studio BI 2005

Hi all--I'm trying to convert a function which I inherited from a SQL Server 2000 DTS package to something usable in an SSIS package in SQL Server 2005. Given the original code here:

Function Main()

on error resume next

dim cn, i, rs, sSQL

Set cn = CreateObject("ADODB.Connection")

cn.Open "Provider=sqloledb;Server=<server_name>;Database=<db_name>;User ID=<sysadmin_user>;Password=<password>"

set rs = CreateObject("ADODB.Recordset")

set rs = DTSGlobalVariables("SQLstring").value

for i = 1 to rs.RecordCount

sSQL = rs.Fields(0).value

cn.Execute sSQL, , 128'adExecuteNoRecords option for faster execution

rs.MoveNext

Next

Main = DTSTaskExecResult_Success

End Function

This code was originally programmed in the SQL Server ActiveX Task type in a DTS package designed to take an open-ended number of SQL statements generated by another task as input, then execute each SQL statement sequentially. Upon this code's success, move on to the next step. (Of course, there was no additional documentation with this code. :-)

Based on other postings, I attempted to push this code into a Visual Studio BI 2005 Script Task with the following change:

public Sub Main()

...

Dts.TaskResult = Dts.Results.Success

End Class

I get the following error when I attempt to compile this:

Error 30209: Option Strict On requires all variable declarations to have an 'As' clause.

I am new to Visual Basic, so I'm on a learning curve here. From what I know of this script:
- The variables here violate the new Option Strict On requirement in VS 2005 to declare what type of object your variable is supposed to use.

- I need to explicitly declare each object, unless I turn off the Option Strict On (which didn't seem recommended, based on what I read).

Given this statement:

dim cn, i, rs, sSQL

I'm looking at "i" as type Integer; rs and sSQL are open-ended arrays, but can't quite figure out how to read the code here:

Set cn = CreateObject("ADODB.Connection")

cn.Open "Provider=sqloledb;Server=<server_name>;Database=<db_name>;User ID=<sysadmin_user>;Password=<password>"

set rs = CreateObject("ADODB.Recordset")

This code seems to create an instance of a COM component, then pass provider information and create the recordset being passed in by the previous task, but am not sure whether this syntax is correct for VS 2005 or what data type declaration to make here. Any ideas/help on how to rewrite this code would be greatly appreciated!

Option Strict controls what we call "late binding." If option strict is ON, then what happens is that the compiler will generate code that takes full advantage of the type system in the CLR. This means that each variable has a compile-time type (hence you need to specify the "As" clause), and because each variable has a compile-time type, the IL that the compiler generates is fast (for example, method calls are translated to a "call" or a "callvirt" IL instruction).

If you turn option strict OFF, this is what is known as the "late binding" mode. There is no equivalent in C#. What the late binding mode allows you to do is to leave variables specified as "object" (and if you omit the "As" clause, the type is assumed to be "object"). The compiler, because it does not know what the type is, needs to generate code that calls the Visual Basic Runtime helpers to execute method calls.

Because of this, the compiler cannot provide checks for you during compile time. For example, you can do something like this:

dim o = CreateObject("ADODB.Connection")
o.foo()

The compiler will not check whether the "foo" method exists, because it doesn't know the type of o. And if "foo" indeed does not exist, you will get a runtime exception. Runtime exceptions are much harder to debug and diagnose; this is probably why people advise against turning option strict off.

However, in certain scenarios, such as yours, I believe that turning option strict off will make life easier. The compiler may not be as helpful, but it will make your code easier to understand.

The CreateObject calls are indeed creating COM objects. Your analysis of what the code is doing is correct. The .NET equivalent of the ADODB.Connection and ADODB.Recordset classes live in the "System.Data" namespace. Take a look here for some information: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemData.asp

I hope this helps - feel free to post any more questions or issues that you run into here :)|||

Hi Timothy--Thanks for the analysis, and the suggestion of turning this option off. How do I do that for just this package? I read somewhere (don't remember the URL at the moment) that I had to turn this option off through a configuration parameter in a file ending in .vbproj. However, the ultimate goal is store this package in the SQL Server database and so give it the ability to schedule it through the SQL Server Agent. That doesn't seem feasible if I have to store connection information externally, which is a security risk...and I can't find any files with that name anyway.

I tried to add the following into the function:

Option Strict Off

That caused another compilation error, unless I'm missing something else. Any ideas on how to turn the Option Strict off for just this task?

|||

Better to leave Option Strict On.

Dim i as Integer

Dim cn as ADODB.Connection

Dim rs as ADODB.Recordset

These declarations will require a reference in the Script task project to the ADODB primary interop assembly.

-Doug

|||What was the error you get with Option Strict Off?

The finest level of granularity is to turn option strict off at the file level - put this at the top of the file:
Option Strict Off

If you pursue any solution where you need to turn on/off option strict, it's best to at the very least put the code you want option strict off for in another file.

I agree too that Option Strict should typically be left On, but there are situations where turning it Off can make it easier to code, at the expenses that I mentioned above.|||

Thanks all--These prior posts helped me work around the problem. It's appreciated--problem solved!

- Jonathan

Help with converting code: VB code in SQL Server 2000->Visual Studio BI 2005

Hi all--I'm trying to convert a function which I inherited from a SQL Server 2000 DTS package to something usable in an SSIS package in SQL Server 2005. Given the original code here:

Function Main()

on error resume next

dim cn, i, rs, sSQL

Set cn = CreateObject("ADODB.Connection")

cn.Open "Provider=sqloledb;Server=<server_name>;Database=<db_name>;User ID=<sysadmin_user>;Password=<password>"

set rs = CreateObject("ADODB.Recordset")

set rs = DTSGlobalVariables("SQLstring").value

for i = 1 to rs.RecordCount

sSQL = rs.Fields(0).value

cn.Execute sSQL, , 128'adExecuteNoRecords option for faster execution

rs.MoveNext

Next

Main = DTSTaskExecResult_Success

End Function

This code was originally programmed in the SQL Server ActiveX Task type in a DTS package designed to take an open-ended number of SQL statements generated by another task as input, then execute each SQL statement sequentially. Upon this code's success, move on to the next step. (Of course, there was no additional documentation with this code. :-)

Based on other postings, I attempted to push this code into a Visual Studio BI 2005 Script Task with the following change:

public Sub Main()

...

Dts.TaskResult = Dts.Results.Success

End Class

I get the following error when I attempt to compile this:

Error 30209: Option Strict On requires all variable declarations to have an 'As' clause.

I am new to Visual Basic, so I'm on a learning curve here. From what I know of this script:
- The variables here violate the new Option Strict On requirement in VS 2005 to declare what type of object your variable is supposed to use.

- I need to explicitly declare each object, unless I turn off the Option Strict On (which didn't seem recommended, based on what I read).

Given this statement:

dim cn, i, rs, sSQL

I'm looking at "i" as type Integer; rs and sSQL are open-ended arrays, but can't quite figure out how to read the code here:

Set cn = CreateObject("ADODB.Connection")

cn.Open "Provider=sqloledb;Server=<server_name>;Database=<db_name>;User ID=<sysadmin_user>;Password=<password>"

set rs = CreateObject("ADODB.Recordset")

This code seems to create an instance of a COM component, then pass provider information and create the recordset being passed in by the previous task, but am not sure whether this syntax is correct for VS 2005 or what data type declaration to make here. Any ideas/help on how to rewrite this code would be greatly appreciated!

Option Strict controls what we call "late binding." If option strict is ON, then what happens is that the compiler will generate code that takes full advantage of the type system in the CLR. This means that each variable has a compile-time type (hence you need to specify the "As" clause), and because each variable has a compile-time type, the IL that the compiler generates is fast (for example, method calls are translated to a "call" or a "callvirt" IL instruction).

If you turn option strict OFF, this is what is known as the "late binding" mode. There is no equivalent in C#. What the late binding mode allows you to do is to leave variables specified as "object" (and if you omit the "As" clause, the type is assumed to be "object"). The compiler, because it does not know what the type is, needs to generate code that calls the Visual Basic Runtime helpers to execute method calls.

Because of this, the compiler cannot provide checks for you during compile time. For example, you can do something like this:

dim o = CreateObject("ADODB.Connection")
o.foo()

The compiler will not check whether the "foo" method exists, because it doesn't know the type of o. And if "foo" indeed does not exist, you will get a runtime exception. Runtime exceptions are much harder to debug and diagnose; this is probably why people advise against turning option strict off.

However, in certain scenarios, such as yours, I believe that turning option strict off will make life easier. The compiler may not be as helpful, but it will make your code easier to understand.

The CreateObject calls are indeed creating COM objects. Your analysis of what the code is doing is correct. The .NET equivalent of the ADODB.Connection and ADODB.Recordset classes live in the "System.Data" namespace. Take a look here for some information: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemData.asp

I hope this helps - feel free to post any more questions or issues that you run into here :)|||

Hi Timothy--Thanks for the analysis, and the suggestion of turning this option off. How do I do that for just this package? I read somewhere (don't remember the URL at the moment) that I had to turn this option off through a configuration parameter in a file ending in .vbproj. However, the ultimate goal is store this package in the SQL Server database and so give it the ability to schedule it through the SQL Server Agent. That doesn't seem feasible if I have to store connection information externally, which is a security risk...and I can't find any files with that name anyway.

I tried to add the following into the function:

Option Strict Off

That caused another compilation error, unless I'm missing something else. Any ideas on how to turn the Option Strict off for just this task?

|||

Better to leave Option Strict On.

Dim i as Integer

Dim cn as ADODB.Connection

Dim rs as ADODB.Recordset

These declarations will require a reference in the Script task project to the ADODB primary interop assembly.

-Doug

|||What was the error you get with Option Strict Off?

The finest level of granularity is to turn option strict off at the file level - put this at the top of the file:
Option Strict Off

If you pursue any solution where you need to turn on/off option strict, it's best to at the very least put the code you want option strict off for in another file.

I agree too that Option Strict should typically be left On, but there are situations where turning it Off can make it easier to code, at the expenses that I mentioned above.|||

Thanks all--These prior posts helped me work around the problem. It's appreciated--problem solved!

- Jonathan

Help with assigning variables to from a SQL query

I've reconfigured Microsoft's IBS Store shopping cart to function within a small e-commerce website. What I am trying to do is to modify the code slightly in order to use a third party credit card processing center. The situation is this: once the customer clicks the final "check out" button, a stored procedure writes all of the product ordering information into the database. I, then, capture what they're wanting to purchase with the following SQL statement:

Dim strSQL as String = "Select orderID, modelNumber from orderDetails" & _
"where CustomerID = " & User.Identity.Name & _
"And orderid = (SELECT MAX(orderid)FROM orderDetails" & _
"where CustomerID = " & User.Identity.Name & ")"

What I would like to do is assign specific values to variables based off of the above query. For example:

Dim orderItem as String = (all of the modelNumbers from the query)
Dim orderIdItem as String = (all of the orderIDs from the query)

How do I do this?? Any help is much appreciated! Thanks in advance.

RonI'm not fluent in VB, but try to grasp the outline of the code :)

[code]
Dim myConnection As SqlConnection = New SqlConnection("..my connection string..")
Dim myCommand As SqlCommand = New SqlCommand("the query...")
myCommand.Connection = myConnection
myConnection.Open()
Dim myReader As SqlDataReader = myCommand.ExecuteReader()

'String Builder objects speed up performance, as strings are immutable.
Dim modelNumbers As System.Text.StringBuilder = New System.Text.StringBuilder
Dim orderID As System.Text.StringBuilder = New System.Text.StringBuilder

' Go through all the rows of the query
While (myReader.Read())
modelNumbers.Append(myReader.GetString(0)) 'Add the ModelNumber of the row to the string
orderID.Append(myReader.GetString(1)) ' Add the OrderID of the row to the string
End While

myReader.Close()
myConnection.Close()
[/code]

HTH|||Thanks for your help.

The code creates this error: "Specified cast is not valid", from the following line:

modelNumbers.Append(myReader.GetString(0)) 'Add the ModelNumber of the row to the string

Any ideas?

Again, thanks for your help!

Ron|||The field is either null or its a byte field or something.

I would check if its null first before adding it to the string.

HTH
Tony|||It doesn't come back null. To test this I simply bound the query data to a datagrid in order to display the information on the page. There are no null items; only data...as expected|||Try

modelNumbers.Append(myReader(0).ToString())

Does this help|||This worked beautifully.
Thank you!

help with a User-Defined function to return a string from multiple records

I need some help with writing a User-Defined function in SQL Server 2000.
I would like to return a space-delimited string, which contains the column
data of several records from a table.
Here's an example:
table_fruit
id textid
-- --
1 APPLE
2 BANANA
4 ORANGE
8 PEAR
16 PLUM
My SQL query string uses a bitwise-AND (&) to determine which records to
return.
SELECT textid FROM table_fruit WHERE ([id] & @.param_fruits) > 0
So, for example, if I pass in the parameter @.param_fruits = 13, then I get
the following records back:
APPLE
ORANGE
PEAR
What I'd like to have is a User-Defined function that returns the data in a
concatenated space-delimited string like this:
APPLE ORANGE PEAR
I need help with writing this function. Thanks very much.To get a space delimited string, you should modify the statement as follows:
=====
-- Your function declarations etc
DECLARE @.returnString VARCHAR(8000)
SET @.returnString = ''
SELECT @.returnString = @.returnString + ' ' + textid
FROM table_fruit WHERE ([id] & @.param_fruits) > 0
RETURN (@.returnString)
=====
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"Scott A. Keen" <noreply@.scottkeen.com> wrote in message
news:%23bivyHrNGHA.2036@.TK2MSFTNGP14.phx.gbl...
>I need some help with writing a User-Defined function in SQL Server 2000.
> I would like to return a space-delimited string, which contains the column
> data of several records from a table.
> Here's an example:
> table_fruit
> id textid
> -- --
> 1 APPLE
> 2 BANANA
> 4 ORANGE
> 8 PEAR
> 16 PLUM
>
> My SQL query string uses a bitwise-AND (&) to determine which records to
> return.
> SELECT textid FROM table_fruit WHERE ([id] & @.param_fruits) > 0
> So, for example, if I pass in the parameter @.param_fruits = 13, then I get
> the following records back:
> APPLE
> ORANGE
> PEAR
> What I'd like to have is a User-Defined function that returns the data in
> a
> concatenated space-delimited string like this:
> APPLE ORANGE PEAR
> I need help with writing this function. Thanks very much.
>|||In addition, this method is unreliable and should be done on the client
side
As an alternative take a look at Erland's (if I remember well) example
CREATE PROCEDURE get_company_names_inline @.customers nvarchar(2000) AS
SELECT C.CustomerID, C.CompanyName
FROM Northwind..Customers C
JOIN inline_split_me(@.customers) s ON C.CustomerID = s.Value
go
EXEC get_company_names_inline 'ALFKI,BONAP,CACTU,FRANK'
CREATE FUNCTION inline_split_me (@.param varchar(7998)) RETURNS TABLE AS
RETURN(SELECT substring(',' + @.param + ',', Number + 1,
charindex(',', ',' + @.param + ',', Number + 1) -
Number - 1)
AS Value
FROM Numbers
WHERE Number <= len(',' + @.param + ',') - 1
AND substring(',' + @.param + ',', Number, 1) = ',')
SELECT TOP 8000 Number = IDENTITY(int, 1, 1)
INTO Numbers
FROM pubs..authors t1, pubs..authors t2, pubs..authors t3
drop table numbers
drop function inline_split_me
drop proc get_company_names_inline
"SriSamp" <ssampath@.sct.co.in> wrote in message
news:%23mDFDKrNGHA.2336@.TK2MSFTNGP12.phx.gbl...
> To get a space delimited string, you should modify the statement as
> follows:
> =====
> -- Your function declarations etc
> DECLARE @.returnString VARCHAR(8000)
> SET @.returnString = ''
> SELECT @.returnString = @.returnString + ' ' + textid
> FROM table_fruit WHERE ([id] & @.param_fruits) > 0
> RETURN (@.returnString)
> =====
> --
> HTH,
> SriSamp
> Email: srisamp@.gmail.com
> Blog: http://blogs.sqlxml.org/srinivassampath
> URL: http://www32.brinkster.com/srisamp
> "Scott A. Keen" <noreply@.scottkeen.com> wrote in message
> news:%23bivyHrNGHA.2036@.TK2MSFTNGP14.phx.gbl...
>|||Thanks very much! Worked great.
I had done the same query but had not declared the VARCHAR large enough, and
didn't use the SET statement to initialize the variable.
Thanks
Scott
"SriSamp" <ssampath@.sct.co.in> wrote in message
news:%23mDFDKrNGHA.2336@.TK2MSFTNGP12.phx.gbl...
> To get a space delimited string, you should modify the statement as
follows:
> =====
> -- Your function declarations etc
> DECLARE @.returnString VARCHAR(8000)
> SET @.returnString = ''
> SELECT @.returnString = @.returnString + ' ' + textid
> FROM table_fruit WHERE ([id] & @.param_fruits) > 0
> RETURN (@.returnString)
> =====
> --
> HTH,
> SriSamp
> Email: srisamp@.gmail.com
> Blog: http://blogs.sqlxml.org/srinivassampath
> URL: http://www32.brinkster.com/srisamp
> "Scott A. Keen" <noreply@.scottkeen.com> wrote in message
> news:%23bivyHrNGHA.2036@.TK2MSFTNGP14.phx.gbl...
column
get
in
>sql

help with a 'simple' sql-statement

Hi there,
i have the following problem: in a database i have to detect if a certain
column in a certain table exists. I
use the function 'ColumnAlreadyExists'. If not i have to create that column
and then i have to fill this
column. This is my code:
****************************************
*********
-- Function to check whether a specific column exists in a table
CREATE FUNCTION ColumnAlreadyExists(@.TableName NVARCHAR(128),@.ColumnName
NVARCHAR(128))
RETURNS INTEGER --Returns 0 if column does not exist. Returns 1 if column
exists.
AS
BEGIN
--See if the Table already contains the column.
IF EXISTS
(SELECT * FROM SysObjects O INNER JOIN SysColumns C ON O.ID=C.ID
WHERE ObjectProperty(O.ID,'IsUserTable')=1
AND O.Name=@.TableName
AND C.Name=@.ColumnName)
RETURN 1
--Table does not contain the column.
RETURN 0
END
GO
-- Add column DataId to table Data if necessary
IF .dbo.ColumnAlreadyExists('data','dataid')=0
BEGIN
ALTER TABLE [data]
ADD [dataid] [int] NULL
-- Fill column DataId for each row in the Data table
DECLARE @.index1 int, @.index2 int, @.datapk int
DECLARE DataID_Cursor CURSOR FOR
SELECT index1, index2, datapk
FROM data
OPEN DataID_Cursor
FETCH NEXT FROM DataID_Cursor
INTO @.index1, @.index2, @.datapk
WHILE @.@.FETCH_STATUS = 0
BEGIN
UPDATE Data
set dataid = ( index1 * index2 )
WHERE datapk = @.datapk
FETCH NEXT FROM DataID_Cursor
INTO @.index1, @.index2, @.datapk
END
END
DROP FUNCTION ColumnAlreadyExists
****************************************
*********
The problem now is that when i want to fill the dataid column it does not
exists yet, cause there was no GO
yet. But when i put a GO between the creating of the column and the filling
of this column i don't know
anymore whether the column existed or not. So my question is: How can i do
the following:
IF .dbo.ColumnAlreadyExists('data','dataid')=0
BEGIN
ALTER TABLE [data]
ADD [dataid] [int] NULL
-- Fill the just created column dataid for each row in the Data table
END
Can anyone help me with this problem?
thanks,
Koert"Koert" <Koert@.discussions.microsoft.com> wrote in message
news:BD268A25-ABFF-4287-B76D-F39A8EB954D9@.microsoft.com...

> Can anyone help me with this problem?
Maybe DECLARE a variable...?|||I already tried that but I can't use that variable after the GO to create th
e
column. I tried:
DECLARE @.result int
SELECT @.result = .dbo.ColumnAlreadyExists('data','dataid')
IF @.result = 0
BEGIN
ALTER TABLE [dta]
ADD [dataid][int] NULL
END
GO
if @.result = 0 <--This one is not declared anymore
BEGIN
--Fill column DataID
END
so this does not work or is there any other way to declare a kind of global
variable'
thanks,
Koert
"Mark Rae" wrote:

> "Koert" <Koert@.discussions.microsoft.com> wrote in message
> news:BD268A25-ABFF-4287-B76D-F39A8EB954D9@.microsoft.com...
>
> Maybe DECLARE a variable...?
>
>|||"Koert" <Koert@.discussions.microsoft.com> wrote in message
news:69895818-6900-4683-B2F5-E85949E48FBF@.microsoft.com...

> so this does not work or is there any other way to declare a kind of
> global
> variable'
Hmm - loathe though I am to suggest it, I think your only option might be to
create a temporary table... Temporary tables are persistent within a
connection, so should survive the GO statement...|||i thought about that to, it seemed to be so easy... The only other way i can
think of is to check if the dataid column is filled in, if not fill it.
thanks for posting,
Koert
"Mark Rae" wrote:

> "Koert" <Koert@.discussions.microsoft.com> wrote in message
> news:69895818-6900-4683-B2F5-E85949E48FBF@.microsoft.com...
>
> Hmm - loathe though I am to suggest it, I think your only option might be
to
> create a temporary table... Temporary tables are persistent within a
> connection, so should survive the GO statement...
>
>

help with a scalare function

i wrote a scalare function-

select name,id

from tableName

where function1(id) / function2(id)>100

but sometimes function2 returns zero so im getting a divide by zero exception.

how can i solve this problem?

thanks in advanced.

? It depends on what do you want for output if function2 returns 0... Here's one way to handle it (return nothing): select name,id from tableName where case function2(id) when 0 then 0 else function1(id) / function2(id) end > 100 ...Keep in mind that using functions in this way is going to force table scans, thereby creating some serious performance issues. You might want to reevaluate the problem you're solving here. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <ppl1@.discussions.microsoft..com> wrote in message news:ea55a515-a0d6-480b-b89a-92cd54cb6d6b@.discussions.microsoft.com... i wrote a scalare function- select name,id from tableName where function1(id) / function2(id)>100 but sometimes function2 returns zero so im getting a divide by zero exception. how can i solve this problem? thanks in advanced.|||ok thanks for the help|||

You can modify your WHERE clause to:

where function1(id) / nullif(function2(id), 0)>100

help with a querie

i need to finish to define this function

create function okJetsHelpFunc
(@.idevent INT)
Returns Table
AS
return select j.*
from jet as j
where j.eventid = @.idevent and abs(dbo.eta(j.id))>4.5 and dbo.pt(j.id)>20.0

GO

right now is working perfectly, but i need to put a condition that return all the jets information if they are 3 jets that fullfil the where statement, and return a null table if not
right now i have this, but it doesnt work

create function okJetsHelpFunc
(@.idevent INT)
Returns Table
AS
if (select count(j.id)
from jet as j
where j.eventid = @.idevent and abs(dbo.eta(j.id))>4.5 and dbo.pt(j.id)>20.0)>=3
return select j.*
from jet as j
where j.eventid = @.idevent and abs(dbo.eta(j.id))>4.5 and dbo.pt(j.id)>20.0

Return null

GO
|||

See if this works:

create function okJetsHelpFunc
(@.idevent INT)
Returns Table
as
return
( select j.*
from jet as j
where j.eventid = @.idevent
and abs(dbo.eta(j.id))>4.5
and dbo.pt(j.id) > 20.0
and ( select count(j.id)
from jet as j
where j.eventid = @.idevent
and abs(dbo.eta(j.id)) > 4.5
and dbo.pt(j.id) > 20.0
) >= 3
)

GO

Help w/aggregate function in Matrix

On a report we have a matrix. The data cell has the following expression:
=iif(Fields!Score.Value=0, "", Fields!Score.Value)
When we run the report, we get the following warning:
The value expression for the textbox 'Score' references a field
outside an aggregate function. Value expressions in matrix cells should be
aggregates, to allow for subtotaling.
What does this mean and how can I resolve it?
The goal it to suppress the display of zero (0). We have tried setting the
format of the data cell to be "#", but the zero is still displayed. So we
have been using expressions like the above to achieve this.
The user creating this report is using the stand-alone C# IDE with Reporting
Services. This warning prevents them from previewing the report. Another
user using VS.NET 2003 is able to preview the report despite the warning.
The report renders on our test reporting server. If we need to just ignore
the warning, how can we get the user using C# to be able to preview the
report?
Thanks,
ChrisMatrix cells are always in the scope of two groupings and you could have
multiple data rows which match the group instance values. Therefore, you
should always use aggregate functions when referencing fields in a matrix
cell (hence, a processing warning gets generated).
If you don't use an explicit aggregate function in the matrix cell, we would
implicitly use the first row's field value. I believe you actually don't
want just the first value, but rather the sum - so you should change the
expression to:
=iif(Sum(Fields!Score.Value)=0, "", Sum(Fields!Score.Value))
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
In your case, I believe you want
"Chris Walls" <chwalls@.community.nospam> wrote in message
news:OVqzX42OFHA.1500@.TK2MSFTNGP09.phx.gbl...
> On a report we have a matrix. The data cell has the following expression:
>
> =iif(Fields!Score.Value=0, "", Fields!Score.Value)
>
> When we run the report, we get the following warning:
>
> The value expression for the textbox 'Score' references a field
> outside an aggregate function. Value expressions in matrix cells should
> be aggregates, to allow for subtotaling.
>
>
> What does this mean and how can I resolve it?
>
>
> The goal it to suppress the display of zero (0). We have tried setting
> the format of the data cell to be "#", but the zero is still displayed.
> So we have been using expressions like the above to achieve this.
>
>
> The user creating this report is using the stand-alone C# IDE with
> Reporting Services. This warning prevents them from previewing the
> report. Another user using VS.NET 2003 is able to preview the report
> despite the warning. The report renders on our test reporting server. If
> we need to just ignore the warning, how can we get the user using C# to be
> able to preview the report?
>
>
> Thanks,
> Chris
>
>

Help using custom VB6 Function in Crystal

I have a function for a date format in VB6 that converts to a Long Integer. I then use the opposing function below to convert to a readable date. Can anyone help me implement the function in Crystal 10.

Public Function fDateLong(plngDate As Long) ' Gets Date from DB format YYYYMMDD (DB date is Long) ,
Dim sDate, sYYYY, sMM, sDD As String
sDate = Trim(CStr(plngDate)) ' trim a Converted Long Date
sYYYY = Left(sDate, 4) ' get YYYY from left
sMM = Mid(sDate, 5, 2) 'get MM from middle
sDD = Right(sDate, 2) ' get DD from right
sDate = sMM & "/" & sDD & "/" & sYYYY ' reassemble
fDateLong = CDate(sDate) ' Convert to Date

End Function

Right now my date is reporting as " 20060612". Any help in implementing this function in Crystal would be appreciated.What is your expected output?|||I would like to take it from the DB format (long) 20060615 to a date format like either June 15 2006 or even 6-15-2006 (or 15-06-2006).

Thanks for you response.|||Create a formula having this code and drag that in the report

Numbervar y:=0;
Numbervar m:=0;
Numbervar da:=0;

y:=Tonumber(left(replace(totext(20060615 ),",",""),4));
m:=Tonumber(mid(replace(totext(20060615 ),",",""),5,2));
da:=Tonumber(mid(replace(totext(20060615 ),",",""),7,2));

monthname(m)+" "+totext(da,0)+ " "+replace(totext(y,0),",","")|||Madhi,
Appreciate the help but I still have no bloody where to put this?
Can you advise?|||As I told you create new formula. Put that code. Save it. Drag it to the details section|||Madhi,

Great stuff; problem solved.

Thanks very much

Help Using Asymetric/Symetric key in a Scalar UDF

The error message I get is as follows:
Invalid use of side-effecting or time-dependent operator in 'OPEN SYMMETRIC KEY' within a function.
&
Invalid use of side-effecting or time-dependent operator in 'CLOSE SYMMETRIC KEY' within a function.

Here is the code I am trying to implement:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER FUNCTION [dbo].[func_GetSIMSPassPhrase]
(
)
RETURNS varchar(30)
AS
BEGIN
OPEN SYMMETRIC KEY sims_sym_Key DECRYPTION BY ASYMMETRIC KEY sims_asym_key
DECLARE @.GUID UNIQUEIDENTIFIER
SET @.GUID = (SELECT key_guid FROM sys.symmetric_keys WHERE name = 'sims_sym_Key')
DECLARE @.passphrase varchar(30)
SELECT @.passphrase = (SELECT CAST(DecryptByKey(EncField) AS VARCHAR(30)) FROM tblEncryptTest)
CLOSE SYMMETRIC KEY sims_sym_Key
RETURN @.passphrase
END

Anyone have any suggestions? TIA

You cannot use OPEN SYMMETRIC KEY in a function. Write a procedure instead - you can use an OUTPUT argument to return the passphrase. For an example, see http://blogs.msdn.com/lcris/archive/2006/01/13/512829.aspx.

Thanks
Laurentiu

|||

Laurentiu,

I found your article after I posted that message. I am still getting this error though:

SELECT permission denied on object 'symmetric_keys', database 'mssqlsystemresource', schema 'sys'.

Here is what I have done so far. I moved OPEN SYMMETRIC KEY to a stored procedure (listed below):

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[uspPassPhraseGet]
@.pss varchar(30) OUTPUT
AS
BEGIN
SET NOCOUNT ON;
OPEN SYMMETRIC KEY sims_sym_Key DECRYPTION BY ASYMMETRIC KEY sims_asym_key
DECLARE @.GUID UNIQUEIDENTIFIER
SET @.GUID = (SELECT key_guid FROM sys.symmetric_keys WHERE name = 'sims_sym_Key')
SET @.pss = (SELECT CAST(DecryptByKey(EncField) AS VARCHAR(30)) FROM tblEncryptTest)
CLOSE SYMMETRIC KEY sims_sym_Key
END

I granted execute permissions to the role that contains the user I am using to access this stored procedure. I am calling this stored procedure from within another stored procedure to access the encrypted passphrase contained in a table encrypted by the Symmetric/Asymetric keys. See example below:

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[uspFreeFormList]
@.userid int
AS

DECLARE @.pss varchar(30)
EXEC [dbo].[uspPassPhraseGet] @.pss OUTPUT

SELECT
...
,CONVERT(varchar(max),DecryptByPassPhrase(@.pss, CONVERT(varchar(max),dbo.tbl_msg_app_freeform.title), 1, CONVERT(varbinary, 23))) as title
,CONVERT(varchar(max),DecryptByPassPhrase(@.pss, CONVERT(varchar(max),dbo.tbl_msg_app_freeform.description), 1, CONVERT(varbinary, 23))) as description
,CONVERT(varchar(max),DecryptByPassPhrase(@.pss, CONVERT(varchar(max),dbo.tbl_msg_app_freeform.shortdesc), 1, CONVERT(varbinary, 23))) as shortdesc,
...
FROM
...
WHERE
...

I know the permissions to the stored procedures are correct because if I set the @.pss output parameter in uspPassPhraseGet to a static string everything works fine. It is when I am accessing the symmetric key that I don't have select granted on sys.symmetric_keys. I have gone in and explicitly granted SELECT for the role I am using to sys.symmetric_keys. However this isn't working. What am I missing? Is there some archane setting I am missing? Also, on a more academic note, is this the right approach to protect a passphrase used in the DecryptByPassPhrase/EncryptByPassPhrase function or is there a better suggestion/scenario to use?

Thanks for your time and attention,

Mike

|||

It looks like you have explicitly denied SELECT permissions on the sys.symmetric_keys catalog to either the use or one of the roles he belongs to.

To see to whom you have denied the permissions, execute the following query in your database:

select user_name(grantee_principal_id) from sys.database_permissions where state = 'D' and major_id = object_id('sys.symmetric_keys')

Then, take the result and execute

revoke select on sys.symmetric_keys to grantee

replacing grantee with the result of the previous query.

Regarding the code, why do you retrieve that GUID in uspPassPhraseGet? And why do you use a passphrase for encryption instead of using the symmetric key itself?

Thanks
Laurentiu