Questions? Feedback?powered byOlark live chat software
Bug 2276

querymode error when using inline views or subselects in select for a computed column 13 February, 2019

mike S
11 February, 2019
Product: PowerBuilder Category: DataWindow
Version: 2017 R3 Publishing: Public
Status: Verifying Priority: P3
Classification: Unsupported Resolution:
Ken Guo 13 February, 2019
Hi Mike,

We apologize that we haven’t any plan to enhance this feature currently. 
Appreciate your understanding. 

Regards,
Ken
mike S 12 February, 2019
hi ken, 
yes, i know it doesn't work with current version of PB.
Is this something that will be fixed?  Or is querymode a feature that is being deprecated and eventually discontinued?

In general querymode is very basic and can only be used in applications that have extensive code written to work around its limitations.  Plus, as i've pointed out here, it doesn't work with current sql standards.  

So i need to know whether i can wait a year or 2 for appeon to fix querymode, or if i need to plan on replacing the querymode with my own querymode.

thanks
Ken Guo 12 February, 2019
Hi Mike,

Thanks for reporting the problem. I can reproduce it on my machine. However I am afraid it cannot be supported in PB currently. 
We’d suggest you to use simple PBSelect SQL for QueryMode. 
For your inline view, you may work it around with database view. 

Regards,
Ken
Chris Pollach 11 February, 2019
Hi Mike;

  We will need some time to analyze this behaviour to confirm. If you have a simple test case against the PB2017R3 demo database "PB Demo DB V2017R3", that would be most appreciated.

  I will now pass this ticket over to the main Support/Engineering team for their review of the situation.

  FWIW: Have tried QueryMode feature with this SQL in PowerServer?

Regards ... Chris
mike S 11 February, 2019
*Phenomenon:
querymode does not support in-line views. It throws an error - "Line 1  Column 24: incorrect syntax." and does not go into query mode.  a retrieve works fine - the sql is correct, its just that querymode hasn't been updated in 20 years.

the same thing happens if you use a subselect in select clause IN SYNTAX MODE.  
If you are in graphical mode and enter the subselect as a computed column in the compute tab then it actually works!  


*Reproduce Steps:

this is a select that fails in querymode with the syntax error 

1) create a datawindow with syntax similar to following (uses inline view):

SELECT podet.pod_po_no,   
rec_times_count, 
unposted_rec
FROM podet 
left outer JOIN
		( select rec_po_co, rec_po_no, rec_po_seq_no,
		  count( receive.rec_seq_no ) rec_times_count, 
		  sum(  rec_units  )  AS unposted_rec  
			from receive 
			join receive_master on recm_no = rec_recm_no
			group by rec_po_co, rec_po_no, rec_po_seq_no
		) rc ON rec_po_co = podet.pod_po_co AND rec_po_no =  podet.pod_po_no AND rec_po_seq_no = podet.pod_seq_no
WHERE ( podet.pod_dist_seq = 0)    


2.  hook up the datawindow.

3.  ls_result = Modify("datawindow.querymode=yes")

ls_result will have:  "Line 1  Column 24: incorrect syntax."

and the datawindow does not go into querymode


Remarks:
hasn't been updated in 20 years?
OS:
All 
Platform:
 
Database Type:
Microsoft SQL Server 
Database Version:
2012