Chain Multiplication on a column

Can someone point me out a function that return the multiplication of all row contents from a column?

It should work like the sum() function, but instead of the summary.. it will return the chain multiplication result. Here is what I need it to do.

x
---
4
2
3
7

I need a function "multiply" so that when I issued "Select multiply(x) ...group by..." It would return 168 which is 4*2*3*7

I need to do this in DB2, I checked the DB2 UDF, but looks like there is no simple way to create a customized column function like this.

I'll be really appreciated if someone can help.

Quote:

Originally Posted by janet04

Can someone point me out a function that return the multiplication of all row contents from a column?

It should work like the sum() function, but instead of the summary.. it will return the chain multiplication result. Here is what I need it to do.

x
---
4
2
3
7

I need a function "multiply" so that when I issued "Select multiply(x) ...group by..." It would return 168 which is 4*2*3*7

I need to do this in DB2, I checked the DB2 UDF, but looks like there is no simple way to create a customized column function like this.

I'll be really appreciated if someone can help.

--------------
Reply : select exp(sum(ln(val_num))) from test_multiply;|||select exp(sum(ln(val_num))) from test_multiply;

It will solve your purpose

CE 3.5, VS 2008, Typed Dataset: Get the updated identity of inserted row

Hello,

Using VS 2008 Beta 2, SQL CE 3.5, on desktop, and Typed Datasets: The INSERT command of dataset table adapter does not return the updated identity of inserted row. Why?

also every time I want to modify the insert command to return the updated identity of inserted row, i get the error: "Unable to parse query text."

(Should I post this in Orcas forum?!)

Regards,

Parham.

