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

Syntax Error when using SQL Expression

Morning all,

When I try to type any SQL Expression within Crystal I am getting syntax errors, even when I type only SELECT {field name} FROM {table name}.

Here is the query I did which gave me the following error:

Query

SELECT  order_progress . date_created 
FROM order_progress
WHERE  order_progress . order_no = order_header . order_no 
AND  order_progress . order_status =77
ORDER BY  order_progress . date_created DESC

Error

Error in compiliing SQL Expression
Database connector Error: 'HY000[Informix] [Informix ODBC Driver]General Error. Syntax Error[Database Vendor Code-11060]'.

Now this means, 1: I am getting a connection error with my db, for no apperent reason. 2: my syntax is not approved?!

Can someone explain why is this happening and how to resolve this?

many thanks

Kind Regards

Jehanzeb

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

3 Answers

  • Best Answer
    Posted on Jul 18, 2008 at 10:44 AM

    An SQL expression field is only meant to return one row of data. Select statements in sql expression fields aren't recommended either, unless they only return one row. What are you trying to achieve with this SQL expression field? Maybe the statement would be better off in a command in a subreport?

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Fritz Feltus

      Thank you for your quick response Fritz. Yes! that is correct however, the original program which was developed about 10-20 years ago, all I have is the source code of it.

      and in the source code the guy has used Array list for days and used "date created" from the order progress table to calculate the days.

      Now because it is in Informix 4GL I can only understand some bits of it and to me it looks like "date created" is the shipped date (as that is the only field in that particular table with date).

      So if we say "date created" is the field of shipment date even then the code doesn't seem to work.

      It gives me 0 days instead of day 1,2,3,4,5,6 etc.

      Providing "date created" is shipped date and "date entered" is when the order was placed, how can we calculate number of jobs which took x amount of days?

      Many thanks

      Kind Regards

      Jehanzeb

  • Posted on Jul 22, 2008 at 07:47 PM

    create a formula that does the datediff....this will be a group. You will then have a formula field that is evaluated to 1, if the status is 77.

    Now, sum that formula in each group footer. Suppress the detail lines...

    ie...

    ........details section (suppressed).....formula = 1

    ........details section (suppressed).....formula = null

    ........details section (suppressed).....formula = 1

    ........details section (suppressed).....formula = null

    ........details section (suppressed).....formula = 1

    Day 1 (Group Footer) - 3

    ........details section (suppressed).....formula = 1

    ........details section (suppressed).....formula = 1

    ........details section (suppressed).....formula = 1

    ........details section (suppressed).....formula = 1

    Day 2 (Group Footer) - 4

    ........details section (suppressed).....formula = 1

    Day 3 (Group Footer) - 5

    So the report would look like this....

    Day 1 - 3

    Day 2 - 4

    Day 3 - 5

    **Don't use a cross tab with this solution. For a cross tab, you would have to calculate the totals in a different manner.

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      Morning Kyle,

      Thanks for the informative answer, I have found out the formula which counts number of days however, I am not sure what you meant by the following:

      >

      > create a formula that does the datediff....this will be a group.

      Done this, it counts the working days as in weekdays and takes bank holidays out as well

      here is the formula:

      WhileReadingRecords;
      //{order_progress.order_status}= 77;
      //{order_header.order_status}>=77;
      
      Local DateVar Start := {order_header.date_entered};   // Starting Date
      Local DateVar End := {order_header.act_despatch};  // Ending Date
      Local NumberVar Weeks;
      Local NumberVar Days;
      Local Numbervar Hol;
      DateVar Array Holidays;
      
      Weeks:= (Truncate (End - dayofWeek(End) + 1
      - (Start - dayofWeek(Start) + 1)) /7 ) * 5;
      Days := DayOfWeek(End) - DayOfWeek(Start) + 1 +
      (if DayOfWeek(Start) = 1 then -1 else 0)  +
      (if DayOfWeek(End) = 7 then -1 else 0);   
      
      Local NumberVar i;
      For i := 1 to Count (Holidays)
      do (if DayOfWeek ( Holidays<i>) in 2 to 6 and
        Holidays<i> in start to end then Hol:=Hol+1 );
      
      Weeks + Days - Hol;
      

      You will then have a formula field that is evaluated to 1, if the status is 77.

      you mean second formula? How I used,

      If Order_header.Order_Status=77 then
      1
      else
      2;
      

      however this doesn't seem to be working.

      > Now, sum that formula in each group footer. Suppress the detail lines...

      which formula to sum up?

      > **Don't use a cross tab with this solution. For a cross tab, you would have to calculate the totals in a different manner.

      so far I used Cross tab and it is showing the right days, however not showing the correct jobs per day.

      You might be right there that it calculates the totals differently, additionally how am I suppose to get the right Percentage if using the Cross tab. The percentage should be, count of number of jobs % Total jobs.

      hence:

       {#NumofJobs} % {#Total_Jobs} 

      however, when I create this formula it does not let me add that into the cross tab.

      I also need to put a subreport in my report, can I use Crosstab to contain a link to my subreport?

      Many thanks

      Kind Regards

      Jehanzeb

  • author's profile photo Former Member
    Former Member
    Posted on Aug 05, 2008 at 11:57 AM

    closing don't even remember what I asked for.

    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.