?
Solved

is null two queries

Posted on 2011-03-11
8
Medium Priority
?
216 Views
Last Modified: 2012-06-27
(select max(isnull(dateentered,0)) from payments where orderid=o.orderid) as paymentdate


instead of 0

I want to use
select dateordered from orders where orderid=o.orderid
0
Comment
Question by:rgb192
[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
8 Comments
 
LVL 15

Expert Comment

by:derekkromm
ID: 35110321
(select max(isnull(dateentered,(select dateordered from orders where orderid=o.orderid))) from payments where orderid=o.orderid) as paymentdate
0
 

Author Comment

by:rgb192
ID: 35110441
(select max(isnull(dateentered,(select dateordered from orders where orderid=70194))) from payments where orderid=70194) as paymentdate


Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'as'.
0
 
LVL 40

Expert Comment

by:lcohan
ID: 35111122
select max(isnull(dateentered,(select dateordered from orders where orderid=70194))) from payments where orderid=70194 as paymentdate
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 40

Expert Comment

by:lcohan
ID: 35111133
Darn...copy/paste...


select max(isnull(dateentered,(select dateordered from orders where orderid=70194))) from payments where orderid=70194
0
 

Author Comment

by:rgb192
ID: 35111429

select max(isnull(dateentered,(select dateordered from orders where orderid=70194))) from payments where orderid=70194

Msg 130, Level 15, State 1, Line 1
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.


I think you copy paste the same query
minus the as paymentdate

but I need the 'as paymentdate'



0
 
LVL 40

Assisted Solution

by:lcohan
lcohan earned 600 total points
ID: 35111777
select top 1
      case when p.dateentered is null then (select dateordered from orders o where o.orderid=p.orderid)
      else p.dateentered end as paymentdate
from payments p
order by 1 desc
0
 
LVL 41

Accepted Solution

by:
Sharath earned 1400 total points
ID: 35112187
try this.
SELECT ISNULL((SELECT MAX(dateentered) 
                 FROM payments 
                WHERE orderid = o.orderid),(SELECT TOP 1 dateordered 
                                              FROM orders 
                                             WHERE orderid = o.orderid)) AS paymentdate

Open in new window

0
 

Author Closing Comment

by:rgb192
ID: 35112576
thanks
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

770 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