Re: How to select first n characters using stored procedure - Database Discussion Boards

riced10-Apr-09 4:11

10-Apr-09 4:11

Should statement 'a' be Iif((IsNull(var1), .F., var1 <> "A"))?

As it stands it does not make sense, (IsNull(var1), "Z", var1 <> "A") is not a statement.

Regards
David R

can I use '+=' in a datarow

Maverickcool9-Apr-09 21:52

Maverickcool

9-Apr-09 21:52

hi I have datarow which contains an int column
I want to increase the value by 1:
(int)row[0][0] += 1 // the first column is an int field

but when I do this I get an error: "The left-hand side of an assignment must be a variable, property or indexer"

is there a way to do this?

Re: can I use '+=' in a datarow

Mycroft Holmes9-Apr-09 22:25

Mycroft Holmes

9-Apr-09 22:25

What database, what language, are you using the CLR.

If you are using SQL then you need to learn the language, it is not C#. Thats why they invented linq - to get you poor sods a language you knew.

Never underestimate the power of human stupidity
RAH

Re: can I use '+=' in a datarow

Colin Angus Mackay10-Apr-09 8:15

Colin Angus Mackay

10-Apr-09 8:15

Maverickcool wrote:
but when I do this I get an error: "The left-hand side of an assignment must be a variable, property or indexer"

Don't you want to understand why you get this error?

You have cast the left side to an int meaning the left side is no longer the indexer.

You could probably write
row[0][0] = ((int)row[0][0]) + 1;
But, since I don't use DataSets it may or may not work.

*Developer Day Scotland - Free community conference Delegate Registration Open

Re: can I use '+=' in a datarow

Maverickcool10-Apr-09 10:36

Maverickcool

10-Apr-09 10:36

Thanks Colin !

Oracle 11g Express Database? (I need TransactionScope support)

devvvy9-Apr-09 17:07

devvvy

9-Apr-09 17:07

Hello

10g Express + ODP.NET (version 2.111.6.20) > support TransactionScope?

I read ODP.NET doc it does support .NET TransactionScope

But what about 10g Express database? My application died silently (i.e. just exit, no exception) on first db call - and if I just comment out TransactionScope, object instance get saved successfully.

Just found out there isn't an "Express" version for 11g? What should I be using...?

Thanks

dev

How to select first n characters using stored procedure

Meax9-Apr-09 8:04

Meax

9-Apr-09 8:04

this works ok in sql server

<br />
SELECT     MessageID, LEFT(Message, 80) AS Expr1<br />
FROM         MSGTble<br />

but when i try to do it through stored procedure, my webform is giving me error

<br />
ALTER PROCEDURE dbo.GetAllMessage<br />
AS<br />
SELECT     MessageID, LEFT(Message, 80) AS Expr1<br />
FROM         MSGTble<br />
RETURN<br />

saying "A field or property with the name 'Message' was not found on the selected data source."

Re: How to select first n characters using stored procedure

Mycroft Holmes9-Apr-09 22:26

Mycroft Holmes

9-Apr-09 22:26

Remove the RETURN from your store proc.

Never underestimate the power of human stupidity
RAH

Re: How to select first n characters using stored procedure

Meax10-Apr-09 3:58

Meax

10-Apr-09 3:58

even after removing the "RETURN", still giving the same error

Re: How to select first n characters using stored procedure

Ramke16-Apr-09 0:05

Ramke

16-Apr-09 0:05

Hi,

if the column is not there you should get the error as invalid column.
Try to execute the procedure within sql query analyzer window and check whether it returns any value correctly.

In the query analyzer window you have to type the procedure name and excute it and see.

It looks to me this could be coding problem on your webform.

regards
ramke

Re: How to select first n characters using stored procedure

Ashfield12-Apr-09 4:31

Ashfield

12-Apr-09 4:31

Its because you don't have a column called Message in your select statement, you alias it to Expr1, and I expect in your code you are referring to a column called Message.

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

Duplicate Rows

Saiyed Alam9-Apr-09 4:50

Saiyed Alam

9-Apr-09 4:50

