ADO SQL - Using text functions in Select statement

[Dig314]

I am connecting to ORACLE through ADO and Excel 2002 VBA.
My SELECT statement works in MS-ACCESS SQL because it understands the
LEFT function. It does not work using VBA and ADO because it thinks
the column name is "LEFT(user_id,2)":

SELECT * FROM CO20.co_table WHERE LEFT(user_id,2) = 79


This is the error message:

Run-time error '-2147217900(80040e14)':
ORA-00904: invalid column name


How can rewrite this statement to work with LEFT or produce the same
results without that function ?

Thanks for the help.

Dig

*************************************************************************
Public adoConn As New ADODB.Connection
Public adoCmd As New ADODB.Command
Public adoRS As New ADODB.Recordset

Public Const strConnection = "Provider = MSDAORA;Data Source =
server;user ID = ID; password = PASS"

Public Const strRS = _
" SELECT * FROM CO20.co_table WHERE left(user_id,2) = 79 "


sub Main()
Call ReturnRecordSet(strRS)
End Sub


Sub ReturnRecordSet(strRSOpen As String)

Set adoConn = New ADODB.Connection
Set adoRS = New ADODB.Recordset
adoConn.ConnectionString = strConnection
adoConn.Open
adoConn.CursorLocation = adUseClient
adoRS.ActiveConnection = adoConn
adoRS.Open strRSOpen
End Sub


*************************************************************************

 

[Jake Marx]

Hi Dig,

In Oracle, I believe you can use SUBSTR instead of LEFT. So it would be:

SELECT * FROM CO20.co_table WHERE SUBSTR(user_id, 1, 2) = '79'

[I think you need the single quotes around the 79 because you are looking
for a string, not a number. But I'm not well-versed with Oracle, so I can't
say for sure.]

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

 

[Harald Staff]

Hi

Another way - % is the SQL wildcard:

SELECT * FROM CO20.co_table WHERE user_id LIKE '79%'

I am not too sure how this (or Jake's solultion) will work on numeric fields
though....
Just as good/bad as LEFT() I guess...

HTH. Best wishes Harald

 

[onedaywhen]

Harald, Yours is better IMHO because it is ANSI standard (i.e.
portable) and I suspect gives better performance (hope it's the
clustered index column for the table). I'd guess it's a fixed length
string: I'd wonder why anyone would want to query a numeric column for
LIKE '79%' i.e. 'I'm interested in the numbers 79, 790, 7900, ...'
<g>.

--

 

[Dig314]

Both SUBSTR and LIKE seem to produce the correct results.
I will probably go with SUBSTR.

Thank you both!

Dig


This site has some dead-links, but otherwise is a good Oracle SQL
reference.
<http://www.cs.ncl.ac.uk/teaching/facilities/swdoc/oracle9i/server.920/a96540/toc.htm>

 

[Jake Marx]

Harald, Yours is better IMHO because it is ANSI standard (i.e.
portable) and I suspect gives better performance (hope it's the
clustered index column for the table). I'd guess it's a fixed length
string: I'd wonder why anyone would want to query a numeric column for
LIKE '79%' i.e. 'I'm interested in the numbers 79, 790, 7900, ...'
<g>.

I agree that LIKE is probably the better solution as it's not proprietary.
SUBSTR works in Oracle, but you need to use LEFT or SUBSTRING in SQL Server.
LIKE will work in either place.

In my (limited) testing of this, I found that LEFT and SUBSTRING executed in
about the same time as LIKE in this case. And yes, hopefully there is at
least some type of index on this column.

I could see maybe wanting to check the first 2 characters if the strings
represented group numbers or some type of classification. Maybe the '79'
represents a certain class of users or something....

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

 

[Harald Staff]

I could see maybe wanting to check the first 2 characters if the strings
represented group numbers or some type of classification. Maybe the '79'
represents a certain class of users or something....

Probably. Unforgivable crime #1 & Far too common stupid thing to do is to
put information into a primary key. Primary keys should be totally invisible
and totally meaningless. Unless one actually enjoys to disconnect all users
and edit-replace millions of values once in a while at nighttime that is.

Best wishes Harald

 

[Rob Bovey]

Probably. Unforgivable crime #1 & Far too common stupid thing to do is to
put information into a primary key. Primary keys should be totally invisible
and totally meaningless. Unless one actually enjoys to disconnect all users
and edit-replace millions of values once in a while at nighttime that is.

You could start a mighty hot thread in the database newsgroups with talk
like that. <g> FWIW, I happen to agree, but you better make sure you have
unique indexes on all your tables so you don't end up inserting duplicate
records that get made artificially unique by the meaningless primary key.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

 

[onedaywhen]

In my (limited) testing of this, I found that LEFT and SUBSTRING executed in
about the same time as LIKE in this case.

Thanks Jake. That doesn't surprise me. I would guess that at some
lower level the implementation of LEFT/SUBSTRING and LIKE are
*implemented* using the same code. But that's just a guess because I
don't know the inner workings of individual DBMS. This is the raison
d'etre of SQL: the implementation is hidden from the user. And that's
why I consider it important to use the ANSI standard SQL if I can help
it. The proprietary functions may offer faster execution, however
issues of maintenance and portability, for either me or my successors,
are more important (yes, I'm on the verge of another MS Access to SQL
Server conversion!) Here a quick read on the subject:

http://www.intelligententerprise.com/030422/607celko1_1.jhtml

I could see maybe wanting to check the first 2 characters if the strings
represented group numbers or some type of classification. Maybe the '79'
represents a certain class of users or something....

In a de-normalized table perhaps <g>. But I'd still argue that would
be a alpha-numeric string data.

--

 

[onedaywhen]

Unforgivable crime #1 & Far too common stupid thing to do is to

put information into a primary key. Primary keys should be totally invisible
and totally meaningless. Unless one actually enjoys to disconnect all users
and edit-replace millions of values once in a while at nighttime that is.

The International Standard Book Number (ISBN) is a great example of a
natural key:

1) It's a fixed length numeric string which makes an excellent key for
a DBMS e.g. joining tables.
2) It's a trusted industry standard so somebody maintains it for the
good of everyone, all enjoy the same experience and the data can
easily reference other databases using a common key.
3) It is guaranteed to be unique. OK occasionally there are problems
and books (dozens rather than millions) get issued with the same ISBN
but these situations can be resolved (see point 2).
4) It has an internal check digit with a public algorithm, allowing
some self validation e.g. can usually tell immediately when the number
has been entered incorrectly.
5) Some of the digits have a special meaning e.g. publisher region
which might be helpful to someone in a library, say.
6) Being a natural key, it can be verified in the real world. Confused
over a couple of ISBNs or some data went corrupt? Simply get the book
down from the shelf or visit Amazon (or countless other websites) or a
bookshop or anyone else in the book industry.

Now, I'm setting up a database and need to choose the primary key
column for my Books table. Yes, I'll use an arbitrary monotonic
integer, not only unique in the table but unique to me! Manually
increasing? No, an IDENTITY/autonumber column will work perfectly!
Glad I avoided committing stupid crime #1 by using that totally
visible and meaningful ISBN.

--

 

[Jake Marx]

Hi onedaywhen,

In a de-normalized table perhaps <g>. But I'd still argue that would
be a alpha-numeric string data.

Exactly. I didn't say that's the way *I* would set it up. <g> But I could
see the use of checking the first two characters of a string in certain
situations (ie, existing databases that aren't normalized).

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]