1 2 Previous Next 24 Replies Latest reply on Jan 26, 2016 3:11 PM by disabled_morkus

    SQL Between

    PSI

      I am having trouble getting BETWEEN to work.

       

      Here is an example of the way I am trying it...SalesReportPL is a FileMaker table

      Let (
      [
      ~SD = Date ( 1 ; 1 ; Year ( Get ( CurrentDate ))) ;
      ~ED =Date (  Month ( Get ( CurrentDate )) + 1 ; 0  ; Year ( Get ( CurrentDate )))
      ];


      ExecuteSQL (

      "SELECT InvoiceNbr
      FROM SalesReportPL
      WHERE SaleDate BETWEEN ? and ? " ; "" ; "" ; ~SD  ;  ~ED  ))

       

      I get a zero even though there are sale dates between those dates. I've tried hard coding the dates.

       

      John

        • 1. Re: SQL Between
          nicolai

          Works fine for me. Try to change this line:

           

          ~ED =Date (  Month ( Get ( CurrentDate )) + 1 ; 0  ; Year ( Get ( CurrentDate )))

          into

           

          ~ED =Date (  Month ( Get ( CurrentDate )) + 1 ; 1 ; Year ( Get ( CurrentDate )))

          • 2. Re: SQL Between
            coherentkris

            SQL dates and FM dates can be mismatched.

            FileMaker SQL Date Formats - SeedCode

            I'm not sure this will work but try

            Let (
            [
            ~SD = Date ( 1 ; 1 ; Year ( Get ( CurrentDate ))) ;
            ~ED =Date (  Month ( Get ( CurrentDate )) + 1 ; 0  ; Year ( Get ( CurrentDate )))
            ];


            ExecuteSQL (

            "SELECT InvoiceNbr , || SaleDate as a.SD
            FROM SalesReportPL
            WHERE a.SD BETWEEN ? and ? " ; "" ; "" ; ~SD  ;  ~ED  ))

             

            You could also try the reverse of this function

            https://www.briandunning.com/cf/958

            To convert the FM dates before putting them into variables ~SD and ~ED

            • 3. Re: SQL Between
              beverly

              because you are calling variables, FM's ExecuteSQL function may not know how to quote properly

              ...

              ; "" ; "" ; GetAsDate(SD) ; GetAsDate(ED) )

               

              that's a start to debugging...

              beverly

              • 4. Re: SQL Between
                PSI

                Sorry to waste your time...It turns out that SalesDate was a text field. You would think that would have been the first thing I checked? DOH!

                 

                I marked Nicolai's answer correct not for his suggestion directly but following his suggestion I found the problem.

                 

                However I do want the end date to be the last date of the month because BETWEEN is inclusive.

                ~ED =Date (  Month ( Get ( CurrentDate )) + 1 ; 0  ; Year ( Get ( CurrentDate )))


                Thanks to all who responded.

                John

                • 5. Re: SQL Between
                  nicolai

                  Thanks, I actually think the other two posts have better debugging ideas.

                   

                  Nice trick to get the last day of the month. I usually use:

                   

                  Date (  Month ( Get ( CurrentDate )) + 1 ; 1  ; Year ( Get ( CurrentDate )))-1

                   

                  but this i mine new favourite ( as it has two character less in the formula)

                  • 6. Re: SQL Between
                    user19752

                    It is true in FM14 yet, BETWEEN is very slower than >= AND <=

                    • 7. Re: SQL Between
                      beverly

                      agreed! use BETWEEN cautiously (or not at all)

                       

                      beverly

                      • 8. Re: SQL Between

                        Sadly, IMHO, FM's SQL is lacking professional features.

                         

                        For example, there is no Perl-compatible RegEx or other desperately missing SQL functions.

                         

                        And, you can only do INSERT, UPDATE, and DELETE from the JDBC driver, not within FM.

                         

                         

                         

                        (Maybe in 15 ...)

                         

                        - m

                        • 9. Re: SQL Between
                          nicolai

                          morkus wrote:

                          Sadly, IMHO, FM's SQL is lacking professional features.

                          there is no Perl-compatible RegEx or other desperately missing SQL functions

                          There is no perl compatible or Regex functions in T-SQL. Does this make it unprofessional?

                           

                          I can not remember them in MYSQL or Oracle, but it's been a while since I worked with both of them. As far as I remember, they are not a part of any SQL standard either.

                           

                          The other thing - why Perl? Why not Python, PHO, Ruby, c, c++ , c# etc...etc.. etc..

                           

                          The answer is simple, SQL is a query and not programming language. FileMaker on the other hand is not a SQL database, so the SQL features are limited.

                          And, you can only do INSERT, UPDATE, and DELETE from the JDBC driver, not within FM.

                           

                          You can use ODBC as well and you can point the DNS on the file performing query, so you can use all three inside FM if you really need to do it this way.

                          • 10. Re: SQL Between

                            Appreciate your reply.

                             

                            "Professional" was probably the wrong word. Sorry it offended you. If you look at the implementation of SQL functions in most database products (it really shouldn't matter what the underlying implementation is, SQL or not SQL as that should be abstracted by the product), then you can better see why I said that.

                             

                            ---

                             

                            Check out this site for an example of REGEX in MySQL:

                             

                            http://www.tech-recipes.com/rx/484/use-regular-expressions-in-mysql-select-statements/

                             

                            I've used RegEx/MySQL with much more complicated RegExes than this site, but it's just a sample.

                             

                            --

                             

                            Perl compatibility is generally, as I've often heard, the gold standard for RegEx. Other languages like Java, etc., are compatible with Perl standard, not, as I understand it, the other way around. I think there's some other Perl history that would better answer your question about why Perl compatibility is often considered important, but I don't have it handy or time to search for it.

                             

                            Regular expression - Wikipedia, the free encyclopedia

                             

                            It's worth pointing out that the FileMaker third-party tool Developer Assistant "RegEx" feature is advertised as being Perl Compatible.I've thrown very complicated RegExes at it and it worked flawlessly. A very cool tool.

                             

                            ---

                             

                            MySQL (or any SQL dialect) is declarative, like the new Java 8 Stream API. With declarative approaches, you don't focus on the "how" (as with procedural code); instead, you focus on the "what". It's possible, for example, in Java 8 to search a text file with one line of code using the declarative (analogous to MySQL) approach and the new java 8 streams API.

                             

                            FileMaker having SQL and (with JDBC, INSERT, UPDATE, and DELETE) makes it feel like a SQL database, but I've heard your comment from others before about FM not being a "SQL database" and I'm assuming you're correct. As I said above, whether FM is a "SQL database" or not, the actual internal implementation should be abstracted by the product -- with no bled through to the implementation layer (what we use). Thus, as long as the FileMaker API (the SQL functions we use) have a function, it's up to the internal structure of FM to implement the SQL. That API should be able to support RegEx, or whatever (if implemented, internally, of course).

                             

                            I'll take your word about ODBC being able to INSERT, UPDATE, and DELETE, but I avoid MS whenever possible. But that's an interesting comment.

                             

                            Thanks!

                             

                            - m

                            • 11. Re: SQL Between
                              nicolai

                              OK, I was wrong. I also checked Oracle and they also have regex functionality built-in. Should have done it before posting. BTW, SQL Server can add support for regex with a CLI, common practice.

                               

                              You are correct, I was taking it a bit personally. You kind of imply that FileMaker not being professional tool (because it is missing a particular feature you desire and which most probably will not make any difference to another million of FIleMaker developers). I am a professional FileMaker Developer and that makes me also unprofessional for the last 15 years (well I hope, I was professional enough when I was using MS Access 15 years ago). I

                               

                               

                              avoid MS whenever possible

                               

                               

                              I would never expect such a comment from a Java developer. Well, this is not about your preferences, it is about what is being used by customers. It is easy to kick Microsoft, especially on this forum with a heavy Apple base,  but they had produced some amazing staff over the years which might explain their sizable market share. It sounds like you have too many customers and can pick and choose, well if they are on Windows, just send it my way

                               

                              Again, I am trying to find any reference in 5 SQL standards and I can't. If you have it, could you post a link? I suspect it is not a part of SQL and vendors add it on the top as their language specific.

                               

                              I mean, I would love to have regex in FileMaker built in, but I would not call them names because it is not there. With you Java experience, why don't you build some local service and use it with FileMaker. Grab some records through JDBC and regex a hell out of them.

                              • 12. Re: SQL Between

                                Agreed, RegEx may be a non-standard SQL feature that most vendors support. You're probably right about that.

                                 

                                I was a MS developer for 20 years before changing over.   SQL Server's Type IV JDBC Driver was rock solid way back when. SQL Server is a great database and I would use it now for some projects if only it ran on Linux.

                                 

                                BTW, as I've said before, I think FM rocks! It's because I like it so much that it's frustrating when basic features are missing (we've discussed those too).

                                 

                                Thanks again for your thoughtful follow ups.

                                 

                                - m

                                • 13. Re: SQL Between
                                  nicolai

                                  morkus

                                  No, thank you. I learned a quite few very useful things from your post and the following debate. Sorry for being a bit harsh.


                                  Nicolai

                                  • 14. Re: SQL Between
                                    user19752

                                    I don't have access to source document, but regex may be defined in standard SQL:1999

                                    PostgreSQL: Documentation: 9.5: Pattern Matching

                                    1 2 Previous Next