Calling Ref Cursors in Data Access application blocks - Database Discussion Boards

Re: find duplicated rows

rkherath6-Jun-07 0:18

rkherath

6-Jun-07 0:18

hi ganesamoorthidhayalan,

it works. thanks a lot.

Regards
Ruwandi

rkherath

HELP...SQL problem

xuyuan5-Jun-07 17:48

xuyuan

5-Jun-07 17:48

I'm using SQL to output some data from the 3 table below (Sales, Product,& Staff).
How if i want to display a table as this...Have any ideas??

ProductId TotalSale Sale_of_team1 sale_of_team2 sale_of_team3
P012 3 0 3 0
P016 27 7 0 20
P017 4 4 0 0
P019 2 0 2 0
P022 2 0 2 0

>>Sales
ProductId Date Week SalesUnit StaffId

P012 20070514 20 3 S002
P016 20070531 22 7 S001
P016 20070531 22 20 S003
P017 20070531 22 2 S001
P017 20070523 21 2 S001
P019 20070530 22 2 S002
P022 20070502 18 2 S002

>>Product
ProductId ProductName Category Cost

P012 ADSL 512M/256K Streamyx 77
P016 LT 38 Lets Talk 38
P017 PLAN A Lets Talk 68
P019 PLAN C Lets Talk 198
P022 Italk Mobile-50 Italk Prepaid Cards 50

>>Staff
StaffId StaffName Team Level

S001 Jass 1 1
S002 kwee 2 2
S003 Rain 3 2

Thank for helping...arigatou gozaimasu!!!

Re: HELP...SQL problem

andyharman5-Jun-07 23:14

andyharman

5-Jun-07 23:14

The following should do the trick:

