problem in joining in sql

Question

4.00/5 (1 vote)

See more:

hello frnds i have a problem in joining ...

SQL

alter PROCEDURE [dbo].[sp_GatherCenterInfoForSpecialpaymentsTypes]      
(      
    @company_u_id int,      
    @UnitId int      
)      
AS      
BEGIN      
    SET NOCOUNT ON;  
 
    SELECT t1.Center_Name, t1.Center_Code, t1.Center_Id,t1.Center_Id_Format  
    FROM DayOpenDayCloseInfo 
    INNER JOIN (  
        (SELECT CenterInfo.Center_Name, CenterInfo.Center_Code, 
         CenterInfo.Center_Id,CenterInfo.Center_Id_Format, 
         MAX(DayOpenDayCloseInfo.DayOPenCloseId)     
         FROM CenterInfo 
         INNER JOIN DayOpenDayCloseInfo
         ON CenterInfo.Center_Id = DayOpenDayCloseInfo.Center_Id   
         WHERE (CenterInfo.Unit_Id = @UnitId) AND (CenterInfo.IsActive = 1) 
             AND (CenterInfo.company_u_id=@company_u_id)
         GROUP BY CenterInfo.Center_Name, CenterInfo.Center_Code, 
             CenterInfo.Center_Id,CenterInfo.Center_Id_Format)
    ) t1
    ON t1.Center_Id = DayOpenDayCloseInfo.Center_Id
    WHERE DayOpenDayCloseInfo.IsActive = 0
END
RETURN

when it give error on execute that ...

"Msg 8155, Level 16, State 2, Procedure sp_GatherCenterInfoForSpecialpaymentsTypes, Line 21
No column name was specified for column 5 of 't1'."

i am unable to understand what the problem is ../

in this i am joining a result set with an table .... as u see here ..

pls rply ..Thnks in advance

Posted 23-Jul-13 23:09pm

GDdixit

Updated 23-Jul-13 23:15pm

Prasad Khandekar

v2

Add a Solution

5 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Prasad Khandekar · Answer 1 · 2013-07-23T23:21:00

Hello,

Have you tried rewriting your proc as shown below.

SQL

ALTER PROCEDURE [dbo].[sp_GatherCenterInfoForSpecialpaymentsTypes]      
(      
    @company_u_id int,      
    @UnitId int      
)      
AS      
BEGIN      
    SET NOCOUNT ON;  
 
    SELECT t1.Center_Name, t1.Center_Code, t1.Center_Id, t1.Center_Id_Format  
    FROM DayOpenDayCloseInfo 
    INNER JOIN (  
        (SELECT CenterInfo.Center_Name, CenterInfo.Center_Code, 
         CenterInfo.Center_Id,CenterInfo.Center_Id_Format, 
         MAX(DayOpenDayCloseInfo.DayOPenCloseId) AS DayOPenCloseId
         FROM CenterInfo 
         INNER JOIN DayOpenDayCloseInfo
         ON CenterInfo.Center_Id = DayOpenDayCloseInfo.Center_Id   
         WHERE (CenterInfo.Unit_Id = @UnitId) AND (CenterInfo.IsActive = 1) 
             AND (CenterInfo.company_u_id=@company_u_id)
         GROUP BY CenterInfo.Center_Name, CenterInfo.Center_Code, 
             CenterInfo.Center_Id, CenterInfo.Center_Id_Format)
    ) t1
    ON t1.Center_Id = DayOpenDayCloseInfo.Center_Id
    WHERE DayOpenDayCloseInfo.IsActive = 0
END
RETURN

Regards,

Guirec · Answer 2 · 2013-07-23T23:20:00

Solution 1

To be hones the query is pretty messy...

But try providing an alias to MAX(DayOpenDayCloseInfo.DayOPenCloseId) in your subquery that should help
something like : MAX(DayOpenDayCloseInfo.DayOPenCloseId) as MaxOpenDay

