Limitations to Unioned SQL Command?

[wwmathieson]

I'm using CR XI, with an ODBC connection to FoxPro. Its a File DSN with Fetch Data in Background turned off. OS is XP Pro on a machine with 1 GB of RAM.

The table structures are such that to collect the data I need, I have strung together 8 SQL statements with UNION ALL and dropped the data into appropriate alias.

The report seems to run OK with parameters that result in a smaller data set, except that some of the data is reported erroneously, with inappropriate and inconsistent rounding!

When the report is run with parameters that result in a larger data set, I get the error message "Failed to retrieve data from the database." followed by "Databse Connector Error: '01501:[Micorsoft]{ODBC Visual FoxPro Driver]Error in row'" followed by "Database Connector Error: '22003:[Microsoft][ODBC Visual FoxPro Driver]Error: Column 8 ( costtodate )'". "costtodate" is the 8th (but not the last) alias being populated by the command.

I've played with reducing the number of SQL statements unioned in the command and been able to get better data set results, but I'm not getting the data I need for the report.

I also found when originally constructing the command that there is a limit of 8 or 9 unions permitted in a command, but the present command does not warn that limit because I'm only using 7 unions....

Am I running into another limit in the use of SQL commands? Is there some environmental setting that I'm missing that will permit the command to run cleanly?

I'm a software consultant, not a programmer, but I suspect that some will suggest that I create a temp table for the report with FoxPro or Access. That's beyond my capabilities and unless its totally automatic and transparent to my client, that's not a viable solution.

Thanks in advance for your insights.

 

[beltmanjr]

Does this run well outside of crystal, in an sql program that communicates directly with the database?

 

[wwmathieson]

I created each individual SQL statement with Crystal originally (copying the SQL text off into a text processor) and then added the UNION ALL connector between each individual statement. The concept has worked well for me with less ambitious projects (fewer SQL statements being unioned) in the past. When I noted this problem with the current project, I started cutting down the number of unioned SQL statements in the command and was able to get good datasets. My fear is that the ODBC driver for FoxPro has some sort of limitation that is preventing me from running the complete command.

I spent the weekend refreshing my understanding of MS Access and have come up with a workaround, linking an Access database to the required FoxPro tables and using Append Queries to create the Dataset I couldn't get from the Crystal command. I hate asking the client to update the Access database (basically just opening and closing the database - "autoexec" macros are a wonderful tool) before running the Crystal Report, but at least they can get their report....

Thanks for reading the thread, and if there are any magical solutions to the Command problem, please let me know.

 

[beltmanjr]

I created each individual SQL statement with Crystal originally (copying the SQL text off into a text processor)

Scary!

Anyway, I just did a 13x union all using a OLE DB and oracle DB in crystal without any issues.

Perhaps just paste the sql here for us to have a look?

 

[wwmathieson]

Here's the full text of the Command:

SELECT 
`actrec`.`recnum` as 'jobnumber', 
`actrec`.`jobnme` as 'jobname', 
`actrec`.`shtnme` as 'jobshort', 
`actrec`.`sttdte` as 'startdate', 
`actrec`.`cmpdte` as 'finishdate', 
`bdglin`.`cstcde` as 'costcode', 
`bdglin`.`matbdg` as 'budgetamt',
0.00 as 'costtodate',
1 as 'costtype',
ctod('00/00/0000') as 'date'
FROM  
`actrec` `actrec` LEFT OUTER JOIN `bdglin` `bdglin` ON `actrec`.`recnum`=`bdglin`.`recnum`
WHERE  
`actrec`.`recnum`={?jobnumber}
AND (`bdglin`.`cstcde`=6000 OR `bdglin`.`cstcde`=7001 OR `bdglin`.`cstcde`=7002 OR `bdglin`.`cstcde`=7003 OR `bdglin`.`cstcde`=7100 OR `bdglin`.`cstcde`=7200)
AND `bdglin`.`matbdg`<>0

UNION ALL

SELECT 
`actrec`.`recnum` as 'jobnumber', 
`actrec`.`jobnme` as 'jobname', 
`actrec`.`shtnme` as 'jobshort', 
`actrec`.`sttdte` as 'startdate', 
`actrec`.`cmpdte` as 'finishdate', 
`bdglin`.`cstcde` as 'costcode', 
`bdglin`.`labbdg` as 'budgetamt',
0.00 as 'costtodate',
2 as 'costtype',
ctod('00/00/0000') as 'date'
FROM  
`actrec` `actrec` LEFT OUTER JOIN `bdglin` `bdglin` ON `actrec`.`recnum`=`bdglin`.`recnum`
WHERE  
`actrec`.`recnum`={?jobnumber}
AND (`bdglin`.`cstcde`=6000 OR `bdglin`.`cstcde`=7001 OR `bdglin`.`cstcde`=7002 OR `bdglin`.`cstcde`=7003 OR `bdglin`.`cstcde`=7100 OR `bdglin`.`cstcde`=7200)
AND `bdglin`.`labbdg`<>0

