Technical
Sql Or Good To Know (changes)

Showing changes from revision #5 to #6: Added | Removed

Set LIMIT non-rails way.

SYBASE

SET ROWCOUNT 50 sets limit to 50 and ones set to 0 it count resets back to unlimited. NOTE: syntax for oracle is different from sybase

Sybase

 find_by_sql("SET ROWCOUNT 50
            (SELECT A.traderId,originalName,sourceId,originalCisCode,P.abbrev as p_abbrev
                 FROM CR_LegalEntityAlias A ,CR_Product P
                 WHERE A.productId = P.productId
                 AND lower(A.originalName) LIKE '%l')
                 UNION
                 (SELECT NULL,name,sourceId,cisCode,P.abbrev
                 FROM CR_LegalEntityException E, CR_Product P
                 WHERE E.productId  = P.productId
                 AND lower(name) LIKE '#{q.gsub("*","%").downcase}')
                 SET ROWCOUNT 0
                 ")

ORACLE

SELECT * 
FROM your_table
WHERE ROWNUM < 6

lower

To make downcase the comparison field

lower(name) LIKE ('some name')

abs

abs will return an absolute value of the numeric field. So if lets say you have a personId that will have a minus when deleted, you can do

SELECT * FROM PEOPLE
WHERE abs(presonId) = 12345

Oracle Date Functions

We have some date conversion error ORA-01861: literal does not match format string so we found you can use following functions instead of TO_DATE

trunk

ClientIssue.count(:conditions => "trunc(created_on) = to_date('23/04/2007','DD/MM/YYYY')")

round

ClientIssue.count(:conditions => "round(created_on,'dd') = to_date('#{Time.now.strftime('%d/%m/%Y')}','DD/MM/YYYY')")

trim or case-insensitive boolet-proof ordering.

This is a good function for order where if string has leading blank space it would appear first, even if you’d do case-sensitive order

Person.find(:all,:order => "trim(upper(name))")

Oracle Convert Functions

to_number
sometimes you get data through web service which would feed params to oracle as strings no matter what type, so you need to make sure your queries setting integers to be integers and not strngs ( Originated by speric my fellow developer )

    to_number('1210.73', '9999.99')      would return the number 1210.73
    to_number('546', '999')     would return the number 546
    to_number('23', '99')     would return the number 23

coalesce
To replace null values with what you need to be default, in this case its going to be zero

coalesce(num_field,0)