Home » SQL & PL/SQL » SQL & PL/SQL » distinct case statement (oracle 10g)
distinct case statement [message #323696] Thu, 29 May 2008 04:52 Go to next message
brightidea
Messages: 2
Registered: May 2008
Junior Member
Hi All,


We have an external partner who uses a query to retrieve all invoice data in one hit, but this also includes detail lines related to these invoices from another table. for example the output looks like:

t1.invoice no - t1.line - t1.fee - t2.dline
123456 - 1 - 1 - 1
123456 - 1 - 1 - 2
123456 - 2 - 1 - 1
123456 - 2 - 1 - 2
654321 - 1 - 1 - 1
654321 - 1 - 1 - 2
654321 - 2 - 0 - 1
654321 - 3 - 1 - 1


I have a case statement where if the fee line is 1, then it uses the amount field of table1, but because of joining table 2, this is duplicated for each table2 line.

is there a way to alter the case statement to only use the t1.fee line if it has not already seen the t1.line for the t1.invoice no?

im thinking along the lines of a non sql programming sense, like a variable that is populated with the previous invoice number and line, so that if it matches the current line, it ignores the fee.
the query is ordered by invoice no & t1.lineno

please advise if this is do-able without creating oracle functions.

Thanks,

Alan
Re: distinct case statement [message #323701 is a reply to message #323696] Thu, 29 May 2008 05:04 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
a variable that is populated with the previous invoice number and line

Are you, perhaps, looking for Oracle LAG function?
Re: distinct case statement [message #323705 is a reply to message #323696] Thu, 29 May 2008 05:15 Go to previous message
brightidea
Messages: 2
Registered: May 2008
Junior Member
Hi Littlefoot,

thank you for this, it does look like this will be the one.

I'll have a play and let you know.


thank you again.
Previous Topic: Oracle 9i database LDAP and Microsoft Active Directory
Next Topic: is this possible?
Goto Forum:
  


Current Time: Sun Dec 04 18:53:28 CST 2016

Total time taken to generate the page: 0.03796 seconds