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

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.

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.


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 100

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Introduction A previously published article on Experts Exchange ("Joins in Oracle", makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
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 how to recover a database from a user managed backup

860 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