Does anybody know why ........ ? - Database Discussion Boards

Chris Meech14-Nov-01 6:09

14-Nov-01 6:09

Is there an equivalent to the C/C++ bitwise operators (&, |, ^) for use in SQL programming? And if not any suggestions on how to "best" emulate them. Using something like

     select ...
       from table t
      where t.columnA in ( 1,2,3,5,7,9,11,13,15 );
or
     select ...
       from table t
      where t.columnA in ( 4,5,6,7,12,13,14,15 );

doesn't sound too inviting from a maintenance standpoint. If I ever add another combination, I have to change all of the 'in' lists.

Thanks.

Chris Meech

Is there an equivalent of the case construct in tSql?

Jason Jystad7-Nov-01 11:32

Jason Jystad

7-Nov-01 11:32

The Case construct I am referring to is like this:

Case {expression}
{result 1} : {code block}
{result 2} : {code block}
{result 3} : {code block}
{result 4} : {code block}
Else {code block}
End Case

I would like to do something like this in tSQL on SQL Server 2000. Rather than using daisy chained If statements.

Any ideas?

Jason Jystad
Cito Technologies
www.citotech.net

Sonork ID 11.9918

>-------------------------------------------------<
Every program has at least one bug and can be shortened by at least one instruction -- from which, by induction, one can deduce that every program can be reduced to one instruction that doesn't work.
>-------------------------------------------------<

Re: Is there an equivalent of the case construct in tSql?

AndyG7-Nov-01 18:29

AndyG

7-Nov-01 18:29

In SQL Server BOL, click the index tab then type CASE. Thats it. In case you don't have BOL installed, here's a site. http://www.sqlteam.com/item.asp?ItemID=922

Andy Gaskell, MCSD

Re: Is there an equivalent of the case construct in tSql?

Jason Jystad8-Nov-01 6:09

Jason Jystad

8-Nov-01 6:09

Thanks!

You are correct, I do not have access to the BOL from where I am. Thanks for the site, I was looking for something like that online, but I wasn't having much luck.

Jason Jystad
Cito Technologies
www.citotech.net

Sonork ID 100.9918

>-------------------------------------------------<
Every program has at least one bug and can be shortened by at least one instruction -- from which, by induction, one can deduce that every program can be reduced to one instruction that doesn't work.
>-------------------------------------------------<

Re: Is there an equivalent of the case construct in tSql?

Gerald Schwab8-Nov-01 6:18

Gerald Schwab

8-Nov-01 6:18

http://msdn.microsoft.com/library/en-us/tsqlref/ts_ca-co_5t9v.asp?frame=true

Re: Is there an equivalent of the case construct in tSql?

Carlos Antollini8-Nov-01 7:27

Carlos Antollini

8-Nov-01 7:27

YES...

Like this

Select
  CASE FieldNumber 
   WHEN 0 THEN "Cero"
   WHEN 1 THEN "One"
   .
   .
   .
   WHEN 9 THEN "nine"
   ELSE "No Value"
   END
From 
     MyTable

Best Regards

Carlos Antollini.

Re: Is there an equivalent of the case construct in tSql?

Nick Parker30-Nov-01 8:51

Nick Parker

30-Nov-01 8:51

Yes, you are correct, it is a lot eaiser to write a Case statement than a nested if statement, especially inside those small stored procedure window of SQL Server 2000.

(CASE WHEN Cust_Name = 1 THEN 'Good Customer' ELSE 'BAD Customer' END)

or you can do nested Case statements such as:

(CASE WHEN Cust_Name = 1 then 'Good Customer' ELSE(CASE WHEN Cust_Name = 2 THEN 'SO-SO Customer' ELSE 'Bad Customer' END) END)

Hope this helps.

Nick Parker

Does anybody know why ........ ?

5-Nov-01 2:33

In my application I first connect to SQL server using MFC ODBC classes,
then the same thread tries to to create ADO Connection object
and it fails. What's strange is that if my application first connects to MSAccess using MFC ODBC classes then it can create ADO just fine.
What's the deal with SQL ODBC drivers and ADO? Confused | :confused:

Re: Does anybody know why ........ ?

Rashid Thadha15-Nov-01 3:12

Rashid Thadha

15-Nov-01 3:12

Make sure you have loaded the Ole Libraries

I think in MFC you need to call AfxOLEInit or something like that.

ADO will only work if you load this

changing the value of a recordset's property

Yair Konfino4-Nov-01 22:25

Yair Konfino

4-Nov-01 22:25

Hi,
I'm trying to change the value of a recordset's property. The property is "scroll backwards" (want to change from false to true).

I opened a recordset, and then I got it's properties. I got the wanted property(according to it's index), but I can't change the value - I get an error saying that "the operation can't be done when the object is open".

Which object is the error talking about?
Do I need to change the property after openning the recordset?
What am I doing wrong?

Can someone please send me a code example of what should I do...???

Thanks.

Re: changing the value of a recordset's property

chris foote11-Nov-01 11:20

chris foote

11-Nov-01 11:20

Take a look at your CursorType if is forward only it might be the source of your problem.

ADO GLOBAL CONNECTION

3-Nov-01 10:35

HI.
I´m developing a database application using ado and, although it´s a large program i don´t want to use more than 2 or 3 open connections.
The problem is that i can´t define a global connection pointer, right now, i can only make it work creating a new con in every cpp file.
I´ve tryed defining a global pointer in every way i know but I´m allways getting memory or even weirder errors.
Can anyone help me out?

Pedro Besteiro
LSI systems integration

Pedro Besteiro
LSI systems integration

Re: ADO GLOBAL CONNECTION

