SQL fields

[Olii]

i want to insert a sql field in a report but even if try with a simple query...I have an error

ex:

select etat from requetes

and i get "sql expression invalid" message

what's the problem...?

The connection with ODBC is ok : the fields r available in the field tree...

 

[kenhamady]

You can add SQL expression fields, not SQL statements.

In other words, you can create an expression to be added to your "Select" clause, creating a virtual column in your original results table. That is what SQL expression fields are for.
Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training and a
Quick Reference Guide to VB/Crystal

 

[Olii]

So how can I make (is it possible??) this report :

table request --> fields : -start_date
-end_date
-status
-worked_time
-Problem_type
-<Other fields I don't need>

I need to calculate the number of request for every status and I can get that number with a &quot;count(status)&quot;. I also need the sum of time worked on each request and I get that one with &quot;sum(val(worked_time))&quot; (I inserted a formula to convert the float in hours/minutes format). All of these arranged in a cross-tab report....like this one :

Q : amount of request for that type
T : Time worked on that type of request

Status
New Assigned Modified Rejected Closed
Q T Q T Q T Q T Q T
Problem

None

Bug

Error

Modification

Report

At first, I thought that I could do it with multiple sql fields (but It's not complete SQL fields as you told me) then I thought I could make a .prg in foxpro to create each table and then insert the fields in the report...even if the values were right in the table, all displayed value in Crystal were equal to 0.00 . If I use the select expert to exclude 0.00 value--> nothing displayed.

Also, for New and Closed status I want to insert a parameter (date range) field to restrict some request from appearing but I don't want this to affect the other request with another status.

Does somebody has a solution for that one ??

 

[MalcolmW]

The obvious answer is that you need control over the SQL - and that to simplify your life, you need to return the data to the report already &quot;digested&quot;, and all that you will be using SCR for will be to display the data with a pretty face.
The question is what is the easiest what to get control over the SQL with foxpro. I'm not a foxpro pro, but it sounds like the prg method will not work, so the easiest alternative is using the SQL Query tool - which allows you to roll your own SQL query to the database of your choice. You then use that query as a datasource for the report.
Have you looked at that option yet? Malcolm
wynden@telus.net
November is &quot;be kind to dogs and programmers&quot; month. Or is that &quot;dogs or programmers&quot;?

 

[Olii]

Yep I tried with .qry --- I noticed that it's possible to base a report on any number of table but ... on only one .qry so it doesn't solve my problem cause I need to have more than one select (cause some selected fields need a different where clause than others because the date restriction applies to some fields but not all....)

so I think i'm gonna use several sub-report to achieve the whole one....

Also...Oracle is gonna take the place of Fox in a while and that report will still be needed sometimes so I can't use Fox to create other tables to base the report on...

thanx and if you see the light... tell me !!

 

[MalcolmW]

If you can wait till you move to Oracle, then you will be able to create a stored proc

http://community.seagatesoftware.co...alPapers/SCR_Oracle_Stored_Procedures.pdf.asp

The subreports are OK too - they just can be very slow depending on how many times they are called.
Good luck, Malcolm
wynden@telus.net
November is &quot;be kind to dogs and programmers&quot; month. Or is that &quot;dogs or programmers&quot;?

 

[Olii]

Do you think that including the same table several times could do it ? that way I could maybe make more than one select...

 

[MalcolmW]

I'm not sure what you mean by including the table several times. You could use a subselect perhaps, or a derived/virtual table (you can do almost anything with a derived table, although it can be resource instensive).

I'd definitely try to do it in SQL before going to the subreports. Sometimes subreports are handy, but the performance can be painful. Malcolm
wynden@telus.net
November is &quot;be kind to dogs and programmers&quot; month. Or is that &quot;dogs or programmers&quot;?

 

[Olii]

What do you mean by derived table and subselect ??

derived table --> Another instance of an already open table?

subselect ------> A select within another select :

select ??? from ?? where in sleect ???

is that it ??

 

[MalcolmW]

derived table <--> virtual table
a derived table is a select statement in the from clause that is assigned an alias as in DT1 below:

select T1.anyfield, DT1.anyfield
from table1 T1
     inner join (
        select table3.id,
               table2.anyfield
        from table2
             inner join table3 on table2.id = table3.id
        ) DT1 on T1.id = DT1.id

So the derived table allows you a lot of flexibility.
For an example of a subselect, check of the FAQ for &quot;Top n&quot; in the ANSI SQL forum Malcolm
wynden@telus.net
November is &quot;be kind to dogs and programmers&quot; month. Or is that &quot;dogs or programmers&quot;?

 

[Olii]

Ok thanx Malcolm...your help is appreciated....as I tought ... I already knew about the top N but I'm gonna explore the derived table...