SRB report from query over MS Access db fails with SQL S1000

Getting your data onto paper - or the web - Discussing the reports features of Base

SRB report from query over MS Access db fails with SQL S1000

Postby pdow » Mon Dec 15, 2008 8:10 am

I'm running Win XP Pro with SP 2 and OO3.0 build 9358.

I opened an MS Access database and created a query, then a report using Sun Report Builder 1.0.5.

When I attempt to run the report, I get SQL status S1000 with no further indication of what's causing the problem; however, the problem appears to be with an expression in the SELECT field list:

This works:

Code: Select all   Expand viewCollapse view
SELECT Classes.ClassName as ClassName,
      Parents.LastName,
      Parents.ID as ID,
      Parents.SSNo as SSNo
FROM Children, Classes, Parents
WHERE Children.ClassId = Classes.ID
  AND Parents.ID = Children.ParentID
      and  ClassName <> 'Alumni'
      and ClassName <> 'Inactive'
      and ClassName <> 'Waiting List'
ORDER BY 1,2,3


This does NOT work:
Code: Select all   Expand viewCollapse view
SELECT Classes.ClassName as ClassName,
      Parents.LastName + ', ' + Parents.FirstName as ParentName,
      Parents.ID as ID,
      Parents.SSNo as SSNo
FROM Children, Classes, Parents
WHERE Children.ClassId = Classes.ID
  AND Parents.ID = Children.ParentID
      and  ClassName <> 'Alumni'
      and ClassName <> 'Inactive'
      and ClassName <> 'Waiting List'
ORDER BY 1,2,3


Something has a problem with the expression "Parents.LastName + ', ' + Parents.FirstName as ParentName," or I'm entering it incorrectly.

Any suggestions?
OOo 3.0.X on Ms Windows XP
pdow
 
Posts: 1
Joined: Sun Dec 14, 2008 10:10 pm

Re: SRB report from query over MS Access db fails with SQL S1000

Postby eremmel » Wed Jan 07, 2009 11:27 pm

It's Microsoft marketing that tells you computers are qualified for non-technicians
eremmel
 
Posts: 1041
Joined: Tue Dec 30, 2008 1:15 am

Re: SRB report from query over MS Access db fails with SQL S1000

Postby Villeroy » Thu Jan 08, 2009 12:05 am

If the driver supports "+" as concatenation operator you should be able to use it in "direct SQL mode", also known as "pass through query" in MS Access. While in SQL view of your query, switch on menu:Edit>Run SQL directly

Like so many other matters of course, the Base developers simply forgot(?) to implement such an operator. In "parsed SQL mode" or in the graphical designer for the "parsed mode" you can work-around this limitation with nested calls to the Concat function.
Code: Select all   Expand viewCollapse view
Concat("Field1",Concat('+',"Field2"))
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27895
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Reporting

Who is online

Users browsing this forum: No registered users and 1 guest