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

Resolved Outer Join syntax from PB to ANSI

  1. How-to
0
Votes
Undo
  1. Luca DG
    Luca DG
  2. PowerBuilder
  3. Tuesday, 13 August 2019 15:53 PM UTC

Hello 

I have several datawindows coming from an old application with graphic SQL written win Powerbuilder outer Join syntax (*= or =*). For example:

select student.name, student_email.email from student, student_email where student.id *= student_email.id

become with ANSI

select student.name, student_email.email from student left outer join student_email on student.id = student_email.id

Changing the connection parameters for outer joins from PB to ANSI i have see that if I open a DW with a simple outer join the syntax changes from PB to ANSI but I have to made any little modify for saving the DW.

Without savig the DW (and so I have also to recheck the Update Properties) the syntax does not change.

Is there any "automatic" way for changing the old PB outer join syntax to the ANSI without re-opening the DW's and save them ?

I'm working with PB 12.5 and SQL 2012 or 2014

Thanks, Luca.

  1. DataWindow
  2. SQL Server
Using a REGISTRATION-FREE .Net DLL in PowerBuilder
Error creating an instance of a .net DataStore
Responses (3)
Olan Knight
Olan Knight Accepted Answer Pending Moderation
  1. Tuesday, 13 August 2019 16:42 PM UTC
  2. PowerBuilder
  3. # 1

Have you tried editing the export files, the ".srd" files with a batch editing app?

1) Place all of the exported SRD files into a single folder
2) Run your editor against the files in that folder, changing "*=" to " left outer join ", and "=*
     to the equivalent ANSI statement.
3) Create a new PBL, import all of the modified SRD files into this new PBL,  and add it to your application Library List.
4) Do a full rebuild.
5) If the DWs in the new PBL regenerated correctly, you are golden.

There are several batch editing tools, and I think that Roland's PBSEARCH handles this operation.
      http://www.topwizprogramming.com/pbsearch.html 

You can use the free tool Notepad++ :
      https://www.makeuseof.com/tag/how-to-find-and-replace-words-in-multiple-files/


Good Luck,

Olan

 

Comment
Kevin Ridley
  1. Kevin Ridley
  2. Tuesday, 13 August 2019 17:41 PM UTC
On #2 above, the "*=" / "=*" is found in the where clause, so that wouldn't work.
  1. Helpful
There are no comments made yet.
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Wednesday, 14 August 2019 21:18 PM UTC
  2. PowerBuilder
  3. # 2

Hi Luca;

   FWIW:  I have had to do this many times in the past. I found that the work was mainly manual in nature - although I did utiliize SQLExpert from Quest. I believe that they now call this product "SQL Optimizer". They have versions for SS, Oracle, & ASE

Regards... Chris

Comment
There are no comments made yet.
Luca DG
Luca DG Accepted Answer Pending Moderation
  1. Thursday, 22 August 2019 20:10 PM UTC
  2. PowerBuilder
  3. # 3

Hello,

thanks for the reply.

I have see that using the ANSI_Escape syntax (found in one other post) PB convert the syntax.

The problem is that if you have complex qury with outer join onm the same table teh SNC goes in error.

So, basically, I have to re-check everything cry

Thanks anyway!

Comment
mike S
  1. mike S
  2. Friday, 23 August 2019 15:28 PM UTC
yes, PB does not handle outer joins well if you have a complex query with multiple columns as part of the join. imo, it was poorly done years ago when it was first done. it will mix and match left and right outer joins and use the same table multiple times when it should be used once. graphical dws are pretty much useless for anything complex.



  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.