1 2 Previous Next 16 Replies Latest reply on Sep 21, 2015 4:52 AM by beverly

    ExecuteSQL Where Query

    sccardais

      I want to return the number of unique values between two date ranges and for selected Departments.

       

      Table = Paychecks

       

      Each record in Paychecks includes a date, amount and the ID of the department the employee (fk_ID_Dept) works in as of the date of the check.


      The query below works if I remove the red text if only 1 department is chosen. If more than one department is chosen, it doesn't work.

       

      What is wrong with the query below? Additional info in attached screenshot to explain if necessary.

       

      // Count Employees between 2 dates

       

      ExecuteSQL (

      "SELECT COUNT (DISTINCT p. fk_ID_Employee)

       

      FROM Paychecks p

       

      WHERE p. Pay_Date>= p. g_P1Start AND p. Pay_Date <= p. g_P1End AND p. fk_ID_Dept = p. gc_DeptsList"

      ; "" ; ""

      )

        • 1. Re: ExecuteSQL Where Query
          beverly

          Lists require the IN () and a comma delimited list. If text, each value is in single quotes. If number no quotes needed. Because you likely need dynamic list, I suggest you search this forum for other queries that us IN and how it must be handled.

           

          -- sent from myPhone --

          Beverly Voth

          --

          • 2. Re: ExecuteSQL Where Query
            sccardais

            Thank you.

             

            I’ll research IN ()

            • 3. Re: ExecuteSQL Where Query
              sccardais

              This updated query uses IN () but it is not working yet.

               

              I’m wondering if it is related to similar question about referencing a $$variable or because fk_ID_Dept is a Return separated list. $$ValList is  comma separated list with values wrapped in single quotes. e.g. ’01’,’02’, etc.

               

              ExecuteSQL (

               

              "SELECT COUNT (DISTINCT p. fk_ID_Employee)

               

              FROM Paychecks p

               

              WHERE p. Pay_Date>= p. g_P1Start AND p. Pay_Date <= p. g_P1End AND p. fk_ID_Dept IN ($$ValList)"

               

              ; "" ; ""

               

              )

              • 4. Re: ExecuteSQL Where Query
                beverly

                Yes. Related to the use of variables.

                 

                Try

                ... IN (" & $myVar & ") ...

                 

                If you have single quoted correctly (for text) then this should work using the variable list this way.

                 

                -- sent from myPhone --

                Beverly Voth

                --

                • 5. Re: ExecuteSQL Where Query
                  sccardais

                  So far, no luck.

                   

                  New query

                   

                  ExecuteSQL (

                   

                  "SELECT COUNT (DISTINCT p. fk_ID_Employee)

                   

                  FROM Paychecks p

                   

                  WHERE p. Pay_Date>= p. g_P1Start AND p. Pay_Date <= p. g_P1End AND p. g_Dept IN (" & $$ValList & ")"

                   

                  ; "" ; ""

                   

                  )

                   

                   

                   

                  Does g_Dept need to be in comma separated format?

                   

                  Results are count of all unique fk_ID_Employee - rather than just those in the selected Depts.

                  • 6. Re: ExecuteSQL Where Query
                    erolst

                    sccardais wrote:

                    ExecuteSQL (

                     

                    "SELECT COUNT (DISTINCT p. fk_ID_Employee)

                    FROM Paychecks p

                    WHERE p. Pay_Date>= p. g_P1Start AND p. Pay_Date <= p. g_P1End AND p. g_Dept IN (" & $$ValList & ")"

                    ; "" ; ""

                    )

                    Does g_Dept need to be in comma separated format?

                     

                    No, but $$ValList does – so if that variable is a CR-delimited list, you need to convert it, e.g. with

                     

                    ExecuteSQL ( "

                      SELECT COUNT (DISTINCT fk_ID_Employee)

                      FROM Paychecks

                      WHERE

                        Pay_Date >= ? AND

                        Pay_Date <= ? AND

                        g_Dept IN (" & Substitute ( $$ValList ; ¶ ; "," ) & ")

                        " ; "" ; "" ; g_P1Start ; g_P1End

                    )

                     

                    btw, you don't have to use a table alias and fully qualified names if your request targets only a single table. Also, you could try BETWEEN:

                     

                    ExecuteSQL ( "

                      SELECT COUNT (DISTINCT fk_ID_Employee)

                      FROM Paychecks

                      WHERE

                        Pay_Date BETWEEN ? AND ? AND

                        g_Dept IN (" & Substitute ( $$ValList ; ¶ ; "," ) & ")

                        " ; "" ; "" ; g_P1Start ; g_P1End

                    )

                    • 7. Re: ExecuteSQL Where Query
                      sccardais

                      The query in your reply results in highlighting g_P1start with error “The specified table cannot be found.”

                       

                      I think the error in your query is related to converting the return separated list to comma separated with values wrapped in single quotes.

                       

                      I think my original $$ValList is OK. The screenshot included 2 posts ago showed the values in $$ValList as comma separated with values wrapped in single quotes. This is the calc I used to convert return separated to comma separated. Note that the final & is followed by double quotes then single quote, then double quote.

                       

                      "'" & LeftWords (Paychecks::gc_DeptsList;1) &"','" & MiddleWords(

                      Substitute(Paychecks::gc_DeptsList;¶;"','");2;12)&”'"

                       

                      Can you think of any other reasons why the query below is failing or other ways to count the number of unique fk_ID_Employee between the two dates if the values in g_Date are also present in the comma separated list of Depts?

                       

                      ExecuteSQL (

                       

                      "SELECT COUNT (DISTINCT p. fk_ID_Employee)

                       

                      FROM Paychecks p

                       

                      WHERE p. Pay_Date>= p. g_P1Start AND p. Pay_Date <= p. g_P1End AND p. g_Dept IN (" & $$ValList & ")"

                       

                      ; "" ; ""

                       

                      )

                       

                      I know it’s getting late for you. This isn’t an emergency!

                       

                      Thank you.

                      • 8. Re: ExecuteSQL Where Query
                        jrenfrew

                        does your query really have ?

                        p. Pay_Date


                        surely it needs to be

                        p.Pay_Date etc etc etc

                        • 9. Re: ExecuteSQL Where Query
                          sccardais

                          Sorry but I don’t understand your question.

                           

                          Pay_Date is a field in Paychecks.

                           

                          Can you elaborate?

                          • 10. Re: ExecuteSQL Where Query
                            erolst

                            sccardais wrote:

                             

                            The query in your reply results in highlighting g_P1start with error “The specified table cannot be found.”

                             

                            I think the error in your query is related to converting the return separated list to comma separated with values wrapped in single quotes.

                             

                            I didn't build a sample database and put my sample quote to the test, but to me it looks syntactically OK (and I don't see any single quotes …)

                             

                            sccardais wrote:

                            This is the calc I used to convert return separated to comma separated. Note that the final & is followed by double quotes then single quote, then double quote.

                             

                            "'" & LeftWords (Paychecks::gc_DeptsList;1) &"','" & MiddleWords(

                            Substitute(Paychecks::gc_DeptsList;¶;"','");2;12)&”'"

                             

                            OK, so that's complicated … what's so special about the first value? Just use:

                             

                            "'" & Substitute ( Paychecks::gc_DeptsList ; ¶ ; "','" ) & "'"

                             

                            That being said, here's probably the error; if these IDs are numeric, don't put the values into single quotes – that's for strings.., Just use:

                             

                            Substitute ( Paychecks::gc_DeptsList ; ¶ ; "," )

                             

                            PS: Didn't see your screenshots because I didn't open the thread proper.

                            • 11. Re: ExecuteSQL Where Query
                              user19752

                              Perhaps he meant you have a space after period, it should not be there.

                               

                              And I thought as so (and hadn't try put a space), but it is allowed on FM and pgsql...

                              • 12. Re: ExecuteSQL Where Query
                                user19752

                                There was an issue that FM is slow on BETWEEN.

                                It seems FM14 didn't fix it yet now, don't use index for BETWEEN.

                                 

                                tested on recent GROUP issue sample file adding random date,

                                BETWEEN 350ms

                                >= AND <= 40ms

                                This seems not so problem on about 30000 records.

                                • 13. Re: ExecuteSQL Where Query
                                  jrenfrew

                                  Well, we live and learn here..

                                  Indeed  you can have a space after the initial table alias.

                                  So thats not the problem

                                  • 14. Re: ExecuteSQL Where Query
                                    beverly

                                    It would be confusing to me to have the space between table.field (before or after the period), so I don't do it.

                                     

                                    But AFAIK, you can have any white space in your query and SQL (even ExecuteSQL) doesn't care. But my *guess* is that the column name is unique in this example and the "p." isn't even needed to be used, so "p. " may not be a problem.

                                     

                                    BTW I use white space to make my queries readable and editable and easy to see mistakes:

                                     

                                    _q = "

                                    SELECT

                                        col1

                                       , col2

                                       , col3

                                       , col4

                                     

                                    FROM tabl1

                                     

                                    WHERE col1 = ?

                                     

                                       AND ( col2 = ? )

                                     

                                    ORDER BY col3

                                    "

                                    1 2 Previous Next