Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: DEFINE ARRAY DATA TYPE IN STORED PROC Post Reply Post New Topic
Author Message
peter
Groupie
Groupie


Joined: 08 Apr 2007
Location: United States
Online Status: Offline
Posts: 51
Quote peter Replybullet Topic: DEFINE ARRAY DATA TYPE IN STORED PROC
    Posted: 19 Jun 2007 at 4:45pm
Hi,
I'm trying to define a passing parm as ARRAY but could not get any luck. Please Help!

Here is what I have: a DB2 Stored Procedure

CREATE PROCEDURE SEL_P100N (
    IN p_user VARCHAR(3)
)
LANGUAGE SQL
RESULT SETS 1
P1: BEGIN
...
...
END P1;

And I'm trying to do:

CREATE PROCEDURE SEL_P100N (
    IN p_users[100] VARCHAR(3) -- ???? I WANT p_users an ARRAY but  could Not find a way ?????
)
LANGUAGE SQL
RESULT SETS 1
P1: BEGIN
...
...
END P1;

IP IP Logged
BrianBischof
Admin Group
Admin Group
Avatar

Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
Quote BrianBischof Replybullet Posted: 19 Jun 2007 at 8:28pm
Can't do it. You can only use arrays within a formula and even then you can't return an array from a formula. Nor can you pass an array as a parameter to a SP. What about returning a comma delimited string and then in CR call the Split() function to convert it to an array in the report?
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>
IP IP Logged
peter
Groupie
Groupie


Joined: 08 Apr 2007
Location: United States
Online Status: Offline
Posts: 51
Quote peter Replybullet Posted: 20 Jun 2007 at 9:23am
Hi Brian,
Thank you for your reply. However, our need is to have the array in the SP so that we can filter/reduce the data come over onto CR.
I was thinking about sending the string with Dilimeter such as AAA,BBB,CCC but this is not a desirable approach because we want to validate the items in the string against a DB2 table before sending over to the SP. And you know CR is not doing that well.
 
By the way:
Another question: Instead of using SP, we plan to connect and do table joins all on CR. But from looking at the Show Query in CR, it does not show the WHERE clause used with Parameters. So the question is: Does CR bring ALL data over then filter then based on the inputs in the Parameters ? We dont want that. We want the data come in to CR as much as the inputs from the Parameters !!!!
IP IP Logged
BrianBischof
Admin Group
Admin Group
Avatar

Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
Quote BrianBischof Replybullet Posted: 20 Jun 2007 at 9:51am
The WHERE clause is tricky because CR "wants" to have the database do as much work as possilble. But the circumstances has to be right. First of all, the record selection formula has to be simple. If you have any formulas in it then it won't get converted to SQL. Plus, sometimes simple formulas won't get passed either and you have to play around with it to get it just right. Also, you have to set the SQL optimization settings in the report options. Look for the settings relating to grouping on server and optimizing server speed (I forget the actual option name right now).

Doing these things should get your WHERE clauses off the client and onto the server.
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>
IP IP Logged
peter
Groupie
Groupie


Joined: 08 Apr 2007
Location: United States
Online Status: Offline
Posts: 51
Quote peter Replybullet Posted: 20 Jun 2007 at 10:10pm

Again, thank you, Brian.

I really want to use SP with a way of passing parm as array so that I can use the Allow Multi Values option in the Parameters. Otherwise, that option is disabled.

IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.012 seconds.