| Need a Help on SQL query [message #641442] |
Fri, 14 August 2015 22:57  |
 |
ajay696
Messages: 3
Registered: August 2015
|
Junior Member |
|
|
Hi All,
I have data in my table like this
SQL: select asset,wonum,changedate,downtime from assetstatus where wonum='1077' order by changedate asc;
ASSET WO Changedate downtime
132476 1077 11-08-15 0
132476 1077 13-08-15 10.933333333333334
132476 1077 14-08-15 0
132476 1077 15-08-15 0.15
132476 1077 16-08-15 0
132476 1077 17-10-15 0
132476 1077 29-10-15 234.51666666666668
I want to select/display this data as below format.
ASSET WO changedate1 changedate2 downtime1 downtime2
132476 1077 11-08-15 13-08-15 0 10.933333333333334
132476 1077 14-08-15 15-08-15 0 0.15
132476 1077 16-08-15 null 0 null
132476 1077 17-10-15 29-10-15 0 234.51666666666668
I have to combine row1 and row2,row3 and row4,......etc and if there is any sequential rows which contains downtime zero (like 5 and 6 rows in my table) then I have to display null values in changedate2 and downtime2 columns for first dowtime zero row.
Any help on this to write select query for this?
Any help would be greatly appreciated
Thanks,
Ajay
[Updated on: Fri, 14 August 2015 22:59] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
| Re: Need a Help on SQL query [message #641447 is a reply to message #641446] |
Sat, 15 August 2015 01:57  |
 |
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
SQL> select asset,wonum,changedate,downtime from assetstatus where wonum='1077' order by changedate asc;
select asset,wonum,changedate,downtime from assetstatus where wonum='1077' order by changedate asc
*
ERROR at line 1:
ORA-00942: table or view does not exist
I have not this table so I can't work, search and test any query with it.
I don't know your Oracle version so I can't know which query could be valid or not in this version.
Anyway, it seems to me this is a basic PIVOT query, so search for this word.
[Updated on: Sat, 15 August 2015 01:58] Report message to a moderator |
|
|
|
|
|
| Re: Need a Help on SQL query [message #641464 is a reply to message #641442] |
Sat, 15 August 2015 09:37 |
 |
EdStevens
Messages: 1376
Registered: September 2013
|
Senior Member |
|
|
A couple of observations ..
First, what is the data type of changedate? If it is not DATE or TIMESTAMP, you have a flawed design. If you do not fix it, you will pay over and over again until such time as you bite the bullet and fix it. Like the old Fram oil filter commercial, "you can pay me now, or you can pay me later".
Second, your example case only deals with the simplistic situation of two changedates per report line, with other report lines showing two more pairs (each) for the same asset/wo combination. What differentiats the data on one report line from that on another?
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
