DAL: Informix and LIKE Function SQL Queries
I am running DAL on a Sun server, using the Pacer technology. I am trying to query the database, but I'm having difficulty translating the SQL queries for an Informix database into a DAL-compatible SQL query.
The query is:
SELECT <whatever> from <table> where acctkey[4,6]="680"
For example - check the 3 characters in the acct code from position 4 to 6 and see if they match 680. How would I do this in DAL?
The query is:
SELECT <whatever> from <table> where acctkey[4,6]="680"
For example - check the 3 characters in the acct code from position 4 to 6 and see if they match 680. How would I do this in DAL?
The way to do this query in DAL is the same as the DB2 ANSI-standard query using the LIKE function. DAL follows the ANSI-standard, and when there is no standard defined it follows DB2's format.
For this query the LIKE function is the appropriate command. The LIKE function allows you to substitute either:
* An underscore character ("_") for EACH position in which you will accept any character before your desired string
* A single percent sign ("%") for a string of ANY LENGTH that you will accept before and/or after your desired string
In the case of your example (We spaced between the underscore characters to make the point about the number of positions before your desired character string compare):
select <whatever> from <table> where acctkey like '_ _ _ 680%';
This means: select when the value of positions 4, 5 & 6 of the account key = 680, and I don't care what follows position 6. In reality the command would look like:
select <whatever> from <table> where acctkey like '___680%';
If you wanted to search for "680" in any position the command would be:
select <whatever> from <table> where acctkey like '%680%';
DAL will translate this "standard format" query into the appropriate format for the individual DBMS. So this should work for Informix since it supports the positioned query (where acctkey[4,6] ="680").
We don't have access to an Informix database at this time, so we can't verify it against Informix, but we feel confident that the LIKE function will be translated properly. We did test with both Sybase and Rdb and it worked fine.
Be aware that you should put the "%" behind the desired compare string in the LIKE clause unless you are searching for the last positions in the field. If the compare doesn't work for you without the "%", add it and try the SQL statement again.
For this query the LIKE function is the appropriate command. The LIKE function allows you to substitute either:
* An underscore character ("_") for EACH position in which you will accept any character before your desired string
* A single percent sign ("%") for a string of ANY LENGTH that you will accept before and/or after your desired string
In the case of your example (We spaced between the underscore characters to make the point about the number of positions before your desired character string compare):
select <whatever> from <table> where acctkey like '_ _ _ 680%';
This means: select when the value of positions 4, 5 & 6 of the account key = 680, and I don't care what follows position 6. In reality the command would look like:
select <whatever> from <table> where acctkey like '___680%';
If you wanted to search for "680" in any position the command would be:
select <whatever> from <table> where acctkey like '%680%';
DAL will translate this "standard format" query into the appropriate format for the individual DBMS. So this should work for Informix since it supports the positioned query (where acctkey[4,6] ="680").
We don't have access to an Informix database at this time, so we can't verify it against Informix, but we feel confident that the LIKE function will be translated properly. We did test with both Sybase and Rdb and it worked fine.
Be aware that you should put the "%" behind the desired compare string in the LIKE clause unless you are searching for the last positions in the field. If the compare doesn't work for you without the "%", add it and try the SQL statement again.