1

This SQL (Firebird 2.5, Dialect 1) query works fine when I run it in Database Workbench, but it fails when I run it under IBO Console with:

count of column list and variable list do not match.

These two queries run successfully if I run them alone under IBO console, so I'm inferring that the problem is because of the "Union." The number and types of columns match for the two queries--both return string, smallint, string (IBO console regards this as a memo), string--so Union should be valid (and DB Workbench finds it so). Thinking that maybe the length of the data was different, I tried casting the third column as a VarChar(500) but that didn't help. Looking for ideas as to why this doesn't work since the part of our app that is executing the query is apparently choking on this in the same way that IBO Console is. 

Select QBI.TXNID as ID,
  Cast (1 as SmallInt) as TransactionType,
  ("Invoice " || QBI.REFNUMBER || ": $" || round(QBI.SUBTOTAL, 2) || " on " || QBI.TXNDATE || " for " || QBI.CUSTOMERREF_FULLNAME) as description,
  case
    when (QBI.CLASSREF_LISTID = "") then "Invoice has no class in Quickbooks"
    else "Invoice class doesn't match any dept on job " || JA.JOBID
  end as Problem
from QBINVOICE QBI
Join JOBACCOUNTINGID JA
  on QBI.CUSTOMERREF_LISTID = JA.jobaccountingid
  and QBI.SOURCEID = JA.SOURCEID
left Join CHARTOFACCOUNTS CA
  on (CA.qblistid = QBI.CLASSREF_LISTID and CA.qbsourceID = QBI.SOURCEID)
  and CA.CHARTACCOUNTTYPE = "SYSTEM"
  and CA.CHARTFETCH = "Y"
left Join DEPARTMENTJOB DJ
  on JA.JOBID = DJ.JobID
  and DJ.departmentID = CA.DEPARTMENTID
where DJ.DEPARTMENTID is null
  and QBI.TXNDATE >= "02/01/2017"

union all

select 
  QBELD.TXNLINEID as ID, 
  Cast (2 as SmallInt) as TransactionType,       
  QBB.VENDORREF_FULLNAME || " bill on " || QBB.TXNDATE || ": $" || round(QBELD.AMOUNT, 2) || " " || QBELD.ACCOUNTREF_FULLNAME || " expense" as description,
  case   
    when (QBELD.CLASSREF_LISTID = "") then "Expense has no class in Quickbooks"     
    else "Expense class doesn't match any dept on job " || JA.JOBID   
  end as Problem 
from QBTxnExpenseLineDetail QBELD 
JOIN QBBILL QBB
  on QBELD.TXNLINEID = QBB.TXNID 
  and QBELD.SOURCEID = QBB.SOURCEID 
Join JOBACCOUNTINGID JA 
  on QBELD.CUSTOMERREF_LISTID = JA.jobaccountingid 
  and QBELD.SOURCEID = JA.SOURCEID 
left Join CHARTOFACCOUNTS CA 
  on (CA.qblistid = QBELD.CLASSREF_LISTID and CA.qbsourceID = QBELD.SOURCEID) 
  and CA.CHARTACCOUNTTYPE = "SYSTEM" 
  and CA.CHARTFETCH = "Y" 
left Join DEPARTMENTJOB DJ 
  on JA.JOBID = DJ.JobID 
  and DJ.departmentID = CA.DEPARTMENTID 
where DJ.DEPARTMENTID is null   
  and QBB.TXNDATE >= "02/01/2017"
Arioch 'The
  • 15,799
  • 35
  • 62
Eric
  • 142
  • 1
  • 13
  • `"` are for identifiers (table names, column names). In SQL string constants need to be enclosed in single quotes, `"Invoice "` references a column. You probably meant to write: `'Invoice '` or e.g. `when (QBI.CLASSREF_LISTID = '')` –  Sep 27 '17 at 20:32
  • 1
    Dialect 1 generally allows the single and double quotes to be used interchangeably, so the double quotes don't appear to be what's the issue here. (Evidenced by the fact that both queries work as desired without the union.) I am aware that when we move to dialect 3, this will be an issue. Partly the double quotes are easier because this query is embedded in a delpi app, and delphi uses the single quotes to delimit strings. – Eric Sep 27 '17 at 21:59
  • @Eric - but you have problems not with Firebird but with IBO components and IBO console. Are you sure IBO is still 100% compatible with Dialect 1 obsoleted 17 years ago? I wonder if they do even a single test of d-1 compatibility today. Try those query in other Fiurebird IDEs: for example FlameRobin and IBExpert Personal. It is almost certainly have nothing with Firebird itself, but with your Delphi library – Arioch 'The Sep 28 '17 at 09:15
  • @Arioch: I wish we were using dialect 3 but that's outside my control. And I'm not actually working with IBO but with a server component (written in Delphi 7) that is simply failing without explaining why. (Wish I were in a position to change that too.) I just find that, if a query fails when it works in DB Workbench, IBO tells me why. Sounds like I'll just have to take a different approach to this. – Eric Sep 28 '17 at 14:26
  • I named you two different IDEs using different db connection libraries ( FIB+ and IBPP). Try them. IF you can show that all IDEs but IBOConsole work okay and IBOC fails - then you can show that their IBO version is buggy, so they should either replace or fix it. And I do not think it has DIRECT connection to dialect. Just make fresh D-3 database, copy into it relevant tables and make the same query in D-3. There is either very indirect link to dialects or none at all – Arioch 'The Sep 28 '17 at 14:43
  • As far as I am aware this error is for cases like `insert into table (column1, column2) values (value1, value2, value3)` (that is insert column count doesn't match values count), does your component maybe try to generate some kind of insert query for this? – Mark Rotteveel Sep 28 '17 at 14:43
  • `Select * from (Select ... union Select ....) as X` ? – Arioch 'The Sep 28 '17 at 14:45
  • Also consider running a trace session to see what queries are sent to Firebird server. – Mark Rotteveel Sep 28 '17 at 14:55
  • you query seems to be good, but without your db and IBO the error unfortunately cannot be repeated. Could you tell what if commenting columns in both sql union parts, after which column adding this error appears (e.g. `Select QBI.TXNID as ID from .....union all select QBELD.TXNLINEID as ID from ....` – Konstantin Streletsky Sep 29 '17 at 07:28
  • @Konstantin: Good approach to debugging this, though in this case I decided to be lazy and just do two queries. (I'm putting the results into a delphi clientdataset, so I just appended the second dataset to the first. Less elegant than the single query, but it worked and let me move on. Hopefully we'll update to newer controls before too long and this will cease to be an issue. – Eric Sep 29 '17 at 14:30

0 Answers0