How to combine two queries

[emuye]

I have two diffenrent queries that I want to use to generate a report.I did try to combine the two queries to one however, due to my limited knowledge i am not able to do that.If someone has any idea what i should do inorder for me to generate the right report using CR 8.5 and oracle 9i would be appreciated. I do not really know if i make a sense here...please let me know if i should provide further information to clarify my point.
One thing i would like to know if i could combine these two queries.
second if that is not possible what step should i take

 

[kskid]

It would be helpful to post

[ol]
[li]The sql queries[/li]
[li]example data[/li]
[li]expected output[/li]
[/ol]

-larry

 

[emuye]

select DISTINCT B.DATELET,P.CCNTY1,P.CFACSSUP,P.CONTID, P.CPROJNUM, P.CDESCR, P.PRROUTE
from LETPROP L, PROJECT R, PROPPROJ J, PROPOSAL P, PROPITEM Q, BIDLET B, BIDTABS C, ITEMLIST I
WHERE L.LETTING = B.LETTING
AND B.LETTING = C.LETTING
AND P.CSPECYR = I.ISPECYR
AND Q.PRPITEM = I.ITEM
AND L.LCONTID = P.CONTID
AND Q.CONTID = P.CONTID
AND C.VENDOR = L.AVENDOR
AND C.CALL = L.CALL
AND C.LINEFLAG = Q.LINEFLAG
AND C.IPLINENO = Q.IPLINENO
AND R.PCN = J.PCN
AND L.LETSTAT = 'A'
AND L.LETTING = '&lettingno'
AND P.CONTID = '&contractid'
AND L.CALL = '&call';
==========================================================

select DISTINCT Q.IPLINENO,I.ITEM,I.IDESCR,Q.QTY,I.IUNITS,Q.PRICE,AVG(C.BIDPRICE),MIN(C.BIDPRICE)
from LETPROP L, PROJECT R, PROPPROJ J, PROPOSAL P, PROPITEM Q, BIDLET B, BIDTABS C, ITEMLIST I,
VENDOR V, BIDDERS D
WHERE L.LETTING = B.LETTING
AND B.LETTING = C.LETTING
AND P.CSPECYR = I.ISPECYR
AND Q.PRPITEM = I.ITEM
AND P.CONTID = L.LCONTID
AND Q.CONTID = P.CONTID
AND C.VENDOR = L.AVENDOR
AND L.CALL = C.CALL
AND Q.LINEFLAG = C.LINEFLAG
AND Q.IPLINENO = C.IPLINENO
AND R.PCN = J.PCN
AND L.LETTING = D.LETTING
AND C.LETTING = L.LETTING
AND C.CALL = D.CALL
AND C.LETTING = D.LETTING
AND V.VENDOR = D.VENDOR
AND L.LETSTAT = 'A'
AND C.LINEFLAG = 'L'
AND L.LETTING = '&lettingno'
AND P.CONTID = '&contractid'
AND L.CALL = '&call'
GROUP BY Q.IPLINENO,I.ITEM,I.IDESCR,Q.QTY,I.IUNITS,Q.PRICE,C.BIDPRICE
ORDER BY Q.IPLINENO


I do not really know how i can tie these two query up and get the right out put.

 

[pgtek]

hi
have you tried creating a view?
You just bring your table in the view and make the links
then select the field you nee for your report

cheers

pg

pgtek

 

[kskid]

OK, we have the queries and it looks like there are two additional tables (VENDORS and BIDDERS) in the 2nd query.

But what do you want accomplished AFTER the queries are combined?

Just a plain report? Any calculations? Any grouping, summary, running totals, and so forth.

The post is still too vague for anyone to provide a suitable solution.

 

[kskid]

What are the key fields that links the tables together? LETTING or CONTID?

 

[Hm786]

Hi,

Create a union of the two queries and either create a view or storedprocedure. In unions you need to have same columns in both the queries so add dummy columns in the queries where there are no common fields. I am currently using lots of Union queries and converting to a stored procedure and accessing my reports to the strored proc.

Thanks,
Hamida

 

[emuye]

Sorry, i have been away from my desk ...that is my question how could i create a view to combine these two queries.I have very limited knowledge of creating a view.please walk me thorugh how i could create a view and combine these two queries.as you can see it is a little bit complicated and i am unable to combine it.I really appreciate your priceless help.

 

[emuye]

Can someone help me how i can create a view inorder to combine the above two queries.I am just trying to generate a report however for me to generate the report somehow i have to find a way to combine the above two queries.please it is urgent...i really need your help
thank you

 

[kskid]

It doesn't look that difficult in combining the SQL's. Just need some additional information.
Even creating a view, we need to know what you want to show in your final report.

Sample data would also help.

I am trying to visualize your database linking by reading your SQL. What is the description of the fields used in your where statements?

What is you primary key? LETTING or CONT_ID?

Based on what I can read thus far, are you wanting the contract information details for each IPLINENO?

Knowing what you are expecting as output would help tremendously.

 

[kskid]

One other thing.

Regarding the parameters you have (&lettingno, &contractid, and &call), I assume they are string characters since they are enclosed in single quotes and that you want to set them up as Crystal parameters as well?