8/29/2025 11:32:55 AM
|
| |
| slxdeveloper.com Community Forums |
|
| |
|
|
The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
Forum to discuss writing script in Architect plugins for SalesLogix & general SalesLogix customization topics (for Windows client only). View the code of conduct for posting guidelines.
|
|
|
|
Subquery is not supported SLX LAN v7.2.1
Posted: 07 Jul 09 12:11 PM
|
Hi all. I tried using the following script but I get an error in SLX Lan V7.2.1: Subquery is not supported
sql_AvgDays = "SELECT AVG(Days) AS AvgDays FROM (SELECT SALES_STAGE, CASE WHEN END_DATE IS NULL THEN Datediff(D, CreateDate, GetDate()) ELSE DateDiff(D, CreateDate, End_Date) END AS Days"
sql_AvgDays = sql_AvgDays & " FROM sysdba.C_myTable) T1"
sql_AvgDays = sql_AvgDays & " WHERE (SALES_STAGE = 'Lost')"
How can I get this to work if it doesn't allow subqueries? |
|
|
|
Re: Subquery is not supported SLX LAN v7.2.1
Posted: 07 Jul 09 9:55 PM
|
I don't think you need a subquery for that. Here's some modified SQL that, I think, gives you what you need:
SELECT t1.SALES_STAGE, avg(Datediff(D, t1.CreateDate, Coalesce(t1.End_Date, GetDate()))) Days
from C_myTable t1
where t1.sales_Stage = 'Lost'
group by t1.Sales_Stage
I cannot remember whether the SLX OLEDB Provider supports COALESCE though. Note also that you should not use the sysdba prefix - you are accessing the database as the sysdba user and therefore that will be your default schema.
|
|
|
| |
| |
|
You can
subscribe to receive a daily forum digest in your
user profile. View the site code
of conduct for posting guidelines.
 Forum RSS Feed - Subscribe to the forum RSS feed to keep on top of the latest forum activity!
|
|
|
|
|
|
|
|