Arie
|
| Posted: 08/27/2002, 2:05 AM |
|
I'm trying to figure out why CC gives me an error if I try to put variables into this custom string. Everything works when these VARs typed into asp page by hand. It's a headache to type them over & over after each change on the page.
Here is the string:
SELECT products.prod_name, sales.sale_qty, products.prod_price, products.prod_commiss, products.prod_mid, sales.sale_aff_id, sales.sale_date, [prod_commiss]*[prod_price]/100*[sale_qty] AS com_total
FROM products INNER JOIN sales ON products.prod_id = sales.sale_prod_id
WHERE (products.prod_mid)="+VARIABLE_1+" AND (sales.sale_aff_id)="+VARIABLE_2+
Thanks
|
|
|
 |
Nicole
|
| Posted: 08/28/2002, 1:42 AM |
|
Arie,
As I understand you’re using custom sql string as form data source. I also suppose that you’re getting the error during design time. It happens because CC connects to db and executes sql in order to retrieve all the field names. You can overcome the problem by splitting select and where clauses.
Put SELECT into SQL field in the form properties->sql tab, and move Where clause into Where field on the same tab.
It should be:
(products.prod_mid)="+VARIABLE_1+" AND (sales.sale_aff_id)="+VARIABLE_2 +"
|
|
|
|
Gianni
|
| Posted: 08/28/2002, 11:35 PM |
|
This resolves also my problem (see thread "custom SQL with session parameters (CC+JSP)").
But the where field is too small to write a complex where predicate. Take for example the where predicate in my thread "CC can't interpret this query (BUG?)".
How could it fit there?
We need another solution, or a larger where field! 
Thanks
Gianni
|
|
|
|
Nicole
|
| Posted: 08/29/2002, 5:28 AM |
|
Gannie,
the another solution is to edit/replace sWhere variable (that stores "Where" clause) in the form Open event.
|
|
|
|
Gianni
|
| Posted: 08/29/2002, 6:25 AM |
|
Thank you Nicole, your hint is OK and it suits my need.
I hope this is a temporary solution: I would rather like to put things in the proper place, that is in the 'where' field, if YES Software is going to widen it.
Feedback on this subject from YES developers will be appreciated.
Have a nice day...
Gianni
|
|
|
|
Arie
|
| Posted: 08/30/2002, 12:10 AM |
|
Nicole,
Thank you very much!
I'll give it a try.
Arie
|
|
|
|
Pavle
|
| Posted: 08/30/2002, 1:10 AM |
|
Can somebody tell me how to let users choose what columns my sql will show for them Like "select var1, var2, ..... from vartable" var1, var2, .... and vartable are users variables.
Thanks
|
|
|
|
|
