Posted on 2011-02-17
Last Modified: 2012-05-11
I have a Crystal Report based on this SQL Command and uses 3 parameters.

BEcause I want to use dynamic parameters I want to create a VIEW in DB.

The problem is that I SUM quantity requested and quantity shipped for the date range given in the subquery.

If i use a view I would not be able to use this. Any ideas.

I do not want to try a 4 table join because items may not match.

select a.stock_number,a.description,a.category,vendor vendcode,
   (select decode(sum(quantity_requested),null,0,sum(quantity_requested))
   from stock_requested_item c, stock_request d
    where c.request_id=d.request_id and c.stock_number=a.stock_number
    and d.request_date between {?P_Begin_Date} and {?P_End_Date}
    and  d.ship_to_org like decode('{?P_Customer}','*All Customers*','%','{?P_Customer}' )
) Total_Ordered,
   (select decode(sum(quantity_shipped),null,0,sum(quantity_shipped)) from
     stock_shipped_item e, stock_shipment f
      where e.shipment_id=f.shipment_id and e.stock_number=a.stock_number
     and f.shipment_date between {?P_Begin_Date} and {?P_End_Date}
     and (e.dispcode is not null )
     and  f.ship_to_org like decode('{?P_Customer}','*All Customers*','%','{?P_Customer}' )  
) Total_Shipped
 (select name||', '||city||', '||state)
 from org where orgcd='{?P_Customer}'
) Customer
  from stock_item a, org b  
  where a.vendor=b.orgcd(+)
  and stock_type in ('P','C')
order by 1

Question by:sam15
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Expert Comment

ID: 34923531
I don't see the problem? Does that SQL work and output what you want when you execute(obvioulsy when you put the date ranges in ect instead of the {?})?

Why can't you create and use that sql as a view?
LVL 17

Expert Comment

ID: 34923622
Create either a VIEW or even better use a Stored Proc as the datasource.

Just create a separate SQL Command to aggregate your SUMS based on your requirement, then link to your main data table. You may need to use the MIN() function to display the SUM amounts,...but if I'm understanding your question, it would not be a difficult workaround...

Hope it helps...
LVL 26

Accepted Solution

Kurt Reinhardt earned 500 total points
ID: 34923837
You won't be able to create the SQL above as a View because you have parameters in the main SELECT clause of the SQL.  To Crystal Reports, a view is the same as a table.  You select fields from it and append values to a WHERE clause against it through your record selection criteria.  The parameters can't bed passed in to the view anywhere but the WHERE clause...

Creating a stored procedure will essentially be the same as the command you've created - you can pass a parameter value in to the body of the SQL, but you'll have the exact same limitations in Crystal Reports as you do with the command - you won't be able to use dynamic parameters. Dynamic parameters have to be built as a Crystal Reports feature, but command and proc parameters have to be built into the actual SQL and will be imported into Crystal Reports as soon as you use the object as a data source.

Using a SQL command in conjunction with any other data sources is a bad idea in terms of performance.  Commands must be executed in entirety before any joins or filters in Crystal Reports can be performed.  So, if you have a command joined to other tables, the command has to bring back 100% of it's records before it's ever joined to the other tables and any record selection criteria against those tables will now happen on the client, not on the database.  This can lead to very poor performance.

Personally, the first thing I'd would normally suggest is to use a SQL expression field to pull back the aggregate sums in the SELECT clause.  A SQL Expression is the Crystal Reports equivalent of a SQL subquery/subselect in the SELECT clause of a query.  The problem, however, is that you're using Oracle and you can't correlate data from a report into a full SELECT SQL Expression from Crystal Reports - you can against other databases like SQL Server and DB2, but not Oracle.  It sucks :(

So, if you want that SQL AND want dynamic parameters, what options do you have?

1)  Create an empty container report that only exists to populate some dynamic parameters
2)  Build a subreport with the SQL Command and the parameters inside the command
3)  Link the main report to the subreport based on the main report parameters to the subreport's command parameters.  Please note - when you attempt to link the main report to the subreport, it'll create a default subreport parameter starting with ?PM-  do NOT link to this parameter, because it exists outside the command.  Drill down into the list and select the actual matching parameter that's embedded inside the command.

I've used this technique for a number of reports and it works well.

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 34926960
Let me confirm your solution for main report and subreport.

I think i would leave my current report as as is now and just link it to a new report with dynamic parameters?

Does this solution take a long time to implement. I have done a few subreports a few years back and i recall it takes some time. That seems the only way though for dynamic parameters.

If i leave the parameter as static i guess i have to keep refreshing the list manually. right?
LVL 101

Expert Comment

ID: 34927303
Yes, if they remain static you have to refresh the list periodically or when the values change.

A subreport is just a report that you embed inside a report.

SInce you have the basic report built you could make a copy of the main report then eliminate everything you don't need in it except the details you want displayed.


Author Closing Comment

ID: 34935229

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

690 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question