join or subselect: urgent

Showing posts with label urgent. Show all posts

Wednesday, March 21, 2012

Joining two datasets in a report

Good afternoon,
This is actually quite urgent and I'd really appreciate any helpful
responses.
Is it possible to join two datasets into a single report based on a specific
column in each dataset? That is, if I have dataset 'dspatient' and dataset
'dsvisits', I'd like to join the two separate datasets on patient_id which
is present in each dataset and display the results.
How might I accomplish this? I should also mention these datasets are
created by two different datasources.
Any help would be greatly appreciated.
Thank you,
JayThis is a multi-part message in MIME format.
--=_NextPart_000_003E_01C53E7B.9989C690
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit
Jay,
Do both of the datasets live on the same server but just different
databases? I'm assuming that since you are using SQL Server 2000?
Mark
--Original Message--
From: Jason [mailto:jason@.grossmans.net]
Posted At: Friday, April 08, 2005 6:15 PM
Posted To: microsoft.public.sqlserver.reportingsvcs
Conversation: Joining two datasets in a report
Subject: Joining two datasets in a report
Good afternoon,
This is actually quite urgent and I'd really appreciate any helpful
responses.
Is it possible to join two datasets into a single report based on a
specific column in each dataset? That is, if I have dataset 'dspatient'
and dataset 'dsvisits', I'd like to join the two separate datasets on
patient_id which is present in each dataset and display the results.
How might I accomplish this? I should also mention these datasets are
created by two different datasources.
Any help would be greatly appreciated.
Thank you,
Jay
--=_NextPart_000_003E_01C53E7B.9989C690
Content-Type: text/html;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
&

Dataset clarification
Jay,
Do both of the datasets live on the same server but =just different databases? I'm assuming that since you are using =SQL Server 2000?
Mark
--Original Message--
From: Jason [mailto:jason@.grossmans.net] =Posted At: Friday, April 08, 2005 6:15 PM
Posted To: =microsoft.public.sqlserver.reportingsvcs
Conversation: Joining two datasets in a report
Subject: Joining two datasets in a report
Good afternoon,
This is actually quite urgent and I'd really =appreciate any helpful responses.
Is it possible to join two datasets into a single =report based on a specific column in each dataset? That is, if I =have dataset 'dspatient' and dataset 'dsvisits', I'd like to join the =two separate datasets on patient_id which is present in each dataset and =display the results.
How might I accomplish this? I should also =mention these datasets are created by two different datasources.
Any help would be greatly appreciated.
Thank you,
Jay

--=_NextPart_000_003E_01C53E7B.9989C690--|||This is a multi-part message in MIME format.
--=_NextPart_000_002E_01C53E77.5A06E9C0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Dataset clarificationOne database is on sqlserver 2000 and the other is =on a vms/vax box, but both datasets are currently in one report (I'm not =sure how I'd store a dataset in a database directly). So, basically I =can get two separate datasets into my report, but cannot make a join on =these datasets for the report.
Thanks!
"Mark Monica" <mmonica@.stf.com> wrote in message =news:%231Wvh0pPFHA.2932@.TK2MSFTNGP09.phx.gbl...
Jay,
Do both of the datasets live on the same server but just different =databases? I'm assuming that since you are using SQL Server 2000?
Mark
--Original Message-- From: Jason [mailto:jason@.grossmans.net] Posted At: Friday, April 08, 2005 6:15 PM Posted To: microsoft.public.sqlserver.reportingsvcs Conversation: Joining two datasets in a report Subject: Joining two datasets in a report
Good afternoon,
This is actually quite urgent and I'd really appreciate any helpful =responses.
Is it possible to join two datasets into a single report based on a =specific column in each dataset? That is, if I have dataset 'dspatient' =and dataset 'dsvisits', I'd like to join the two separate datasets on =patient_id which is present in each dataset and display the results.
How might I accomplish this? I should also mention these datasets are =created by two different datasources.
Any help would be greatly appreciated.
Thank you,
Jay --=_NextPart_000_002E_01C53E77.5A06E9C0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Dataset clarification
One database is on sqlserver 2000 and =the other is on a vms/vax box, but both datasets are currently in one report (I'm not =sure how I'd store a dataset in a database directly). So, basically I =can get two separate datasets into my report, but cannot make a join on these =datasets for the report.
Thanks!
"Mark Monica" wrote in =message news:%231Wvh0pPFHA.=2932@.TK2MSFTNGP09.phx.gbl...
Jay,
Do both of the datasets live on the same server but =just different databases? I'm assuming that since you are using SQL =Server 2000?
Mark
--Original Message-- From: Jason [mailto:jason@.grossmans.net] Posted At: Friday, April 08, 2005 6:15 =PM Posted To: =microsoft.public.sqlserver.reportingsvcs Conversation: Joining two datasets in a =report Subject: Joining two datasets in a report = Good afternoon,
This is actually quite urgent and I'd really =appreciate any helpful responses.
Is it possible to join two datasets into a single =report based on a specific column in each dataset? That is, if I have dataset = 'dspatient' and dataset 'dsvisits', I'd like to join the two separate =datasets on patient_id which is present in each dataset and display the results.
How might I accomplish this? I should also =mention these datasets are created by two different datasources.
Any help would be greatly appreciated.
Thank you,
Jay

