Hi

In DBISAM3 I had a bit of SQL that I used to determine a tenant had paid
the expected rent rent or an exact multiple of the expected rent.

     (I.ExpectedRent <> 0)
 AND (I.ExpectedRent <> I.Rent)
 AND ((I.ExpectedRent/I.Rent) <> ROUND(I.ExpectedRent/I.Rent TO 2))

This worked well as the fields are MONEY/Floats.

In ElevateDB this does not work.  The fields are now DECIMAL(19,2) and
the BCD works in the background to tidy up the roundings.  (It took me
hours to work out what was going wrong

What I need is SQL that does the same as my original.  I have tried
CASTing fields to FLOAT but don't seem to have found the magic formula.

Cheers

Jeff

Jeff

>In DBISAM3 I had a bit of SQL that I used to determine a tenant had paid
>the expected rent rent or an exact multiple of the expected rent.
>
> (I.ExpectedRent <> 0)
> AND (I.ExpectedRent <> I.Rent)
> AND ((I.ExpectedRent/I.Rent) <> ROUND(I.ExpectedRent/I.Rent TO 2))
>
>This worked well as the fields are MONEY/Floats.
>
>In ElevateDB this does not work. The fields are now DECIMAL(19,2) and
>the BCD works in the background to tidy up the roundings. (It took me
>hours to work out what was going wrong
>
>What I need is SQL that does the same as my original. I have tried
>CASTing fields to FLOAT but don't seem to have found the magic formula.

Couple of thoughts

1. Store everything as pennies (or cents or whatever) and then you're dealing with integers. Have a COMPUTED column which divides by 100 and casts as DECIMAL(19,2) for presentation

2. Try MOD and test for remainder > 0.009 or something

3. CAST(II.RENT * 100 AS INTEGER)

My favourite is number 1

Roy Lambert

Jeff,

try

 (I.ExpectedRent <> 0)
 AND (I.ExpectedRent <> I.Rent)
 AND (trunc(I.ExpectedRent/I.Rent)*I.Rent <> I.Rent))

Barry

My favorite number is irrational.

Jeff


Barry's idea may work but just looking at your code again this

((I.ExpectedRent/I.Rent) <> ROUND(I.ExpectedRent/I.Rent TO 2))

I'm guessing will almost always return true. On one side you're rounding it on the other you're not so to pick a couple of numbers randomly think about

22/7 <> ROUND(22/7 TO 2)

Floats are pretty much guaranteed to be awkward

It could still fall foul of the float nightmare but

( I.Rent MOD I.ExpectedRent) <> 0

might work, if not you have to work out a suitable sensitivity range and test for BETWEEN


Roy Lambert

On 11/11/2014 5:35 a.m., Roy Lambert wrote:
> Jeff
..
..
>
> ( I.Rent MOD I.ExpectedRent) <> 0
>
Thanks Roy and Barry.

MOD is the magic trick and what I'd have used in Delphi.  I looked for
it in the EDB manual but searching for  "mod" gives you multiple
occurrences of module, modify, modification etc. so I missed it.
DBISAM3 didn't have MOD, so I just assumed it wasn't a standard SQL
function.

My final SQL is:-

SELECT COUNT(*) FROM {Import} I
LEFT OUTER JOIN Tenants  T ON T.TenantCode = I.TenantCode
WHERE (((NOT T.VacateDate IS NULL) AND (T.VacateDate < CURRENT_DATE))
   OR ((I.ExpectedRent <> 0)
        AND(I.ExpectedRent <> I.Rent)
        AND((CAST(I.Rent * 100 AS INTEGER)
           MOD CAST(I.ExpectedRent * 100 AS INTEGER)) <> 0)
        ))
   AND NOT Skip

I need the CAST(RowName * 100 AS INTEGER) to make the MOD work.

Cheers and thanks again

Jeff

On 11/11/2014 10:21 a.m., Jeff Cook wrote:
>
> I need the CAST(RowName * 100 AS INTEGER) to make the MOD work.

Oops!
>            CAST(ColumnName * 100 AS INTEGER)