Solved

Oracle syntax

Posted on 2013-02-04
9
273 Views
Last Modified: 2013-02-07
I have a query that has 2 dates(start and end date) and if it does not have a value, then its considered a blank, and I want to sort by another date(create date) but if the date has a value I want it sorted by start date. In other words I want the blanks to use create date and if it has a value sort by the start date but I want them all in one sortable field

So maybe we can include an aliased field that has the logic above and I can sort a column but it would have to be a mixture of the 2 fields so if I have 3 records2 with start dates and one blank that has a create date in between the 2 start dates I want them coming back with the blank as the middle date from the sort....

Hope this makes sense.
0
Comment
Question by:jknj72
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 250 total points
ID: 38851045
select * from your_table
order by nvl(start_date, create_date)


if that doesn't do it, please provide sample data an expected output
0
 

Author Comment

by:jknj72
ID: 38851066
I think that will work how can I get that into one aliased field in my select statement?

logic
if start_date = '' then
     create_date
else
  start_date
end if as 'SORTED DATE'

What would be the syntax for this?
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 250 total points
ID: 38851073
same expression:

select nvl(start_date, create_date) sorted_date
  from yourtable ...
0
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 

Author Comment

by:jknj72
ID: 38851092
that should do it. Thanks for your help
0
 

Author Comment

by:jknj72
ID: 38851098
thanks
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 38851100
isn't a split in order?  the accepted post simply restates the same expression originally posted
0
 
LVL 32

Expert Comment

by:awking00
ID: 38851672
FWIW, could also have used coalesce(start_date, create_date) sorted_date.
0
 

Author Closing Comment

by:jknj72
ID: 38865248
thanks
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question