--=_NextPart_000_002E_01C53E77.5A06E9C0--|||This is a multi-part message in MIME format.
--=_NextPart_000_0071_01C53E82.4266EF80
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Dataset clarificationYou cannot joing two datasets together. You have =two choices, do this in a stored procdure using linked databases (be =careful or you can really have performance problems. You should use =openquery and not 4 part naming if using linked databases). Or you can =use subreports.
-- Bruce Loehle-Conger MVP SQL Server Reporting Services
"Jason" <jason@.grossmans.net> wrote in message =news:eOJtlmqPFHA.2132@.TK2MSFTNGP14.phx.gbl...
One database is on sqlserver 2000 and the other is on a vms/vax box, =but both datasets are currently in one report (I'm not sure how I'd =store a dataset in a database directly). So, basically I can get two =separate datasets into my report, but cannot make a join on these =datasets for the report.
Thanks!
"Mark Monica" <mmonica@.stf.com> wrote in message =news:%231Wvh0pPFHA.2932@.TK2MSFTNGP09.phx.gbl...
Jay,
Do both of the datasets live on the same server but just different =databases? I'm assuming that since you are using SQL Server 2000?
Mark
--Original Message-- From: Jason [mailto:jason@.grossmans.net] Posted At: Friday, April 08, 2005 6:15 PM Posted To: microsoft.public.sqlserver.reportingsvcs Conversation: Joining two datasets in a report Subject: Joining two datasets in a report
Good afternoon,
This is actually quite urgent and I'd really appreciate any helpful =responses.
Is it possible to join two datasets into a single report based on a =specific column in each dataset? That is, if I have dataset 'dspatient' =and dataset 'dsvisits', I'd like to join the two separate datasets on =patient_id which is present in each dataset and display the results.
How might I accomplish this? I should also mention these datasets =are created by two different datasources.
Any help would be greatly appreciated.
Thank you,
Jay --=_NextPart_000_0071_01C53E82.4266EF80
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Dataset clarification
You cannot joing two datasets together. =You have two choices, do this in a stored procdure using linked databases (be =careful or you can really have performance problems. You should use openquery and =not 4 part naming if using linked databases). Or you can use =subreports.
-- Bruce Loehle-Conger MVP =SQL Server Reporting Services
"Jason" wrote =in message news:eOJtlmqPFHA.2132=@.TK2MSFTNGP14.phx.gbl...
One database is on sqlserver 2000 and =the other is on a vms/vax box, but both datasets are currently in one report =(I'm not sure how I'd store a dataset in a database directly). So, =basically I can get two separate datasets into my report, but cannot make a join =on these datasets for the report.

Thanks!

"Mark Monica" wrote in =message news:%231Wvh0pPFHA.=2932@.TK2MSFTNGP09.phx.gbl...
Jay,
Do both of the datasets live on the same server =but just different databases? I'm assuming that since you are using SQL =Server 2000?
Mark
--Original Message-- From: Jason [mailto:jason@.grossmans.net] Posted At: Friday, April 08, 2005 6:15 =PM Posted To: =microsoft.public.sqlserver.reportingsvcs Conversation: Joining two datasets in a =report Subject: Joining two datasets in a report = Good afternoon,
This is actually quite urgent and I'd really =appreciate any helpful responses.
Is it possible to join two datasets into a single =report based on a specific column in each dataset? That is, if I have =dataset 'dspatient' and dataset 'dsvisits', I'd like to join the two =separate datasets on patient_id which is present in each dataset and display =the results.
How might I accomplish this? I should also =mention these datasets are created by two different datasources.
Any help would be greatly appreciated.
Thank you,
Jay =

