I have 2 queries that i want to include without linking

[ss7415]

query 1
select *
from shipment
where bol_no is not null and receive_date is null and status_code <> 'R'

query 2

(select *
where shipment.order_no = ordhead.order_no
and status = 'A')

i want to get all data from both queries.

do i do a union or union all? i get an error

 

[ss7415]

basically , i want to get all the records from both queries into one query

 

[Madawc]

Is there any one-to-one relationship between individual records in the two datasets?

If not, maybe you should put one into a subreport, putting it in the report header or report footer.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[ss7415]

there is no field i can link the two tables together with. is there a way of doing besides a subreport? can i get both queries into one query without linking?

 

[ss7415]

i mean there is no one to one relationship between the two queries

 

[ss7415]

both queries have different fields that im trying to link together into one query

 

[ss7415]

any suggestion, that i can try, at this point anything will help. Thanks, all

 

[lbass]

You have to have a matching number of fields on each side of the union all, and the corresponding field (by order in the query) must be of the same datatype (and similar length).

You should probably specifiy the content of the error message.

-LB

 

[ss7415]

ERROR is :

SQL Error: ORA-01790: expression must have same datatype as corresponding expression


SELECT order_no,shipment, BOL_NO,SHIP_DATE,EST_ARR_DATE,STATUS_CODE,TO_LOC,FROM_LOC
FROM shipment
where bol_no is not null and receive_date is null and status_code <> 'R'


union all


select ordhead.ORDER_NO,ordhead.SUPPLIER ,ordhead.LOCATION,
ordhead.NOT_BEFORE_DATE,ordhead.NOT_AFTER_DATE,
ordhead.EARLIEST_SHIP_DATE ,ordhead.LATEST_SHIP_DATE ,ship_date
from ordhead,
shipment
where shipment.order_no = ordhead.order_no
and status = 'A'

 

[cehowski]

The link seems to be on your Order #. Are you sure they are of the same type and size? I'm not that familiar with Unions so I'm not sure if there might be another problem. I thought they were primarily for joining all records of two tables with the same structure, or combining two tables with different structures and a unique identifier.

 

[ss7415]

ok this might explain it better

i have this query, it doesnt work unless i run it seperately. so if i run the top part by itself it work or i run the bottem part by itself it works. When i do the union all join i get an error

SQL Error: ORA-01789: query block has incorrect number of result columns


if i cant do this with an union all, how can i make this work so that i can get the data from both queries in one combined query?



SELECT shipment, BOL_NO,SHIP_DATE,EST_ARR_DATE,STATUS_CODE,TO_LOC,FROM_LOC,
ship_date
FROM shipment

where bol_no is not null and receive_date is null and status_code <> 'R'


union all


select ordhead.order_no,shipment,
ordhead.SUPPLIER ,ordhead.LOCATION,
ordhead.NOT_BEFORE_DATE,ordhead.NOT_AFTER_DATE
,ordhead.EARLIEST_SHIP_DATE ,ordhead.LATEST_SHIP_DATE ,status
from ordhead,
shipment
where shipment.order_no = ordhead.order_no
and status = 'A'

 

[ss7415]

i think i got it to work
Thanks

SELECT order_no,shipment, BOL_NO,SHIP_DATE,EST_ARR_DATE,STATUS_CODE,TO_LOC,FROM_LOC,
ship_date
FROM shipment

where bol_no is not null and receive_date is null and status_code <> 'R'


union all


select ordhead.order_no,shipment, BOL_NO,SHIP_DATE,EST_ARR_DATE,STATUS_CODE,TO_LOC,FROM_LOC,
ship_date
from ordhead,
shipment
where shipment.order_no = ordhead.order_no
and status = 'A'

 

[lbass]

Looks like you got it right--that each field in the second query must match the type of the corresponding (by order) field in the first query, and that you must have the same number of fields.

-LB