sql query condition in oracle report IF ELSE

Question

I have The following tables:

DOCUMENT(iddoc,doctype,title,publishingdate,validTillDate)
USERS(iduser,fname,lname)
TRANSACTION(idtrans,iduser,iddoc,transdate,schedulereturndate)

I'm asked to Indicate for a given document whether it is available or not and if it is borrowed by whom, and when it should be returned. So how can i have these conditions in my query.

where my code will be like this:

if(d.validTillDate < SYSDATE){

SELECT u.iduser t.schedulereturndate
FROM USERS u, TRANSACTION t
WHERE u.iduser=t.iduser

}

SO I WANNA KNOW HOW CAN I CODE THIS IF

Can you please post some data, what you already tried and the problem you are facing with? — Aleksej, Jan 21 '16 at 14:19
1. mysql and oracle are 2 different products. Pls remove the unnecessary tag. 2. Pls provide sample data and expected output. 3. Pls describe what you have tried so far to achieve the expected results what problems you encountered. — Shadow, Jan 21 '16 at 14:20
why you check `validitydate` to see if item is available. Shouldnt check for `schedulereturndate`? — Juan Carlos Oropeza, Jan 21 '16 at 14:29
`d.validitydate < SYSDATE` means *document is no more valid*, not *document is still valid*. Is this what you want? — Sasha, Jan 21 '16 at 14:36

Rene · Accepted Answer · 2016-01-21T14:39:00.873

0

The query for a borrowed document would be something like this:

    SELECT d.iddoc,u.iduser t.schedulereturndate,'Borrowed'
    from document d,
         ,USERS u
         ,TRANSACTION t
    WHERE u.iduser=t.iduser
    and   t.iddoc=d.iddoc
    and   d.validitydate<sysdate
    union
 SELECT d.iddoc,null,null,'Not borrowed'
    from document d,
    WHERE d.validitydate is null 
    or d.validitydate>=sysdate

Edit ) added a union for the not borrowed documents.

edited Jan 21 '16 at 14:39

answered Jan 21 '16 at 14:32

Rene

10,391
5
33
46

And i have to indicate if it's not borrowed – joseph kettaneh Jan 21 '16 at 14:35
Implicit JOIN sintaxis are deprecated since sql 2005, Please use explicit JOIN instead http://stackoverflow.com/questions/44917/explicit-vs-implicit-sql-joins Aaron Bertrand wrote a nice article about it https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins – Juan Carlos Oropeza Jan 21 '16 at 14:37

score 0 · Answer 2 · answered Jan 21 '16 at 14:55

It's hard to understand your question for me. If I guessed right, then:

SELECT d.iddoc, u.iduser, t.schedulereturndate
FROM
    document d
    LEFT JOIN transaction t ON
        (d.iddoc=t.iddoc)
            -- join by iddoc field
        AND (SYSDATE BETWEEN t.transdate AND t.schedulereturndate)
            -- I assume we need only current transactions, not past (or future?)
WHERE
    (SYSDATE<=d.validTillDate)
        -- I assume we need only documents whose validity date not passed yet

Assuming there are no active transactions for iddoc=1, one active transaction for iddoc=2 and two active transactions for iddoc=3, the result will look like:

iddoc | iduser | schedulereturndate
------+--------+-------------------
1       NULL     NULL
2       534      2017-09-08
3       54334    2016-03-02
3       2433     2016-07-01

sql query condition in oracle report IF ELSE

2 Answers2