--=_NextPart_000_0071_01C53E82.4266EF80--|||This is a multi-part message in MIME format.
--=_NextPart_000_0067_01C53E7C.A4688FF0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Dataset clarificationAlright, thanks. I'll work on the subreport option =hopefully.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message =news:eDZiswqPFHA.3144@.tk2msftngp13.phx.gbl...
You cannot joing two datasets together. You have two choices, do this =in a stored procdure using linked databases (be careful or you can =really have performance problems. You should use openquery and not 4 =part naming if using linked databases). Or you can use subreports.
-- Bruce Loehle-Conger MVP SQL Server Reporting Services
"Jason" <jason@.grossmans.net> wrote in message =news:eOJtlmqPFHA.2132@.TK2MSFTNGP14.phx.gbl...
One database is on sqlserver 2000 and the other is on a vms/vax box, =but both datasets are currently in one report (I'm not sure how I'd =store a dataset in a database directly). So, basically I can get two =separate datasets into my report, but cannot make a join on these =datasets for the report.
Thanks!
"Mark Monica" <mmonica@.stf.com> wrote in message =news:%231Wvh0pPFHA.2932@.TK2MSFTNGP09.phx.gbl...
Jay,
Do both of the datasets live on the same server but just different =databases? I'm assuming that since you are using SQL Server 2000?
Mark
--Original Message-- From: Jason [mailto:jason@.grossmans.net] Posted At: Friday, April 08, 2005 6:15 PM Posted To: microsoft.public.sqlserver.reportingsvcs Conversation: Joining two datasets in a report Subject: Joining two datasets in a report
Good afternoon,
This is actually quite urgent and I'd really appreciate any =helpful responses.
Is it possible to join two datasets into a single report based on =a specific column in each dataset? That is, if I have dataset ='dspatient' and dataset 'dsvisits', I'd like to join the two separate =datasets on patient_id which is present in each dataset and display the =results.
How might I accomplish this? I should also mention these datasets =are created by two different datasources.
Any help would be greatly appreciated.
Thank you,
Jay --=_NextPart_000_0067_01C53E7C.A4688FF0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Dataset clarification
Alright, thanks. I'll work on the =subreport option hopefully.
"Bruce L-C [MVP]" wrote in message news:eDZiswqPFHA.3144=@.tk2msftngp13.phx.gbl...
You cannot joing two datasets =together. You have two choices, do this in a stored procdure using linked databases (be =careful or you can really have performance problems. You should use openquery =and not 4 part naming if using linked databases). Or you can use subreports.

-- Bruce Loehle-Conger =MVP SQL Server Reporting Services

"Jason" =wrote in message news:eOJtlmqPFHA.2132=@.TK2MSFTNGP14.phx.gbl...
One database is on sqlserver 2000 =and the other is on a vms/vax box, but both datasets are currently in one report =(I'm not sure how I'd store a dataset in a database directly). So, =basically I can get two separate datasets into my report, but cannot make a join =on these datasets for the report.

Thanks!

"Mark Monica" wrote in =message news:%231Wvh0pPFHA.=2932@.TK2MSFTNGP09.phx.gbl...
Jay,
Do both of the datasets live on the same server =but just different databases? I'm assuming that since you are using =SQL Server 2000?
Mark
--Original Message-- From: Jason [mailto:jason@.grossmans.net] Posted At: Friday, April 08, 2005 6:15 =PM Posted To: microsoft.public.sqlserver.reportingsvcs Conversation: Joining two datasets in a report =Subject: Joining two datasets in a report
Good afternoon,
This is actually quite urgent and I'd really =appreciate any helpful responses.
Is it possible to join two datasets into a =single report based on a specific column in each dataset? That is, if I =have dataset 'dspatient' and dataset 'dsvisits', I'd like to join the =two separate datasets on patient_id which is present in each dataset =and display the results.
How might I accomplish this? I should also =mention these datasets are created by two different datasources. = Any help would be greatly appreciated. = Thank you,
Jay

--=_NextPart_000_0067_01C53E7C.A4688FF0--sql

Monday, March 19, 2012

Joining on and Grouping by CASE function column alias (URGENT)

I REALLY need to perform a JOIN and a GROUP BY on a CASE function column alias, but I'm receiving an "Invalid column name" error when attempting to run the query. Here's a snippet:

