Temporary tables

sventevit

New Member
I am new to Progress DB (used to work with SQL Server). In SQL Server there is a neat feature for preparing subselects ('with' keyword).

How can I write this query in Progress:
Code:
[B]WITH ProductAndCategoryNamesOverTenDollars (ProductName, CategoryName, UnitPrice) AS
(
   SELECT
      p.ProductName,
      c.CategoryName,
      p.UnitPrice
   FROM Products p
      INNER JOIN Categories c ON
         c.CategoryID = p.CategoryID
   WHERE p.UnitPrice > 10.0
)[/B]

SELECT *
FROM [B]ProductAndCategoryNamesOverTenDollars[/B]
ORDER BY CategoryName ASC, UnitPrice ASC, ProductName ASC
 

Cringer

ProgressTalk.com Moderator
Staff member
I'm not completely sure, but I think you need to define and populate a temp table defined as

Code:
DEFINE TEMP-TABLE ProductAndCategoryNamesOverTenDollars NO-UNDO
  FIELD ProductName LIKE Products.ProductName
  FIELD CategoryName LIKE Categories.CategoryName 
  FIELD UnitPrice LIKE Products.UnitPrice.

FOR EACH Products NO-LOCK
  WHERE Products.UnitPrice GT 10.0,
  EACH Category of Products NO-LOCK:
  CREATE ProductAndCategoryNamesOverTenDollars.
  BUFFER-COPY Products to ProductAndCategoryNamesOverTenDollars.
  BUFFER-COPY Category to ProductAndCategoryNamesOverTenDollars.
END.

FOR EACH ProductAndCategoryNamesOverTenDollars BY CategoryName UnitPrice ProductName:

END.
 

tamhas

ProgressTalk.com Sponsor
Are you trying to do this in SQL or ABL?

If the former, you might move the question to the SQL forum since you are likely to get ABL answers on this forum.
 

Cringer

ProgressTalk.com Moderator
Staff member
Aha I didn't think of that possibility.

Out of interest, is my 'solution' a viable one for what he is trying to achieve?
 

medu

Member
Not available on Progress SQL engine, as far that I know the WITH recursive select is part of SQL-99 standard while Progress only implements SQL-92.
 

sventevit

New Member
Sorry for my late response...

I think I need SQL statement, so I can execute it in WinSQL client and also in Visual Studio to populate some datasets.
What is ABL? :confused:

Cringer, your syntax is totaly new to me, have to look it up yet...
 

medu

Member
What is ABL? :confused:

Cringer, your syntax is totaly new to me, have to look it up yet...

Well, that's ABL and you sure does not want to use it in WinSQL :)

As I've said the WITH option is not available in Progress SQL engine but in your case i think the SQL is not that complicated that you can't rewrite it using a plain SELECT statement.
 
Top