3 Replies Latest reply on Mar 29, 2010 2:33 PM by mrvodka

    Calculated SQL text

    mdaracz

      Title

      Calculated SQL text

      Post

      Hello Forum,

      I am trying to import some records to my current customer table based on the following Calcualted query, but I am not getting any resulting data. My query looks like this. 

       

       

      "SELECT AR_Customers.Address1, AR_Customers.Address2, AR_Customers.Name, AR_Customers.City, AR_Customers.Website, AR_Customers.Zip, AR_Customers.Phone1, AR_Customers.State, AR_Customers.CustomerClassCode, AR_Customers.CustomerKey
      FROM AR_Customers, OE_Orders
      WHERE OE_Orders.InvoicePostedDate >= '" & Get ( CurrentTimeStamp ) - 31556926 & "'"

       

       

      Basically, I want customers we did business with during the past year based on WHERE OE_Orders.InvoicePostedDate

       

      this query works perfectly fine in my sql server, but not on filemaker... what am I doing wrong? is it soething with quotations? thanks!

        • 1. Re: Calculated SQL text
          mrvodka

           


          md100 wrote:

          Hello Forum,

          I am trying to import some records to my current customer table based on the following Calcualted query, but I am not getting any resulting data. My query looks like this. 

           

           

          "SELECT AR_Customers.Address1, AR_Customers.Address2, AR_Customers.Name, AR_Customers.City, AR_Customers.Website, AR_Customers.Zip, AR_Customers.Phone1, AR_Customers.State, AR_Customers.CustomerClassCode, AR_Customers.CustomerKey
          FROM AR_Customers, OE_Orders
          WHERE OE_Orders.InvoicePostedDate >= '" & Get ( CurrentTimeStamp ) - 31556926 & "'"

           

           

          Basically, I want customers we did business with during the past year based on WHERE OE_Orders.InvoicePostedDate

           

          this query works perfectly fine in my sql server, but not on filemaker... what am I doing wrong? is it soething with quotations? thanks!


          You need to convert the time into the MS SQL server datetime format.
          For example for the records that have a date >= to 30 days ago...
          "SELECT AR_Customers.Address1, AR_Customers.Address2, AR_Customers.Name, AR_Customers.City, AR_Customers.Website, AR_Customers.Zip, AR_Customers.Phone1, AR_Customers.State, AR_Customers.CustomerClassCode, AR_Customers.CustomerKey
          FROM AR_Customers, OE_Orders
          WHERE OE_Orders.InvoicePostedDate >=' " & Let ( d = Get ( CurrentDate ) -30; Year ( d ) & "-" & Month ( d ) & "-" & Day ( d ) & " 00:00:00' " )
          If you want all the records for the current year then you can just adjust the last part to:
          >=' " & Year ( d ) & "-01-01 00:00:00' " )

           

           


          • 2. Re: Calculated SQL text
            mdaracz

            Mr. Vodka. Thanks for the quick reply.  

            Unfortunately that didn't work. 

             

            as soon as i click "specify" in the scrip step option the data does not show up...   

             

            Thank you. 

             

            • 3. Re: Calculated SQL text
              mrvodka

              I am not sure I follow you. You have specified the DSN already correct? Then when you go and select the Import script step there you should be able to select your DSN and then there should be an option for calculated SQL text. Do not check 'Perform without dialog' and 'Specify import order' for now to troubleshoot.