SQLite question

[Peter K]

I've got a sql statement that I'm having a problem with. There's a variable called property that I've created that will have a value of 1, 2 or 3. I know that the variable has a value because before the sql statement I've put an answer statement that tells me what the value of the variable is. The sql statement is:

Code: Select all

put "Select borrowers.name, borrowers.prop_addr, borrowers.pur_price, borrowers.fin_amt, borrowers.prop_type, property, " & \
         "banks.bank_name, bank_ltv.ltv, bank_ltv.min_loan, bank_ltv.max_loan " & \
         "from borrowers, banks, bank_ltv where banks.bank_number = bank_ltv.bank_number and banks.prop_type = '@property' " & \
         "and borrowers.fin_amt > bank_ltv.min_loan and borrowers.fin_amt <= bank_ltv.max_loan" into tSQL
   

The problem is in the where clause banks.prop_type = '@property'. This doesn't work. It doesn't work if I use '$property' or '%property''.

The question is, how do I pass a variable to a sql statement to be used in a where clause?

Thanks,

Peter

[dglass]

Probably just need the variable name:

Code: Select all

"... banks.prop_type = " & property & "..."

Although 'property' strikes me as a dicey name for a variable; seems like it could be a reserved word (does LC have those?).

[Peter K]

I'm getting an error message:

revDB Error: revdberr, Database Error: no such column: property.

Maybe I'm misinterpreting the error message, but it seems to be looking for a column in one of the tables called property. I'll change the name of the variable in case property is a reserved word.

Peter

[wsamples]

Peter, a variable enclosed within double quotes is no longer a variable. It's now treated as a literal. You have to close the quoted portion of the argument and append the variable using & or &&, as appropriate. If it's in the middle of such an argument you then continue by appending the rest of the argument with another & or && and the rest, within double quotes of course.

It can be clumsy and confusing and sometimes requires experimentation to get it right when you're just getting started. (Was, and still is, for me, anyway )

[Peter K]

Ignore my last email. It worked. The problem was that the variable property was in my select statement.

Thanks,

Peter

[Peter K]

Is there anyway I can put something in the where clause of a sql statement so that I just get the last record in a table?

[dglass]

Going to vary based on the DB engine you are using, some will have an explicit function for it.

SQLite has a function 'last_insert_rowid()' which will return the last ROWID value inserted into the table.

If your table has INTEGER keys you could do 'SELECT MAX(Table_Key) FROM Table', or 'SELECT stuff FROM Table ORDER BY Table_Key DESC LIMIT 1'

[Peter K]

the syntax that I'm using is

select .....
from .....
where (select max(userID) from borrowers), ....

I'm getting a syntax error message near ",". I get a syntax error message if I take the comma out also.

[dglass]

Can you post the entire SQL statement?

[SparkOut]

the syntax that I'm using is

select .....
from .....
where (select max(userID) from borrowers), ....

I'm getting a syntax error message near ",". I get a syntax error message if I take the comma out also.

results in a sql query of the sort "select <data> from <table> where 876"

(876 is of course an arbitrary value I made up)

Try "where tablekey = (select max(userID) from borrowers)" perhaps? ie, give the where clause a column to match. Otherwise, please can we see more of the query syntax?

[Peter K]

I'll try everyone's suggestions and post the query when I get home tonight.

In the meantime I've gone further in the development of my system and I've come across an issue that I can't solve. I'm using the Case statement in a select statement to do some math. The first part (if the column value = '1') works fine there's just multiplication and division in it. However the math for the second part (if the column value = '2') contains some exponentiation in it. Other forms of SQL seem to be able to handle this, but I can't figure out how to do this in SQLite.

Any ideas?

thanks,

Peter

[dglass]

Can you post what you have in the CASE statement?

[Peter K]

Here's the sql statement:

Code: Select all

put "Select mortgage_rates.loan_type, CASE mortgage_rates.int_only when '1' then 'No' when '2' then 'Yes' END, mortgage_rates.int_rate, " & \
         "CASE mortgage_rates.int_only when '1' then cust_info.loan_amt * ((mortgage_rates.int_rate/1200)/((1-(1-mortgage_rates.int_rate/1200)*-360))) when '2' then (cust_info.loan_amt * mortgage_rates.int_rate/100)/12 END, " & \
         "cust_info.bank_name, cust_info.p_price, cust_info.loan_amt, cust_info.ltv, cust_info.min_loan, cust_info.max_loan, " & \
         "cust_info.name, cust_info.prop_addr, cust_info.prop_type " & \
         "from cust_info, mortgage_rates where cust_info.bank_number = mortgage_rates.bank_number " & \
         "and cust_info.loan_amt > mortgage_rates.min_loan " & \
         "and cust_info.loan_amt <= mortgage_rates.max_loan and " & prop & " = mortgage_rates.prop_type " & \
         "order by mortgage_rates.loan_type" into tSQL

The first case statement is fine, it's the second that's giving me problems. If mortgage_rates.int_only = '1' I have to calculate monthly mortgage payments paying principle and interest. It's called compound interest. the *-360 on the second line should be exponentiation (to the negative 360 power). I can't figure our how to do exponentiation in SQLite.

Thanks,

Peter

[Peter K]

Sparkout -

I tried your suggestion. Here's the query:

Code: Select all

put "Create Table cust_info As Select borrowers.name, borrowers.prop_addr, " & p_price & " as p_price, " & \
         " " & loan_amt & " as loan_amt, borrowers.prop_type, " & \
         "banks.bank_name, bank_ltv.ltv, bank_ltv.min_loan, bank_ltv.max_loan, banks.bank_number, banks.prop_type " & \
         "from borrowers, banks, bank_ltv where tablekey = (select max(userID) from borrowers) " & \
         "and bank_ltv.prop_type = " & prop & " and banks.prop_type = " & prop & " " & \
         "and " & loan_amt & " > bank_ltv.min_loan and " & loan_amt & " <= bank_ltv.max_loan and bank_ltv.ltv > " & tLTV & " " & \
         "and banks.bank_number = bank_ltv.bank_number" into tSQL
   

I get an error message: no such column tablekey.

Thanks,

Peter

[dglass]

I get an error message: no such column tablekey.

Thanks,

Peter

You need to replace 'tablekey' in your SQL with whatever the table's key field is named.