max date in create table in sql

[sarahzhou]

Hi Instructors,

I created table as below:

create table WORK.RelationTable as select * from connection to ServerName
(select distinct A.*
           ,INDV_KEY
           ,TO_KEY
           ,RLTN_TX
           ,RLTN_STRT_DT
           ,max(RLTN_END_DT) as RLTN_END_DT from &TableSourceOne. A
INNER join ##TableSourceTwo B
on (A.INDV_KEY=B.TO_INDV_KEY)
);

the error occurs at max(RLTN_END_DT) as RLTN_END_DT, when I change back to RLTN_END_DT, then it runs ok.

I tried add a filter where  RLTN_END_DT=max(RLTN_END_DT), it doesn't work

What shoud I do?

Thank you!

RLTN_END_DT: relation end datetime

[Kurt_Bremser]

• Please post the complete log from that step into a window opened with this button: 
• Since this happens in explicit pass-through, you should consult sources that deal with that specific DBMS

[ChrisNZ]

1. Is max a valid function name in your database?

2. Do you really want to use max() and have no group by clause?

[sarahzhou]

I added group by clause

create table WORK.RELATION as select * from connection to SQLSERVER
		    (select distinct A.*, INDV_KEY, TO_INDV_KEY, RLTN_TX, RLTN_STRT_DT, MAX(RLTN_END_DT) as RLTN_END_DT from &TABLENAMEONE. A
				INNER join ##TABLEDATATWO B
					on (A.INDV_KEY=B.TO_INDV_KEY)				
			)GROUP BY INDV_KEY;

ERROR: CLI describe error: [SAS][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Column 'TABLENAMEONE.INDV_KEY' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. :
[SAS][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]The batch could not be analyzed because of compile errors.

what is wrong with my "MAX(RLTN_END_DT)".

Please help. Thank you!

[ChrisNZ]

The message couldn't be clearer:

  Column 'TABLENAMEONE.INDV_KEY' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

> what is wrong with my "MAX(RLTN_END_DT)".

 Nothing. As the message mentions, the issue is with INDV_KEY

What result do you expect from this query? What total time do you expect ?

Also adding group by outside of the parentheses makes no sense.

[sarahzhou]

In previous code which run correct and get the table below

INDV_KEY	TO_INDV_KEY	RLTN_TX	RLTN_STRT_DT	RLTN_END_DT
1F6A57115A	1F6A57115B	member	14-Feb-18	30-Apr-18
1F6A57115A	1F6A57115B	member	24-Feb-18	.

I only want the first row to be returned.

Thank you

[Kurt_Bremser · Accepted solution]

First of all, you must tell SQL from where each individual column has to be taken, so all of your column names should either have an A. or B. as prefix. Even if the procedure does not complain (because a certain column is contained in only one table), working without prefixes is sloppy programming.

Finding a single record in a group is done like this in SQL:

select a.*
from table a
group by a.indv_key
having rltn_end_dt = max(a.rltn_end_dt)

[sarahzhou]

Thank you! The code was fixed. 🙂
Thank you very much!