Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Posts
    2

    Unanswered: Help with Crystal Report Verify database error

    Hello Everybody

    when i use this query as given below

    SELECT tbl_license.license_number FROM (tbl_businessDetails LEFT JOIN tbl_license ON tbl_businessDetails.business_id=tbl_license.busine ss_id) LEFT JOIN tbl_payment_details ON (tbl_license.license_number=tbl_payment_details.li cense_number) AND (tbl_license.fiscal_year=tbl_payment_details.fisca l_year)
    WHERE tbl_businessDetails.running='Y' and (tbl_license.fiscal_year = (select tbl_settings.fiscal_year-1 from tbl_settings) )
    GROUP BY tbl_businessDetails.business_name, tbl_license.license_number, tbl_license.fiscal_year, tbl_license.amount, tbl_businessDetails.business_id, tbl_businessDetails.owner_name, tbl_businessDetails.b_address1, tbl_businessDetails.b_city, tbl_businessDetails.b_state, tbl_businessDetails.b_zip

    my application works fine but when i add Union in this query as given below

    SELECT tbl_license.license_number FROM (tbl_businessDetails LEFT JOIN tbl_license ON tbl_businessDetails.business_id=tbl_license.busine ss_id) LEFT JOIN tbl_payment_details ON (tbl_license.license_number=tbl_payment_details.li cense_number) AND (tbl_license.fiscal_year=tbl_payment_details.fisca l_year)
    WHERE tbl_businessDetails.running='Y' and (tbl_license.fiscal_year = (select tbl_settings.fiscal_year-1 from tbl_settings) )
    GROUP BY tbl_businessDetails.business_name, tbl_license.license_number, tbl_license.fiscal_year, tbl_license.amount, tbl_businessDetails.business_id, tbl_businessDetails.owner_name, tbl_businessDetails.b_address1, tbl_businessDetails.b_city, tbl_businessDetails.b_state, tbl_businessDetails.b_zip

    UNION

    SELECT "license_number"='' ,"fiscal_year" ='' ,"amount" ='' ,"amt paid" ='', tbl_businessDetails.business_id, tbl_businessDetails.business_name, tbl_businessDetails.owner_name, tbl_businessDetails.b_address1, tbl_businessDetails.b_city, tbl_businessDetails.b_state, tbl_businessDetails.b_zip
    FROM tbl_businessDetails
    WHERE tbl_businessDetails.running='Y' and ( tbl_businessDetails.business_id NOT in (SELECT tbl_businessDetails.business_id FROM (tbl_businessDetails LEFT JOIN tbl_license ON tbl_businessDetails.business_id=tbl_license.busine ss_id) LEFT JOIN tbl_payment_details ON (tbl_license.license_number=tbl_payment_details.li cense_number) AND (tbl_license.fiscal_year=tbl_payment_details.fisca l_year)
    WHERE tbl_businessDetails.running='Y' and (tbl_license.fiscal_year = (select tbl_settings.fiscal_year-1 from tbl_settings) ) ))
    GROUP BY tbl_businessDetails.business_name, tbl_businessDetails.business_id, tbl_businessDetails.owner_name, tbl_businessDetails.b_address1, tbl_businessDetails.b_city, tbl_businessDetails.b_state, tbl_businessDetails.b_zip;

    it starts giving error as 20534 database dll error. help is required very much

    thanks in advance
    deep singh

  2. #2
    Join Date
    Mar 2004
    Posts
    1

    Crystal Report question

    I don't know if you found your answer, but it is pretty straight-forward.
    The reason you are getting the database dll error is that Crystal query editor doesn't support UNION queries. It also doesn't support GROUP BY queries, and a few other things. A pretty poor tool, all in all.

    What you will need to do to run your report using a union query is to create a view or stored procedure with your union query and link the report to this.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I don't know about other RDBMS, but if you are querying against SQL server your UNION syntax is invalid. The two parts of your UNION query must contain the same number of columns in the same order and they must have matching datatypes.

    I think your SELECT syntax is a little odd as well. I recommend that you develop and test all your SQL statements in an SQL Editior such as Query Analyzer before loading them into a Crystal Report. Actually, I HIGHLY recomment that you do no SQL in Crystal at all. Put your code in a stored procedure and then just have your Crystal Report call that procedure.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Apr 2004
    Location
    Colombia
    Posts
    9

    Post I need some help CR 8.5

    Hi blindman, i see the help about Eror 20534 from singh12 on 03-23-04. Was excelent, because i have the same error in my aplication, but i'm newie and i want some help for change this code to code without this error.

    this is the code.
    cr1.Reset
    cr1.ReportFileName = (App.Path & "\FILES_COOP\costumers.rpt")
    cr1.Connect = "PWD = mypass"
    cr1.SelectionFormula = "{costumers.ID}= " & Text1.Text & ""
    cr1.Action = 1

    when i run this code, always i have the 20534 error. How i have to do for fix this.. ??

    Really gratefully

    my email: groval@yahoo.com

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    groval, this looks like a different issue to me. I don't know the answer, but perhaps if you post your question in a little more detail as a new thread somebody will be able to help you.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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