Creating an email with data from an SQLite table

[Peter K]

I pretty much got everything to work the way that I wanted it to. I was able to use the format command inside the repeat loop. I'm going to play with the numberformat command for the last item because it's a calculation of a monthly mortgage payment and I'd like to be able to add commas to it. Right now the monthly mortgage amount will show as 2150.60 and I'd like it to format as 2,150.60.

Thanks for everything. I would still be banging my head against the wall if it wasn't for you.

Peter

[Peter K]

One more item has come up. My query is working, but when I add one field to it, something goes wrong. The following query works:

Code: Select all

put "CREATE TABLE temp_deals as SELECT mortgage_rates.loan_type loan_type, " & \
         "CASE mortgage_rates.int_only when '1' then 'No' when '2' then 'Yes' END deal_type, " & \
         "min(mortgage_rates.int_rate) int_rate, " & \
         "min(round((mortgage_rates.factor * cust_info.loan_amt)/10000,2)) payment " & \
         "FROM cust_info, mortgage_rates " & \
         "WHERE cust_info.bank_number = mortgage_rates.bank_number " & \
         "and mortgage_rates.prop_type = cust_info.prop_type " & \
         "and cust_info.loan_amt  > mortgage_rates.min_loan " & \
         "and cust_info.loan_amt <= mortgage_rates.max_loan " & \
         "GROUP BY mortgage_rates.loan_no, mortgage_rates.int_only " & \
         "ORDER BY mortgage_rates.loan_no, mortgage_rates.int_only" into tSQL

When I add "mortgage_rates.bank_number after CASE mortgage_rates.int_only...

The query doesn't return the correct bank number. The reason for the group by statement is that I'm looking for the bank that offers the lowest interest rate for each type of mortgage that is available. For each row that is returned, the query returns the bank_number that I get for the first row that is returned, not the correct answer.

Any ideas? I'm sure that it's something to do with the group by statement.

Thanks,


Peter

[dglass]

Can you post the 'broken' SQL?

[Peter K]

Here you go...

Code: Select all

put "CREATE TABLE temp_deals as SELECT mortgage_rates.loan_type loan_type, " & \
         "CASE mortgage_rates.int_only when '1' then 'No' when '2' then 'Yes' END deal_type, " & \
         "cust_info.bank_name, " & \
         "min(mortgage_rates.int_rate) int_rate, " & \
         "min(round((mortgage_rates.factor * cust_info.loan_amt)/10000,2)) payment " & \
         "FROM cust_info, mortgage_rates " & \
         "WHERE cust_info.bank_number = mortgage_rates.bank_number " & \
         "and mortgage_rates.prop_type = cust_info.prop_type " & \
         "and cust_info.loan_amt  > mortgage_rates.min_loan " & \
         "and cust_info.loan_amt <= mortgage_rates.max_loan " & \
         "GROUP BY mortgage_rates.loan_no, mortgage_rates.int_only " & \
         "ORDER BY mortgage_rates.loan_no, mortgage_rates.int_only" into tSQL
   

I've tried adding cust_info.bank_name to the group by and/or order by clause with no help.


Peter

[dglass]

This is what is tripping your query up:

If the expression is an aggregate expression, it is evaluated across all rows in the group. Otherwise, it is evaluated against a single arbitrarily chosen row from within the group. If there is more than one non-aggregate expression in the result-set, then all such expressions are evaluated for the same row.

Probably going to have to rethink your query, or bring back all the individual records and do the aggregation in the application rather than letting the DB engine do it so you can keep track of the proper bank_number/name on your own.