1. Tracy Lamb
  2. PowerBuilder
  3. Friday, 20 July 2018 15:13 PM UTC

I use the DW Query mode in all of the master lists and search screens in my app... it works like a charm.  But, I have a new requirement in one of my master windows to show the number of linked documents, which is stored in a different table.  So my basic query changed from:

SELECT c.cal_procedure ,           
c.description ,           
c.active ,           
c.create_user ,           
c.create_date ,           
c.edit_user ,           
c.edit_date
FROM cal_procedure c

to:

SELECT c.cal_procedure ,           
c.description ,           
c.active ,           
c.create_user ,           
c.create_date ,           
c.edit_user ,           
c.edit_date ,
(SELECT COUNT(*) FROM cal_procedure_doc where cal_procedure = c.cal_procedure) AS cpd
FROM cal_procedure c

Now I get an error every time I enter a query parameter.  The error is "Incorrect Syntax near the keyword 'and' ".  Is there a better way to construct the SQL statement?  I've tried a couple other ways with no luck.

TIA,
Tracy

Accepted Answer
mike S Accepted Answer Pending Moderation
  1. Friday, 20 July 2018 16:03 PM UTC
  2. PowerBuilder
  3. # Permalink

querymode has never been kept up to date.  standard sql like subqueries are not handled correctly.   

This should be reported as a bug  -  standard sql should work.

 

you can create a view if you don't need to edit anything.

or you can rewrite your query:

SELECT c.cal_procedure ,
c.description ,
c.active ,
c.create_user ,
c.create_date ,
c.edit_user ,
c.edit_date ,
cpd
FROM cal_procedure c
join (SELECT COUNT(*) as cpd, cal_procedure FROM cal_procedure_doc GROUP BY cal_procedure) calcount ON calcount.cal_procedure = c.cal_procedure

 

Comment
  1. Tracy Lamb
  2. Thursday, 26 July 2018 13:47 PM UTC
SELECT c.cal_procedure ,

c.description ,

c.active ,

c.create_user ,

c.create_date ,

c.edit_user ,

c.edit_date ,

cpd

FROM cal_procedure c

left outer join (SELECT COUNT(*) as cpd, cal_procedure FROM cal_procedure_doc GROUP BY cal_procedure) calcount ON calcount.cal_procedure = c.cal_procedure
  1. Helpful
  1. Tracy Lamb
  2. Thursday, 26 July 2018 13:49 PM UTC
I'm using SQL Server version 2012. The above syntax throws the following error when the dw is taken out of query mode... with or without query values:

"multi-part identifier "c.cal_procedure" could not be bound"
  1. Helpful
  1. mike S
  2. Thursday, 26 July 2018 16:27 PM UTC
run that in sql enterprise manager to figure out what the sql problem is.
  1. Helpful 1
There are no comments made yet.
mike S Accepted Answer Pending Moderation
  1. Monday, 11 February 2019 18:19 PM UTC
  2. PowerBuilder
  3. # 1

I just noticed that if you change to graphical mode instead of syntax mode, you can add your computed column in the compute tab of the graphical select. then it should work (at least it does for me). 

 

So it seems like while in querymode the datawindow is not able to handle sql that we write.

This of course makes zero sense, and gets back to the original point that query mode was never kept up to date. 

Comment
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.