Passing SQL Parameters (again)

[bigfoot]

I was hoping someone could help me.
I have a multi table report that I am fixing in Crystal 8.5 and I am trying to pass a parameter to the SQL Server.
The report takes 2 parameters from the user. wh_id and order number (not shown).

I have been there are read the FAQ

http://www.tek-tips.com/faqs.cfm?fid=3826

And it does not work as expected.

Here is my formula. What am I doing wrong?

(
{t_order_detail.item_number} <> "6824" and
{t_hu_master.type} = "PP" and
{t_order.wh_id} = {?Warehouse}
)

The author of the FAQ said to enclose the formula in ()'s, but it does not show up in the "Show SQL Query" window even after pressing Reset.

What's happening is that Crystal is bringing a ton of records from the SQL Server and filtering them.

Someone said to make this a stored procedure but I don't want to redo all of the fields.

I'm new to Crystal, and need to fix this report.

Please help.

 

[lbass]

I think you should show the entire record selection formula in this thread.

It might also help to see the generated "show SQL query" in its entirety.

-LB

 

[bigfoot]

That is the entire record selection formula.

 

[lbass]

You said you also had a parameter on order number, so I assumed you were only showing part of the selection formula.

How about showing the SQL query, also, so we can see how you've worked with the tables?

-LB

 

[bigfoot]

It's not my query, and it's ugly.

SELECT
    t_order."wh_id", t_order."order_number", t_order."cust_po_number", t_order."department", t_order."load_id", t_order."load_seq", t_order."carrier", t_order."date_shipped", t_order."bill_to_name", t_order."bill_to_addr1", t_order."bill_to_addr2", t_order."bill_to_city", t_order."bill_to_state", t_order."bill_to_zip", t_order."bill_to_phone", t_order."ship_to_name", t_order."ship_to_addr1", t_order."ship_to_addr2", t_order."ship_to_city", t_order."ship_to_state", t_order."ship_to_zip", t_order."ship_to_phone", t_order."bill_frght_to_addr3",
    t_order_detail."wh_id", t_order_detail."order_number", t_order_detail."line_number", t_order_detail."item_number", t_order_detail."qty", t_order_detail."item_weight", t_order_detail."qty_shipped", t_order_detail."item_description",
    t_order_detail_comment."comment_text",
    t_hu_master."type",
    t_carrier."carrier_name", t_carrier."address1", t_carrier."city", t_carrier."state", t_carrier."zip", t_carrier."phone", t_carrier."fax",
    t_hu_detail."loaded_qty",
    t_item_master."um", t_item_master."std_hand_qty", t_item_master."unit_weight"
FROM
    { oj ((((("t_order" t_order INNER JOIN "t_order_detail_comment" t_order_detail_comment ON
        t_order."wh_id" = t_order_detail_comment."wh_id" AND
    t_order."order_number" = t_order_detail_comment."order_number")
     INNER JOIN "t_hu_master" t_hu_master ON
        t_order."order_number" = t_hu_master."control_number" AND
    t_order."wh_id" = t_hu_master."wh_id" AND
    t_order."load_id" = t_hu_master."load_id")
     INNER JOIN "t_carrier" t_carrier ON
        t_order."carrier" = t_carrier."carrier_code" AND
    t_order."carrier_scac" = t_carrier."scac_code")
     INNER JOIN "t_order_detail" t_order_detail ON
        t_order."wh_id" = t_order_detail."wh_id" AND
    t_order."order_number" = t_order_detail."order_number")
     INNER JOIN "t_hu_detail" t_hu_detail ON
        t_order_detail."item_number" = t_hu_detail."item_number" AND
    t_order_detail."line_number" = t_hu_detail."line_number")
     INNER JOIN "t_item_master" t_item_master ON
        t_hu_detail."item_number" = t_item_master."item_number" AND
    t_hu_detail."wh_id" = t_item_master."wh_id"}
WHERE
    t_order_detail."item_number" <> '6824' AND
    t_hu_master."type" = 'PP'
ORDER BY
    t_order."order_number" ASC,
    t_order_detail."line_number" ASC

The parameter on order number I left off because I could not get the first one to work so I did not bother.

 

[bigfoot]

As you can see, the parameters are not being passed to SQL Server, and as a result, is bringing back so many records that the system cannot handle it.

This was written by someone who is no longer working here. I am NOT a Crystal guy, by any means.
I have not used it since version 5 and now I remember why.


Any and all help would be really, really great. I am so lost.

 

[bmarks]

Are you using the GUI in the database expert?

I have found better performance when I write the SQL myself and use the "Add Command" as my datasource.

In fact, since my database is InterBase, if I use the record selection formula, it does not get passed to the database.

You may want to try this and turn your "Show SQL" into a well formatted SQL Server query and use it as a command.

One assumption: I'm using Crystal 9, but I think you can do this in 8.5 as well.

 

[lbass]

You first set up the parameter in field explorer->parameter->new, right?

Did you get any error messages when you set up the record selection formula?

-LB

 

[bigfoot]

Record selection formula is set up ok. No errors.
The problem is that I don't see the parameters in the "Show SQL" window which means they are not being passed to SQL Server.

When I run the query in Query Analyzer, it runs fast. When I comment out the 2 parameters, it chokes, the same as it does in Crystal.

Add Command. You mean like a stored procedure? I have a stored procedure but then I have to change all of the fields in the report because the datasources is different, right?

(thanks for helping)

 

[bmarks]

The SQL command structure is different than a stored procedure, but has some of the same concepts.

In Crystal 9, it works this way (not sure about 8.5, but give it a try):
- Click on Database, and choose database expert
- Find your datasource on the left side
- Instead of choosing your tables, choose Add Command
- in the command window, type in the SQL query. For the parameters use {?parametername}
- you also need to use the Create button to create the parameters using the same name as in the SQL query.

It's basically the same thing as using the GUI, but you have more control over SQL statement and can do more with it.

For keeping your fieldnames and not having to rebuild, try adding the command to your current datasources and before leaving the database expert, delete the tables form the selected tables box. I think this should work as the fieldnames will be the same. (I've done this before, but don't remember the exact steps at this time - so do this on a copy of the report in case I don't have it quite right).

 

[lbass]

bmarks - Commands are not available in 8.5.

I see no reason why the parameter would not appear--I'm at a loss.

Actually the parameter itself would not appear in the SQL query, but instead would appear something like:

t_order."wh_id" = 'Warehouse123'

-LB

 

[bmarks]

Ok, too bad - Commands are quite powerful.

I did use 8.5, but no longer have access to it. But I remember there being settings for passing grouping to the database, so I'm thinking there might be a setting/option for sending the selection to the database as well.

 

[bigfoot]

We have version 9 but are not using it yet because we don't know if there are any upgrade issues.

Everything is as it shpould be in the book.