Home » SQL & PL/SQL » Client Tools » Query lines to mimic excel formula (Oracle SQLite developer)
Query lines to mimic excel formula [message #651007] Mon, 09 May 2016 13:12 Go to next message
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
Re: Query lines to mimic excel formula [message #651008 is a reply to message #651007] Mon, 09 May 2016 13:21 Go to previous messageGo to next message
BlackSwan
Messages: 25720
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.

Re: Query lines to mimic excel formula [message #651009 is a reply to message #651007] Mon, 09 May 2016 13:30 Go to previous messageGo to next message
Michel Cadot
Messages: 65203
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

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

Re: Query lines to mimic excel formula [message #651050 is a reply to message #651007] Tue, 10 May 2016 08:53 Go to previous message
thatjeffsmith
Messages: 71
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.
Previous Topic: how to use variables in sql using sqlplus
Next Topic: TNSNAMES not detected
Goto Forum:
  


Current Time: Thu Nov 23 21:50:59 CST 2017

Total time taken to generate the page: 0.02783 seconds