SELECT Sales.ProductId,
   SUM(Sales.SalesUnit) AS TotalSale,
   SUM(CASE Staff.Team = 1 THEN Sales.SalesUnit ELSE 0 END AS Sale_of_team1,
   SUM(CASE Staff.Team = 2 THEN Sales.SalesUnit ELSE 0 END AS Sale_of_team2,
   SUM(CASE Staff.Team = 3 THEN Sales.SalesUnit ELSE 0 END AS Sale_of_team3
FROM Sales
INNER JOIN Staff
ON Staff.StaffId = Sales.StaffId
GROUP BY Sales.ProductId

The "case" clauses allow you to conditionally summate a column.

Or

SELECT Product.ProductId,
   (SELECT SUM(SalesUnit) FROM Sales) AS TotalSale,
   (SELECT SUM(SalesUnit) FROM Sales
       INNER JOIN Staff
       ON Staff.StaffId = Sales.StaffId
       AND Staff.Team = 1
       WHERE Sales.ProductId = Product.ProductId) AS Sale_of_team1,
   (SELECT SUM(SalesUnit) FROM Sales
       INNER JOIN Staff
       ON Staff.StaffId = Sales.StaffId
       AND Staff.Team = 2
       WHERE Sales.ProductId = Product.ProductId) AS Sale_of_team2,
   (SELECT SUM(SalesUnit) FROM Sales
       INNER JOIN Staff
       ON Staff.StaffId = Sales.StaffId
       AND Staff.Team = 3
       WHERE Sales.ProductId = Product.ProductId) AS Sale_of_team3
FROM Products

This uses correlated sub-queries to do the work.

Regards
Andy

convert varchar to date

rkherath5-Jun-07 17:32

rkherath

5-Jun-07 17:32

hi All,

i have a dates field which contains dates as varchar type

Dates

(yyyymmdd)
20071231
20070312
20071123

i wanna convert this in to

yyyy/mm/dd

2007/12/31
2007/03/12
2007/11/23

how can i do this please tell me

convert(varchar,dates,101)

didnt include the '/'

regards
Ruwandi

rkherath

Re: convert varchar to date

Krish - KP5-Jun-07 17:40

Krish - KP

5-Jun-07 17:40

to get the dates with "/" seperated you can use
substring(dates, 1, 4) + '/' + substring(dates, 5, 2) + '/' + substring(dates, 7, 2)
But the dates remains varchar

convert function actually converts to datetime data type.

Regards
KP

Re: convert varchar to date

rkherath5-Jun-07 18:55

rkherath

5-Jun-07 18:55

hi,

thank. i got it .

thx a lot.

regards
Ruwandi

rkherath

SSRS;Minus Sign

Amit Kumar G5-Jun-07 16:34

Amit Kumar G

5-Jun-07 16:34

I need to change the display from (123.98) to -123.98 in SSRS.

I tried couple of fromat option on text box and still no good results...

Any Help!!

Amit

Calling Ref Cursors in Data Access application blocks

kjosh5-Jun-07 16:21

kjosh

5-Jun-07 16:21

Hi,

I have one procedure in Oracle which is returning the REF Cursor I am accessing it by using the Data access application block DAAB like the following:

OracleParameter param = new OracleParameter();

param.ParameterName = "Emp_Cursor";

param.OracleType = OracleType.Cursor;

param.Direction = ParameterDirection.Output;

param.Size = 3000;

Database db = DatabaseFactory.CreateDatabase("DBConnection");

//DbCommand cmd = db.GetSqlStringCommand("Select * from emp");

DbCommand cmd = db.GetStoredProcCommand("emppackage.select_employee");

db.AddOutParameter(cmd, param.ParameterName, param.DbType, param.Size);

DataSet ds = new DataSet();

ds = db.ExecuteDataSet(cmd);

GridView1.DataSource = ds;

GridView1.DataBind();

But iam getting this error:

ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'SELECT_EMPLOYEE'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

How to call Ref Cursors in DAAB.

Thanks in advance.

SSIS: Changing app.config settings for a custom dll called from the script task

fodonnel5-Jun-07 13:46

fodonnel

5-Jun-07 13:46

Hi,
I am using a custom .NET DLL from within a script task in SSIS. I want to override some configuration settings in the the DLL (app.config settings). Can I do this is SSIS and if so how?

Thanks,
Fergal.

How to run a stored procedure from within a stored procedure

AndyDev5-Jun-07 13:41

AndyDev

5-Jun-07 13:41

Hi,

I have two stored procedures that returns a SELECT result.

I want to be able to return this results, from a third stored procedure, according to a parameter.

Something like this:

IF (@DisplayUnknown = 0)<br />
    BEGIN<br />
        EXEC stpSnapshotNormal(@Type, @dtDate)<br />
    END<br />
ELSE<br />
    BEGIN<br />
        EXEC stpSnapshotUnknown(@Type, @dtDate)<br />
    END

This of course does not work.
Does anyone knows how is it possible to make work?

Thanks!

Re: How to run a stored procedure from within a stored procedure

Krish - KP5-Jun-07 17:52

Krish - KP

5-Jun-07 17:52

the problem is parameters are enclosed with in parenthesis

use this way
IF (@DisplayUnknown = 0)
BEGIN
EXEC stpSnapshotNormal @Type, @dtDate
END
ELSE
BEGIN
EXEC stpSnapshotUnknown @Type, @dtDate
END

Regards
KP

Re: How to run a stored procedure from within a stored procedure

AndyDev5-Jun-07 18:04

AndyDev

5-Jun-07 18:04

Thanks, for some reason I missed that!
Hate when it happens Wink | ;)

Re: How to run a stored procedure from within a stored procedure

Krish - KP6-Jun-07 1:37

Krish - KP

6-Jun-07 1:37

Welcome

Regards
KP

SQL problem

kozu5-Jun-07 12:35

kozu

5-Jun-07 12:35

Hi,
I have a table that looks like this:

Name Job Salary
Jim Eng 50000
Jane NULL 100000
Bill NULL 55000
Bill Dev 55000

I need a query that would strip out the rows that have NULLs for 'Job' if there's a row with the same 'Name' with a non NULL 'Job'.
The result would look like this:

Jim Eng 50000
Jane NULL 100000
Bill Dev 55000

I could easily do this in C# after I bring all the rows in, but I thought it would be more interesting to get the right data with a query.
Any ideas?

thanx

Re: SQL problem

andyharman5-Jun-07 23:19

andyharman

5-Jun-07 23:19

SELECT * FROM MyTable<br />
WHERE Job IS NOT NULL<br />
OR Name NOT IN (SELECT NAME FROM MyTable WHERE Job IS NOT NULL)

Re: SQL problem

kozu6-Jun-07 5:04

kozu

6-Jun-07 5:04

thanks Andy, that was it!

Exporting ERD from Visio 2007 Enterprise Architect to SQL Server 2005

salman_syed_015-Jun-07 6:10

salman_syed_01

5-Jun-07 6:10

Hi can u help me?
i have created an Entity Relation Diagram in Visio Enterprise Architect 2007. Now i want to export the ERD from Visio to SQL Server 2005.I don`t know how to do that?
I need help on this stage.
Thanks.

SQL Case problem

si_695-Jun-07 4:19

si_69

5-Jun-07 4:19

Hi all

i am having problems with a case statment within my query, i want to list a series of dates and times, and then list against it if it is AM or PM
im trying to do something like the below, but im getting an error

anyone any ideas ??

select mydatetime,
CASE mydatetime
WHEN datepart(hour,mydatetime) > 11 THEN 'PM'
ELSE 'AM'
as exp1
from mytable

thanks

simon

Re: SQL Case problem

si_695-Jun-07 4:36

si_69

5-Jun-07 4:36

DOH !!!

select mydatetime,
CASE
WHEN datepart(hour,mydatetime) > 11 THEN 'PM'
ELSE 'AM'
as exp1
from mytable

Re: SQL Case problem

Krish - KP5-Jun-07 17:34

Krish - KP

5-Jun-07 17:34

CASE statemet to be terminated with END
which is missing in your select query

Regards
KP

Re: SQL Case problem

Ilya Verbitskiy5-Jun-07 4:42

Ilya Verbitskiy

5-Jun-07 4:42

Hi,

Try this code:

select mydatetime,
'exp1' =
case
when datepart(hour,mydatetime) > 11 THEN 'PM'
else 'AM'
end
from mytable

Reporting Services Multilingual ?

ArunkumarSundaravelu5-Jun-07 3:08

ArunkumarSundaravelu

5-Jun-07 3:08

Hi
We are using reporting service,
How to change the reporting services column header,headers...etc
into different language, in c# we use resource file and use this
resource file to change different language(multilingual Option).
How can change these into reporting services.

Regards
Arunkumar Sundaravelu

Error in executing stored procedure:not a valid identifier

Kusumasrinivas5-Jun-07 0:24

Kusumasrinivas

5-Jun-07 0:24

Hey

I have written the following the stored procedure and executed it.But i am getting the following error. I don't know the reason for this.

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

Create PROCEDURE [dbo].[GSU_Site_ReterieveActiveSitesOnSearch]

@whereClause nvarchar(2000)

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

declare @sqlstr as varchar(max)

set @sqlstr='SELECT Site.siteid as siteid,'

set @sqlstr=@sqlstr+ 'Site.Sitename as sitename, '

set @sqlstr= @sqlstr+ 'Customer.customerid,'

set @sqlstr= @sqlstr+ 'Customer.customername as CustomerName,'

set @sqlstr= @sqlstr+ 'Site.City as City,'

set @sqlstr= @sqlstr+ 'site.Address as Address,'

set @sqlstr =@sqlstr+ 'Site.state , '

set @sqlstr= @sqlstr+ 'Country.countryid as countryid,'

set @sqlstr= @sqlstr+ 'Country.countryname as country,Businessunit.businessunitid ,businessunit.businessunitname as BUName,'

set @sqlstr= @sqlstr+ 'SystemType.SystemTypeID,SystemType.SystemTypeName FROM Site INNER JOIN Country '

set @sqlstr= @sqlstr+ 'ON Country.countryid = Site.countryid INNER JOIN Customer ON Customer.customerid=Site.customerid '

set @sqlstr= @sqlstr+ 'INNER JOIN Businessunit ON Businessunit.businessunitID=Site.BusinessUnitID INNER JOIN SystemType ON '

set @sqlstr= @sqlstr+ 'SystemType.SystemTypeID=Site.SystemTypeID INNER JOIN GSUStatus ON Site.GSUStatusID=GSUStatus.GSUStatusID '

set @sqlstr= @sqlstr+@whereClause

--

--set @sqlstr=@sqlstr+' WHERE GSUStatus.GSUStatusID=' +@GSUStatusID

--if @BusinessUnitID <> 0

--set @sqlstr=@sqlstr+'and site.BusinessUnitID ='+@BusinessUnitID

--if @CountryID <> 0

--set @sqlstr=@sqlstr+'and site.countryid='+@CountryID

--if @CustomerID <> 0

--set @sqlstr=@sqlstr+'and site.customerid='+@CustomerID

--if @SystemTypeID <> 0

--set @sqlstr=@sqlstr+'and site.SystemTypeID='+@SystemTypeID

--if @SiteName <> ''

--set @sqlstr=@sqlstr+'and site.Sitename like ' + @SiteName

--if @Address <> ''

--set @sqlstr=@sqlstr+'site.Address like '+ @Address

--if @City <> ''

--set @sqlstr=@sqlstr+'site.City like '+ @City

--if @State <> ''

--set @sqlstr=@sqlstr+'and site.state like '+ @State

print @sqlstr

exec @sqlstr

END

I executed the procedure by pasing parameters

Exec [GSU_Site_ReterieveActiveSitesOnSearch]

" where GSUStatus.GSUStatusID=1 and site.Sitename like 'lakshmisite' "

and getting the following error

- exc {"The name 'SELECT Site.siteid as siteid,Site.Sitename as sitename, Customer.customerid,Customer.customername as CustomerName,Site.City as City,site.Address as Address,Site.state , Country.countryid as countryid,Country.countryname as country,Businessunit.businessunitid ,businessunit.businessunitname as BUName,SystemType.SystemTypeID,SystemType.SystemTypeName FROM Site INNER JOIN Country ON Country.countryid = Site.countryid INNER JOIN Customer ON Customer.customerid=Site.customerid INNER JOIN Businessunit ON Businessunit.businessunitID=Site.BusinessUnitID INNER JOIN SystemType ON SystemType.SystemTypeID=Site.SystemTypeID INNER JOIN GSUStatus ON S' is not a valid identifier."} System.Exception {System.Data.SqlClient.SqlException}

Please let me know the problem in this.

Thanks

Kusuma

Re: Error in executing stored procedure:not a valid identifier

andyharman5-Jun-07 0:59

andyharman

5-Jun-07 0:59

Try:
exec(@sqlstr)

Re: Error in executing stored procedure:not a valid identifier

P_pk9-Jun-07 0:15

P_pk

9-Jun-07 0:15

may be you have definied only one parameter and in the execution statement you have give two parameters

PPK

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.