|
JCapper Message Board JCapper 101
|
| Home |
Register
Log In |
| By | SQL query questions | |
| GlennF 8/30/2009 8:56:15 AM | 1. Is there any way to insert comments in a SQL query. I searched the internet and found double dashes and brackets are considered the standards for this but that doesn't seem to work 2.Can I specify dates for a query. I tried using "AND date > mo/day/year" which seems to be recognized as a proper SQL expression but doesn't do anything 3. lower case letters such as dist= 'd' is converted automatically to uppercase (i.e dist = 'D') . Is this a bug?
| |
| jeff 8/30/2009 9:47:37 AM | No. There isn't a way to insert comments within the text of the SQL Expression itself - at least not for SQL Expressions used to query access database .mdb files. In the final program version you'll be able to store SQL Expressions as part of a UDM Definition. In the UDM Wizard I plan on providing a separate field just for user comments. Query results can be filtered down to specific month and year like this: SELECT * FROM STARTERHISTORY WHERE YEAR = 2009 AND MONTH = 5 or like this: SELECT * FROM STARTERHISTORY WHERE YEAR(DATE) = 2009 AND MONTH(DATE) = 5 Results for a specific race card can be had like this: SELECT * FROM STARTERHISTORY WHERE YEAR = 2009 AND MONTH = 5 AND DAY(DATE) = 2 AND TRACK = 'HOL' or like this: SELECT * FROM STARTERHISTORY WHERE YEAR(DATE) = 2009 AND MONTH(DATE) = 5 AND DAY(DATE) = 2 AND TRACK = 'HOL' -jp .
| |
| jeff 8/30/2009 9:58:04 AM | 3. lower case letters such as dist= 'd' is converted automatically to uppercase (i.e dist = 'D') . Is this a bug? No. It's not a bug. It's by design. I created a handful of assertions wthin the program to prevent execution of a few bad or invalid SQL Expressions. For example, I wanted the program to check for the presence of and require the word "SELECT" as part of the expression. If the user keyed in "seLECt" that would be valid SQL and allowable. I wasn't going to trap for all possible upper case and lower case ways a user could key in words like SELECT or FROM. So I made the program convert the entire expression to upper case before making the evaluation. -jp .
| |
| GlennF 8/30/2009 10:14:36 AM | Thanks Jeff. I think I figured out a partial workaround. Here is a sample: SELECT * FROM STARTERHISTORY WHERE SURFACE <>'comment: F01 equals AFR' AND RANKF01<>999 AND VALF01<>999 AND GAPF01<>999
| |
| jeff 8/30/2009 10:46:47 AM | That definitely works... brilliant! -jp .
| |
| Hdcper 8/30/2009 8:15:06 PM | Jeff, Would it be easy to make it possible to save a Sql Expression, so it can be used say the next day for further testing? Rather than need to re-type it later. Bill
| |
| jeff 8/30/2009 8:54:12 PM | Bill, Right now I'm keeping a handful of interesting SQL Expressions in a simple text file using Notepad - and I suggest anyone interested in SQL Expression based UDMs do the same. When Phase2 of this project is released I plan on simply lifting SQL Expressions from my text file and pasting them directly (in their entirety) into the UDM Wizard and hitting the SAVE button. In the future that simple step will be all that is required to create a UDM. Until then why not use a text editor like Notepad? -jp .
| |
| GlennF 8/30/2009 9:10:26 PM | You can just select the entire text in the SQL window then copy and paste to Notepad or, if you want to be fancier, a boilerplate type program. It would be nice to have this ability directly in Jcapper though.
|
| Reply |
|
Copyright © 2018 JCapper Software back to the JCapper Message Board www.JCapper.com |