UNION ALL

SELECT 
`actrec`.`recnum` as 'jobnumber', 
`actrec`.`jobnme` as 'jobname', 
`actrec`.`shtnme` as 'jobshort', 
`actrec`.`sttdte` as 'startdate', 
`actrec`.`cmpdte` as 'finishdate', 
`bdglin`.`cstcde` as 'costcode', 
`bdglin`.`eqpbdg` as 'budgetamt',
0.00 as 'costtodate',
3 as 'costtype',
ctod('00/00/0000') as 'date'
FROM  
`actrec` `actrec` LEFT OUTER JOIN `bdglin` `bdglin` ON `actrec`.`recnum`=`bdglin`.`recnum`
WHERE  
`actrec`.`recnum`={?jobnumber}
AND (`bdglin`.`cstcde`=6000 OR `bdglin`.`cstcde`=7001 OR `bdglin`.`cstcde`=7002 OR `bdglin`.`cstcde`=7003 OR `bdglin`.`cstcde`=7100 OR `bdglin`.`cstcde`=7200)
AND `bdglin`.`eqpbdg`<>0

UNION ALL

SELECT 
`actrec`.`recnum` as 'jobnumber', 
`actrec`.`jobnme` as 'jobname', 
`actrec`.`shtnme` as 'jobshort', 
`actrec`.`sttdte` as 'startdate', 
`actrec`.`cmpdte` as 'finishdate', 
`bdglin`.`cstcde` as 'costcode', 
`bdglin`.`subbdg` as 'budgetamt',
0.00 as 'costtodate',
4 as 'costtype',
ctod('00/00/0000') as 'date'
FROM  
`actrec` `actrec` LEFT OUTER JOIN `bdglin` `bdglin` ON `actrec`.`recnum`=`bdglin`.`recnum`
WHERE  
`actrec`.`recnum`={?jobnumber}
AND (`bdglin`.`cstcde`=6000 OR `bdglin`.`cstcde`=7001 OR `bdglin`.`cstcde`=7002 OR `bdglin`.`cstcde`=7003 OR `bdglin`.`cstcde`=7100 OR `bdglin`.`cstcde`=7200)
AND `bdglin`.`subbdg`<>0

UNION ALL

SELECT 
`actrec`.`recnum` as 'jobnumber', 
`actrec`.`jobnme` as 'jobname', 
`actrec`.`shtnme` as 'jobshort', 
`actrec`.`sttdte` as 'startdate', 
`actrec`.`cmpdte` as 'finishdate', 
`bdglin`.`cstcde` as 'costcode', 
`bdglin`.`othbdg` as 'budgetamt',
0.00 as 'costtodate',
5 as 'costtype',
ctod('00/00/0000') as 'date'
FROM  
`actrec` `actrec` LEFT OUTER JOIN `bdglin` `bdglin` ON `actrec`.`recnum`=`bdglin`.`recnum`
WHERE  
`actrec`.`recnum`={?jobnumber} 
AND (`bdglin`.`cstcde`=6000 OR `bdglin`.`cstcde`=7001 OR `bdglin`.`cstcde`=7002 OR `bdglin`.`cstcde`=7003 OR `bdglin`.`cstcde`=7100 OR `bdglin`.`cstcde`=7200)
AND `bdglin`.`othbdg`<>0

UNION ALL

SELECT 
`actrec`.`recnum` as 'jobnumber', 
`actrec`.`jobnme` as 'jobname', 
`actrec`.`shtnme` as 'jobshort', 
`actrec`.`sttdte` as 'startdate', 
`actrec`.`cmpdte` as 'finishdate', 
`bdglin`.`cstcde` as 'costcode',  
`bdglin`.`usrcs6` as 'budgetamt',
0.00 as 'costtodate',
6 as 'costtype',
ctod('00/00/0000') as 'date'
FROM  
`actrec` `actrec` LEFT OUTER JOIN `bdglin` `bdglin` ON `actrec`.`recnum`=`bdglin`.`recnum`
WHERE  
`actrec`.`recnum`={?jobnumber}
AND (`bdglin`.`cstcde`=6000 OR `bdglin`.`cstcde`=7001 OR `bdglin`.`cstcde`=7002 OR `bdglin`.`cstcde`=7003 OR `bdglin`.`cstcde`=7100 OR `bdglin`.`cstcde`=7200)
AND `bdglin`.`usrcs6`<>0

