Solved

is null two queries

Posted on 2011-03-11
8
209 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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 150 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 350 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

734 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