Re: Here's one for the SQL experts -- MIDRANGE-L

× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.

On 23 Feb 2013 11:17, Jack Callahan wrote:

<<SNIP>>

SELECT account_id
, SELECT MAX( date_of_call ) FROM callrptview
WHERE acctview.account_id = callrptview.account_id
, other_columns_from_acctview
FROM acctview
WHERE account_id EXISTS IN ( SELECT DISTINCT account_id FROM callrptview )

A scalar fullselect [aka scalar subselect] must be in parentheses, and the predicate would be either one of EXISTS or IN. So for example, either of the following variations of the above should make the query functional as intended:

SELECT account_id
, ( SELECT MAX( date_of_call )
FROM callrptview
WHERE acctview.account_id = callrptview.account_id
) as maxdate
, other_columns_from_acctview
FROM acctview
WHERE account_id IN ( SELECT DISTINCT account_id FROM callrptview )
/* and the DISTINCT is optional */

SELECT account_id
, ( SELECT MAX( date_of_call )
FROM callrptview
WHERE acctview.account_id = callrptview.account_id
) as maxdate
, other_columns_from_acctview
FROM acctview
WHERE EXISTS ( SELECT '1'
FROM callrptview
WHERE callrptview.account_id = acctview.account_id )

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.