Hello,

I would like to write a dynamic sql , where by if my user fills Sales Tax code on the form then first query will get executed otherwise if he fills both i.e State Code and Sales Tax Code on the form then the second query will be executed. I read the help for sql functions, but could not understand how do i achieve this.


|select supplier Localisation table in case State Code is filled



select tdind012.*
from tdind012
where tdind012._index1 = {:tiitm001.suno}
and tdind012.scty = :state.code

selectdo
endselect



|select supplier Localisation table in case State Code and Sales Tax Code is filled

select tdind012.*
from tdind012
where tdind012._index1 = {:tiitm001.suno}
and tdind012.scty = :state.code
and tdind012.svat = :sales.tax.code

selectdo
endselect



Waiting for your replies gurus.

Regards

Learner

Before moving to dynamic SQL: how many index fields has index 1? If it is only one field I assume you can better specify something like:

select tdind012.*
from tdind012
where tdind012._index1 = {:tiitm001.suno}
and tdind012.scty = :state.code
and (:sales.taxcode="" or tdind012.svat = :sales.tax.code)

selectdo
endselect

Do you have an index that starts with svat, or has svat somewhere in an index and scty or suno before? If this is not the case, I assume solution above is the best anyway.

Hope this helps,
Dick

Hi,

Well the table only contains 1 index i.e. on supplier, and there are no fields other than supplier in this index.

I will implement the solution given by you.

Regards

Learner

Instead of using
:sales.taxcode=""
you should use:
isspace(:sales.taxcode)

Sorry for this mistake. I am not a programmer ;),
Dick

Hi,

Ok, but let's say if i would have liked to go for dynamic SQL, then ..... how the whole sql query would have been ??

Regards

Learner

something like:

long sql.id

sql.id = sql.parse ("select tdind012.* " & "from tdind012 " &
"where tdind012._index1 = {:tiitm001.suno} " &
"and tdind012.scty = :state.code" &
"and (:sales.taxcode="" or tdind012.svat = :sales.tax.code)")

sql.exec(sql.id)
while sql.fetch(sql.id)
|Here is the loop you use instead of the selectdo/endselect
endwhile

sql.break(sql.id)
sql.close(sql.id)

Hope this helps,
Dick