• You are here:  
  • Home
  • Q&A
  • Q&A
  • PowerBuilder
  • Re: DW with PostgreSQL - Crosstab with Retrieval argument

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

Resolved Re: DW with PostgreSQL - Crosstab with Retrieval argument

  1. Issue
0
Votes
Undo
  1. Sivaprakash BKR
    Sivaprakash BKR
  2. PowerBuilder
  3. Thursday, 16 March 2023 08:30 AM UTC

Hello,

PB 2019R3 - PostgreSQL 11

Sample Query to construct a datawindow.   Actual query is bit length and does more

SELECT *
FROM crosstab(
     $$SELECT *
     FROM example
     Where field1 = '100'
     And   field2 = '200'
     ORDER BY id ASC, key ASC;$$
) AS ct(blablafirst INT, blablasecond TEXT, blablathird TEXT);


The above query works. NO issues.

When we replace '100' with retrieval argument like this

     Where field1 = :as_data1
     And   field2 = '200'                 <---------------  Error in this line
And then retrieve the datawindow, we get error


Any solution?

Happiness Always
BKR Sivaprakash

 

 

  1. DataWindow
  2. PowerBuilder (Appeon)
Application EXE Working in source machine not in o...
PowerBuilder 2017 Installer
Responses (5)
Andreas Mykonios
Andreas Mykonios Accepted Answer Pending Moderation
  1. Thursday, 16 March 2023 09:53 AM UTC
  2. PowerBuilder
  3. # 1

Hi.

I'm not familiar with postgresql, but I guess that powerbuilder for some reason fails to replace the argument in the $$ sub-command. Instead of doing that why don't you try to work with getsqlselect and setsqlselect? If the query without arguments works, setsqlselect should also work...

Andreas.

Comment
Sivaprakash BKR
  1. Sivaprakash BKR
  2. Thursday, 16 March 2023 11:11 AM UTC
Thanks Andreas. That's what we did, as a workaround.

Since PB supports PostgreSQL, I thought this ($$ sub-command) should also work.

$$ sub-command could also be replaced with " (Double-quote). Again that also didn't work.
  1. Helpful
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Thursday, 16 March 2023 12:24 PM UTC
Hi BKR;

In this case, how about implementing this DBMS specific SQL feature with a "Stored Procedure"?

Regards ... Chris
  1. Helpful 2
Sivaprakash BKR
  1. Sivaprakash BKR
  2. Friday, 17 March 2023 08:26 AM UTC
Chris,

As Andreas pointed out, SetSQLSelect works. SetSQLSelect is better than Stored Procedure, IMO, in my use case.

And also, not sure whether SP are fully supported in PB 2019 R3 [ I haven't used PostGreSQL SP's and remember reading about its limitations in this forum ].

  1. Helpful
There are no comments made yet.
John Raghanti
John Raghanti Accepted Answer Pending Moderation
  1. Thursday, 16 March 2023 11:12 AM UTC
  2. PowerBuilder
  3. # 2

Hi, what's the error that you're getting? Did you remember to define the retrieval argument in the Specify Retrieval Arguments window?

Comment
There are no comments made yet.
Sivaprakash BKR
Sivaprakash BKR Accepted Answer Pending Moderation
  1. Thursday, 16 March 2023 11:27 AM UTC
  2. PowerBuilder
  3. # 3

Thanks John,

Don't know how the error message disappeared.  I did paste, now it's not there.  Nevertheless I'm pasting it again.  Here is the message, after retrieval argument is given.

 

Happiness Always
BKR Sivaprakash

 

 

 

Comment
mike S
  1. mike S
  2. Thursday, 16 March 2023 14:32 PM UTC
make sure you don't have some weird whitespace. replace all the whitespace with actual spaces
  1. Helpful
Sivaprakash BKR
  1. Sivaprakash BKR
  2. Friday, 17 March 2023 08:32 AM UTC
Nothing like that mike. We just replaced the hardcoded retrieval value with retrieval argument. That's all.

Works fine if we replace the retrieval argument with hardcoded value.

SetSQLSelect also works fine.

  1. Helpful
There are no comments made yet.
Andreas Mykonios
Andreas Mykonios Accepted Answer Pending Moderation
  1. Thursday, 16 March 2023 11:48 AM UTC
  2. PowerBuilder
  3. # 4

Just to check, have you tried to put some code in dberror event of your datawindow and see what's the value of sqlsyntax?

Andreas.

Comment
Sivaprakash BKR
  1. Sivaprakash BKR
  2. Friday, 17 March 2023 08:33 AM UTC
Though I havent checked that yet, I think the error might pop up first, before sending the query to db. Nevertheless, let me check it and come back.
  1. Helpful
Andreas Mykonios
  1. Andreas Mykonios
  2. Friday, 17 March 2023 09:05 AM UTC
Even better, if possible add sql preview event to your datawindow to see sqlsyntax in there.

Andreas.
  1. Helpful
Andreas Mykonios
  1. Andreas Mykonios
  2. Friday, 17 March 2023 10:02 AM UTC
Well using dberror and sqlpreview should be done while running the application.

Andreas.
  1. Helpful
There are no comments made yet.
Olan Knight
Olan Knight Accepted Answer Pending Moderation
  1. Thursday, 16 March 2023 22:32 PM UTC
  2. PowerBuilder
  3. # 5

Things may be different with PB2019 thru PB2022, but when using previous versions of PB whenever I created a crosstab DW I was NOT able to modify it after it had been created and compiled. I always had to recreate the crosstab if I wanted to make a change.

I hope things are different now, but for me there came a point where it was simply not worth spending any more time trying to modify the existing DWO, it was far faster just to recreate it.

Comment
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Thursday, 16 March 2023 23:22 PM UTC
Hi Olan;

That was and still is, a long term restriction of the Crosstab DWO style. :-(

Regards ... Chris
  1. Helpful 1
Sivaprakash BKR
  1. Sivaprakash BKR
  2. Friday, 17 March 2023 08:29 AM UTC
Olan,

Only in query we generate Cross Tab and result is shown in Tabular DW. Just to overcome the limitations of cross-tab dw, you mentioned, we went with this [special (!)] query. We used a Tabular DW to show the output.

  1. Helpful 1
Olan Knight
  1. Olan Knight
  2. Saturday, 18 March 2023 14:05 PM UTC
That was a good solution, Sivaprakash! :)
  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.