Re: problem getting sum of each row - Database Discussion Boards

Re: Return two values

DanB198321-Dec-06 0:24

DanB1983

21-Dec-06 0:24

Thats magic thanks remmber seeing that some where and when you said that it all fell into place.

Thanks again.

Dan

DateDiff returns in HH:MM format

VK-Cadec20-Dec-06 8:22

VK-Cadec

20-Dec-06 8:22

Hi - Is there a way we could return the result in above format - know of hours, minutes, seconds.

Thanks for your time.

Vani

Re: DateDiff returns in HH:MM format

Colin Angus Mackay20-Dec-06 22:41

Colin Angus Mackay

20-Dec-06 22:41

VK-Cadec wrote:
Is there a way we could return the result in above format - know of hours, minutes, seconds.

DateDiff returns the result in an internal format. It is merely the rendering of that data on the screen that gives it a format. The formatting of data should be the function of your application's presentation layer, not the database.

Upcoming Scottish Developers events:
* Glasgow: Tell us what you want to see in 2007

My: Website | Blog | Photos

problem getting sum of each row

xoxoxoxoxoxox20-Dec-06 6:13

xoxoxoxoxoxox

20-Dec-06 6:13

Hi,
I'm trying to get total of each row in my table. For eample, I have columns x, y and z (all int). To get total, i'm writing "select (x + y + z) As total from myTable".
It works fine as long as one of the colunm is not empty. If it finds any null data in x, y or z then total shows up null. How can i get total in such case?

Thanks in advance

Re: problem getting sum of each row

ed^nrgc20-Dec-06 6:25

ed^nrgc

20-Dec-06 6:25

Have you tried IsNull()?

Re: problem getting sum of each row

xoxoxoxoxoxox20-Dec-06 6:50

xoxoxoxoxoxox

20-Dec-06 6:50

Thank you very much! IsNull worked fine.

Re: problem getting sum of each row

Keith Malwitz20-Dec-06 6:41

Keith Malwitz

20-Dec-06 6:41

Any expression containing a null value will evaluate to null.

There are a couple of ways around this. You could define your database columns so that they are not allowed to contain null values, and assign them a default value of zero.

Another method would be to test each value for null, and use 0 for the value of that operand if the result is true.

Hope that helps.

Re: problem getting sum of each row

RinkRat20-Dec-06 6:59

RinkRat

20-Dec-06 6:59

Could try something like:

SELECT x,y,z,
COALESCE(x,0) + COALESCE(y,0) + COALESCE(z,0) as [Row Total]
FROM .............

link the foxpro(.dbf) table with mssql

Guru_yogi20-Dec-06 1:12

Guru_yogi

20-Dec-06 1:12

without using linked server is there another method to do the linking between faxpro(.dbf) & mssql server

Gurudatta B. Shelke

Re: link the foxpro(.dbf) table with mssql

Pete O'Hanlon20-Dec-06 22:46

Pete O'Hanlon

20-Dec-06 22:46

Please stop reposting questions. People have already told you how to do this with linked servers. If you don't want to use linked servers (and to be honest, I can't see any reason why you wouldn't), you should reply to them on that post.

the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer

Deja View - the feeling that you've seen this post before.

Guy's pls help...

tilak.gummadi19-Dec-06 23:19

tilak.gummadi

19-Dec-06 23:19

can some one tell me how to create a sequence in sql server 2000. pls help me asap....

tilak

Re: Guy's pls help...

Mairaaj Khan20-Dec-06 0:57

Mairaaj Khan

20-Dec-06 0:57

tilak.gummadi wrote:
can some one tell me how to create a sequence in sql server 2000

In SQL Server IDENTITY property is the equivalent of the oracle's sequences. IDENTITY property can be set to the columns of the types tinyint, smallint, int, bigint, numeric, decimal. It is used in the form of IDENTITY(seed, increment)
Example,

CREATE TABLE abc(
f1 int IDENTITY(10,2), 
f2 varhar(100)
)

Also have a look at this[^] for more details.

Regards

________________________________
Success is not something to wait for, its something to work for.

Re: Guy's pls help...

tilak.gummadi20-Dec-06 16:58

tilak.gummadi

20-Dec-06 16:58

Thanks a lot uroojkhan....this thing realy helped me a lot...

tilak

how to use linked server for foxpro & mssql linking

Guru_yogi19-Dec-06 18:51

Guru_yogi

19-Dec-06 18:51

i want to link foxpro file with mssql
for that somebody suggest me to use linked servers
but i dont know how to use linked server so pls
help me about how to use linked server

Gurudatta B. Shelke

Re: how to use linked server for foxpro & mssql linking

Eric Dahlvang20-Dec-06 3:44

Eric Dahlvang

20-Dec-06 3:44

