Print Page | Close Window

Question on SQL and Crystal

Printed From: Crystal Reports Book
Category: General Information
Forum Name: Talk with the Author
Forum Discription: Ask Brian questions about his books and give him your comments. Like the book? Hate the book? Have suggestions? Let me know!
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=14843
Printed Date: 28 Apr 2024 at 11:48pm


Topic: Question on SQL and Crystal
Posted By: Logle0917
Subject: Question on SQL and Crystal
Date Posted: 02 Nov 2011 at 3:54am
Can I create my SQL Statement for the whole report and then paste it into Crystal to pull a report without having to create the report using the Crystal Gui?  I am using Toad Oracle for Data Analyst I need to put the report into Crystal and create parameters so that others can pull the report.  Is this possible to do?  Thanks!!
 
Logle


Replies:
Posted By: jorrebor
Date Posted: 02 Nov 2011 at 5:44am
you can create sql statements in every editor of your liking.

check whether it retrieves the righ data and then copy paste the sql statement in the "edit command" box from crystal

set your parameter in the where clause

it's pretty straightforward what you are asking.

hope this helps!

Posted By: Logle0917
Date Posted: 02 Nov 2011 at 6:17am
Ok I used the Add Command Button to paste my SQL in and then I get this error  Failed to retrieve data from the databas. Details ORA-00911: invalid character [Database Vendor Code:911]
 
 
Here is my code and it works in Toad for Data Analyst. We are pulling from an Oracle Server 10.2.5 
 

SELECT

PWLDBA.ENTORDER.ENTORDERNO,

PWLDBA.TESTREQ.ACCESSION,

PWLDBA.ENCOUNTER.ENCOUNTERNO,

PWLDBA.PATIENT.NAMELAST,

PWLDBA.PATIENT.NAMEFIRST,

PWLDBA.PATIENT.NAMEINITIAL,

PWLDBA.PATIENT.NAMESUFFIX,

'F' AS PATIENT_GENDER,

PWLDBA.PATIENT.SSN,

PWLDBA.PATIENT.DOB,

' ' As AGE,

PWLDBA.RC.DESCRIPTION AS ETHNICITY,

'NA' AS PATIENT_OCCUPATION,

PWLDBA.PATIENT.ADDR1,

PWLDBA.PATIENT.ADDR2,

PWLDBA.PATIENT.CITY,

PWLDBA.PATIENT.STATE,

PWLDBA.PATIENT.COUNTRY,

PWLDBA.PATIENT.PHONE,

PWLDBA.SF.SRCFAC,

PWLDBA.SF.SRCFACNAME,

PWLDBA.PATIENT.PID,

PWLDBA.RD.NAMELAST AS ORDERING_PROVLNAME,

PWLDBA.RD.NAMEFIRST AS ORDERING_PROVFNAME,

PWLDBA.RD.NAMEINITIAL AS ORDERING_PROVINITIAL,

PWLDBA.RD.NAMEDEGREE AS TITLE,

PWLDBA.RD.NAMESUFFIX,

PWLDBA.RD.PMRY_SFKEY,

PWLDBA.RD.ADDR1,

PWLDBA.RD.ADDR2,

PWLDBA.RD.CITY,

PWLDBA.RD.STATE,

PWLDBA.RD.ZIP,

PWLDBA.RD.CONTACTPHONE,

PWLDBA.RD.UPIN,

PWLDBA.RD.NPI,

PWLDBA.PATIENT.ITKEY_0,

PWLDBA.PATIENT.GROUP_0,

PWLDBA.TESTREQ.COLLECTED AS PRIMARY_COL_DT,

PWLDBA.TESTREQ.RECEIVED AS PRIMARY_REC_DT,

PWLDBA.PTRESULT.FINALSTAMP AS FINAL_COMP_DT,

'NA' AS CASECURRENTSTEPDESC,

'DIGENE' AS CASE_TYPE,

PWLDBA.TEST.SHORTNAME,

PWLDBA.TEST.TESTCODE,

'NA' AS LOCALSTANDARDCODE,

PWLDBA.PWSP.DESCRIPTION AS PRIMSPECDESC,

PWLDBA.TESTREQ.SOURCE,

'NA' AS SNOMED_CODES,

PWLDBA.TESTREQ.ICD9KEY_0,

