• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 404
  • Last Modified:

Simple inner join using a column that is in lowr case in one table, upper in the other, How?

Given two tables:
Table_1                                      Table_2
userid      G85914E                     userid  g85914e

Select A.* from  Table_1 A, Table_2 B
Where A.userid = B.userid

How to code in Sybase SQL so that The row described in both tables will match?

0
garyinmiami2003
Asked:
garyinmiami2003
  • 3
  • 2
2 Solutions
 
Joe WoodhousePrincipal ConsultantCommented:
Select A.* from  Table_1 A, Table_2 B
Where A.userid = upper(B.userid)
0
 
manekshCommented:
Hi,

I would suggest  to cover all scenarios (if Table_1 might have some in lower case too)

Select A.* from  Table_1 A, Table_2 B
Where upper(A.userid) = upper(B.userid)

Thanks
Maneksh
0
 
Joe WoodhousePrincipal ConsultantCommented:
Maneksh, be aware that in Sybase ASE that version guarantees a table scan, unless one is using ASE 15.0+ and has built a functional index on upper(Table_1.userid).

garyinmiami2003, let us know if there's any possibility that Table_1 has any lower case. If not, the first version has a chance of using an index, but the second does not. Broadly, one wants WHERE clauses to look like this:

     WHERE   [column on its own]  [relational operator]  [expression whose value can be known at compile time]

Relational operator means "=", "<", ">", "<=", ">=", "IN", "EXISTS". Specifically it excludes "!=", "<>", "!<", "!>". "IS NULL" is ok.

Expression whose value can be known at compile time means not a local variable.

It's a bit more complicated than the above, but those are the main rules. In this specific case, we want to avoid any functions on the LHS of the WHERE clause if possible.
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
garyinmiami2003Author Commented:
400  points went to Joe.  He responde first.  Maneksh added value to the solution.  Maneksh added value to the answer.  I hope you all think the points were awarded fairly.
0
 
manekshCommented:
Yep ,

I completely agree with you Joe. There is always the risk of table scan in that query.

Sorry for not mentioning it.

Thanks
Maneksh



0
 
Joe WoodhousePrincipal ConsultantCommented:
Just to clarify - without a specific functional index, putting a function on the LHS of a WHERE clause doesn't just have a "risk" of a table scan... it will guarantee it.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now