Outer apply from Crystal Reports sql to OE 11.5 database?

Posted by richtpt on 24-Feb-2017 16:32

In Crystal Reports I'm writing a command to get the data.  When I try to save the command it tells me it can't find the outer apply table.field.  Here's the sql (note the outer apply is simplified for testing):

select tag_rec."tag-num", tags.TagDetTally, pro_duct.desc2 as 'Size', tag_rec.desc2 as 'TRLength'
  from tag_rec
       inner join pro_duct on pro_duct.product = tag_rec.product
       outer apply (select tag_detail.tagnum as 'TagDetTally '
                      from tag_detail
                     where tag_detail."tag-num" = tag_rec."tag-num") tags

It keeps saying it can't find tags.TagDetTally.  This works fine in Crystal when connecting to a sql server database.  Is this not possible?  Thanks!

p.s. Connection is using the Progress OpenEdge 11.5 ODBC driver.  The driver works fine, just can't get this outer apply working.

All Replies

Posted by marian.edu on 25-Feb-2017 00:50

That’s simply because cross/outer apply is a sql server thingy and not an SQL standard, you can probably get along with a regular (outer) join here…

select tag_rec."tag-num", tag_detail."tag-num" , pro_duct.desc2 as 'Size', tag_rec.desc2 as 'TRLength'
  from tag_rec
       inner join pro_duct on pro_duct.product = tag_rec.product
       outer join tag_detail on tag_detail."tag-num" = tag_rec."tag-num”

Marian Edu

Acorn IT 
+40 740 036 212

This thread is closed