Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2004
    Posts
    21

    Question Unanswered: Insert parameter into "Show SQL Query"?

    yay, I get to post the first thread in the new Crystal reports section

    so here goes. ... I'm using CR 8.5. I have a query that I need to use a nested select in. I can't seem to get Crystal to format the query as I need in order to take advantage of keys, but I can go to "Show SQL Query" and edit the query to get my desired results. The problem is, I don't know how to get the data in "Show SQL Query" to use a parameter. I need to let the user enter a date into the subquery. How can I do this?

    what I'm trying to do is something like this:

    SELECT [columns] FROM [tables] WHERE invoice IN(SELECT invoice FROM [table] WHERE invdate = {parameter})

    But if I try to insert something like {?invdate} I get syntax errors. I thought about trying to use a subreport. But the subreport will run once for every result of the parent report. This would give me incredibly poor performance. When manualy entered, ther above sample gives me the results I need in about 30 secconds, the subreport option takes hours. What can I do?

  2. #2
    Join Date
    May 2003
    Location
    UK
    Posts
    233

    Re: Insert parameter into "Show SQL Query"?

    Not sure if this possible to do, but if you are working off a SQL server db, you could write the SQL as a view in and then draw the data direct form this view.

  3. #3
    Join Date
    Feb 2004
    Posts
    21
    I had thought about that, but since I need to have the user enter a date, I can't use a view. So far my research has shown that this simply can not be done in Crystal 8.5. This is a serious disapointment to me. What I've done is build the report without the nested select and simply have the SQL database return the entire content of the database, then crystal does the date sorting. This takes hours, but I've scheduled the report in CE to run at 4am, so at least my sales people can see it each day without waiting. This is less than ideal, but it's what I needed to do to get it to work.

    I've been told that versions 9 and 10 can do whatI need though. I'm not 100% sure if I'll upgrade or not still. Hopefully version 10 is a lot less buggy than version 8.5 and enterprise 9. If I need to go through the same stuff I had to in order to get CE9 up and running reliably, it won't be worth any performance gain.

    - Anthony

  4. #4
    Join Date
    Feb 2004
    Posts
    8
    Can't you use a parameter field?

    John

  5. #5
    Join Date
    Feb 2004
    Posts
    21
    John, I have a parameter field. The question is, how do I get crystal to insert it's value into a query entered into the "Show SQL query" window.

  6. #6
    Join Date
    Oct 2003
    Location
    BA, Argentina
    Posts
    39
    Go to Report > Edit Selection Formula > Record

    there you can write, for example:

    {Table.CustomerName}={?Name)

    Then, when you show the SQL Query, cr asks you the parameter and generates the query with your parameter.

  7. #7
    Join Date
    Feb 2004
    Posts
    21
    Originally posted by jigarzon
    Go to Report > Edit Selection Formula > Record

    there you can write, for example:

    {Table.CustomerName}={?Name)

    Then, when you show the SQL Query, cr asks you the parameter and generates the query with your parameter.
    I know that. I need to put a parameter in a nested Select though (see my 1st post) .... Crystal won't create this kind of query. I'm really beginning to dislike this software

  8. #8
    Join Date
    Oct 2003
    Location
    BA, Argentina
    Posts
    39
    Sorry for not reading your first post. I dont know if CR supports what you want, i ve never used a nested query in CR... can you change your query using a join instead of a subquery?? or, maybe, you can use a stored function to get the results you want... just giving ideas, i made things really nasty to get what I wanted with CR...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •