Query 1

[emuye]

I have a query that i have written using oracle 9i SQL Plus and i want to use this query in order to generate a report using CR 8.5.I am not sure if CR 8.5 has the capability for me to use my own Query.Please help

 

[Turkbear]

Hi,
I would create a View from your Query and use that as the basis for the report.

 

[emuye]

I am not clear on that. Could you walk me through what i should do to use this query.I am fairly new to CR.please help

 

[Turkbear]

Hi,
Connect to your Oracle dataabse instance then use a

Create or Replace view My_View_Name as
<your query goes here>;

Then select the view as the datasource for your Crystal report by selecting it from the list of tables/views available to you after connecting to your Oracle instance.
Be sure that, under the options tab, both tables and views are selected.

If you post your query I could be more specific.

 

[emuye]

Attached my query created using Oracle 9i database


SELECT DISTINCT V.VENDOR,R.ADDRNUM, V.VNAMEL, R.AADDR1,P.BILLTO,R.ACITY, R.ASTATE,
R.AZIPCODE,J.ROUTE,Q.CPROJNUM,Q.CFACSSUP,Q.CCNTY1
FROM VENDOR V, VENDADDR R, LETPROP L, PLANHOLD P,PROPOSAL Q, PROJECT J,PROPPROJ K
WHERE V.VENDOR = R.VENDOR
AND K.CONTID = Q.CONTID
AND K.PCN = J.PCN
AND L.LCONTID = K.CONTID
AND P.VENDOR = V. VENDOR
AND L.LETTING = P.LETTING
AND L.CALL = P.CALL
AND L.CALL = '019'
AND L.LETTING = '04082701'
AND R.ADDRNUM = P.BILLTO;

 

[synapsevampire]

You can use your own query.

As Turk suggested, you might do so by creating a View, however you should redesign it to allow for parameter passing (create parameters in the report using Insert->Field Object->Right click parameters and select new).

Then in the Report->Selection Formulas->Record place something like:

(
L.CALL = {?MyCallParm}
)
and
(
L.LETTING = {?MyLettingParm}
)

The report will now prompt for those values.

The view should look like:

SELECT DISTINCT
V.VENDOR,
R.ADDRNUM,
V.VNAMEL,
R.AADDR1,
P.BILLTO,
R.ACITY,
R.ASTATE,
R.AZIPCODE,
J.ROUTE,
Q.CPROJNUM,
Q.CFACSSUP,
Q.CCNTY1,
L.CALL,
L.LETTING
FROM VENDOR V, VENDADDR R, LETPROP L, PLANHOLD P,PROPOSAL Q, PROJECT J,PROPPROJ K
WHERE V.VENDOR = R.VENDOR
AND K.CONTID = Q.CONTID
AND K.PCN = J.PCN
AND L.LCONTID = K.CONTID
AND P.VENDOR = V. VENDOR
AND L.LETTING = P.LETTING
AND L.CALL = P.CALL
AND R.ADDRNUM = P.BILLTO;

The alternative is to use the Add Command under the Oracle Server data sources to paste in the SQL, but you're probably better off with a View to allow for simplified maintenance and reusability.

-k

 

[emuye]

It worked fine now.Thank you Gusy for your help.It really saved me a lot of work.I do not really know what i would do without your help.thank you again.

 

[Turkbear]

Hi,
We are glad to help...( We give so that we can receive..)

 

[emuye]

I am glad to hear that....One more thing i forgot one field to include in the view table that i have created how can i add that?

 

[Turkbear]

Hi,
Redo the command after adding the field to the select statement - the Create or Replace part handles the addition....It will rewrite the view.

 

[emuye]

thank you!