Posted 23-Jul-13 23:20pm

Guirec

v2

Comments

GDdixit 24-Jul-13 5:39am

it work but not giving the desired result

actually the query result which is inside inner joining brackets

i.e. (SELECT CenterInfo.Center_Name, CenterInfo.Center_Code,
CenterInfo.Center_Id,CenterInfo.Center_Id_Format,
MAX(DayOpenDayCloseInfo.DayOPenCloseId)
FROM CenterInfo
INNER JOIN DayOpenDayCloseInfo
ON CenterInfo.Center_Id = DayOpenDayCloseInfo.Center_Id
WHERE (CenterInfo.Unit_Id = @UnitId) AND (CenterInfo.IsActive = 1)
AND (CenterInfo.company_u_id=@company_u_id)
GROUP BY CenterInfo.Center_Name, CenterInfo.Center_Code,
CenterInfo.Center_Id,CenterInfo.Center_Id_Format)
)

give a result set and a single row for each center_id

but when i join this with a table than i only want to select those results which which satisfy this condition ... (DayOpenDayCloseInfo.IsActive = 0)

there is multiple records in DayOpenDayCloseInfo for each center_id but i have to slect only those results which satisfy this condition e.i. (DayOpenDayCloseInfo.IsActive = 0) and "MAX(DayOpenDayCloseInfo.DayOPenCloseId) " for each center_id

Guirec 24-Jul-13 5:45am

Yes you first problem is fixed... that was for sure.
Now as I told your query is pretty messy and I am not surprised it is not bringing the result you expect.

If you need some help regarding this then you'd better describe your tables and the expected output...

Statements like : "give a result set and a single row for each center_id" does not mean much. In SQL a select always return a result set and nothing else. a result set period.

I suggest you open a second question.

GDdixit 24-Jul-13 6:04am

ok .. i am opening a new question now

Samresh.ss · Answer 3 · 2013-07-23T23:24:00

Solution 3

Put a column name alias for your column of 'MAX(DayOpenDayCloseInfo.DayOPenCloseId)'

Posted 23-Jul-13 23:24pm

Samresh.ss

Sharathkumar k.r · Answer 4 · 2013-07-23T23:27:00

ALTER PROCEDURE [dbo].[sp_GatherCenterInfoForSpecialpaymentsTypes]
(
@company_u_id int,
@UnitId int
)
AS
BEGIN
SET NOCOUNT ON;

SELECT t1.Center_Name, t1.Center_Code, t1.Center_Id, t1.Center_Id_Format
FROM DayOpenDayCloseInfo
INNER JOIN (
(SELECT CenterInfo.Center_Name, CenterInfo.Center_Code,
CenterInfo.Center_Id,CenterInfo.Center_Id_Format,
MAX(DayOpenDayCloseInfo.DayOPenCloseId) AS DayOPenCloseId
FROM CenterInfo
INNER JOIN DayOpenDayCloseInfo
ON CenterInfo.Center_Id = DayOpenDayCloseInfo.Center_Id
WHERE (CenterInfo.Unit_Id = @UnitId) AND (CenterInfo.IsActive = 1)
AND (CenterInfo.company_u_id=@company_u_id)
GROUP BY CenterInfo.Center_Name, CenterInfo.Center_Code,
CenterInfo.Center_Id, CenterInfo.Center_Id_Format)
) t1
ON t1.Center_Id = DayOpenDayCloseInfo.Center_Id
WHERE DayOpenDayCloseInfo.IsActive = 0
END
RETURN

Member 8488336 · Answer 5 · 2013-07-25T02:17:00

Solution 5

provide alias to MAX(DayOpenDayCloseInfo.DayOPenCloseId) in sub query

example

MAX(DayOpenDayCloseInfo.DayOPenCloseId) AS DayOPenCloseId

Posted 25-Jul-13 2:17am

Member 8488336