Data Source for SQL statement starting with "WITH"

View Replies (4)

Resolved Data Source for SQL statement starting with "WITH"

How-to

Votes

Undo

Lars Mosegaard
PowerBuilder
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

Accepted Answer

Chris Pollach @Appeon Accepted Answer Pending Moderation

Tuesday, 17 July 2018 13:31 PM UTC
PowerBuilder
# 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 Accepted Answer Pending Moderation

Tuesday, 17 July 2018 07:05 AM UTC
PowerBuilder
# 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
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.

Helpful 0

There are no comments made yet.

René Ullrich Accepted Answer Pending Moderation

Tuesday, 17 July 2018 05:18 AM UTC
PowerBuilder
# 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
Tuesday, 17 July 2018 05:33 AM UTC

Hi Rene, Thanks for that. Sadly SQL Server doesn't like that syntax (near with)

Helpful 0

There are no comments made yet.

Alfredo Aldama Accepted Answer Pending Moderation

Tuesday, 17 July 2018 03:53 AM UTC
PowerBuilder
# 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
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

Helpful 0

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.

Please login to post a reply

You will need to be logged in to be able to post a reply. Login using the form on the right or register an account if you are new here. Register Here »

Forgot Password?

We use cookies which are necessary for the proper functioning of our websites. We also use cookies to analyze our traffic, improve your experience and provide social media features. If you continue to use this site, you consent to our use of cookies.