SELECT NewColumn=
 CASE
 WHEN Table1.Name LIKE '%FOO%' THEN 'FOO TOO'
 END,
 Table2.SelectCol2
FROM Table1
JOIN Table2 ON NewColumn = Table2.ColumnName
GROUP BY NewColumn, Table2.SelectCol2
ORDER BY Table2.SelectCol2

I really appreciate any help anyone can provide.

Thanks,
DC RossYou could do it as a sub query

Select NewColumn from (Select case....) MySub group by MySub.NewColumn...etc, etc|||Not tested, but you should be able to do it like this:


SELECT NewColumn=
 CASE WHEN Table1.Name LIKE '%FOO%' THEN 'FOO TOO' END,
 Table2.SelectCol2
FROM Table1
JOIN Table2 ON CASE WHEN Table1.Name LIKE '%FOO%' THEN 'FOO TOO' END = Table2.ColumnName
GROUP BY NewColumn, Table2.SelectCol2
ORDER BY Table2.SelectCol2

I'm sure there's some rule, but I've never figured out when SQL lets you use an alias and when it doesn't. But, in this case, it apparently doesn't, so just use the CASE statement and you should be all set.|||I didn't know you could use a CASE in the JOIN syntax? Does it work?|||Works for me|||Cool I'll have to remember that one, top tip.

Friday, March 9, 2012

Join two table from different database. URGENT!

Hi,

anyone could help me on how to do the inner join of two table from two different database. Below is my scripts:

Dim sqlconnection1 As New SqlConnection(ConfigurationSettings.AppSettings("appDSN"))

Dim sqlCommand2 As New SqlCommand("", SqlConnection1)

Dim sqlconnection2 As New SqlConnection(ConfigurationSettings.AppSettings("smDSN"))

Dim sqlCommandSM2 as new sqlCommand("", sqlconnectionSM2)

Sub Page_Load(Source as Object, E as EventArgs)

sqlCommand2.CommandText = "Select * from invitation inner join Guest on invitation.guestid = Guest.Guestidinner join Department on Department.departmentcode = Guest.deptCode where eventcode = '" & ecode & "'"

sqlconnection1.Open()

.....................

The invitation table is from SqlConnection1 and the Department table is from the sqlconnection2. How to do this . Pls .. pls help me.

Ann123

In SQL Server, you can refer to tables in a different database inside the SQL using the appropriate syntax.

DatabaseName.Owner.TableName

For instance:

MyOtherDatabase.dbo.Guest

This presumes that the user specified has rights to both the default database in the connection as well as the second database.

|||

thanks so much douglas for your reply.

I know what you mean. In fact, i've done that in asp and its working well. However, i don't know how to do the syntax inasp.NET since i have to do 2 different connection and join them together in select statement using the inner join.

I ever try below syntax, but still got error.

Dim sqlconnection1 As New SqlConnection(ConfigurationSettings.AppSettings("appDSN"))

Dim sqlCommand2 As New SqlCommand("", SqlConnection1)

Dim sqlconnectionSM2 As New SqlConnection(ConfigurationSettings.AppSettings("smDSN"))

Dim sqlCommandSM2 as new sqlCommand("", sqlconnectionSM2)

sqlCommand2.CommandText = "Select * from invitation inner join Guest on invitation.guestid = Guest.Guestid inner joinsqlCommandSM2.SM2 .dbo.Department on Department.departmentcode = Guest.deptCode where eventcode = '" & ecode & "'"

TheSM2 is my Database andDepartment is refer to the table in SM2 Db.

Anybody know how to do the correct syntax??

thnks

Ann123

|||

Inside your SQL statement, you cannot reference you sqlCommandSM2, because that is a VB.NET variable, and the SQL Server getting the command will not know what that is. A simplified SQL statement using two databases is (presuming SM2 is the name of your other database):

sqlCommand2.CommandText = "Select * from invitation inner join Guest on invitation.guestid = Guest.Guestid inner joinSM2.dbo.Department onSM2.dbo.Department.departmentcode = Guest.deptCode where eventcode = '" & ecode & "'"

|||

hi douglas.. thanks once again for your reply.

i've done that but the problem now is,how to bindthe 2 sqlcommand when it executed since it connected to different sqlconnection and sqlcommand.

