SQL 2012 :: Group By Expression - Outer Reference
Sep 2, 2014When attempting to pull this query I get this error: "Each GROUP BY expression must contain at least one column that is not an outer reference."
View 1 Replies
ADVERTISEMENT
SQL 2012 :: DTSX Giving Errors - Object Reference Not Set To Instance Reference
Sep 10, 2014I am using vs 2010 to write my dtsx import scripts.I use a script component as a source to create a flat file destination file.Everything have been working fine,but then my development machine crashed and we have to install everything again.Now when i use the execute package utility to test my scripts i get the following error:
Error system.NullReferenceException: Object refrence not set to an instance reference.
In PreExecute section
TextReader = new system.io.streamreader(" file name")
In the CreateNewOutputRows:
dim nextLine as string
nextLine = textReader.ReadLine
[code]...
is there something which i did not install or what can be the error?
Case Expression Plus Outer Join In Ole Db Source
Oct 26, 2006I'm trying to generate the data for a 2-column table, where both columns are defined as NOT NULL and the second column is a uniqueidentifier.
In SQL Server Management Studio, this works fine:insert into table_3(column_a, column_b)
select table_1.column_a, (case when table_2.column_b is NULL then newid() else table_2.column_b end) as column_b
from table_1 left outer join table_2 on table_1.column_c = table_2.column_c
That is, column_b of the SELECT result has no NULL values, and all 35,986 rows are successfully inserted into a previously empty table_3. (If I comment out the INSERT INTO clause and project table_2.column_b instead of "(case ... end) as column_b", the SELECT result includes 380 rows with a NULL in column_b, so I know the case expression plus the outer join are working as expected.)
But when I use the SELECT query as the SQL command in an OLE DB Source component that is connected directly to the OLE DB Destination for the result table, I get this error:There was an error with input column "column_b" (445) on input "OLE DB Destination Input" (420
The column status returned was: "The value violated the integrity constraints for the column.".
And sure enough, when I modify the result table to allow NULL in column_b, truncate it, and re-run the data flow, it inserts the exact same 380 rows with a NULL in column_b among the 35,986 rows.
So what is SSIS doing to screw up the results of the SELECT command???
Right Outer Join For Group
Dec 12, 2007
Hi,
In my database there are following two tables with sample data:
Customer_Product
=================
Branch Customer ProductID
B1 C1 P1
B1 C1 P2
B1 C2 P1
B2 C3 P2
B2 C3 P3
Product
=================
ID Name
P1 Prod 1
P2 Prod 2
P3 Prod 3
I need to write a query which can display the sum of each product for each branch. The required output is as follow:
Required Output
================
Branch Product Total
B1 P1 2
B1 P2 1
B1 P3 0
B2 P1 0
B2 P2 1
B2 P3 1
The Query I tried is:
SELECT Branch, ProductID, COUNT(*) as Total
FROM Customer_Product cp
RIGHT OUTER JOIN Product pd ON cp.ProductID=pd.id
GROUP BY Branch, ProductID
ORDER BY Branch, ProductID
But Right outer join act on Table level instead of Group. How can i generate desired output by keeping in mind that both Branch and Product are dynamic?
Thanks.
Repeate Outer Group In Matrix
Jan 19, 2007Hi,
I am trying to make this report and I am using matrix in it. Currently it merge my outer group value. Is there any ways that I can use Matrix and have my outer group values REPEATED and not merged. Any help will be appreciated.
Thanks,
-Rohit
Group By After Outer Join - Getting Duplicate Or Null Values
Oct 18, 2013I've got 2 tables of towns. I'm using outer join because i need all the town from both tables. However I'm sometimes getting duplicates.
My query
select a.town, b.town
from a
outer join b on a.town = b.town
group by a.town, b.town
How to stop getting null values?
portsmouth null
portsmouth portsmouth
southampton southampton
null southampton
TownA null
null TownB
I'm looking for distinct values like this:
portsmouth portsmouth
southampton southampton
TownA null
null TownB
etc...
T-SQL (SS2K8) :: Outer Join Add Non Matching Rows To Each Order Group?
Mar 30, 2015In Outer join, I would like to add the outer columns that don't exist in the right table for each order number. So currently the columns that don't exist in the right table only appear once for the entire set. How can I go about adding PCity, PState to each order group, so that PCity and PState would be added as null rows to each group of orders?
if OBJECT_ID('tempdb..#left_table') is not null
drop table #left_table;
if OBJECT_ID('tempdb..#right_table') is not null
drop table #right_table;
create table #left_table
[Code]....
SQL Server 2012 :: Obtaining A Comma Delimited List For Each Group In The Output Of A Group By Query?
Jan 10, 2014I'd like to ask how you would get the OUTPUT below from the TABLE below:
TABLE:
id category
1 A
2 C
3 A
4 A
5 B
6 C
7 B
OUTPUT:
category count id's
A 3 1,3,4
B 2 5,7
C 2 2,6
The code would go something like:
Select category, count(*), .... as id's
from TABLE
group by category
I just need to find that .... part.
Group Expression Does Not Work
Dec 10, 2007HI,
I tried to run the following query.
Code Block
WITH YesterdayCTE AS
(
SELECT type = 'Members Joined Yesterday'
, Borrowers = (select count(*)
from LoanApplication INNER JOIN
Member ON LoanApplication.MemberFK = Member.Id AND LoanApplication.Id = Member.LastLoanApplicationFK INNER JOIN
CreditUnion ON Member.CreditUnionFK = CreditUnion.Id
where (LoanApplication.SubmittedOn >= GETDATE()-1) AND (Member.CuStatus = 'Approved')
GROUP BY CreditUnion.Name )
, Depositors = (select count(*)
from CDOrder INNER JOIN
Member ON CDOrder.MemberFK = Member.Id AND CDOrder.Id = Member.LastCDOrderFK INNER JOIN
CreditUnion ON Member.CreditUnionFK = CreditUnion.Id
where (CDOrder.SubmittedOn >= GETDATE()-1) AND (Member.CuStatus = 'Approved')
GROUP BY CreditUnion.Name )
),
MonthlyCTE AS
(
SELECT type = 'Members Joined Last Month'
, Borrowers = (select count(*)
from LoanApplication INNER JOIN
Member ON LoanApplication.MemberFK = Member.Id AND LoanApplication.Id = Member.LastLoanApplicationFK INNER JOIN
CreditUnion ON Member.CreditUnionFK = CreditUnion.Id
where (LoanApplication.SubmittedOn >= GETDATE()-30) AND (Member.CuStatus = 'Approved')
GROUP BY CreditUnion.Name )
, Depositors = (select count(*)
from CDOrder INNER JOIN
Member ON CDOrder.MemberFK = Member.Id AND CDOrder.Id = Member.LastCDOrderFK INNER JOIN
CreditUnion ON Member.CreditUnionFK = CreditUnion.Id
where (CDOrder.SubmittedOn >= GETDATE()-30) AND (Member.CuStatus = 'Approved')
GROUP BY CreditUnion.Name )
),
YearlyCTE AS
(
SELECT type = 'Members Joined Last year'
, Borrowers = (select count(*)
from LoanApplication INNER JOIN
Member ON LoanApplication.MemberFK = Member.Id AND LoanApplication.Id = Member.LastLoanApplicationFK INNER JOIN
CreditUnion ON Member.CreditUnionFK = CreditUnion.Id
where (LoanApplication.SubmittedOn >= GETDATE()-360) AND (Member.CuStatus = 'Approved')
GROUP BY CreditUnion.Name )
, Depositors = (select count(*)
from CDOrder INNER JOIN
Member ON CDOrder.MemberFK = Member.Id AND CDOrder.Id = Member.LastCDOrderFK INNER JOIN
CreditUnion ON Member.CreditUnionFK = CreditUnion.Id
where (CDOrder.SubmittedOn >= GETDATE()-360) AND (Member.CuStatus = 'Approved')
GROUP BY CreditUnion.Name )
),
combinedCTE AS
(
SELECT * FROM YesterdayCTE
UNION ALL
SELECT * FROM MonthlyCTE
UNION ALL
SELECT * FROM YearlyCTE
)
SELECT *
, Members = Borrowers + Depositors
FROM combinedCTE
But I get the following error message.
An error occurred while reading data from the query result set.
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. (Microsoft Report Designer)
===================================
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. (.Net SqlClient Data Provider)
Please can anyone help me to make this work?
Thanks
GROUP BY Column-name Problem (need Expression)
Aug 11, 2005Please advise on how to get the GROUP BY coded in an acceptable way:DECLARE @LO INTDECLARE @HI INTDECLARE @StartDate varchar(10)DECLARE @EndDate varchar(10)SELECT @StartDate = '01/01/2005'SELECT @EndDate = '06/30/2005'SELECT @LO= 250SELECT @HI= 333SELECTStateCD, CountyCD, Zip, Z.CityName, Z.StateCode, Z.CountyName, 'Criteria' = 'JumboRange:' + Convert(varchar(4),@LO) + '-' +Convert(varchar(4),@HI), 'StartingDate' = @StartDate, 'ThruDate' = @EndDate, JumboAmount = SUM(JumboAmount), JumboMortgages = SUM(JumboMortgages), JumboFIXMortgages= SUM(JumboFIXMortgages), JumboFIXAmount = SUM(JumboFIXAmount), JumboARMMortgages = SUM(JumboARMMortgages), JumboARMAmount= SUM(JumboARMAmount)FROM LoanDetails T INNER JOIN dbo.ZipCodesPreferred ZON T.StateCD = Z.FIPS_State AND T.CountyCD = Z.FIPS_County AND T.Zip =Z.ZipCodeGROUP BYStateCD, CountyCD, Zip, Z.CityName, Z.StateCode, Z.CountyName, 'Criteria' = 'JumboRange:' + Convert(varchar(4),@LO) + '-' +Convert(varchar(4),@HI), 'StartingDate' = @StartDate, 'ThruDate' = @EndDate
View 2 Replies View RelatedUsing An Expression To Group On Multiple Fields
Mar 23, 2008
I have an exisitng report that lists unit name, provider, runday, shift, patient. The report groups by unit name and there is a page break after each unit name. So in the current environment the report prints one page per unit that contains all of the providers, rundays, shifts, and patients for that unit name its grouping on. So the report would like like this when it prints:
Unit A
Provider 1 Runday 1 Shift 1
patient 1
patient 2
Provider 1 Runday 1 Shift 2
patient 1
patient 2
Provider 1 Runday 2 Shift 1
patient 1
patient 2
Provider 1 Runday 2 Shift 2
patient 1
patient 2
Provider 2 Runday 1 Shift 1
patient 1
patient 2
Provider 2 Runday 1 Shift 2
patient 1
patient 2
Provider 2 Runday 2 Shift 1
patient 1
patient 2
Provider 2 Runday 2 Shift 2
patient 1
patient 2
PAGE BREAK
Unit B............(repeat data from page 1)
PAGE BREAK
Unit C............(repeat data from page 1
The end user would like the ability to keep the report as is but would like to also be able to print the report as one page per each unit name, provider, runday and shift. so it would look like this
Unit A
Provider 1 Runday 1 Shift 1
patient 1
patient 2
PAGE BREAK
Unit A
Provider 1 Runday 1 Shift 2
patient 1
patient 2
PAGE BREAK
Unit A
Provider 1 Runday 2 Shift 1
patient 1
patient 2
So I created a boolean parameter with a prompt of Page break by Unit, Provider, Runday & Shift? My thought is if the users sets this to False, the report will group on just Unit Name (the first example). If the user sets this to True, the report will group on Unit Name, Provider, Runday & Shift.
I set the grouping expression for this data table as:
=iif(Parameters!Grouping.Value = "False", Fields!unit_Name.Value,(Fields!unit_Name.Value,Fields!Lname.Value,Fields!Rundays.ValueFields!Shift.Value))
Within the expression editor window it displays a syntax error and the report will not run.
Any help would be greatly appreciated!!!!!!!!!
Group Expression That Calculate Percentages.
Mar 8, 2007I have an expression in a group that calculates percent of sales:
=iif(Sum(Fields!BOOKD.Value)=0,0,IIF(Sum(Fields!NET.Value)=0,0,((Sum(Fields!BOOKD.Value)-Sum(Fields!NET.Value))/Sum(Fields!BOOKD.Value))))*100
The problem I'm having is that if both the
Sum(Fields!BOOKD.Value)=0 and
Sum(Fields!NET.Value)=0 ,
the expression returns the message #ERROR instead of a 0.
I have the initial query set to display 0 if the value is null.
,SUM(ISNULL(R.BOOKD,0))BOOKD
,SUM(ISNULL(R.NET,0))NET
I would greatly appreciate any help on how to solve this issue.
Thank Barb
SQL 2012 :: SSRS Average Column Group Value For Row Group
Feb 28, 2014I'm having a fight with Reporting Services at the minute when trying to compute an average at the row group level for a value summed in a column group.I have the following column groups:
Year
Month
Date
And the following row groups:
Region
Product
SubType (hidden, data at the date level is summed to Product)
At the moment I'm computing the average for SubType for each Date at the Product level (giving a decimal value), so for each day I end up with a nice average, that works. However I am unable to average that average over the whole Year for a Product. The issue being that I'm trying to combine Row Groups (Product) and Column Groups (Date/Year)
SQL 2012 :: Fast Way To Do Group By Count On Items Within Group?
May 1, 2014select top 15 count(*) as cnt, state from table
group by state
order by cnt desc
[code[...
Can the above three queries be combined into one and still be fast, if so how?What i am trying to go is an item count, by group, similar to ones Inbox in Outlook.
SQL Server 2008 :: Using Expression In GROUP BY Clause
Feb 23, 2015I am working to move an application from MySQL to SQL Server. The person who developed the MySQL application has little database experience, and took some shortcuts that the lax nature of MySQL allows. One query with which I am struggling looks something like this:
SELECT StartTime + Offset, min(Sensor), max(Sensor)
FROM SensorData
WHERE SensorID = @SensorID AND
StartTime + Offset > @BeginTime AND
StartTime + Offset < @EndTime
GROUP BY (StartTime + Offset) / 100
ORDER BY StartTime + Offset
What we are trying to accomplish is to return minimum and maximum sensor values over a number of periods between the BeginTime and EndTime. When I run this query in MySQL on a sample dataset, it returns a small number of rows, with each one being the min/max values for a portion of the overall period.
Under MS SQL Server 2008, SP3, I get the two following error messages:
Column 'SensorData.StartTime' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Column 'SensorData.Offset' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
SQL 2012 :: Tuning SP (Left Outer Join)
Mar 21, 2014I am working on tuning of sp. They wrote several left outer join. Is there any option to avoid them.
View 3 Replies View RelatedSQL Server Reporting Services 2005 Group Expression
Mar 8, 2007I have an expression in a group that calculates a percentage of sales:
=iif(Sum(Fields!BOOKD.Value)=0,0,IIF(Sum(Fields!NET.Value)=0,0,((Sum(Fields!BOOKD.Value)-Sum(Fields!NET.Value))/Sum(Fields!BOOKD.Value))))*100
If there is a 0 balance for both the book dollar and the net dollar the expression returns #Error.
I have made sure that if the value is null it should return a 0 in the initial query.
,SUM(ISNULL(R.BOOKD,0)) BOOKD
,SUM(ISNULL(R.NET,0)) NET
Any suggestions would be greatly appreciated.
SQL Server 2012 :: Attempting To Replace Not In With Left Outer Join
Apr 15, 2015I'm attempting to improve a vendor created query.
Here is the original -
SELECT m.lID
FROM Message m
inner join Message_Cc mCC on m.lID=mCC.lMessage and mCC.lOfficeRecipient = 200321
INNER JOIN UserRole d on mCC.szRecipient=d.szRoleName
inner Join Map_UserAtOfficeToRole a2 on a2.lUserRole = d.lid AND d.nRecordStatus = 1
[Code] ....
If I run this without the LEFT OUTER JOIN and the is null statement I get 648 rows. But If I include it I get 0 rows. I can't understand why I get 0 rows with the outer join.
SQL Server 2012 :: Maximum Tenancy Reference
Jul 9, 2014I have the following script -
SELECTDISTINCTLOC.[place-ref] AS 'Place Ref'
,PLA.address1 AS 'Address1'
,PLA.address2 AS 'Address2'
,PLA.address3 AS 'Address3'
,PLA.address4 AS 'Address4'
,PLA.address5 AS 'Address5'
,PLA.[post-code] AS 'Postcode'
,LOC.[location-sts] AS 'Location Status'
[Code] ....
Above is a small example.
As you can see 13 Alamein Avenue is listed twice. This is because it pulls back the former and the current tenancy. I don't want to exclude former tenancies - so I want to pull back the most recent Tenancy Reference for each Place Reference.
ERROR [42000] [Lotus][ODBC Lotus Notes]Table Reference Has To Be A Table Name Or An Outer Join Escape Clause In A FROM Clause
May 27, 2008I am using web developer 2008, while connecting to I wanted to fetch data from Lotus notes database file, for this i used notesql connector, while connectiong to notes database i am fetting error
ERROR [42000] [Lotus][ODBC Lotus Notes]Table reference has to be a table name or an outer join escape clause in a FROM clause
I have already checked that database & table name are correct, please help me out
How i can fetch the lotus notes data in my asp.net pages.
ERROR [42000] [Lotus][ODBC Lotus Notes]Table Reference Has To Be A Table Name Or An Outer Join Escape Clause In A FROM Clause
May 27, 2008I am using web developer 2008, while connecting to I wanted to fetch data from Lotus notes database file, for this i used notesql connector, while connectiong to notes database i am fetting error
ERROR [42000] [Lotus][ODBC Lotus Notes]Table reference has to be a table name or an outer join escape clause in a FROM clause
I have already checked that database & table name are correct, please help me out
How i can fetch the lotus notes data in my asp.net pages.
SQL 2012 :: Unable To Add Service Reference In A CLR Database Project?
Apr 17, 2014I created a CLR procedure with a service reference in Visual Studio 2010 that is being used in SQL Server 2008 R2. We recently tried to deploy it to a 2012 SQL Server, but were unable to due to compatibility issues. When I tried to upgrade the package to Visual Studio 2013 I got this error message:
Unable to cast object of type 'Microsoft.VisualStudio.Data.Tools.Package.Project.DatabaseFileNode' to type 'Microsoft.VisualStudio.Data.Tools.Package.Project.Internal.FolderNode'
I tried to create this using the 2012 Data Tools, but was unable to add a service reference with the GUI. It appears Microsoft has removed this functionality. My company has a third party app that we have limited control over, which is why we need this functionality.
[URL]
SQL 2012 :: Suppress SSDT Database Reference Error
Aug 21, 2015In database projects, VS2015, I want to create some views for me database that will reference another database table using 3 part naming reference.
Works fine in SSMS but when I try and build me project I is throwing up a reference error.
I can't import the other database into this project, so is there a way to suppress the error? I don't really want to exclude these view from the project.
SQL 2012 :: DENY Permission On Column Subsequently Reference In View?
Aug 6, 2014Just encountered something that I wasn't expected, in that a user who has an explicit deny on a column in a table was able to select it when referenced through a view in a schema they have the SELECT permission on. This seems to me to go against the principle that DENY overrides everything when it comes to permissions? Is this how it's meant to work?
Code is below:-
--create test user
CREATE USER TestDenyOnViewUser WITHOUT LOGIN
GO
--create test schema (authorization dbo - same owner as dbo schema so ownership chaining will apply)
CREATE SCHEMA TestDenyOnView AUTHORIZATION dbo
[Code] ......
Transact SQL :: Difference Between Outer Apply And Outer Join
May 10, 2010what is difference between outer apply and outer join ,both return rows from left input as well as right input . isnt it?
View 3 Replies View RelatedSQL Server 2012 :: Variable Not Passing Through To Expression
May 22, 2014I have created a variable in a SQL Task and assigned it to a string variable. When I debug the container with a breakpoint, I can see the correct date value being assigned to the variable.
I have an ADO Net source setup to an Oracle connection. I need to pull the Oracle data down that has an updated date greater than the updated date in my ODS.
My issue is that the variable is not being passed through to my expression that I use for an ADO Net source.
"SELECT * FROM BI_EDW.GL_JE_HEADERS WHERE LAST_UPDATE_DATE > To_Date('" + (DT_WSTR, 19) @[User::varLastUpdateDate] + "','yyyy-mm-dd hh24:mi:ss')"
SQL 2012 :: Truncation Occurred During Evaluation Of The Expression
Apr 22, 2015I'm migration SSIS 2005 packages to 2012. It works fine when i run in 2005 but in 2012 it gives an error about truncate ?
(((DT_DATE)@ArchiveStartTime < (DT_DATE)@ArchiveEndTime) ? ((getdate() < (DT_DATE)((DT_WSTR,12)(DT_DATE)(DT_DBDATE)getdate() + " " + @ArchiveEndTime)) && ((DT_DBTIME)getdate() > (DT_DBTIME)@ArchiveStartTime)) : ((getdate() < (DT_DATE)((DT_WSTR,12)(DT_DATE)(DT_DBDATE)getdate() + " " + @ArchiveEndTime)) || ((DT_DBTIME)getdate() > (DT_DBTIME)@ArchiveStartTime))) && (@ChangeLogsRemoved > 0)
User variables:
ArchiveEndTime Sting 14:56
ArchiveStartTime Sting 11:00
SQL 2012 :: Regular Expression For Capital Characters In String
Mar 1, 2014I need to verify data in a column and do pattern matching on the string in each field.
I've create a CLR Function that will verify the element against the patter and return a True or Fales....
I have only used reg expressions once and am struggling mightly. I'm bacially here. A
I need to match a pattern that each word in the string will be a Capital letter.
ex. The beginning of the day - Fail
ex. The Beginning Of The Day - Pass
[URL] .....
SQL 2012 :: Master / Driver Package And Expression To Set Variable
May 5, 2015I have been using SSIS for a while now, originally in SQL 2008 but more lately SQL 2012.
I discovered the GETDATE() function in SSIS so I thought I would use this in a variable expression in a Master/Driver package with the child parameters mapped to this variable. A big mistake. The value is not persisted, it gets updated each time the variable is read, so it's back to setting the variable value using a script task in the Master/Driver package.
SQL 2012 :: Rename A File In SSIS (SSDT) Using Expression
Jul 23, 2015I have a file named abc-72015.txt.
I have a package that renames the file from abc.txt to abc-72015.txt
I want to do the reverse. I wan to rename it from abc-72015.txt to abc.txt.
I have a variable named myFileValue with a value of abc.txt.
Can I do this by setting the variable as an expressions and use a function to rename it as abc.txt?
What would the syntax be?
SQL 2012 :: Only One Expression Can Be Specified In Select List When Subquery Not Introduced With EXISTS
Apr 28, 2015I am getting error [[Msg 116, Level 16, State 1, Line 7 .Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.]] for the below script.
==============================================
Declare @mSql2 Nvarchar(max)
SET @mSql2= (select * from Tablename)
================================================
SQL Server 2012 :: Filtering Common Table Expression Within View
Sep 1, 2015I have a multi-tenant database where each row and each table has a 'TenantId' column. I have created a view which has joins on a CTE. The issue I'm having is that entity framework will do a SELECT * FROM MyView WHERE TenantId = 50 to limit the result set to the correct tenant. However it does not limit the CTE to the same TenantId so that result set is massive and makes my view extremely slow. In the included example you can see with the commented line what I need to filter on in the CTE but I am not sure how to get the sql plan executor to understand this or weather it's even possible.I have included a simplified view definition to demonstrate the issue...
ALTER VIEW MyView
AS
WITH ContactCTE AS(
SELECT Col1,
Col2,
TenantId
[code]....
Reporting Services :: SSRS Group Total Expression - Add Total Disabled
Oct 26, 2015For some reason my Add Total is grey out, when i tried to add grand total using some expression.
I have two row & two column groups?
Is there any alternative or how can i enable add total? using expression..as you can see in my Attached Image
I'm using iff condition in my expression..