locked
Changing the query RRS feed

  • Question

  • User-614943948 posted

    I am using Crystal Reports 9.0 and that report takes input data at runtime from an ASP.NET forum. When I go to Database>Show Query, this is how the query looks.

    SELECT "eicr_part1"."cmpid", "eicr_part1"."PurReport", "eicr_part1"."inspDate", "global_customers"."add1", "global_customers"."add2", "global_customers"."add3", "global_customers"."town", "global_customers"."county", "global_customers"."postcode", "job_headers"."cust_po_no", "sites_national"."s_add1", "sites_national"."s_add2", "sites_national"."s_add3", "sites_national"."s_town", "sites_national"."s_county", "sites_national"."s_postcode", "eicr_part1"."rdbPremises", "eicr_part1"."rdbPremeissesOther", "eicr_part1"."EvAlt", "eicr_part1"."EvAltY", "eicr_part1"."GenCond", "eicr_part1"."testingDetail", "eicr_part1"."qty1", "eicr_part1"."qty2", "eicr_part1"."OccName", "eicr_part1"."observ", "areas_national"."name", "job_threads"."date_of_inspection", "system_users"."su_firstname", "system_users"."su_surname", "job_headers"."jid", "job_headers"."arid_id", "eicr_part1"."extent", "eicr_part1"."Limitation", "eicr_part1"."LstInsp", "eicr_part1"."Records", "eicr_part1"."EAInstall", "eicr_part1"."nextInspDue", "eicr_part1"."monYear", "eicr_part1"."Overall", "global_customers"."company", "job_threads"."cmpid", "signatures"."blob_data"
     FROM   (((((("gessica"."dbo"."eicr_part1" "eicr_part1" INNER JOIN "gessica"."dbo"."job_threads" "job_threads" ON "eicr_part1"."cmpid"="job_threads"."cmpid") INNER JOIN "gessica"."dbo"."job_headers" "job_headers" ON "job_threads"."jid_ptr"="job_headers"."jid") INNER JOIN "gessica"."dbo"."sites_national" "sites_national" ON "job_headers"."site_ptr"="sites_national"."sid") INNER JOIN "gessica"."dbo"."global_customers" "global_customers" ON "job_headers"."cust_ptr"="global_customers"."gcid") INNER JOIN "gessica"."dbo"."system_users" "system_users" ON "job_headers"."engineer_id"="system_users"."id") INNER JOIN "gessica"."dbo"."areas_national" "areas_national" ON "job_headers"."arid_id"="areas_national"."arid") INNER JOIN "gessica"."dbo"."signatures" "signatures" ON "system_users"."id"="signatures"."suid_ptr"
     WHERE  "eicr_part1"."cmpid"=1253607


    I want to add Top 1 with the select statement. How can I accompalish this?

     

    Tuesday, March 25, 2014 6:28 AM

All replies

  • User1208776063 posted

    SELECT "eicr_part1"."cmpid", "eicr_part1"."PurReport", "eicr_part1

    You can add TOP 1 before the list of columns

    SELECT TOP 1 "eicr_part1"."cmpid", "eicr_part1"."PurReport", "eicr_part1. Also you should not enclose column names within double quotes. It should be just eicr_part1.cmpid and so on

    Tuesday, March 25, 2014 6:39 AM
  • User-614943948 posted

    Where is that option of adding Top 1 into the query? In Crystal Reports 9.0, there is no query editor. Using section expert, you can change joints, indexing and other things. But where is this option adding Top 1 in the query? I mean where is the query editor in Crystal Reports 9?

     

    I didn't enclose the columns in double quotes. When I clicked on show query, it asked for cmpid as input and displayed that query in double quotes

    Tuesday, March 25, 2014 6:44 AM
  • User-614943948 posted

    Or as an alternative, is there a way i can add an AND condition right after that where clause so that the query becomes like this?

     

    SELECT eicr_part1.id, eicr_part1.cmpid, eicr_part1.PurReport, eicr_part1.inspDate, global_customers.add1, global_customers.add2, global_customers.add3, global_customers.town, global_customers.county, global_customers.postcode, job_headers.cust_po_no, sites_national.s_add1, sites_national.s_add2, sites_national.s_add3, sites_national.s_town, sites_national.s_county, sites_national.s_postcode, eicr_part1.rdbPremises, eicr_part1.rdbPremeissesOther, eicr_part1.EvAlt, eicr_part1.EvAltY, eicr_part1.GenCond, eicr_part1.testingDetail, eicr_part1.qty1, eicr_part1.qty2, eicr_part1.OccName, eicr_part1.observ, areas_national.name, job_threads.date_of_inspection, system_users.su_firstname, system_users.su_surname, job_headers.jid, job_headers.arid_id, eicr_part1.extent, eicr_part1.Limitation, eicr_part1.LstInsp, eicr_part1.Records, eicr_part1.EAInstall, eicr_part1.nextInspDue, eicr_part1.monYear, eicr_part1.Overall, global_customers.company, job_threads.cmpid, signatures.blob_data
    FROM   ((((((gessica.dbo.eicr_part1 eicr_part1 
    INNER JOIN gessica.dbo.job_threads job_threads ON eicr_part1.cmpid=job_threads.cmpid) 
    INNER JOIN gessica.dbo.job_headers job_headers ON job_threads.jid_ptr=job_headers.jid) 
    INNER JOIN gessica.dbo.sites_national sites_national ON job_headers.site_ptr=sites_national.sid) 
    INNER JOIN gessica.dbo.global_customers global_customers ON job_headers.cust_ptr=global_customers.gcid) 
    INNER JOIN gessica.dbo.system_users system_users ON job_headers.engineer_id=system_users.id) 
    INNER JOIN gessica.dbo.areas_national areas_national ON job_headers.arid_id=areas_national.arid) 
    INNER JOIN gessica.dbo.signatures signatures ON system_users.id=signatures.suid_ptr
    WHERE  eicr_part1.cmpid=1253607
    and eicr_part1.id in (select min(id) from eicr_part1 where cmpid=1253607)

    Tuesday, March 25, 2014 8:09 AM