sqlCommand2.CommandText = "Select * from invitation inner join Guest on invitation.guestid = Guest.Guestid inner joinSM2.dbo.DepartmentonSM2.dbo.Department.departmentcode = Guest.deptCode where eventcode = '" & ecode & "'"

sqlconnection1.Open()

Dim employee2 As SqlDataReader =sqlCommand2.ExecuteReader()

.....

The sqlCommand2 is refer to sqlconnection1 whereas the second sqlCommandSM is refer to sqlconnection2 (connection for the SM2).

i've tried to do like this (below), so that it execute the same connection concurrently but got error.

Dim employee2 As SqlDataReader =sqlCommand2.ExecuteReader()

Dim employee2 As SqlDataReader =sqlCommandSM2.ExecuteReader()

How should i do the syntax ?

thanks.

Ann123

Friday, February 24, 2012

jOIN Query help(urgent)

i want to create a join query (for view) that will show one data per day for each agent.
just a select query joing these two tables..Seems like the date field is given me problem
i want result like below

TOTALCALL , TOTALESCA , AGENTID , DATE
50 , 5 , IDME1 , 10/28/2004 12:28:00 PM

TOTALESCA shows NuMbers of escalated calls out of totalcalls

table 1

TOTALCALL
AGENTID
DATE

TABLE 2

TOTALESCA
AGENTID
DATE

SAMPLE DATA ON TABLE 1

TOTALCALL, AGENTID, DATE
50 , IDME1 , 10/28/2004 12:28:00 PM

SAMPLE DATA ON TABLE 2

TOTALESCA, AGENTID , DATE
5 , IDME1 , 10/28/2004 12:28:00 PMTry datepart function to ignore the time part in your query and try it. If you still have issue, publish the query you have and I could help|||gives me inaccurate results and date column on select shows wrong data's.

possible unique data is agent id and date (wihout time messed), if i join with agent id then it will filter out table 2
Note.. There's some days agent wont escalate anycall and so therefore no record on table2, but has record on table1.
i want a query that will still show 0 on TOTALESCA column even if agent didnt escalate any call on that day.
Begining to think this is not possible with query
any idea?
eg below

TOTALCALL , TOTALESCA , AGENTID , DATE
30, 0 , IDME1 , 10/28/2004

SELECT table1.TOTALCALL, table2.TOTALESCA,
table2.[agent Id],
datepart(day,table1.Date)
FROM dbo.totalcall table1,
dbo.totalEsc table2
WHERE datepart(day,table1.Date)=datepart(day,table2.Date )|||I'd suggest:SELECT Coalesce(a.AGENTID, b.AGENTID)
, Convert(CHAR(10), Coalesce(a.[DATE], b.[DATE]), 121)
, Sum(TOTALCALL) AS DAY_CALLS
, Sum(TOTALESCA) AS DAY_ESCA
FROM table1 AS a
FULL JOIN table2 AS b
ON (a.AGENTID = b.AGENTID
AND Convert(CHAR(10), a.[DATE], 121) = Convert(CHAR(10), b.[DATE], 121))
GROUP BY Coalesce(a.AGENTID, b.AGENTID)
, Convert(CHAR(10), Coalesce(a.[DATE], b.[DATE]), 121)-PatP|||pat your query work like charm

but i get this message too
Null value is eliminated by an aggregate or other SET operation.