31-Jan-02 0:19

Just define pointer at connection in any your class (as class member) and set it everytime when constructing class.

Database Design Question

1-Nov-01 6:54

I am trying to design a table which encompasses a Parent-Child relationship.
I essentially have three classes of data that can be stored in this table. The Level 1 items are the parents of Level 2s and Level2s are parents of Level 3s.

I was wondering what is the more recommended approach to dealing with these situations? What constraint enforcement mechanism is deemed better? Would it be better to use triggers or should i used some form of a foreign key on the same table?

ADO Errors ?!!

Ravish31-Oct-01 2:27

Ravish

31-Oct-01 2:27

I am using SQL 7.0 with MDAC 5.0. One of the stored procedure has a Select statement followed by Insert statement.
If the Insert statement fails due to any reason I cannot get the error in ADO Error collection object. But strangely if I put the insert first and then the select, the ADO error collection is filled ?
What is wrong or What am I doing wrong ?

Thanks

I want a ADO wrap class not using MFC,who can help me?

Jeason Zhao28-Oct-01 22:04

Jeason Zhao

28-Oct-01 22:04

Just as the title!!!!!!
Thanks in advance!

Please pardon my weak English!

Re: I want a ADO wrap class not using MFC,who can help me?

Daniel Turini28-Oct-01 22:18

Daniel Turini

28-Oct-01 22:18

You can try using something like this:

<br />
#pragma warning(push)<br />
#pragma warning(disable: 4146)<br />
#import "\program files\common files\system\ado\msado25.tlb" rename("EOF", "adoEOF")<br />
#pragma warning(pop)<br />

then you can call ADO directly using smart pointers (e.g., ADODB::_RecordsetPtr). It's not MFC dependant.
See more info in MSDN on how to do this.

Furor fit laesa saepius patientia

Why won't database shrink using MSDE?

25-Oct-01 4:39

Have an application that is targetted for both SQL Server 7 and 2000 and MSDE.

Is there a known problem with the Transaction log within MSDE that prevents it from shrinking ? I'm calling various things like DBCC SHRINKFILE through Query Analyser but it often doesn't have any effect.

Net result is that my transaction log keeps growing and growing - not something to ship to customers.

Does anyone know of a dirty way around this problem ?

Re: Why won't database shrink using MSDE?

AndyG25-Oct-01 16:15

AndyG

25-Oct-01 16:15

I'm not totally sure this will work with MSDE, but its worth a shot.

Try backing up the transaction log. When you backup the transaction log, the transaction log automatically get truncated. Truncating the log just gets rid of log entries that have been commited to the database, you will not lose any active transactions. Then try DBCC SHRINKFILE to physically shrink the file.

Andy Gaskell, MCSD

Re: Why won't database shrink using MSDE?

25-Oct-01 21:35

Thanks for the idea. We'd actually tried that out and it works fine in SQL2000 but not on MSDE. Now fairly convinced that it is a problem with the MSDE implementation as we can see that before the SHRINKFILE command the log is only 7% used.

Another idea that someone gave us was to send some NULLOPs to the DB as a way of moving the active transactions around and then trying to shrink it.

We've just downloaded the SQL2000 Desktop edition which seems to respond to shrink requests and if that works, we'll use that and throw out the older MSDE installation.

I'd still be interested to know whether this is a bug in MSDE, a feature of MSDE by design to encourage you to distribute SQL2000.

Numeric Datatype

Michael Martin24-Oct-01 20:52

Michael Martin

24-Oct-01 20:52

Looking at the Numeric Datatype I see the following attributes by default.

Length 9
Precision 18
Scale 0

I know Scale is the number of decimal places but what to Length and Precision mean? The actual size of the Datatype is 8 bytes so Length doesn't seems to number of bytes.

Inside SQL Server and Online Help, haven't helped. Does anybody know the answer and where I can find this sort of information for other datatypes?

Michael Martin
Pegasystems Pty Ltd
Australia
martm@pegasystems.com
+61 413-004-018

"Don't belong. Never join. Think for yourself. Peace"
- Victor Stone

Re: Numeric Datatype

SasaT19-Nov-01 23:44

SasaT

19-Nov-01 23:44

It's easy to answer all your questions. To preserve some space here, I'll just tell you where can you find those informations - in the SQL Server Books Online. Search on the index tab for the 'length of data type' and you'll get all the informations you're asking for.

Best regards, Sasa Smile | :)

Checking flag adFldIsNullable with Field "Attributes" property

24-Oct-01 2:33

Hi!

I'am using ADO 2.6 with the Microsoft OLE DB Provider for Jet 4.0 driver to programmatically check the nullability of a field with the ADO Field "Attributes" property. On Win9x and WinNT(SP6) my program worked OK, but on Win2K(SP2) this property returns True even if the field is a required (non-nullable) field. Where is the problem?

Thanks,
Florin

SQL Query

Drawil23-Oct-01 10:39

Drawil

23-Oct-01 10:39

Hello

I use "RecordSet.Requery()" function in VC++ to get Records of access database, it works properly but when I use it with MS SQL 6.5 database I havn't goten any record, for the same condition and identical tables.

Any help Confused | :confused:

Best Regards

Re: SQL Query

Drawil23-Oct-01 22:07

Drawil

23-Oct-01 22:07

Hello again
I am so sorry if i bother any body, I found the error, it was in the table contents.

Best Regards Smile | :)

Last Visit: 31-Dec-99 18:00 Last Update: 12-Jun-24 4:13

Refresh

ᐊ Prev 1...2445 2446 2447 244824492450 2451 2452 2453 2454 Next ᐅ

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