subquery doesn't work with TOP if multiple rows returned using command feature of Crystal Reports

[littlered]

Hi, I am using Crystal Reports 2016 for Sage to pull data for itemcodes which have may have one or more binlocations using the COMMAND feature. The subquery needs to pull a certain number of itemcodes. The outer query then selects additional data (represented by the ... in the query below) for those itemcodes. If each itemcode has only one binlocation, three rows of data should be output. If one or more itemcodes has more than one binlocation, a row should be output for each of the itemcode/binlocation combinations.

The query works fine if I specify an itemcode in the subquery and specify 'x.itemcode =' in the outer query.

If I run the query as shown below with 'TOP 3' in the subquery and 'x.itemcode IN' in the outer query, the query never returns, and I have to kill CR and start again.

Please advise how I can correct. Thanks!

SELECT

x.itemcode

...

FROM

ci_item x

WHERE

x.itemcode IN

(

SELECT TOP 3

a.itemcode

FROM

ci_item a

,im_itemwarehouse b

WHERE

a.itemcode = b.itemcode

and b.warehousecode = '002'

and a.udf_inventory_class = 'HDHV'

and a.lastphysicalcountdate is null

and b.quantityonhand <> 0

)

[littlered]

Hey Jamie,

I did start out using LIMIT and it wouldn't run. That's how I wound up with TOP which is happy unless it is in a subquery...

Thanks!

Lori

[JWiseman]

The only other suggestions I can think of, if you want to pursue the Command object method, would be to:

1. check for an updated ODBC driver for Providex
2. or try using a derived table in a join to provide the Top N.

As Dell mentioned there is a built-in Top N in Crystal which would avoid this issue.

[littlered]

Hi,

I think both of you are correct in that the DB we are using is balking at my wishes. We use Providex, so we don't have the Sage version with SQL. I unfortunately do not have any other means to send my query to the DB. I will try the TopN and see if I can get that to help. This may turn out to be a report that really needs a nice programming language like SAS to get the job done.

Thanks!

Lori

[JWiseman]

Ok...another suggestion is to try using a LIMIT in the subquery after the ORDER BY...i.e. eliminate the TOP N part of the subquery. As Dell mentions, this could be a DB limitation. It'd be good to know what DB your Sage system is using.

[DellSC]

I believe this is a limitation of how your database operates and has nothing really to do with Crystal. What type and version of database is your Sage system working on? Have you tried running your query in a tool other than Crystal that connects to your database? I don't think it will work there either.

One option that might work for you would be to use the TopN functionality that's built into Crystal.

-Dell

[littlered]

Hi Jamie,

No other commands or tables are used. Since the subquery runs ok without the outer query, I figure I am violating a rule I don't know about regarding SQL when using COMMAND.

Thanks!

Lori

[littlered]

Hi Jamie,

Adding the ORDER BY to the subquery works only if I remove the outer query. ie the subquery runs fine on its own with both the ORDER BY and the TOP. Thanks for the thoughts!

Lori

[JWiseman]

Are there any additional Commands or DB tables being used on the report?

Also, if you run the query in the workbench query analyzer (i.e. outside of Crystal) is everything OK there?

[JWiseman]

Hi Lori, does adding an ORDER BY in your TOP N subquery make a difference? And if you then run the subquery on its own does that work?