Hello Friends
What should be the query to show or find duplicate rows in a table?
Thanking in advance
Johnny

Re: Duplicate Rows

Rob Philpott9-Apr-09 5:25

Rob Philpott

9-Apr-09 5:25

One way is to group by every column, so if you table has four columns, this would do the trick:

select col1, col2, col3, col4 from MyTable
group by col1, col2, col3, col4 having count(1) > 1

Regards,
Rob Philpott.

Re: Duplicate Rows

Saiyed Alam9-Apr-09 6:49

Saiyed Alam

9-Apr-09 6:49

Thanks .It worked.

cursor goes in to infinit loop.........

pranavcool9-Apr-09 0:28

pranavcool

9-Apr-09 0:28

hi,
I have written a cursor that goes into infinite loop. I am not able to make out where i have done wrong.

the following is the code.
/******************************************************************************/
declare @dirid int
declare @topic varchar(8000)

create table #topic(Dir_Id int,topic_Id varchar(1000))

insert into #topic(Dir_Id,topic_Id)select D.Dir_Id,D.topicid FROM Category CN
INNER JOIN directory D ON CN.Cat_Id = D.OrgType_Id
INNER JOIN SubCategory SB ON D.Country = SB.SubCat_ID
WHERE CN.Cat_Name = 'Academic'

declare curtopic cursor for
select Dir_Id,topic_Id from #topic
open curtopic
fetch next from curtopic into @dirid,@topic
begin
while @@fetch_status = 0
set @topic = ''
SELECT @topic = topic_Id from #topic
fetch next from curtopic into @dirid,@topic
end
close curtopic
deallocate curtopic
print @topic
drop table #topic

/****************************************************************************/

i want to retrieve the data row by row for which i have written cursor.
but it is going in to infinite loop.

any help would be great full.

regards,
pranav

Pranav Dave

Re: cursor goes in to infinit loop.........

Rob Philpott9-Apr-09 0:56

Rob Philpott

9-Apr-09 0:56

pranavcool wrote:
while @@fetch_status = 0
set @topic = ''

There you have it - that's the loop that executes. You need to put the statements in a block using begin and end.

Regards,
Rob Philpott.

Can Predictive Analysis be done in SQL Server 2005?

sunit_828-Apr-09 23:47

sunit_82

8-Apr-09 23:47

I want to know if SQL Server 2005 has predictive analysis tools?

http://www.microsoft.com/sql/technologies/dm/TATForecast/TATForecast.html

In above link you can see how Office 2007 has this Forcast feature using the analysis services(2008 i think). Can such a thing be done in VS 2005/2008 or SQL Server Business Intelligence Development Studio 2005

If yes, then can you point me to some useful links or demos.

If it cannot be done in SQL server 2005, but can be done in SQL server 2008( which i think it can be) then please give some useful links and demo for same.

Thanx,
s.a.w

s.a.w.

How to intergrate 2 table

cocoonwls8-Apr-09 18:28

cocoonwls

8-Apr-09 18:28

Hi all,

I am using SQL2005. I have 2 tables which are T1 and T2. I would like to intergrate them into one tabel, what i mean is add column when intergrate them instead of add row record.According what i know if using join or union will create a extra row.Record to be intergrate have to match the T1.AID with T2.ID

Below is my senario.

Table  : T1                        Table  : T2
===========================        ===================
AID | Code | Type | Amount1         ID | Code | Amount2
===========================        ===================
A    123     IA     200            B    223    12
B    223     IP     100            C    323    29 
C    323     MR      55            D    444    30

Result have to get:

=====================================
AID | Code | Type | Amount1 | Amount2
=====================================
A     123    IA      200       0
B     223    IP      100       12
C     323    MR       55       29

The record D in T2 would not include in new table.

Any idea are welcome.

Thanks in advance
cocoonwls

Re: How to intergrate 2 table

Kevin Horgan8-Apr-09 20:39

Kevin Horgan

8-Apr-09 20:39

Hi,

If you know that table T1 has more records than table T2 you could do a LEFT OUTER JOIN as follows:-

