Crystal Reports Subqueries

Question

I need to create a report that references multiple tables, including one table that is a line-by-line detail of an order. On orders, we have packages and the only way to determine if the package is of a certain status is to analyze the statuses of it's component members. The invoice detail is stored in sequential order with the line number and itemlevel of each line. Entries with itemlevel > 1 are parts of kits that immediately precede them. So, e.g.:

Invoiceiid | Lineno | LevelNo | SKU
----------------------------------------------------
10192      | 1      | 1       | K-PACKAGE
10192      | 2      | 2       | SCREEN
10192      | 3      | 2       | CABLE
10192      | 4      | 1       | LCD PROJECTOR

In this example, the SCREEN and CABLE are part of the K-PACKAGE, while the LCD PROJECTOR is not.

I modify the status of these packages depending on the status of their component parts. For my purposes, an average of the statuses works fine. I have the command included below as a smaller version of the SQL query I'm using (the larger version is just a brute force approach to handle multiple numbers of sub-items). The command is accepted in the Database Expert, Edit Command screen, but actually attempting to preview the report returns

"Database Connector Error: 'HY000:[Oracle][ODBC][Ora]ORA-01427: single-row subquery returns more than one row [Database Vendor Code: 1427 ]'"

when it actually starts attempting to pull results.

Here is the chopped down SQL query I'm using to try and find the problem. Right now, I'm only displaying the profitcenter to try and see if that was the issue.

select ih.billdate, ih.invoiceid, ih.profitcenter, co.contractid, il.action,
(
      case il.status
            when 15 then
            (
                  case when (((select il2.itemlevel from invoiceline il2 where il2.invoiceiid = il.invoiceiid and il2.lineno = (il.lineno + 1)) = 1) or ((select count(il2.itemlevel) from invoiceline il2 where il2.invoiceiid = il.invoiceiid and il2.lineno = (il.lineno + 1)) = 0))
                        then 15
                  when (((select max(il2.lineno) from invoiceline il2 where il2.invoiceiid = il.invoiceiid) <= (il.lineno + 2)))
                        then (select il5.status from invoiceline il5 where il5.invoiceiid = il.invoiceiid and il5.lineno = (il.lineno + 1))
                  else
                        15
                  end
            )
            else
                  il.status
      end
      ) as "Status",
il.amount, il.discountamount, im.revenuegroup, im.subrentalexpense, im.discountgroup, im.subrentglcode, im.sellglcode from invoicehead ih inner join contract co on ih.contractiid = co.contractiid left outer join invoiceline il on ih.invoiceiid = il.invoiceiid inner join invmaster im on il.inviid = im.invi

If I change

then (select il5.status from invoiceline il5 where il5.invoiceiid = il.invoiceiid and il5.lineno = (il.lineno + 1))

to just give a strict number (15, e.g.), the report previews with no problems.

And just to head things off at the pass: there is no other reference (at least none I can find) between the main package item and the component parts, nor can I change the database schema. And everything has to be in Crystal Reports.

Any thoughts?

Answer 1

Hi Michael,

It seems like you have records with the same (Invoiceiid, Lineno), can you check with the folowing query:

SELECT invoiceiid, lineno, COUNT(*)
  FROM invoiceline
 GROUP BY invoiceiid, lineno
HAVING COUNT(*) > 1

If this is the case I would suggest you correct your data and then add a UNIQUE CONSTRAINT on (Invoiceiid, Lineno)

Update

@Michael: in response to your comment: IF you are unable to correct the data you will have to decide what to do when you encounter dupicates. For example you can modify subqueries il2 and il5 so that they will return at most one row, e.g. replace SELECT il2.itemlevel with SELECT MAX(il2.itemlevel).

You could also use analytics to read the value of the next row (it will be more efficient than a subquery):

SELECT ih.billdate, ih.invoiceid, ih.profitcenter, co.contractid, il.action,
       (CASE il.status
          WHEN 15 THEN
           (CASE
              WHEN LEAD(il2.itemlevel) OVER(PARTITION BY invoiceiid ORDER BY lineno) IS NULL THEN
               15
              WHEN MAX(il2.lineno) OVER(PARTITION BY invoiceiid) <= il.lineno + 2 THEN
               LEAD(il5.status) OVER(PARTITION BY invoiceiid ORDER BY lineno)
              ELSE
               15
           END)
          ELSE
           il.status
       END) AS "Status", il.amount, il.discountamount, im.revenuegroup, im.subrentalexpense, im.discountgroup,
       im.subrentglcode, im.sellglcode
  FROM invoicehead ih
 INNER JOIN contract co ON ih.contractiid = co.contractiid
  LEFT OUTER JOIN invoiceline il ON ih.invoiceiid = il.invoiceiid
 INNER JOIN invmaster im ON il.inviid = im.invi

Answer 2

With thanks to Vincent...it turns out there were two different reasons for this occurring. One was that I did have duplicate entries as Vincent point out and I was able to repair those.

The other problem was I was assuming NULL values went to 0 (in this case they did not) and my boundaries were not being set properly. After adjusting my exception conditions, I was able to get the code working. Much thanks!