Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Crystal Reports XI Array Help? Post Reply Post New Topic
<< Prev  Page  of 2
Author Message
ronc96@hotmail.
Newbie
Newbie


Joined: 29 Mar 2012
Location: United States
Online Status: Offline
Posts: 8
Quote ronc96@hotmail. Replybullet Posted: 30 Mar 2012 at 5:25am

Dblank,

 

I do have one more question...I should have explained this exactly the way that it is (I am pulling Data from Multiple Tables, not just one Table)...

 

In your previous reply, you posted that I could try this:

 

SELECT     table1.patientid, table1.visitdate, table1.visitid, table1.doctor

FROM         table1 INNER JOIN

                          (SELECT     patientid, COUNT(DISTINCT visitid) AS Visits

                            FROM          table1 AS INCIDENTS_1

                            WHERE      (visitdate BETWEEN @startdate AND @enddate)

                            GROUP BY patientid

                            HAVING      (COUNT(DISTINCT visitid) > 1)) AS Visits ON table1.patientid = Vistis.patientid

 

 

My problem is this:

 

Since I am pulling from Multiple Tables, I am somewhat unsure of where to put the Nested Query…

 

I am thinking that, since the Person.PID and Document.DCRID (field that I need to do a count on) are from the Person and Document tables, respectively, I should put the Nested Query between them in the FROM Clause.

 

Does this sound about right??

 

 

 

This is what I currently have in my Add Command (without the line breaks…just did that for ease of use for this example):

 

 

 SELECT DISTINCT "USR"."PVID", "PERSON"."PID", "USR"."FIRSTNAME", "USR"."LASTNAME", "PERSON"."LASTNAME", "PERSON"."FIRSTNAME", "DOCUMENT"."DCRID", "DOCUMENT"."DB_CREATE_DATE" , "PERSON"."DATEOFBIRTH", "OBS"."OBSVALUE", "OBS"."OBSDATE", "DOCTYPES"."DESCRIPTION", "OBSHEAD"."NAME", "DOCTYPES"."DTID"

 

 FROM   (((("ML"."PERSON" "PERSON" INNER JOIN "ML"."DOCUMENT" "DOCUMENT" ON "PERSON"."PID"="DOCUMENT"."PID") INNER JOIN "ML"."OBS" "OBS" ON "PERSON"."PID"="OBS"."PID") INNER JOIN "ML"."OBSHEAD" "OBSHEAD" ON "OBS"."HDID"="OBSHEAD"."HDID") INNER JOIN "ML"."USR" "USR" ON "DOCUMENT"."USRID"="USR"."PVID") INNER JOIN "ML"."DOCTYPES" "DOCTYPES" ON "DOCUMENT"."DOCTYPE"="DOCTYPES"."DTID"

 

 WHERE  "PERSON"."LASTNAME"='TEST' AND "DOCUMENT"."DB_CREATE_DATE">={?Start Date} AND "DOCUMENT"."DB_CREATE_DATE"<={?End Date} AND ("DOCTYPES"."DTID"=1 OR "DOCTYPES"."DTID"=1.51680266801937e+015)


 ORDER BY "USR"."PVID", "PERSON"."PID"

 

 

 

I am really confused as to how to incorporate this Nested Query into my Main Query…

 
I believe that I am also a little confused as to what the "AS Visits" and "AS Incidents" were for...
 
In addition, since the two fields, in my Subquery, are from 2 separate tables (Person and Document), would I need to do an Inner Join on those tables BEFORE I ever tried to do an Inner Join on the Person (Person.PID) table and the Inner Query??
 
Thanks,
 
Ron
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 30 Mar 2012 at 8:43am
Sorry not at my desktop at the moment but the inner query is basically the same thing as a table. Instead of a table name put the query in parenthesis and then name it ( the 'as visits'). You can then use any values from it by using the name.field (e.g. visits.patientid).
The 'as incidents_1' was a typo. It should have been as table_1.
Since you are using the table twice in the same query, once in the main query and once in the sub query you have to alias it in the sub query to differentiate the use of the one table (alias by adding "_1" at the end of the table name).

Edited by DBlank - 30 Mar 2012 at 8:44am
IP IP Logged
ronc96@hotmail.
Newbie
Newbie


Joined: 29 Mar 2012
Location: United States
Online Status: Offline
Posts: 8
Quote ronc96@hotmail. Replybullet Posted: 11 Apr 2012 at 3:07am
DBlank,
 
Thank you for all of your help here...it is much appreciated.
 
Just for your own curiosity (and, to possibly help others), I ended up using the following Query/Subquery within the "Add Command":
 
SELECT
   "USR"."PVID",
   "DATA"."PID",
   "USR"."FIRSTNAME",
   "USR"."LASTNAME",
   "DATA"."LASTNAME",
   "DATA"."FIRSTNAME",
   "DOCUMENT"."DCRID",
   "DOCUMENT"."DB_CREATE_DATE" ,
   "DATA"."DATEOFBIRTH",
   "OBS"."OBSVALUE",
   "OBS"."OBSDATE",
   "DOCTYPES"."DESCRIPTION",
   "OBSHEAD"."NAME",
   "DOCTYPES"."DTID"
FROM (
   SELECT "PERSON"."PID",   
   "PERSON"."LASTNAME",
   "PERSON"."FIRSTNAME",
   "PERSON"."DATEOFBIRTH"
   FROM "ML"."DOCTYPES" "DOCTYPES"
   INNER JOIN "ML"."DOCUMENT" "DOCUMENT" ON "DOCUMENT"."DOCTYPE" = "DOCTYPES"."DTID"
         INNER JOIN "ML"."PERSON" "PERSON" ON "PERSON"."PID" = "DOCUMENT"."PID"
   WHERE ("DOCTYPES"."DTID"=1 OR "DOCTYPES"."DTID"=1.57271928125056e+015)
   AND "DOCUMENT"."DB_CREATE_DATE">={?Begin Date}
      AND "DOCUMENT"."DB_CREATE_DATE"<={?End Date}
   GROUP BY "PERSON"."PID", "PERSON"."LASTNAME", "PERSON"."FIRSTNAME", "PERSON"."DATEOFBIRTH"
   HAVING COUNT("DOCUMENT"."DCRID") > 1
) "DATA"
INNER JOIN "ML"."DOCUMENT" "DOCUMENT" ON "DATA"."PID"="DOCUMENT"."PID"
INNER JOIN "ML"."DOCTYPES" "DOCTYPES" ON "DOCTYPES"."DTID" = "DOCUMENT"."DOCTYPE"
INNER JOIN "ML"."OBS" "OBS" ON "DATA"."PID"="OBS"."PID"
INNER JOIN "ML"."OBSHEAD" "OBSHEAD" ON "OBS"."HDID"="OBSHEAD"."HDID"
INNER JOIN "ML"."USR" "USR" ON "DOCUMENT"."USRID"="USR"."PVID"
WHERE ("DOCTYPES"."DTID"=1 OR "DOCTYPES"."DTID"=1.57271928125056e+015)
   AND "DOCUMENT"."DB_CREATE_DATE">={?Begin Date}
      AND "DOCUMENT"."DB_CREATE_DATE"<={?End Date}
ORDER BY "USR"."PVID", "DATA"."PID"
 
 
Thanks again,
 
Ron
IP IP Logged
<< Prev  Page  of 2
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | KONACAD

© 2004-2026 Brian Bischof. All Rights Reserved.