OraFAQ Forum: SQL & PL/SQL » Query

Home » SQL & PL/SQL » SQL & PL/SQL » Query - Is there a way?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Query - Is there a way? [message #7515]

Thu, 19 June 2003 01:47

Andrew
Messages: 144
Registered: March 1999

Senior Member

Hi,

I've got the following query: -

SELECT NVL(TR.CASH_AMOUNT, 0), NVL(TR.CREDITCARD_AMOUNT, 0), NVL(TR.CHEQUE_AMOUNT, 0),
	TO_DATE(TR.TRANS_DATE), TR.CARD_NUM, ST.BANKED, TR.ID
	FROM TB_TRANSACTIONS TR, TB_SAGE_TRANS ST
	WHERE TR.ID = ST.TRANS_ID (+)
	AND NVL(ST.BANKED, -1) = -1
	AND TR.CASHREG_ID = 1
	AND TR.TRANS_DATE <= TO_DATE('19.06.2003 10:00:00','DD.MM.YYYY HH24:MI:SS')

I'd like to add another criteria that only returns records where one or more of

NVL(TR.CASH_AMOUNT, 0), NVL(TR.CREDITCARD_AMOUNT, 0), NVL(TR.CHEQUE_AMOUNT, 0)

have a value greater than 0.

I've tried adding

 OR NVL(TR.CASH_AMOUNT, 0) > 0

but got an error.

Is the above possible, if so, what change is needed?

Regards,

Andrew

Report message to a moderator

Re: Query - Is there a way? [message #7516 is a reply to message #7515]

Thu, 19 June 2003 02:26

Maaher
Messages: 7065
Registered: December 2001

Senior Member

What error did you receive?

MHE

Report message to a moderator

Re: Query - Is there a way? [message #7517 is a reply to message #7516]

Thu, 19 June 2003 03:02

Andrew
Messages: 144
Registered: March 1999

Senior Member

Hi,

The error was: -

ORA-01719: outer join operator (+) not allowed in operand of OR or IN

Regards,

Andrew

Report message to a moderator

Re: Query - Is there a way? [message #7522 is a reply to message #7517]

Thu, 19 June 2003 04:17

Maaher
Messages: 7065
Registered: December 2001

Senior Member

There you go, it means that you cannot use an OUTER JOIN (+) in combination with IN or OR, but only with the AND operator.

You can add the following to your query:

AND (NVL(TR.CASH_AMOUNT, 0) > 0
     OR
     NVL(TR.CREDITCARD_AMOUNT, 0) > 0 
     OR
     NVL(TR.CHEQUE_AMOUNT, 0) > 0
     )

Perhaps this 'll help you out.

MHE

Report message to a moderator

Re: Query - Is there a way? [message #7545 is a reply to message #7522]

Fri, 20 June 2003 01:46

Souvik Das
Messages: 1
Registered: June 2003

Junior Member

An easier method is:

AND ((NVL(TR.CASH_AMOUNT, 0)+ NVL(TR.CREDITCARD_AMOUNT, 0)+ NVL(TR.CHEQUE_AMOUNT, 0)) > 0)

Report message to a moderator

Re: Query - Is there a way? [message #15129 is a reply to message #7517]

Wed, 22 September 2004 01:11

Ashu Mehta
Messages: 1
Registered: September 2004

Junior Member

Instead of 'OR' operand, redesign your query with Decode or UNION clause.

Report message to a moderator

Previous Topic:	to print the months/days of a given date
Next Topic:	Migrate Item Attachment - Long Text

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 15:17:33 CDT 2024