OraFAQ Forum: Client Tools » Query lines to mimic excel formula

Home » Other » Client Tools » Query lines to mimic excel formula (Oracle SQLite developer)

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

Query lines to mimic excel formula [message #651007]

Mon, 09 May 2016 13:12

Skyfly
Messages: 1
Registered: May 2016
Location: Saudi arabi

Junior Member

Assume I have date columns in d1 & e1 and in q1 I have a reference date '01-JAN-2016' and in r1 I have '01-JAN-2017', and I want to write a query lines that mimics an excel formula line of
=ROUND(IF(AND(D2<$Q$1,E2<$R$1),E2-$Q$1,(IF(AND(D2>$Q$1,E2>$R$1),$R$1-D2,E2-D2)))/366,2)

TIA

Report message to a moderator

Re: Query lines to mimic excel formula [message #651008 is a reply to message #651007]

Mon, 09 May 2016 13:21

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Welcome to this forum.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

Report message to a moderator

Re: Query lines to mimic excel formula [message #651009 is a reply to message #651007]

Mon, 09 May 2016 13:30

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

And explain us what the formula does, this an Oracle forum not an Excel one.

Report message to a moderator

Re: Query lines to mimic excel formula [message #651050 is a reply to message #651007]

Tue, 10 May 2016 08:53

thatjeffsmith
Messages: 81
Registered: July 2009
Location: Raleigh, NC

Member

When you see data in a table, displayed in a grid, they're KIND OF like rows in a spreadsheet

But you can't really reference them like rows in a spreadsheet

The only reliable way of referencing a particular row is by it's ROWID or Primary Key, or a UNIQUE column value

Rows are not ordered in an Oracle table - order is ONLY guaranteed when using an ORDERY BY in your query.

So what's row #1 when you run a query once, could be row #100 when you run it the second time.

Report message to a moderator

Previous Topic:	how to use variables in sql using sqlplus
Next Topic:	TNSNAMES not detected

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Feb 02 20:19:55 CST 2025