Talk with the Author
 Crystal Reports Forum : General Information : Talk with the Author
Message Icon Topic: Question on SQL and Crystal Post Reply Post New Topic
Author Message
Logle0917
Newbie
Newbie


Joined: 21 Jul 2011
Location: United States
Online Status: Offline
Posts: 14
Quote Logle0917 Replybullet Topic: Question on SQL and Crystal
    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
IP IP Logged
jorrebor
Newbie
Newbie


Joined: 12 Sep 2011
Location: Netherlands
Online Status: Offline
Posts: 18
Quote jorrebor Replybullet 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!
IP IP Logged
Logle0917
Newbie
Newbie


Joined: 21 Jul 2011
Location: United States
Online Status: Offline
Posts: 14
Quote Logle0917 Replybullet 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; 

 

IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
Quote hilfy Replybullet 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
IP IP Logged
fdjhfgu
Newbie
Newbie


Joined: 25 Nov 2011
Online Status: Offline
Posts: 1
Quote fdjhfgu Replybullet 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.

_________________________________________________________________________________

uggs uk sale
cardy uk uggs
ugg classic short
IP IP Logged
Post Reply Post New Topic
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



This page was generated in 0.031 seconds.