how to train code value

[dufeng1010]

for example, i have a order_pay table. it's ddl is below:
create table order_pay(
order_id varchar(100) comment 'order_id',
pay_amount decimal(18,2) coment 'pay_amount',
pay_type varchar(10) comment 'pay_type',
primary key(order_id)
) comment 'order_pay'

pay_type's column value '00' as 'cash' pay, '01' as 'wechat', '02' as 'ali'.

how do i train data and ddl。it can answer below question.
"Calculate the amount of cash payments"

[dufeng1010]

@zainhoda

[zainhoda]

There are several possibilities. You can try something like:

vn.train(documentation="""pay_type's column value '00' as 'cash' pay, '01' as 'wechat', '02' as 'ali'.""")

If there's some reference table for pay_type that shows the lookup from 00 to cash etc then you can train the question-SQL pair

vn.train(question="Calculate the amount of cash payments", sql="SELECT ... FROM ... WHERE pay_type_description = 'Cash'")

The second is the preferred method especially if you allow for introspection queries, which would perform a select distinct query on the reference table before returning the final SQL.

[dufeng1010]

i use second method, i have more than 20 payment methods， i add question_to_sql for every payment methods. it works. but But this method is too troublesome。for example:

vn.train(question="Calculate the amount of cash payments", sql="SELECT ... FROM ... WHERE pay_type = '00'")

vn.train(question="Calculate the amount of wechat payments", sql="SELECT ... FROM ... WHERE pay_type = '01'")
......

first method it not works.

[dufeng1010]

i want use only a sql train code value. for example:
select
case when pay_type='00' when 'cash'
when pay_type='01' then 'wechat
else 'other' end
from order_pay

but it not works