'NA' AS CPTCODE,

'NA' AS LOINCCODES,

'NA' AS UMLCODES,

PWLDBA.TEST.TESTCODE,

PWLDBA.ANALYT.DESCRIPTION AS RESULTNAME,

PWLDBA.PTRESULT.VALUE AS RESULT_VALUE,

PWLDBA.PTRESULT.VALUE AS HPV_DETECTED,

PWLDBA.TESTCOMMENT.D_COMMENT,

PWLDBA.PTRESULT.ABNRANGE,

'NA' AS CLINICALHIST,

'NA' AS SPECIMENSOURCE,

PWLDBA.TEST.NAME,

'DIGENE' AS HPVTEST_MFG,

'NA' AS ADDENDUM,

'SED MEDICAL LAB' AS LABCODE

 

FROM

PWLDBA.TESTREQ

INNER JOIN PWLDBA.PTRESULT

  ON PWLDBA.PTRESULT.TESTREQKEY=PWLDBA.TESTREQ.TESTREQKEY

 

INNER JOIN PWLDBA.ANALYT

  ON PWLDBA.ANALYT.ANALYTKEY=PWLDBA.PTRESULT.ANALYTKEY

 

INNER JOIN PWLDBA.TEST

  ON PWLDBA.TEST.TESTKEY=PWLDBA.TESTREQ.TESTKEY

 

INNER JOIN PWLDBA.PWSP

   ON PWLDBA.PWSP.PWSPKEY=PWLDBA.TESTREQ.PWSPKEY

 

INNER JOIN PWLDBA.RD

  ON PWLDBA.TESTREQ.RDKEY_0=PWLDBA.RD.RDKEY

 

INNER JOIN PWLDBA.TESTCOMMENT

  ON PWLDBA.TESTCOMMENT.TESTREQKEY=PWLDBA.TESTREQ.TESTREQKEY

 

INNER JOIN PWLDBA.ENTORDER

  ON PWLDBA.ENTORDER.ENTORDERKEY=PWLDBA.TESTREQ.ENTORDERKEY

 

INNER JOIN PWLDBA.ENCOUNTER

  ON PWLDBA.ENCOUNTER.ENCOUNTERKEY=PWLDBA.ENTORDER.ENCOUNTERKEY

 

INNER JOIN PWLDBA.PT

  ON PWLDBA.PT.PTKEY=PWLDBA.ENCOUNTER.PTKEY

 

INNER JOIN PWLDBA.SF

  ON PWLDBA.SF.SFKEY=ENCOUNTER.SFKEY

 

INNER JOIN PWLDBA.PATIENT

      ON PWLDBA.PATIENT.PATIENTKEY=PWLDBA.ENCOUNTER.PATIENTKEY

 

INNER JOIN PWLDBA.RC

  ON PWLDBA.RC.RCKEY=PWLDBA.PATIENT.RCKEY

 

 

WHERE PWLDBA.TEST.SHORTNAME LIKE '%HPV' AND PWLDBA.TESTREQ.STATUS ='D'

ORDER BY

PWLDBA.ENTORDER.ENTORDERNO,

PWLDBA.ENCOUNTER.ENCOUNTERNO ASC; 

 

Posted By: hilfy
Date Posted: 02 Nov 2011 at 3:54pm

Get rid of the semi-colon at the end and it should save correctly.

Also, if you need to filter the data based on parameters, you need to create them in the command editor (not in the main report!) and include them in the SQL of your command.  Otherwise Crystal will not pass the selection criteria through to the database, instead it will load the full result set from the query and filter it in memory.
 
-Dell


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: fdjhfgu
Date Posted: 25 Nov 2011 at 9:05pm



Uggs On Sale At this time there are various types of UGG boots on sale offered. can be program, the standard get started on the badge folded up or down is still quite

well-liked within a variety of hues. One of many hottest colours with the moment is grapes, a deep red coloring, which gives you a whole new dimension to some

preferred type.

_________________________________________________________________________________

http://www.womensnowboots.co.uk/ - uggs uk sale
http://www.womensnowboots.co.uk/knit-cardy-boots-c-2_6.html - cardy uk uggs
http://www.womensnowboots.co.uk/classic-short-boots-c-2_9.html - ugg classic short


Print Page | Close Window