1. will gardner
  2. PowerBuilder
  3. Tuesday, 30 November 2021 17:00 PM UTC

Hello, 

We have an app with about 423 different sql queries that use *= in them which is causing errors when migrating to a newer sql server.  

Apparently this syntax was deprecated in the early 90s and it is was used for left outer joins.

Do any of you have any advice for a good way to do this, or perhaps a tool that could convert the syntax?

any help/advice is greatly appreciated.

Thanks

 

example is below:

select a.code_id, b.code_id, c.code_id
into :ls_cat, :ls_type, :ls_subtype
from mc_perm, mc_cod2 a, mc_cod2 b, mc_cod2 c
where (mc_perm.mc_perm_id = :ll_mc_perm_id)
and (mc_perm.category *= a.code_id and a.code_type_id = 22)
and (mc_perm.type *= b.code_id and b.code_type_id = 24)
and (mc_perm.subtype *= c.code_id and c.code_type_id = 66); 

Matt Balent Accepted Answer Pending Moderation
  1. Wednesday, 1 December 2021 20:47 PM UTC
  2. PowerBuilder
  3. # 1

Since you already have a bunch of refactoring to do don't forget to convert the entire SQL.  What I mean by this is

 

Starting SQL:

SELECT...

FROM RED, BLUE, GREEN

WHERE RED.ID *= BLUE.ID

AND BLUE.ID = GREEN.ID

 

Correctly rewritten:

SELECT...

FROM RED

LEFT JOIN BLUE ON RED.ID = BLUE.ID

JOIN GREEN ON BLUE.ID = GREEN.ID

 

Incorrectly rewritten:

SELECT...

FROM RED, GREEN

LEFT JOIN BLUE ON RED.ID = BLUE.ID

WHERE GREEN.ID = BLUE.ID

Comment
There are no comments made yet.
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Tuesday, 30 November 2021 19:44 PM UTC
  2. PowerBuilder
  3. # 2

Apart from Chris' great advice, here are 2 tips:

1. just in case: search not only for *= but also for =*

2. if you use Sql Sever management studio, you can create a new query using the query designer ( CTRL+SHIFT+Q). You can then paste your "old" SQL and your old sql will automatically be converted to modern SQL. It might save you some headaches. (I'm using the latest SSMS from the free sqlserver developer edition).

regards

Comment
There are no comments made yet.
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Tuesday, 30 November 2021 18:20 PM UTC
  2. PowerBuilder
  3. # 3

Hi Will;

  If the DWO objects were built where the SQL was saved in Graphics mode, then the Transaction Object's DBParm setting controls the join syntax automatically, as follows:

SQLCA.DBParm = "OJSyntax='PB'"             // VS

SQLCA.DBParm = "OJSyntax='ANSI_Escape'"    // VS

SQLCA.DBParm = "OJSyntax='ANSI'" 

  If you saved the SQL from the DW Painter "As Syntax" - then you will need to update every DWO's SQL manually. Of course, in-line SQL is going to be a manual update anyway. That is why I always recommend...

  1. Never use in-line SQL. Use a DWO instead.
  2. Always build DWO's & save then in SQL Graphics mode.

HTH

Regards ... Chris

 

 

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.
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.