select data with same range of period

hi
i have 2 tables
gen_ledgers : tran_no,doc_date
master_gen_ledgers : tran_no,doc_date
the second table is the master
and the first one is the details
actually there is no foreign key between them
i want to select data from both with records which has same period of date like this

select b.tran_no,b.doc_date,a.tran_no2,a.tran_no,A.DOC_DATE,(extract(year from A.DOC_DATE) ) as yr,(extract(month from A.DOC_DATE) ) as mn
from gen_ledgers b,master_gen_ledger a
where 
a.tran_no = b.tran_no
and (extract(year from A.DOC_DATE)) =  (extract(year from b.DOC_DATE)); 

Open in new window


but it returne error
NiceMan331Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulEE Topic AdvisorCommented:
What is the error message please?

Always include error code(s)/message(s) if hitting an error
NiceMan331Author Commented:
it stands on the last raw and release this error :

ORA-00920: invalid relational operator

Open in new window

Wasim Akram ShaikCommented:
Just a hint.. could be one of the reasons for the error...prior join and conversion.. check whether both the date conditions are in the same format.. if not use to_char or to_date appropriately..

extract(year from to_date(column_name,'YY-MM-DD')

select b.tran_no,b.doc_date,a.tran_no2,a.tran_no,A.DOC_DATE,(extract(year from A.DOC_DATE) ) as yr,(extract(month from A.DOC_DATE) ) as mn
from gen_ledgers b,master_gen_ledger a
where
a.tran_no = b.tran_no
and (extract(year from to_char(A.DOC_DATE,'DD-MM-YY')) =  (extract(year from to_char(b.DOC_DATE,'DD-MM-YY')));

if you still encounter the error.. do post the error as suggested
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

PortletPaulEE Topic AdvisorCommented:
I don't see what would cause that error from the query provided. However I would suggest this as te starting point:
SELECT
           b.tran_no
	, b.doc_date
	, a.tran_no2
	, a.tran_no
	, A.DOC_DATE
	, (extract(year FROM A.DOC_DATE)) AS yr
	, (extract(month FROM A.DOC_DATE)) AS mn
FROM gen_ledgers b
INNER JOIN master_gen_ledger a ON a.tran_no = b.tran_no
;

Open in new window


Currently your existing query is simply asking that all dates get aligned by year, but no specific date range has been requested (i.e. all years would be returned). What is it you really want to achieve?
NiceMan331Author Commented:
WASIM I GOT SAME ERROR
paul , each year has new serial of tran_no
so the query should have a condition of same year also
Wasim Akram ShaikCommented:
"SAME"

if you can post the "SAME" error message here.. then we would be able to help you quickly..!!

did you try to_date too?

select b.tran_no,b.doc_date,a.tran_no2,a.tran_no,A.DOC_DATE,(extract(year from A.DOC_DATE) ) as yr,(extract(month from A.DOC_DATE) ) as mn
from gen_ledgers b,master_gen_ledger a
where
a.tran_no = b.tran_no
and (extract(year from to_date(A.DOC_DATE,'DD-MM-YY')) =  (extract(year from to_date(b.DOC_DATE,'DD-MM-YY')));
PortletPaulEE Topic AdvisorCommented:
assuming those 2 doc_date columns are the date data type:
SELECT
           b.tran_no
	, b.doc_date
	, a.tran_no2
	, a.tran_no
	, A.DOC_DATE
	, (extract(year FROM A.DOC_DATE)) AS yr
	, (extract(month FROM A.DOC_DATE)) AS mn
FROM gen_ledgers b
INNER JOIN master_gen_ledger a ON a.tran_no = b.tran_no
              AND TRUNC(A.DOC_DATE,'YEAR') = TRUNC(b.DOC_DATE,'YEAR')
;

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NiceMan331Author Commented:
YES , i tried
same error
slightwv (䄆 Netminder) Commented:
I wonder if the tools and/or versions you are using don't like the EXTRACT function?

Try TO_CHAR everywhere you are using EXTRACT?

from:
(extract(year FROM A.DOC_DATE))

to:
(to_char(a.doc_date,'YYYY'))
NiceMan331Author Commented:
The last code of Paul works well , and return a result , but I will check the result if it is correct tomorrow I will accept his solution , thanx
NiceMan331Author Commented:
paul
the code is 100 % correct , i got an answer
but if you don't mind i have 2 questions :
1- why EXTRACT not works here inspite that i'm using it in another queries and reslut correct ?
2- actually doc_date in both tables should be same value to link
i mean the join should be
where a.tran_no = b.tran_no
and a.doc_date = b.doc_date
but in fact i found some of records dosent match same doc_date within same year
example :
record :  
a.tran_no = 1500   a.doc_date = '18-jan-15'
b.tran_no = 1500   b.doc_date = '19-jan-15'
as per your last query they match according to same year , which is correct
but i need to get those records whom their doc_date doesnt match in same year in purpose to update them
PortletPaulEE Topic AdvisorCommented:
I don't know why extract isn't working for you, but as there was no obvious syntax error I could see I simply used TRUNC() as the alternative as I know this works on every version of Oracle I have ever encountered.

---------------
a JOIN should  NOT be formed in the WHERE clause
that is very ancient approach (pre dating a 1986 standard)

please use explicit join syntax
-------------

>>"actually doc_date in both tables should be same value"

date/time information can be very tricky in joins particularly if the precision of the time information is very finegrained.
(really the transaction number by itself should be enough, but the current design does not allow that)

>>"i need to get those records whom their doc_date doesn't match"

This should find records where the year does match but the day does not. Is this sufficient?
SELECT
           b.tran_no
	, b.doc_date
	, a.tran_no2
	, a.tran_no
	, A.DOC_DATE
	, (extract(year FROM A.DOC_DATE)) AS yr
	, (extract(month FROM A.DOC_DATE)) AS mn
FROM gen_ledgers b
INNER JOIN master_gen_ledger a ON a.tran_no = b.tran_no
              AND TRUNC(A.DOC_DATE,'YEAR') = TRUNC(b.DOC_DATE,'YEAR')
WHERE TRUNC(A.DOC_DATE) <> TRUNC(b.DOC_DATE)
;

Open in new window

NiceMan331Author Commented:
ok thanx paul
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.