Skip to Content
author's profile photo Former Member
Former Member

Help with SQL Query CRystal

Dear all,

I am having weird problem with this query. I have two optional parameters set on the report and One parameter for Date Range.

Optional are One is Account number and other is Product Group (Stock Code).

When I skip both parameters (account number and product group) the report shows 0 records.

I found out that the SQL query is showing Stock_Code=" at the end of the query.

I am not sure why it is coming up there.

Here is the query

 SELECT order_header.order_no, order_header.order_status,
order_header.date_entered, order_header.act_despatch,
ndmas.ndm_name, order_lines.stock_code,
order_header.account_no
FROM   maxmast.order_header order_header,
maxmast.order_lines order_lines, maxmast.ndmas ndmas,
maxmast.slcust slcust
WHERE  (order_header.order_no=order_lines.order_no) AND
(ndmas.ndm_ndcode=slcust.slm_custcode) AND
(order_header.account_no=slcust.slm_custcode) AND
(order_header.date_entered>={ts '2008-06-01 00:00:00'} AND
order_header.date_entered<={ts '2008-06-30 00:00:00'}) AND
order_lines.stock_code>=''

Can you see the last option after date range this weird thing is coming up.

How can I get rid of this

Please note: When I use the parameters only Account number and delete the Product Group parameter, the report runs fine.

Regards

Jehanzeb

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Aug 01, 2008 at 03:41 PM

    Can you copy and paste your record selection formula into a reply here so we can see it?

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      Morning Crystalier,

      I certainly can, here it is

      {order_header.date_entered} >= {?Start Date} and
      {order_header.date_entered} <= {?End Date} and
      (not HasValue({?Account Number}) or {order_header.account_no} = {?Account Number})and
      (not HasValue({?Product Group}) or {order_lines.stock_code}[1 to 5] = {?Product Group});
      

      Regards

      Jehanzeb

  • author's profile photo Former Member
    Former Member
    Posted on Aug 04, 2008 at 09:00 AM

    Solved! The " " " was coming because it was not set to optional within parameter value. I thought I did however, it wasn't now when I set to optional it is not counting the value as " " " within SQL.

    Thanks for all your help guys.

    Regards

    Jehanzeb

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.