Skip to main content
Question

Lobby Data Source Designer error - missing right parenthesis

  • May 1, 2023
  • 3 replies
  • 183 views
B
Do Gooder (Customer)
Forum|alt.badge.img+6

The following code works in the Database to return the percent of sales quotations won: 

SELECT (SELECT COUNT (quotation_no) FROM order_quotation_line WHERE objstate IN ('CO Created' , 'Won') )
 /(SELECT COUNT (quotation_no) FROM order_quotation_line WHERE objstate NOT IN ('CO Created' , 'Won')) * 100 as WonPercent FROM Dual

 

However, When entering the code above into the lobby data source designer columns. I receive the following error:

Any thoughts on why my code is working in DB but not in IFS data source designer??

3 replies

A
Superhero (Employee)
  • anmise
  • Superhero (Employee)
  • May 1, 2023

Does your column include the actual select?

 

(SELECT COUNT (quotation_no) FROM &AO.order_quotation_line WHERE objstate IN ('CO Created' , 'Won'))/(SELECT COUNT (quotation_no) FROM &AO.order_quotation_line WHERE objstate NOT IN ('CO Created' , 'Won')) * 100

 

B
Do Gooder (Customer)
Forum|alt.badge.img+6
  • bpamlin
  • Author
  • Do Gooder (Customer)
  • May 1, 2023

@anmise I do not have the select keyword when placing into the lobby Data source columns. Thanks,

A
Hero (Partner)
Forum|alt.badge.img+10
  • AussieAnders
  • Hero (Partner)
  • May 1, 2023

IFS will build an SQL query from the data source field as per following:

SELECT
   <Columns>
FROM
   <View Name>
WHERE
   <Condition>
--etc.

If we format what you put in the SQL Query Tool a little, we get:

SELECT 
   (SELECT COUNT (quotation_no) FROM order_quotation_line WHERE objstate IN ('CO Created' , 'Won') ) /(SELECT COUNT (quotation_no) FROM order_quotation_line WHERE objstate NOT IN ('CO Created' , 'Won')) * 100 as WonPercent
FROM
   Dual

Therefore, this needs to go into your column:

(SELECT COUNT (quotation_no) FROM order_quotation_line WHERE objstate IN ('CO Created' , 'Won') ) /(SELECT COUNT (quotation_no) FROM order_quotation_line WHERE objstate NOT IN ('CO Created' , 'Won')) * 100

And with View: DUAL

What’s the purpose with this data source? To show the total percentage since a certain date? Or do you want to group by customer, period and/or something else?

Also, I don’t think the calculation is quite right. Say you won 3 quotes and lost 2. You’re current query would give you a won percentage of 3/2 = 150%.

I think you need to divide by the total number of quotation lines, to get 3/5 = 60%

Terms & ConditionsCookie settingsAccessibility statement