• You are here:  
  • Home
  • Q&A
  • Q&A
  • PowerBuilder
  • Data Source for SQL statement starting with "WITH"

Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (4)

Resolved Data Source for SQL statement starting with "WITH"

  1. How-to
0
Votes
Undo
  1. Lars Mosegaard
    Lars Mosegaard
  2. PowerBuilder
  3. Tuesday, 17 July 2018 03:22 AM UTC

I am creating a new datawindow using a SQL Select.

Because of some limitations in SQLServer, I need start with a WITH  (Common Table Expression)

I can't seem to get PowerBuilder to understand the result set and get no columns (Or PB Crash)   PB12.6

For example:

 

with hello ( descr, mydate)
as ( select 'Right now' , getdate() )

select * from hello



  1. DataWindow
  2. SQL Server
MySQL ADO.NET driver is not available for Powerbui...
web service target needs to return a file
Accepted Answer
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Tuesday, 17 July 2018 13:31 PM UTC
  2. PowerBuilder
  3. # Permalink

Hi Lars;

   PowerBuilder's SQL prowess is built upon the ANSI SQL standard. As such, the WITH statement is not an ANSI standard. Originally added to Oracle in 1999, its an "optional" alternative to a Create View approach. The WITH statement has been implemented by various DBMS vendors - man of whom have their own syntax variations.

   My suggestion would be encapsulate the WITH within your SS instance as a Stored Function / Procedure and just let PB handle the result set. That should take the SQL Painter ( and it's ANSI mentality ) out of the equation.

Food for thought.

Regards ... Chris

Comment
There are no comments made yet.
Responses (4)
François Rossignol
François Rossignol Accepted Answer Pending Moderation
  1. Tuesday, 17 July 2018 07:05 AM UTC
  2. PowerBuilder
  3. # 1

I use SQL Server CTE in multiple datawindows and never had a problem with them.

But I don't use the SELECT * statement I use SELECT col1, col2 etc...

Did you try that ?

Comment
Lars Mosegaard
  1. Lars Mosegaard
  2. Tuesday, 17 July 2018 23:23 PM UTC
Thanks Francois,



In the real query i did try selecting actual View column names with no real luck.



Following on from Chris' reply, I have to accept that PB struggles with non-ANSI and in my experience SQLServer struggles with Group by (In my experience). So will probably have to bite the bullet and create a real View or SP.
  1. Helpful
There are no comments made yet.
René Ullrich
René Ullrich Accepted Answer Pending Moderation
  1. Tuesday, 17 July 2018 05:18 AM UTC
  2. PowerBuilder
  3. # 2

Hi Lars,

I use Oracle and had the same problem. I hope my workaround is also usable for you.

Try

SELECT *
FROM (

with hello ( descr, mydate)
as ( select 'Right now' , getdate() )

select * from hello);
Comment
Lars Mosegaard
  1. Lars Mosegaard
  2. Tuesday, 17 July 2018 05:33 AM UTC
Hi Rene, Thanks for that. Sadly SQL Server doesn't like that syntax (near with)
  1. Helpful
There are no comments made yet.
Alfredo Aldama
Alfredo Aldama Accepted Answer Pending Moderation
  1. Tuesday, 17 July 2018 03:53 AM UTC
  2. PowerBuilder
  3. # 3

 

 

Hi,

 

I hope this workaround work !

Table Student
Id | Student | State
-------------------------
1 | Charles | 1
2 | Ana      | 1
3 | Diana   | 2
4 | Tom     | 2

 

Select T1.Id, getdate() From
(Select * From Student where State = 1) T1

 

 

Regards !

Comment
Lars Mosegaard
  1. Lars Mosegaard
  2. Tuesday, 17 July 2018 04:13 AM UTC
Thanks Alfredo... Not sure...

This is probably a better example of a Common Table Expression where the datawindow painter can't seem to understand the result set:



WITH Sales_CTE (SalesPersonID, NumberOfOrders)

AS

(

SELECT SalesPersonID, COUNT(*)

FROM Sales.SalesOrderHeader

WHERE SalesPersonID IS NOT NULL

GROUP BY SalesPersonID

)

SELECT AVG(NumberOfOrders) AS "Average Sales Per Person"

FROM Sales_CTE
  1. Helpful
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.