Subquery Question 2

[scriptscribe]

Hi,

I'm running the following subquery:

select *
into #SumDaysTemp 
from (select * from #SumDays 
	join (Select Max(Acctnum) as AcctNumID,a.AcctNum, a.firstname, a.lastname, a.dob
	From #Sumdays a
	group by a.AcctNum, a.firstname, a.lastname, a.dob
	Having (Count(AcctNum) > 1)) B on 
	c.AcctNum = B.AcctNum)

The subquery itself runs fine if I run it by itself. All I want to do is put the results of the subquery into a temp table "#SumDaysTemp" to do some date math and I'm getting the following error:

Line 11: Incorrect syntax near ')'.

Any help would be greatly appreciated.

Thanks,
Mike

 

[ptheriault]

You have an extra paren.

select *
into #SumDaysTemp
from (select * from #SumDays
join (Select Max(Acctnum) as AcctNumID,a.AcctNum, a.firstname, a.lastname, a.dob
From #Sumdays a
group by a.AcctNum, a.firstname, a.lastname, a.dob
Having (Count(AcctNum) > 1)) B on
c.AcctNum = B.AcctNum)

try this.

select *
into #SumDaysTemp
from (select * from #SumDays
join (Select Max(Acctnum) as AcctNumID,a.AcctNum, a.firstname, a.lastname, a.dob
From #Sumdays a
group by a.AcctNum, a.firstname, a.lastname, a.dob
Having (Count(AcctNum) > 1) B on
c.AcctNum = B.AcctNum)

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[monksnake]

I don't see the alias c anywhere.

--> c.AcctNum

Another thing you'll get reamed for in here, you should never put "SELECT *". You should always specify all fields needed in a query.

I think your query isn't running cause you have to alias the derived table

select *
into #SumDaysTemp 
from (select * from #SumDays 
    join (Select Max(Acctnum) as AcctNumID,a.AcctNum, a.firstname, a.lastname, a.dob
    From #Sumdays a
    group by a.AcctNum, a.firstname, a.lastname, a.dob
    Having (Count(AcctNum) > 1)) B on 
    c.AcctNum = B.AcctNum) [!]d[/!]

<.

 

[scriptscribe]

It should have read "#SumDays c" in the subquery.

 

[scriptscribe]

You have an extra paren.

select *
into #SumDaysTemp
from (select * from #SumDays
join (Select Max(Acctnum) as AcctNumID,a.AcctNum, a.firstname, a.lastname, a.dob
From #Sumdays a
group by a.AcctNum, a.firstname, a.lastname, a.dob
Having (Count(AcctNum) > 1)) B on
c.AcctNum = B.AcctNum)

try this.

select *
into #SumDaysTemp
from (select * from #SumDays
join (Select Max(Acctnum) as AcctNumID,a.AcctNum, a.firstname, a.lastname, a.dob
From #Sumdays a
group by a.AcctNum, a.firstname, a.lastname, a.dob
Having (Count(AcctNum) > 1) B on
c.AcctNum = B.AcctNum)

Now I'm getting the error message: Incorrect Syntax on Line 8 Near "B

 

[ptheriault]

Sorry about that. I was having a hard time reading the format.

[COLOR=blue]Select[/color] *
[COLOR=blue]into[/color] #SumDaysTemp
[COLOR=blue]from[/color] ([COLOR=blue]select[/color] * 
       [COLOR=blue]from[/color] #SumDays
        [COLOR=blue]join[/color] ([COLOR=blue]Select[/color] [COLOR=#FF00FF]Max[/color](Acctnum) [COLOR=blue]as[/color] AcctNumID
                  , a.AcctNum
                  ,a.firstname 
                  ,a.lastname
                  ,a.dob
             [COLOR=blue]From[/color] #Sumdays a
             [COLOR=blue]group[/color] [COLOR=blue]by[/color] a.AcctNum
                     ,a.firstname
                     ,a.lastname
                     ,a.dob
              [COLOR=blue]Having[/color] ([COLOR=#FF00FF]Count[/color](AcctNum) > 1) ) B
      [COLOR=blue]on[/color] c.AcctNum = B.AcctNum)

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[scriptscribe]

Still getting the same error near ')' on the last line.

 

[monksnake]

Have you not tried my solution?
(Look where the **** is)

select *
into #SumDaysTemp 
from (select * from #SumDays 
    join (Select Max(Acctnum) as AcctNumID,a.AcctNum, a.firstname, a.lastname, a.dob
    From #Sumdays a
    group by a.AcctNum, a.firstname, a.lastname, a.dob
    Having (Count(AcctNum) > 1)) B on 
    c.AcctNum = B.AcctNum) ****d****

<.

 

[scriptscribe]

Yes, I tried it, sorry. I got the message:

The column 'acctnum' was specified multiple times for 'd'.

 

[scriptscribe]

I figured it out. I had to alias the columns in the subquery.

Here's the code that worked:

select * into #SumDaysTemp from
(select c.AcctNum,c.FirstName,c.LastName,c.DOB,c.SSN,c.AdmitDate,c.DischargeDate
,c.CoverageID,c.StartDate,c.EndDate,c.ProgramType,c.FinClassCurrent,c.InsuranceCode1
,c.InsuranceCode2,c.InsuranceCode3,c.ServiceType,c.DOB as DateOfBirth
 from #SumDays c
join (Select Max(Acctnum) as AcctNumID, a.AcctNUm as AN, a.firstname as FN
	, a.lastname as ln, a.dob as db
	From #Sumdays a
	group by a.AcctNum, a.firstname, a.lastname, a.dob
	Having (Count(AcctNum) > 1)) B
on c.AcctNum = B.AN) d

Thanks for all of your help.

Mike

 

[monksnake]

Remove all references to 'a':

    join (Select Max(Acctnum) as AcctNumID, AcctNum, firstname, lastname, dob
    From #Sumdays 
    group by AcctNum, firstname, lastname, dob

<.

 

[monksnake]

Ahh good call. One for you as well.

<.

 

[scriptscribe]

Woo hoo! My first star!

 

[monksnake]

Looks like your 4th

<.

 

[scriptscribe]

True, but it's my first in a programming-type forum.