SQL Query Error

[Ron Neville]

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 

[Kevin Frank]

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.

[comment]

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.

[Ron Neville]

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... 

 

[LaRetta]

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.

[Ron Neville]

Thank you Loretta.. You used to help me 10 years ago  and will create a relationship.. Thank you all

[Kevin Frank]

21 hours ago, comment said:

 

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

Obvious now that you mention it. Thanks.