Oracle SQL Statement

I'm doing this report on Crystal Report and trying to merge this 2 sql statements into one so I can consolidate the records for one report. I've played around but still no go.

Anyone has any idea?

Statement 1
[php]
SELECT CNTR.ARR_DTTM, TO_CHAR(CNTR.ARR_DTTM, 'DY') AS ADAY, TO_CHAR(CNTR.ARR_DTTM, 'HH24MI') AS ATIME
FROM TOPS.VESSEL_CALL VES, TOPS.BERTHING BERTHING, TOPS.CNTR_EVENT_LOG CNTR
WHERE ((VES.VV_CD = BERTHING.VV_CD) AND (BERTHING.VV_CD = CNTR.LD_VV_CD))
AND CNTR.ARR_DTTM BETWEEN TO_DATE('{?StartDate}', 'MM/DD/YYYY') AND TO_DATE('{?EndDate}', 'MM/DD/YYYY')
AND CNTR.PURP_CD = 'EX'
[/php]

Statement 2
[php]
SELECT CNTR.EXIT_DTTM, TO_CHAR(CNTR.EXIT_DTTM, 'DY') AS ADAY, TO_CHAR(CNTR.EXIT_DTTM, 'HH24MI') AS ATIME
FROM TOPS.VESSEL_CALL VES, TOPS.BERTHING BERTHING, TOPS.CNTR_EVENT_LOG CNTR
WHERE ((VES.VV_CD = BERTHING.VV_CD) AND (BERTHING.VV_CD = CNTR.DISC_VV_CD))
AND CNTR.EXIT_DTTM BETWEEN TO_DATE('{?StartDate}', 'MM/DD/YYYY') AND TO_DATE('{?EndDate}', 'MM/DD/YYYY')
AND CNTR.PURP_CD = 'IM'
[/php]

One retrieves from EXIT_DTTM table and the other ARR_DTTM with 2 different tables for merging.

Or anyone knows of a nice resourceful forum I can get help from? Thanks

Note : Sorry for the messed up forum layout :D

What are you using to display them? Might be easier to use a scripting language to join them into one table for display? Have you looked into JOINS? INNER JOIN, OUTER JOIN, LEFT JOIN, RIGHT JOIN .... If your result can be a record where both WHERE clauses are true with one result, I could see how it would work combined, but if you are looking for 2 seperate records, not sure how that would work.

I just need the raw data. I'm using Crystal Reports to format the data retrieved and display it as a report based on user's input which are the start date and end date. I wish I could use a scripting language, but I'm required to use CR.

I'm not quite sure what you mean here

If your result can be a record where both WHERE clauses are true with one result, I could see how it would work combined, but if you are looking for 2 seperate records, not sure how that would work.

The data are pretty similar, except they are taken from 2 different tables. I'm retrieving the time and day of each record from the arr_dttm and exit_dttm columns

Is there a field that relates these tables together?

for example:

table one:
id_one
name_one

table two:
id_two
name_two
id_one

You can join 2 tables together with a common identifier (foreign key) and use a WHERE clause, SELECT * FROM table_one, table_two WHERE id_one.table_one = id_one.table_two

for example.

What I am gathering is you are wanting the arrival and exit times/days for each record. How many tables are you dealing with? google Oracele Joins and see if there is a type of one that can help you out.
What is the link between the arrival and exit tables? Is there some kind of common link, like an id where the 2 can be related in some way?

Thanks for the help. Found out that I didn't have to rewrite the query. Just needed to add the Union command to merge the two queries together. Union for distinct queries, Union all for everything ;)

[php]
SELECT CNTR.ARR_DTTM, TO_CHAR(CNTR.ARR_DTTM, 'DY') AS ADAY, TO_CHAR(CNTR.ARR_DTTM, 'HH24MI') AS ATIME
FROM TOPS.VESSEL_CALL VES, TOPS.BERTHING BERTHING, TOPS.CNTR_EVENT_LOG CNTR
WHERE ((VES.VV_CD = BERTHING.VV_CD) AND (BERTHING.VV_CD = CNTR.LD_VV_CD))
AND CNTR.ARR_DTTM BETWEEN TO_DATE('{?StartDate}', 'MM/DD/YYYY') AND TO_DATE('{?EndDate}', 'MM/DD/YYYY')
AND CNTR.PURP_CD = 'EX'

UNION ALL

SELECT CNTR.EXIT_DTTM, TO_CHAR(CNTR.EXIT_DTTM, 'DY') AS ADAY, TO_CHAR(CNTR.EXIT_DTTM, 'HH24MI') AS ATIME
FROM TOPS.VESSEL_CALL VES, TOPS.BERTHING BERTHING, TOPS.CNTR_EVENT_LOG CNTR
WHERE ((VES.VV_CD = BERTHING.VV_CD) AND (BERTHING.VV_CD = CNTR.DISC_VV_CD))
AND CNTR.EXIT_DTTM BETWEEN TO_DATE('{?StartDate}', 'MM/DD/YYYY') AND TO_DATE('{?EndDate}', 'MM/DD/YYYY')
AND CNTR.PURP_CD = 'IM'
[/php]

ah.... Union is something mySQL doesn't do.... until version 4.0 ( time to upgrade - lol )

http://www.mysql.com/doc/en/UNION.html

But that is definitely a handy function....

Not sure, if I should open a new thread.. (i can split them up later anyways :D) Anyone here knows about left and inner joins for Oracle?? My Oracle query doesn't return anything while the Access version returns the records I need.. The queries are as follows :

Oracle SQL


SELECT *
FROM tops.berthing berthing, tops.vessel_call ves, tops.cntr cntr, tops.nominated_vsl nom, tops.vessel_call vessel_call_1
WHERE ((berthing.vv_cd = ves.vv_cd)
AND (ves.vv_cd = cntr.disc_vv_cd)
AND (nom.nom_vv_cd = cntr.nom_load_vv_cd)
AND (vessel_call_1.vv_cd = cntr.load_vv_cd))


Access SQL


SELECT *
FROM (((TOPS_VESSEL_CALL INNER JOIN TOPS_BERTHING ON TOPS_VESSEL_CALL.VV_CD = TOPS_BERTHING.VV_CD)
INNER JOIN TOPS_CNTR ON TOPS_VESSEL_CALL.VV_CD = TOPS_CNTR.DISC_VV_CD)
LEFT JOIN TOPS_NOMINATED_VSL ON TOPS_CNTR.NOM_LOAD_VV_CD = TOPS_NOMINATED_VSL.NOM_VV_CD)
LEFT JOIN TOPS_VESSEL_CALL AS TOPS_VESSEL_CALL_1 ON TOPS_CNTR.LOAD_VV_CD = TOPS_VESSEL_CALL_1.VV_CD