Jump to content

SQL Query Error


This topic is 2798 days old. Please don't post here. Open a new topic instead.

Recommended Posts

Can anyone see whats wrong here:

Let     (     [     invoiceID = id ;     decimal = Left ( Evaluate ( 1/2 ) ; 1 ) ;     SQL = ExecuteSQL    (                     "                     SELECT SUM ( b.\"Amount\" )                     FROM \"Invoice Data\" b                     WHERE b.\"id_Invoice\" = ? AND b.\"Type\" = Rental                     "                     ; "" ; "" ;                     invoiceID                     )     ] ;     If ( SQL ; Substitute ( SQL; "." ; decimal ) ; 0 )     )

There are 2 tables Invoice and Invoice Data. The items added to the invoice can either be a rental, Sale or service item and want to total each type on the invoice layout.. Thank you 

Link to comment
Share on other sites

Instead of Rental in the statement, try either... 'Rental' or ?

If the latter, then you'll need to change this...

    ; "" ; "" ; invoiceID 

to

    ; "" ; "" ; invoiceID ; "Rental" 

Also bear in mind case-sensitivity... for SQL, Rental is not the same as rental

A couple other thoughts:

1. in general, I avoid using SQL in calculated fields, for performance reasons

2. I have no idea what problem you're trying to solve with...

decimal = Left ( Evaluate ( 1/2 ) ; 1 )

...but I'm guessing there's a less convoluted way to go about it.

Link to comment
Share on other sites

This:

 decimal = Left ( Evaluate ( 1/2 ) ; 1 ) 

can be simplified to:

 decimal = Left ( 1/2 ; 1 ) 

or even:

 decimal = Left ( .5 ; 1 )

This last one needs to entered with the correct decimal separator being used by the file at the time of entry - see:
http://fmforums.com/topic/71052-get-system-decimal-separator/

 

3 hours ago, Kevin Frank said:

2. I have no idea what problem you're trying to solve with...


decimal = Left ( Evaluate ( 1/2 ) ; 1 )

 

Well obviously they're trying to determine the decimal separator character being used by the file.

Link to comment
Share on other sites

The main reason I want to use SQL is I dont want to create a relationship via key to get what I need. Could I please ask if someone could write the SQL code to achieve what I require. Above is giving me $0.00 and I don't know enough about SQL yet to solve..

Invoice - Prime key is id
Invoice Data - Foriegn Key is id_Invoice

Each invoice line item is either a "Rental" , "Product" or "Service" item and "Amount" is the total for the line.

I just want to show total amount for  Services, Products and Rentals... I thank you so much for your help... 

 

Link to comment
Share on other sites

4 minutes ago, Ron Neville said:

The main reason I want to use SQL is I dont want to create a relationship via key to get what I need.

Hi Ron,

I believe it is a mistake to prefer SQL over relationships in a case such as this.  You will be displaying invoices and their lines and totaling them frequently.  ExecuteSQL() is great for gathering values while running a script but not for displaying related data.  And Kevin's statement about ExecuteSQL() being a larger performance hit is SPOT ON, particularly on tables which are known to be frequently viewed, worked in, and totaled.

Link to comment
Share on other sites

This topic is 2798 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.