You are viewing this site as a guest. You can still ask questions and help others! Join our Dev2Dev IT Community to receive your own blog, share your knowledge, and much more.

dynamic sql

2 replies [Last post]
Guest's picture
Guest

Hi,

I have created two promt tables for two fields : x_category and x_model
based on category i shud get the model types ,so for tat i have used the dynamic sql concept.
below is the code :

Local string &categ = EXO_CUST_DYANM.EXO_CATEGORY_K;

Evaluate &categ
When "TV"
EXO_CUST_DYANM.EXO_MODEL_K.SqlText = "SELECT EXO_MODEL_K ,EXO_CATEGORY_K FROM PS_EXO_MODEL_PROM WHERE EXO_CATEGORY_K='TV' ";
Break;
When "MOBILE"
EXO_CUST_DYANM.EXO_MODEL_K.SqlText = "SELECT EXO_MODEL_K ,EXO_CATEGORY_K FROM PS_EXO_MODEL_PROM WHERE EXO_CATEGORY_K='MOBILE' ";
Break;
When "LAPTOP"
EXO_CUST_DYANM.EXO_MODEL_K.SqlText = "SELECT EXO_MODEL_K ,EXO_CATEGORY_K FROM PS_EXO_MODEL_PROM WHERE EXO_CATEGORY_K='LAPTOP' ";
Break;

End-Evaluate;

its working fine.my doubt is if i have many categories for eg:100 the same code becomes too lengthy .so what is the other way to write the code.please provide me code.

Thanks in advance.

Hari_aindian's picture
User offline. Last seen 6 years 21 weeks ago. Offline
Joined: 08/02/2010
Posts: 84
Re: dynamic sql

Let say the value &categ is taken from this field EXO_CUST_DYANM.EXO_CATEGORY_K. Then you should be having a control table for category or as Xlat values in PSXLATITEM table.

You can create a rowset for this control table or PSXLATITEM table. Loop through the rowset. Pass on the values to the condition as like below.

For &t=1 to Controltable rs.Activerowcount
if controltable rs(&t).EXO_CATEGORY_K.value=EXO_CUST_DYANM.EXO_CATEGORY_K then
EXO_CUST_DYANM.EXO_MODEL_K.SqlText = "SELECT EXO_MODEL_K ,EXO_CATEGORY_K FROM PS_EXO_MODEL_PROM WHERE EXO_CATEGORY_K=:1"|controltable rs(&t).EXO_CATEGORY_K.value;
end-if;
end-for;
Let me know if this solves your purpose.

Regards,
Hari.A

prashob's picture
prashob (not verified)
Re: dynamic sql

To fill the value on one field based on the value of another we have two options.

One option here is to create a sql with text

SELECT EXO_MODEL_K ,EXO_CATEGORY_K FROM PS_EXO_MODEL_PROM WHERE EXO_CATEGORY_K= :1

then in the field change of category write the code to fille the data in the model field by passing the selected category value to the dynamic SQL.

second option is to create 2 sql views one with a key field and category code and another with key field category code and model then place the model immediately after category code........

Thanks,
Prashob