UNION ALL

SELECT 
`actrec`.`recnum` as 'jobnumber', 
`actrec`.`jobnme` as 'jobname', 
`actrec`.`shtnme` as 'jobshort', 
`actrec`.`sttdte` as 'startdate', 
`actrec`.`cmpdte` as 'finishdate', 
`bdglin`.`cstcde` as 'costcode',  
`bdglin`.`usrcs7` as 'budgetamt',
0.00 as 'costtodate',
7 as 'costtype',
ctod('00/00/0000') as 'date'
FROM  
`actrec` `actrec` LEFT OUTER JOIN `bdglin` `bdglin` ON `actrec`.`recnum`=`bdglin`.`recnum`
WHERE  
`actrec`.`recnum`={?jobnumber}
AND (`bdglin`.`cstcde`=6000 OR `bdglin`.`cstcde`=7001 OR `bdglin`.`cstcde`=7002 OR `bdglin`.`cstcde`=7003 OR `bdglin`.`cstcde`=7100 OR `bdglin`.`cstcde`=7200)
AND `bdglin`.`usrcs7`<>0

UNION ALL

SELECT 
`actrec`.`recnum` as 'jobnumber', 
`actrec`.`jobnme` as 'jobname', 
`actrec`.`shtnme` as 'jobshort', 
`actrec`.`sttdte` as 'startdate', 
`actrec`.`cmpdte` as 'finishdate', 
`sbcgln`.`cstcde` as 'costcode',
`sbcgln`.`bdgprc` as 'budgetamt',  
0.00 as 'costtodate',
`sbcgln`.`csttyp` as 'costtype',
ctod('00/00/0000') as 'date'
FROM   
`sbcgln` `sbcgln` LEFT OUTER JOIN (`actrec` `actrec` LEFT OUTER JOIN `prmchg` `prmchg` ON `actrec`.`recnum`=`prmchg`.`jobnum`) ON `sbcgln`.`recnum`=`prmchg`.`recnum`
WHERE  
`actrec`.`recnum`={?jobnumber}
AND (`sbcgln`.`cstcde`=6000 OR `sbcgln`.`cstcde`=7001 OR `sbcgln`.`cstcde`=7002 OR `sbcgln`.`cstcde`=7003 OR `sbcgln`.`cstcde`=7100 OR `sbcgln`.`cstcde`=7200)
AND `prmchg`.`status`=1

UNION ALL

SELECT 
`actrec`.`recnum` as 'jobnumber', 
`actrec`.`jobnme` as 'jobname', 
`actrec`.`shtnme` as 'jobshort', 
`actrec`.`sttdte` as 'startdate', 
`actrec`.`cmpdte` as 'finishdate', 
`jobcst`.`cstcde` as 'costcode', 
0.00 as 'budgetamt', 
`jobcst`.`cstamt` as 'costtodate', 
`jobcst`.`csttyp` as 'costtype', 
`jobcst`.`entdte` as 'date'
FROM   
`actrec` `actrec` INNER JOIN `jobcst` `jobcst` ON `actrec`.`recnum`=`jobcst`.`jobnum`
WHERE  
`actrec`.`recnum`={?jobnumber}
AND `jobcst`.`status`=1 
AND (`jobcst`.`cstcde`=6000 OR `jobcst`.`cstcde`=7001 OR `jobcst`.`cstcde`=7002 OR `jobcst`.`cstcde`=7003 OR `jobcst`.`cstcde`=7100 OR `jobcst`.`cstcde`=7200)
AND `jobcst`.`entdte` <= {?costdate}

I apologize for the length of the code, but I said it was a big one. (and the attachment process here is still a minor mystery to me or I would have just posted a text file) I guess my fear is that the problem lies with the ODBC driver for FoxPro....

 

[beltmanjr]

What kind of field is `jobcst`.`cstamt`? (last costtodate)?
I guess crystal sets it to number field everywhere as you hardcode a number, except for the last one.

 

[wwmathieson]

The FoxPro definition for jobcst.cstamt is numeric, 12 digits wide, including 2 decimal places.

 

[beltmanjr]

Did you try what happens if you leave the last union statement out?

Or what if you change the last
`jobcst`.`cstamt` as 'costtodate'
to
0.00 as 'costtodate' ?

 

[IdoMillet]

I believe Foxpro has its own unique limitation on number of UNIONS.

You shouldn't need to use append queries in MS Access.
Just use a UNION query in Access against the linked tables. Access doesn't suffer from the same limitation as FoxPro on the number of UNIONS.

- Ido

view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com