what does that means?|||It means that you've got rows in one table that aren't matched in the other... Either somebody had no calls escalated (which I'd expected) or they escalated calls that they never got (which would worry me). As there is a perfectly reasonable explanation, I wouldn't get worked up about the message.

-PatP|||It means that you've got rows in one table that aren't matched in the other... Either somebody had no calls escalated (which I'd expected) or they escalated calls that they never got (which would worry me). As there is a perfectly reasonable explanation, I wouldn't get worked up about the message.

-PatP|||pat check your pm|||From the PM, I got:hey pat,
can you help me join this query with another table? the query i got from you yesterday.
there's one more table that has agent name, agent id, supervisor and manager. agent id is unique. i want the same result on this query but now to show agent name, agent id, supervisor and manager. let's say this is table3 and has this columns

agent_ID NVARCHAR(20),
SUP_LAST NVARCHAR(25),
SUP_FRST NVARCHAR(25),
MGR_LAST NVARCHAR(255),
MGR_FRST NVARCHAR(255)

i will love to concenate like
EMP_FRST+' '+EMP_LAST) AS Agent,
(SUP_FRST+' '+SUP_LAST) AS Supervisor,
(MGR_FRST+' '+MGR_LAST) AS ManagerThere was also some informaiton that pointed back into this thread too. Moving on, I'd suggest:SELECT Coalesce(a.AGENTID, b.AGENTID)
, Convert(CHAR(10), Coalesce(a.[DATE], b.[DATE]), 121)
, Sum(TOTALCALL) AS DAY_CALLS
, Sum(TOTALESCA) AS DAY_ESCA
, EMP_FRST + ' ' + EMP_LAST AS Agent
, SUP_FRST + ' ' + SUP_LAST AS Supervisor
, MGR_FRST + ' ' + MGR_LAST AS Manager
FROM table1 AS a
FULL JOIN table2 AS b
ON (a.AGENTID = b.AGENTID
AND Convert(CHAR(10), a.[DATE], 121) = Convert(CHAR(10), b.[DATE], 121))
LEFT JOIN table3 AS c
ON (c.AGENTID = Coalesce(a.AGENTID, b.AGENTID))
GROUP BY Coalesce(a.AGENTID, b.AGENTID)
, Convert(CHAR(10), Coalesce(a.[DATE], b.[DATE]), 121)-PatP|||To eliminate the error do either:

set ansi_warnings off

or

, Sum(isnull(TOTALCALL, 0)) AS DAY_CALLS
, Sum(isnull(TOTALESCA, 0)) AS DAY_ESCA|||thanks
i had this error
Server: Msg 8120, Level 16, State 1, Line 1
Column 'c.EMP_FRST' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'c.EMP_LAST' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'c.SUP_FRST' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'c.SUP_LAST' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'c.MGR_FRST' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'c.MGR_LAST' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

but i fixed it when i added all column to groupby
rdjabarov
set ansi_warnings off works but i want to use the query to create view.
where should i put it
im getting error when i try

ALTER VIEW myview
as
set ansi_warnings off
SELECT...

or

ALTER VIEW myview
set ansi_warnings off
as

SELECT ...|||I would recommend that you avoid changing settings to suppress messages. That has always been a receipe for disaster for me. You could also suppress them using:SELECT Coalesce(a.AGENTID, b.AGENTID)
, Convert(CHAR(10), Coalesce(a.[DATE], b.[DATE]), 121)
, Sum(Coalesce(TOTALCALL, 0)) AS DAY_CALLS
, Sum(Coalesce(TOTALESCA, 0)) AS DAY_ESCA
, EMP_FRST + ' ' + EMP_LAST AS Agent
, SUP_FRST + ' ' + SUP_LAST AS Supervisor
, MGR_FRST + ' ' + MGR_LAST AS Manager
FROM table1 AS a
FULL JOIN table2 AS b
ON (a.AGENTID = b.AGENTID
AND Convert(CHAR(10), a.[DATE], 121) = Convert(CHAR(10), b.[DATE], 121))
LEFT JOIN table3 AS c
ON (c.AGENTID = Coalesce(a.AGENTID, b.AGENTID))
GROUP BY Coalesce(a.AGENTID, b.AGENTID)
, Convert(CHAR(10), Coalesce(a.[DATE], b.[DATE]), 121)
, EMP_FRST, EMP_LAST, SUP_FRST, SUP_LAST, MGR_FRST, MGR_LAST-PatP|||thanks pat
that works without error|||The setting needs to be set on the connection that is used to create an object, so it needs to preceed the CREATE statement.

Reciepe for disaster? For that matter any T-SQL statement can be viewed as a potential receipe! In addition, relying on default settings is a receipe for disaster in itself!|||Reciepe for disaster? For that matter any T-SQL statement can be viewed as a potential receipe! In addition, relying on default settings is a receipe for disaster in itself!True, but what I meant was that changing settings (of any kind, any where) to make warning messages go away has always proved to be a disaster for me. I didn't mean that you ought to rely on default settings, I meant that changing settings to suppress messages was a receipe for disaster.

I always try find the underlying source of the problem, and correct it or code to ignore the meassage instead of finding ways to suppress the message.

-PatP|||That's why I gave 2 options, SET and ISNULL (which you changed to Coalesce).

Wednesday, March 21, 2012

Joining two datasets in a report

Monday, March 19, 2012

Joining on and Grouping by CASE function column alias (URGENT)

Friday, March 9, 2012

Join two table from different database. URGENT!

Friday, February 24, 2012

jOIN Query help(urgent)

join or subselect

Blog Archive

About Me