SELECT t1.AID,t1.CODE,t1.TYPE,t1.AMOUNT1,COALESCE(t2.AMOUNT2,0)
FROM t1 LEFT OUTER JOIN t2 on (t1.AID = t2.ID)

I hope this helps.

Cheers,

Kevin

Re: How to intergrate 2 table

cocoonwls8-Apr-09 21:32

cocoonwls

8-Apr-09 21:32

Hi Kevin,

I got it!thanks alot Laugh | :laugh:

I have edit my sql to match my case...

thanks in advance
cocoonwls

Re: How to intergrate 2 table

cocoonwls8-Apr-09 21:33

cocoonwls

8-Apr-09 21:33

Hi Kevin,

I got it,Thanks alot! Laugh | :laugh:

I have success to use the left outer join in my case...Thanks again

cocoonwls

Re: How to intergrate 2 table

Kevin Horgan9-Apr-09 8:02

Kevin Horgan

9-Apr-09 8:02

Hi Cocoonwis,

You need to change the GROUP BY clause so it does not include the Amount fields.

Try this instead...

SELECT
t1.AID,
t1.CODE,
MAX(distinct(t1.TYPE) as t1Type),
SUM(t1.Amount) as t1Amount,
COALESCE(SUM(t2.Amount),0)
FROM t1
left outer join t2
on t1.AID = t2.ID
group by t1.AID,t1.CODE

Good luck,

Kevin

On Apr 9, 2009, at 9:17 AM, The Code Project forums wrote:

Hi Kevin,

Thanks for your help.I got it right now Smile | :)

But i have another question about it, if the records are duplicated in t1, and also t2. How could i sum the amount in t1.Amount and t2.Amount. Example there are 2 record A1 in t1, then i would like to sum them.Also in t2, i would like to sum the amount which have the same id.

I have try in my database, it dosen't SUM for me if i write like :

SELECT
t1.AID,
t1.CODE,
MAX(distinct(t1.TYPE) as t1Type),
SUM(t1.Amount) as t1Amount,
COALESCE(SUM(t2.Amount),0)
FROM t1
left outer join t2
on t1.AID = t2.ID
group by t1.AID,t1.CODE,t1.Amount,t2.Amount

thanks in advance
cocoonwls

Re: How to intergrate 2 table

cocoonwls10-Apr-09 0:34

cocoonwls

10-Apr-09 0:34

Hi kevin,

First of all,thanks for your help Smile | :)

I am facing another problem of the sql.How about if i need to filter also of the date?For example,
in t1 have 3 record as below:

Table  : T1                                  Table  : T2
======================================       ===============================
AID | Code | Type | Amount1 | Date           ID | Code | Amount2 | Date
======================================       ===============================
A    123     IA     200      1/15/2009        A    223    12       1/16/2009
B    223     IP     100      1/24/2009        B    323    29       2/13/2009
C    323     MR      55      2/11/2009        E    444    30       2/13/2009

If i want the data which are between 1/1 to 1/30.
The result will be:

Table : TableResult

==================================================      
AID | Code | Type | Amount1 | Amount2 | Date 
==================================================
A    123     IA     200         12      1/15/2009  
B    223     IP     100         0       1/24/2009

Note that the T2.ID for B is not include in.

any ideas are welcome D'Oh! | :doh:

Thanks in advance

regards
cocoonwls

Re: How to intergrate 2 table

Kevin Horgan10-Apr-09 1:05

Kevin Horgan

10-Apr-09 1:05

Hi cocoonwls,

You will need to expand the query to include a WHERE clause. So something like this for example...

SELECT
t1.AID,
t1.CODE,
MAX(distinct(t1.TYPE) as t1Type),
SUM(t1.Amount) as t1Amount,
COALESCE(SUM(t2.Amount),0)
FROM t1
left outer join t2
on t1.AID = t2.ID
WHERE t1.DATE >= '20090101' AND t1.DATE <= '20090130'
group by t1.AID,t1.CODE

Cheers,

Kevin

How to create encrypted trigger

azad yadav8-Apr-09 17:51

azad yadav

8-Apr-09 17:51

Hi friend

How to create encrypted trigger in sql 2005
plz help me
Thanks and Regards
Azad yadav

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