My first ExecSQL Join

[Ron Cates]

Quite simply can anyone point out what is wrong with this?

Let ([

~begin = Reports » Selector::gDateBegin ;
~end = Reports » Selector::gDateEnd ;
~type = Reports » Selector::gPaymentType

];

ExecuteSQL (

"
SELECT SUM ( n.commission )

FROM Commission n

INNER JOIN Payments p
ON p._pk_payment_id = n._fk_payment

WHERE p.date >= ? AND p.date <= ? AND n.commissionType = ?
"

;"";""; ~begin ; ~end ; "Sales"
)

)

Just trying to pull the total Sales commissions for a date range.

Thanks

 

Never mind. I got it. Just had to do some quoting...

Let ([

~begin = Reports » Selector::gDateBegin ;
~end = Reports » Selector::gDateEnd ;
~type = Reports » Selector::gPaymentType

];

ExecuteSQL (

"
SELECT SUM ( n.commission )

FROM Commission n

INNER JOIN Payments p
ON p.\"_pk_payment_id\" = n.\"_fk_payment\"

WHERE p.\"date\" >= ? AND p.\"date\" <= ? AND n.commissionType = ?
"

;"";""; ~begin ; ~end ; "Sales"
)

)

Edited October 19, 2015 by Ron Cates

[beverly]

 

Cheers! you found a common mistake: use of keyword/reserved word in the name of a table or field (column). Consider renaming your 'date' field to something like 'paymentDate'. You'll be glad later. 

[Fitch]

Yep, and fields that begin with an underscore was the other issue. Good job, Ron.