2

I need to convert a number of queries and functions from Progress / 4GL to SQL.

Can you help me get started, please? Here is one of the 4GL statements I need to convert. 

for each Part where (  
  Part.NonStock = false AND  
  Part.InActive = false AND  
  Part.QtyBearing = true) no-lock , 

  each PartWhse outer-join  where ( 
    Part.Company = PartWhse.Company and 
    Part.PartNum = PartWhse.PartNum) no-lock , 

  each PartCost outer-join  where (
    Part.Company = PartCost.Company and 
    Part.PartNum = PartCost.PartNum) no-lock .

Can you explain the 4GL bits and give some hints as to what the SQL would look like.

I have some SQL knowledge, but next to no 4GL knowledge.

Michael Vincent
  • 1,620
  • 1
  • 20
  • 46

1 Answers1

2
select *
from part p 
left outer join partwhse w on p.Company = w.Company
left outer join partCost c on p.Company = c.Company and p.PartNum = c.PartNum
where p.NonStock = false
and p.Inactive = false
and p.QtyBearing = true;

The no-lock bits would just add with (no lock) to the table declarations which isn't good practice and I'd avoid unless really needed.

simon at rcl
  • 7,326
  • 1
  • 17
  • 24
  • Thank you, Simon, though I needed quotes around true and false for it to get through the syntax checker. The query takes ages - 5 minutes and counting - to run, so I guess there is still work to do on it. Thanks again – Michael Vincent Sep 18 '15 at 14:04
  • No problem! Using SSMS, get the query plan and post it as a new question about speeding up the query. – simon at rcl Sep 18 '15 at 14:34
  • I cannot say about the sql-part but for Progress the no-lock part is essential. No-lock means "read-only", without it record locking as well as transactions will occur (in Progress 4GL). – Jensd Sep 18 '15 at 16:25
  • 1
    The no-lock syntax above is legal but it is also unusually placed. Usually the NO-LOCK (or SHARE-LOCK or EXCLUSIVE-LOCK) would immediately follow the table name. It may be that familiarity makes it seem natural but IMHO it does read much more easily to put the lock state right after the table name. – Tom Bascom Sep 18 '15 at 17:17
  • The problem with using NO LOCK in SQL Server is that it allows uncommitted reads - which leaves you open to reading incorrect and/or incostent data. This is sometimes OK (some people have a horror of doing it; I feel it's usually alright). – simon at rcl Sep 19 '15 at 17:03
  • +1 for the bit about NO LOCK in SQL Server - I didn't know that about that functionality! So does not using NO LOCK in SQL Server actually prevent other processes from updating a record, even if there is no modification of it? – zr00 Oct 17 '18 at 18:24
  • Short answer: it can. Longer answer: it's complicated and I think depends on what it being updated (and how) and what's being read. Using NOLOCK allows reads of uncommitted data as I said, and this means potentially inconsistent data which is bad unless the user knows to expect it. – simon at rcl Oct 18 '18 at 17:00