How to add a Visual FoxPro database to SQL Server as a linked server[^]

--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters

SQL Server text column question

Marcus Spitzmiller19-Dec-06 9:12

Marcus Spitzmiller

19-Dec-06 9:12

Hi - I just have a general SQL server question I'm hoping someone can help answer.

I have a table with a column of datatype TEXT called NOTE. I am trying to trim those records in the text field to say, 10000 characters where datalength(NOTE) > 10000.

for example, normally i would do something like 'update notetable set note = LEFT(note, 10000) where length(note) > 10000', but of course i can't do this with this datatype.

This datatype is a pain..Does anyone know the syntax for this?

Thanks!

Marcus Spitzmiller

You will never hear me say, "I listen to pretty much everything." That is for people who don't care what goes in their ears.

Re: SQL Server text column question

PIEBALDconsult19-Dec-06 9:28

PIEBALDconsult

19-Dec-06 9:28

Convert to/from VARCHAR?

Re: SQL Server text column question

Marcus Spitzmiller19-Dec-06 9:35

Marcus Spitzmiller

19-Dec-06 9:35

No - it'll still ultimately reside in the same column - I just need to trim it.

any thoughts?

Marcus Spitzmiller

You will never hear me say, "I listen to pretty much everything." That is for people who don't care what goes in their ears.

Re: SQL Server text column question

Eric Dahlvang20-Dec-06 3:51

Eric Dahlvang

20-Dec-06 3:51

I don't see why what PIEBALDconsult suggested will not work:

select cast(mytextcolumn as varchar(1000)) as MyTextColumnTrimmed from mytesttable

Try it out.

--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters

Re: SQL Server text column question

Marcus Spitzmiller20-Dec-06 5:25

Marcus Spitzmiller

20-Dec-06 5:25

I guess I misunderstood the suggestion.

However, I did:

select cast(NOTE as varchar(16349)) as NOTE
from S_NOTE_ACCNT
WHERE datalength(NOTE) > 16349

and I get:

The size (16349) given to the type 'varchar' exceeds the maximum allowed for any data type (8000).

So really, I need to trim the field to 16349, but apparently I can't this way...any thoughts?

Marcus Spitzmiller

You will never hear me say, "I listen to pretty much everything." That is for people who don't care what goes in their ears.

Re: SQL Server text column question

Michael Potter20-Dec-06 10:26

Michael Potter

20-Dec-06 10:26

I don't have the data to test with but, I do believe that SUBSTRING works with the TEXT & NTEXT data types.

UPDATE
    noteTable
SET
    note = SUBSTRING(note,0,10000)
WHERE
    DATALENGTH(note) > 10000

Re: SQL Server text column question

Marcus Spitzmiller20-Dec-06 12:12

Marcus Spitzmiller

20-Dec-06 12:12

Thanks, that works. - Apparently I didn't look hard enough. I guess I didn't realize that substring existed. I tried substr and when that didn't work, I guess I thought that that function didn't exist.

Thanks again!

Marcus Spitzmiller

You will never hear me say, "I listen to pretty much everything." That is for people who don't care what goes in their ears.

Questions about various DB connection methods

Jay Legue19-Dec-06 8:26

Jay Legue

19-Dec-06 8:26

Hello All,

I am looking to get some guidance and feed back from the community as to the prevalence and usage of the following technologies:

ODBC Descriptor support
OLE DB support
External Linked Server support
Data Link support for Data Transformation Services

Specifically I am hoping to find out when each technology should be used and the types of applications that use them. Additionally, I am wondering if there are alternatives to these technologies and why they may have been used instead.

Any guidance/feed back is appreciated.

Thanks,

---Jay

How do I format this...

Steven J Jowett19-Dec-06 4:43

Steven J Jowett

19-Dec-06 4:43

I have a stored procedure that can select data from several tables/views. One two of the parameters are DateTime fields. The SQL code is as follows :-

EXEC(N'SELECT * FROM ' + @objectName + ' WHERE DepotId = ' + @DepotId + ' AND [TIME] >= ' + @StartTime + ' AND [TIME] <= ' + @FinishTime + ' AND Intake = ' + @Intake + ';')

I am getting a error regarding the time parameters. I need to know how to format these for the where clause to work.

I have tried enclosing in double-quotes e.g. [TIME] <= "' + @StartTime + '" AND ...

I have also tried enclosiing hashes e.g. [TIME] <= #' + @StartTime + '# AND ...

neither works, please help

Steve Jowett

Re: How do I format this...

Steven J Jowett19-Dec-06 5:38

Steven J Jowett

19-Dec-06 5:38

I've sorted it. I should have done it like this...
[TIME] <= ''' + @StartTime + ''' AND ...

Steve Jowett

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