In order to get the last inserted identity, execute: SELECT @.@.IDENTITY against the same still open connection that executed the INSERT statement. You can only run a single statement in a command against SQL Compact (that's probably why you get the error)

|||

ErikEJ wrote:

In order to get the last inserted identity, execute: SELECT @.@.IDENTITY against the same still open connection that executed the INSERT statement. You can only run a single statement in a command against SQL Compact (that's probably why you get the error)

Tahnks Erick.

This means that if i have inserted some rows into the table and then updated the table to database with the Update command of my Table Adapter, I should REFILL the table to getback the updated identities of the inserted rows?!

Regards,

Parham.

|||

This might help you: http://groups.google.dk/group/microsoft.public.dotnet.framework.adonet/browse_thread/thread/3422d5f0774d605f/34a537895803c758?lnk=st&q=dataset+sql+ce+identity+last+inserted&rnum=1&hl=en#

Alternatively you could use uniqueidentifier columns instead, with a new value of Guid.NewGuid() (set in your code, so you will know the value)

I will do some tetsing later today and revert if there are other options.

Catching return values of a SP

I have calling a stored procedure that returns two values, and I want to catch these values and to store them into a variable.

Here is a piece of my SP inside SQL Server that shows the returned values:

…
SELECT @.Id = SCOPE_IDENTITY()
SELECT @.Id AS user_id
SELECT 1 AS Value
END
GO

In my aspx page I am trying to call the first value like this:

Dim nID
CmdInsert.Parameters.Add(New SqlParameter("@.RETURN_VALUE", SqlDbType.bigint, 8, "user_id"))
 CmdInsert.Parameters("@.RETURN_VALUE").Direction = ParameterDirection.ReturnValue
CmdInsert.Parameters("@.RETURN_VALUE").Value = nID

And to check if the right value is returned I use:

strConnection.open()
 cmdInsert.ExecuteNonQuery
 'Set the value of a textbox
 ident.text = nID
 strConnection.close()

But now no value appears in the textbox, How can I achieve it? What is wrong?You are sort of combining a few different approaches to solving this problem. Since only one ReturnValue can be returned from a stored procedure and you need 2 values, that approach won't work. And since you only have 2 values, I think that you should use OUTPUT parameters.

The stored procedure would look like this:

CREATE PROCEDURE
 myProcedure
AS
 @.myInput1 varchar(50),
 @.myInput2 varchar(50),
 @.myOutput1 bigint OUTPUT,
 @.myOutput2 bigint OUTPUT
INSERT <etc etc
SET @.myOutput1 = SCOPE_IDENTITY
SET @.myOutput2 = 2

Your aspx page code would look like this:

CmdInsert.Parameters.Add("@.myOutput1", SqlDbType.bigint)
CmdInsert.Parameters("@.myOutput1").Direction = ParameterDirection.Output
CmdInsert.Parameters.Add("@.myOutput2", SqlDbType.bigint)
CmdInsert.Parameters("@.myOutput2").Direction = ParameterDirection.Output
strConnection.open()
cmdInsert.ExecuteNonQuery
'Set the value of a textbox
ident.text = CmdInsert("@.myOutput1")
strConnection.close()

Terri|||I have followed all your steps, and now this error message appears:

BC30367: Class 'System.Data.SqlClient.SqlCommand' cannot be indexed because it has no default property.

What does it mean?|||Sorry, the line afected is this:

Line 105: ident.text = CmdInsert("@.Id")|||I'm the one who's sorry. The correct syntax for that line is:

ident.text = CmdInsert.Parameters("@.Id").Value

Terri|||i think its something like :

ident.text=convert.toint32(CmdInsert.Parameters("@.Id").Value)

hth|||Good! now runs fine

Thank you very much,
Cesar

Catching return codes of SP in ODBC way

Hi,
I use SQLPrepare and SQLExecute functions to execute a stored
procedure, which may return various codes. I dont know how to catch
these return codes in my VC function.
Is there any API defined in ODBC for retrieving the return code?
Thanks
KarthikI got it from another thread....
If you wanted to access the return value from the stored
procedure, you would execute a command string like the following:
SQLCHAR * szSQLStmt = (SQLCHAR*) "{? = call sp_test(?, ?)}";
And do a binding like this:
SQLINTEGER returnVal;
ret = SQLBindParameter(hstmt1, 1, SQL_PARAM_OUTPUT, SQL_C_SLONG,
SQL_INTEGER, 4, 0,
&returnVal, 0, NULL);
After the call to the stored procedure, returnVal will contain the
return
value.

Catching a Return from SQL Stored Procedure

Hi All

Here is my SP

ALTER PROCEDUREdbo.InsertPagerDays

@.ReportEndDatedatetime,

@.PagerDaysint,

@.UserIDvarchar(25)

AS

IF EXISTS

(

-- you cannot add a pager days more than once per report date

SELECTReportEndDate, UserIdfromReportPagerDayswhereReportEndDate = @.ReportEndDateandUserId = @.UserID

)

Return1else

SET NOCOUNT OFF;

INSERT INTO[ReportPagerDays] ([ReportEndDate], [PagerDays], [UserID])VALUES(@.ReportEndDate, @.PagerDays, @.UserID)

RETURN

My Question is, this SP will not let you enter in a value more than once (which is what i want) but how do I write my code to inform the user? Here is my VB code becuase the SP does not error out (becuase it works it acts as if the record updates)

How can I catch the Return 1

'set parameters for SP

Dim cmdcommand =New SqlCommand("InsertPagerDays", conn)

cmdcommand.commandtype = CommandType.StoredProcedure

cmdcommand.parameters.add("@.ReportEndDate", rpEndDate)

cmdcommand.parameters.add("@.PagerDays", PagerDays)

cmdcommand.parameters.add("@.UserId", strUserName)

Try

'open connection here

conn.Open()

'Execute stored proc

cmdcommand.ExecuteNonQuery()

Catch exAs Exception

errstr =""

'An exception occured during processing.

'Print message to log file.

errstr ="Exception: " & ex.Message

lblstatus.ForeColor = Drawing.Color.Red

lblstatus.Text ="Exception: " & ex.Message

'MsgBox(errstr, MsgBoxStyle.Information, "Set User Report Dates")

Finally

If errstr =""Then

lblstatus.ForeColor = Drawing.Color.White

lblstatus.Text ="Pager Days Successfully Added!"

EndIf

'close the connection immediately

conn.Close()

EndTry

You need to add a parameter with ParameterDirection.ReturnValue - seehere for more info.

CASTing a datatype returned by CASE Statement

I realize that the CASE statement doesn't like different datatypes as return values but if I want to format the "0" in the second WHEN condition below to "000", how do I do that? I have a "Region" that is "000" and would like it to show up that way at the very top of my report. I have the GROUP BY and ORDER BY to work fine, it just shows up as "0" and I can't change it. I realize it is being read as an int but am having trouble with the CAST and where to place it. Thanks again, you guys are great.

ddave

SELECT Region =
CASE WHEN branch_num IN(48,53,78,173,186,198,208,212,257,286,287,317,35 3,398,440,
478,571,572,610,1069) THEN 44
WHEN branch_num IN(484,532,841,864,7001,7101,7102,7103,7104,9031) THEN 0
ELSE 999
ENDThat depends (doesn't it always?) on what you really want. If you want the other regions to show using normal INT formatting, but 0 to be a special case That is one thing, if you want all the region numbers to be zero filled, that is something different. If you want something I haven't thought of yet, then that's probably different too.

The quick and dirty would be to use:SELECT Region =
CASE
WHEN branch_num IN(48,53,78,173,186,198,208,212,257,286,287,317,35 3,398,440,
478,571,572,610,1069) THEN ' 44'
WHEN branch_num IN(484,532,841,864,7001,7101,7102,7103,7104,9031) THEN '000'
ELSE '999'
END

-PatP|||Pat,

Once again, "You da Man!!". It works perfectly. I decided to use '000', ' 1', ' 78', etc. I spent over an hour on it and I knew it was something easy. I mean I don't expect a medal or anything but you can be lost w/o "the little details". Thanks again.

ddave|||If I want the format to show the Region field just once, is there a way to do that? My current report has a Region field immediately to the left of BranchNo. Branches are contained within the Regions. I got it to list Region every time I show a record but just in case the manager wants it formatted the way I mention I want to be prepared. The example I was to follow has Region just once.

This is an example of what I have now:

code:--------------------
Region BranchNo OrderNo ErrorCode1 ErrorCode2 ErrorCode3
000 478 111 0 1 1
000 478 112 0 0 0
000 478 113 1 0 0
001 610 119 0 0 0
001 610 120 1 0 0
----------------------

This is an example of what I wish to try:

code:--------------------
Region BranchNo OrderNo ErrorCode1 ErrorCode2 ErrorCode3
000 478 111 0 1 1
478 112 0 0 0
478 113 1 0 0
001 610 119 0 0 0
610 120 1 0 0
----------------------

ddave|||What reporting tool are you using? Hopefully this isn't 100% Transact-SQL based, right?

-PatP|||Well, I am looking at the data in Query Analyzer but that is a good question. I guess the real answer is that we haven't decided yet. I can use Access though I have to figure out the mechanics which I know won't be difficult. I can even stick it on an Excel spreadsheet as long as it looks good. I say Access because that is "what the others did" but it is not an issue.

ddave|||It's a presntation issue, and Access is very good at it, and can easily do what your asking...

I'd love to setup reporting services though...

Anyone seen it?

What's the installation like?

What's the interface?

Can you use the same box as sql server?

PS. If they say Crystal...run...|||Reporting Services is quite cool, but it is rather complex and it requires Visual Studio to develop reports.

MS-Access would be beauteous, and would make the formatting, grouping, etc rather simple. I'm not nearly as alergic to Crystal Reports as most folks around here seem to be, but I would STRONGLY advise using Access unless you have another tool of choice.

-PatP

cast not valid

Hello:
I want to make a query that return all record that don't have one valid
cast, something like this:
select * from table where cast(field as bigint) is valid
how can I do that or some variant?. the real problem is to import some table
in a dts but when I try to convert the str to bigint, raise one execption,
that I want to jump and eliminate this record but continue, instead the dts
stop.
Best regards,
Owen.Owen wrote:
> Hello:
> I want to make a query that return all record that don't have one
> valid cast, something like this:
> select * from table where cast(field as bigint) is valid
> how can I do that or some variant?. the real problem is to import
> some table in a dts but when I try to convert the str to bigint,
> raise one execption, that I want to jump and eliminate this record
> but continue, instead the dts stop.
> Best regards,
> Owen.
CAST is not a BOOLEAN function. That is, it does not return whether a
value _can_ be converted from one type to another. It explicitly tries
to convert and throws an exception if a failure occurs. You could use
the ISNUMERIC() function or roll your own integer check function or use
the one here:
http://www.aspfaq.com/show.asp?id=2390
David Gugick
Quest Software
www.imceda.com
www.quest.com|||SELECT *
FROM Table
WHERE (x NOT LIKE '%[^0-9]%'
AND LEN(x) BETWEEN 1 AND 18)
OR x IS NULL
David Portas
SQL Server MVP
--