GROUP BY Column-name Problem (need Expression)

Aug 11, 2005

Please advise on how to get the GROUP BY coded in an acceptable way:

DECLARE @LO INT
DECLARE @HI INT
DECLARE @StartDate varchar(10)
DECLARE @EndDate varchar(10)

SELECT @StartDate = '01/01/2005'
SELECT @EndDate = '06/30/2005'
SELECT @LO= 250
SELECT @HI= 333

SELECT
StateCD
, 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 Z
ON T.StateCD = Z.FIPS_State AND T.CountyCD = Z.FIPS_County AND T.Zip =
Z.ZipCode
GROUP BY
StateCD
, 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


ADVERTISEMENT

Reporting Services :: Display Group Name Value Of Each Group In Column Header Outside The Group?

Sep 29, 2015

I have an SSRS 2012 table report with groups; each group is broken ie. one group for one page, and there are multiple groups in multiple pages.

'GroupName' column has multiple values - X,Y,Z,......

I need to group 'GroupName' with X,Y,Z,..... ie value X in page 1,value Y in page 2, value Z in page 3...

Now, I need to display another column (ABC) in this table report (outside the group column 'GroupName'); this outside column itself is another column header (not a group header) in the table (report) and it derives its name partly from the 'GroupName'  values:

Example:

Value X for GroupName in page 1 will mean, in page 1, column Name of ABC column must be ABC-X Value Y for GroupName in page 2 will mean, in page 2, column Name of ABC column must be ABC-Y Value Z for GroupName in page 3 will mean, in page 3, column Name of
ABC column must be ABC-Z

ie the column name of ABC (Clm ABC)  must be dynamic as per the GroupName values (X,Y,Z....)

Page1:

GroupName                 Clm ABC-X

X

Page2:

GroupName                 Clm ABC-Y

Y

Page3:

GroupName                 Clm ABC-Z

Z

I have been able to use First(ReportItems!GroupName.Value) in the Page Header to get GroupNames displayed in each page; I get X in page 1, Y in page 2, Z in page 3.....

However, when I use ReportItems (that refers to a group name) in the Report Body outside the group,

I get the following error:

Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope

I need to get the X, Y, Z ... in each page for the column ABC.

I have been able to use this - First(Fields!GroupName.Value); however, I get ABC-X, ABC-X, ABC-X in each of the pages for the ABC column, instead of ABC-X in page 1, ABC-Y in page 2, ABC-Z in page 3, ...

View 4 Replies View Related

Group Expression Does Not Work

Dec 10, 2007

HI,

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

View 7 Replies View Related

Using 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!!!!!!!!!

View 7 Replies View Related

Group Expression That Calculate Percentages.

Mar 8, 2007

I 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

View 4 Replies View Related

SQL 2012 :: Group By Expression - Outer Reference

Sep 2, 2014

When 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 View Related

SQL Server 2008 :: Using Expression In GROUP BY Clause

Feb 23, 2015

I 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.

View 9 Replies View Related

SQL Server Reporting Services 2005 Group Expression

Mar 8, 2007

I 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.

View 1 Replies View Related

SQL 2012 :: SSRS Average Column Group Value For Row Group

Feb 28, 2014

I'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)

View 0 Replies View Related

Add Values To A Column With Derived Column Expression?

Feb 25, 2008

Hi, how are you?
I'm having a problem and I don't know if it can be solved with a derived column expression. This is the problem:


We are looking data in a a sql database.

We are writting the SQL result in a flat file.

We need to transform data in one of the columns.

For example: we can have 3 digits as value in a column but that column must be 10 digit length. So we have to complete all the missing digits with a zero. So, that column will have the original 3 digits and 7 zeros. How we can do that tranformation? We must do it from de the flat file or it can be a previous step?
Thanks for any help you can give me.
Regards,

Beli

View 10 Replies View Related

SQL Server 2012 :: Group Column Based On Another Column

Jul 11, 2014

I have Table Like this

t_id w_id t_codew_name
358553680A1100EVM Method Project
358563680A1110EVM Method Project
358453684A1000Basic
358463684A1010Basic
358473685A1020Detail

[Code] ....

View 1 Replies View Related

Column Visibility Expression

Nov 8, 2005

I'm trying to show a column only if a certain parameter contains a certain string of characters.  So far I've got it working if the parameter is equal to the string of characters by doing
=IIF (Parameters!Param1.Value = "String1", False, True)
but I would like it to work if the Param1.Value contains "String1" ... I tried
=IIF (Parameters!Param1.Value like "%String1%", False, True)
but it doesn't work.  Any suggestions?
 
TIA

View 4 Replies View Related

Dervied Column - Expression?

Feb 22, 2007

Hi:

I have a Dervied Column Component, in which there is a column called RecStatusCode. The criteria to give a value to that column is below in SQL:

(SELECT CASE CertParticipant
WHEN 'Y' THEN 'C'
WHEN NULL THEN 'A'
END
FROM [dbo].[SchoolCertRequest]
WHERE SchoolID = (SELECT SchoolCode FROM [WebStrategy].[LAF].[LoanApplication] WHERE LoanApplicationID = @LoanApplicationID) and SchoolBranch = (SELECT BranchCode FROM [WebStrategy].[LAF].[LoanApplication] WHERE LoanApplicationID = @LoanApplicationID) AND LoanType=(SELECT LoanApplicationTypeCode FROM [WebStrategy].[LAF].[LoanApplication] WHERE LoanApplicationID = @LoanApplicationID))

My question is how can I use this SQL in that expression field, or is it possible to have a user variable, with the above sql being the criteria, and the value could be used in the derived column, by just using the variable in the expression. In either case, I hope you understand what I am trying to accomplish. The reason why its like this is because currently in my Data Flow Task, i Have a OLEDB source which returns 25+ column values, the source connects to this derived column item, and then finally the destination oledb where the mapping takes place. So basically I am trying to avoid MERGE JOIN, because i tried using it , and it complicated the whole thing much more then it really needs to be. Like the RecStatusCode column, i have 5 more columns which are dervied, and the values are coming from a SQL statement similar to above. I really need some help here, and I have not seen anything in reference to this in BOL or any articles online. Thanks in advance.

View 4 Replies View Related

Reporting Services :: SSRS Group Total Expression - Add Total Disabled

Oct 26, 2015

For 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.. 

View 15 Replies View Related

Transact SQL :: Select Row With Max (column Value) Group By Another Column

May 19, 2015

i dont't know how to select row with max column value group by another column. I have T-SQL

CREATE PROC GET_USER AS
BEGIN
SELECT T.USER_ID ,MAX(T.START_DATE) AS [Max First Start Date] ,
MAX(T.[Second Start Date]) AS [Max Second Start Date],
T.PC_GRADE,T.FULL_NAME,T.COST_CENTER,T.TYPE_PERSON_NAME,T.TRANSACTION_NAME,T.DEPARTMENT_NAME ,T.BU_NAME,T.BRANCH_NAME,T.POSITION_NAME
FROM (

[code]....

View 3 Replies View Related

Using Logical Expression Inside A Column

May 16, 2007

Hi Experts,

I am new to this forum and SQL usage.

I have a database with following table fields as

Refernce - Varchar field
D_C - Varchar field (containing either D or C) and
Amount - Integer

Now i want to create a View & split the Amount column into 2 basd on
split this Amount into 2 columns as below:

Refernce - Varchar field
D_C - Varchar field (containing either D or C) and
Debit Amount - Integer (where D_C is D)
Credit Amount - Integer (where D_C is C)

Regards
SEkar

View 5 Replies View Related

SUM A Column Of Values Derived From An IIF Expression

Jan 31, 2007

I have a table in my report which shows sales values for each month by looking at the month number

so for Jan i use

=IIf(Fields!period_.Value=1,Fields!nett_.Value,0)

for Feb

=IIf(Fields!period_.Value=2,Fields!nett_.Value,0)

this is all good and I get a series of rows per customer with the correct value in the correct column

JAN FEB Mar

Customer A 250

Customer A 350

Customer A 5000

.

However I want to create a summary a line

JAN FEB Mar

Customer A 250 350 5000



I tried using SUM but this doesn't give all of the values in the summary line. It might just give the first and the rest are zeros. Or if the customer didn't have a value in Jan, but did in Feb and March, Feb's value is shown, but March is at 0 etc.

Have spent far too long on this today, so if anyone as any suggestion (come back Crystal all is forgiven ) on any approach I could take to this I'd appreciate it.

Cheers

Steven

View 4 Replies View Related

Question On Derived Column Expression

Apr 30, 2007

Hi, all experts here,



Thank you very much for your kind attention.



I am having a question on derived column expression. The expression I am trying to use for the derived column is as below (column1 is a numeric data type column):

case

when column1<0 then 'yes'

else 'no'

end

But I got the error message though, would please any experts here give me any advices on the expression I used? What is wrong with the expression I used above?

Thank you very much and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,





View 9 Replies View Related

Derived Column: Expression Question

Dec 4, 2006

The following simple expression is not working for in a derived column transform. I have tried several things back and forth with lots of frustration.

DAY((DT_DATE)mydate)

In fact any of the date/time functions are not working for me and I keep getting the following error

[Derived Column [380]] Error: The "component "Derived Column" (380)"
failed because error code 0xC0049067 occurred, and the error row
disposition on "output column "Derived Column 1" (824)" specifies
failure on error. An error occurred on the specified object of the specified component.

mydate happens to be imported as string[DT_STR] column from a flat file source.

Thank you in advance.

View 1 Replies View Related

What Is The Constraints Expression For Unique(Column)?

Oct 8, 2007

I am in the "Check Constraints" window trying to add a constraint that during inserts only allow distinct Column2 insertions. What is the Expression for that? I tried UNIQUE(Column2) it give me an error.

Here is my table definition:
Column Name Data Type Allow Nulls
--------------------------------------------------------------------------------------------
Column1(PrimaryKey) int No
Column2 nvarchar(MAX) No


View 9 Replies View Related

Derived Column Transformation Expression

Mar 7, 2006

Hi.

I am using the following expression to check if the first charcter of a string is not the letter "E" and if it is, strip it off by selecting the remainder of the string:

SUBSTRING([Derv.comno],1,1) == "E" ? SUBSTRING([Derv.comno],2,10) : [Derv.comno]

This is ok in 99.9% of cases, but ideally I would like to be able to check, and alter the string if the first charcter is anything but numeric

I had something like this in mind:

SUBSTRING([Derv.comno],1,1) != ("1","2","3") ? SUBSTRING([Derv.comno],2,10) : [Derv.comno] 

but the syntax is incorrect.

Could you tell me if what I am attempting is actually possible, and if so, point me in the right direction regarding the syntax!

Thanks

 

 

 

 

 

 

 

 

 

 

 

 

 

  

 

 

 

 

View 2 Replies View Related

Problem With Derived Column Expression

Feb 14, 2008

I'm trying to import from Excel, using
(Assignment == 0 ? "In Use" : Assignment == 1 ? "In Stock" : "Retired") to convert 3 values to text and I' getting the following error:
An error occurred while attempting to perform data conversion. The source type could not be converted to the destination type.

Could someone please tell me what I am doing wrong?

Thanks
Dean

ps. just fixed it myself by changing the data type of the input column

View 1 Replies View Related

Derived Column Expression Question......

May 7, 2007

Greetings,



I have an existing 2000 DTS package that uses the following case statement:


Case
When TERMS_PERCENT ='0'
then 0
else cast(TERMS_PERCENT as decimal(6,2))/100
end as TermsPct



to convert a source DT_STR(4) datatype to a DT_Numeric(5,2) destination column and would like to use an equivelent derived column expression in 2005. Being a DBA by nature and experience I'm having trouble converting this statement to a valid expression without failure, any help would be greatly appreciated.

View 4 Replies View Related

Updating A Column In A CommonTable Expression

Nov 22, 2007

I'm new to CTE and I'm trying to update some columns in my cte listed below


USE ELU;

GO

WITH FollowupGrid (TransactionID,

DetailLineNum,

TransactionDate,

Quantity,

ItemDescription,

ProductSize,

TransactionType,

FollowupResolve,

ReplenishResolve) AS

(

SELECT TransactionID,

DetailLineNum,

TransactionDate,

Quantity,

ItemDescription,

ProductSize,

TransactionType,

FollowupResolve,

ReplenishResolve

FROM vwFollowupTransactions

WHERE CustomerNum = N'6299'

UNION

SELECT

r.TransactionID,

r.DetailLineNum,

r.TransactionDate,

r.Quantity,

r.ItemDescription,

r.ProductSize,

r.TransactionType,

r.FollowupResolve,

r.ReplenishResolve

FROM vwReplenishTransactions r

WHERE CustomerNum = N'6299'

)



UPDATE FollowupGrid

SET FollowupResolve = N'Yes'

FROM vwFollowupTransactions f INNER JOIN

FollowupGrid ON f.DetailLineNum = FollowupGrid.DetailLineNum AND

f.TransactionID = FollowupGrid.TransactionID

WHERE (f.FollowupResolve is null)

UPDATE FollowupGrid

SET ReplenishResolve = N'Yes'

FROM vwReplenishTransactions t INNER JOIN

FollowupGrid ON t.DetailLineNum = FollowupGrid.DetailLineNum AND

t.TransactionID = FollowupGrid.TransactionID

WHERE (f.ReplenishResolve is null)

(

select * from FollowupGrid

)

GO


When I run this query I get an error message which states"

"Update or insert of view or function 'FollowupGrid' failed because it contains a derived or constant field."

How would I go about updating the FollowupResolve and ReplenishResolve fields in a CTE?

View 1 Replies View Related

Ms Query Sql Error 'Incorrect Column Expression'

Jul 22, 2005

I am getting an error from the case part of the select statement below which reads 'Incorrect Column Expression' then it quotes the case statement. All I am trying to do is convert and return the weight value to kilos if it was entered in pounds.

SELECT Salesinv.Unique, Salesinv.SalesNo, Salesinv.PurchNo, Salesinv.SalesInvNo, Salesinv.InvValue,

(case when Salesinv.WUnits = 'Llb' then round(Salesinv.NettWeight/2.2046,0) else Salesinv.NettWeight end)

FROM Salesinv Salesinv
WHERE (Salesinv.Unique>=38397.3092 And Salesinv.Unique<=38537.39885)

Any help would be greatly appreciated, hopefully thanks in advance.

View 12 Replies View Related

Incorrect Column Expression With CHAR In MS Query

Jan 16, 2014

I am working with Excel, then within Excel I am using MS Query to query a database. I am trying to use the CAST function on a field with numbers (1,2 or 3 digits) so I can convert it to a text value with three digits, i.e. 1 would read 001, 12 would read 012, etc.

I am not using CAST in the design grid. Is this even possible?

I am modifying the underlying SQL code. Here is the line that is giving me trouble:

CAST(GL02GLF.GLF_SEQ_NUM as CHAR(3)) as “Sequence”

View 1 Replies View Related

Simple Derived Column Formatting Expression

Jul 10, 2006

What is the equivalent SSIS expression for this C expression to format an unsigned integer by padding it with leading zeros?

sprintf(publ_doc_id, "%010u", id)

View 1 Replies View Related

UDF Or Trigonometric Functions In An Expression For A Derived Column?

May 24, 2006

Hi all!

I am importing data from a CSV to a DB with a SSIS package.
Among some things things it already does, it has to decide if the relation between one row and the following is acceptable.
If it is not, the 2nd row is discarded, the next one is taken and the relation value is calculated again to decide whether to keep this one or not, and so on.

To calculate this value, I need to apply a formula that includes sin(), cos() and acos() functions.
I have already written this formula as a scalar-valued function in my SQL Server.

So, my question is:
- Is there a way to call a function (a UDF) within the Expression in a Derived Column dataflow item?
and if not,
- Hoy can I use trigonometric functions within the Expression in a Derived Column dataflow item?

I hope someona can tell me something about this... I'm falling into despair! :-s

Thanks a lot!!

Cau

View 5 Replies View Related

Expression Problem In Derived Column Component

Nov 22, 2006

I am reading an excel file with a field that consists of lastname, firstname. I am using the Derived Column transformation to separate the two fields. The firstname expression works fine: SUBSTRING(F1,FINDSTRING(F1,",",1) + 1,LEN(F1) - FINDSTRING(F1,",",1))

However, the lastname field keeps giving me an error when I use SUBSTRING(F1,1,FINDSTRING(F1,",",1) - 1). I'm sure I've used this substring before in visual studio. Could this be a bug? The error is below.

[Add Columns [2886]] Error: The "component "Add Columns" (2886)" failed because error code 0xC0049067 occurred, and the error row disposition on "output column "LastName" (3100)" specifies failure on error. An error occurred on the specified object of the specified component.

View 1 Replies View Related

Is It Possible To Run A Dynamic Expression In The Derived Column Transfermation?

Nov 13, 2006

I would like to run following T-SQL statement in the Derived Column:

declare @strData varchar(1000), @strSQL varchar(3000)
select @strdata = 'LANEX ~11/13/06 10:10:08 ~112233 9th ST S. Arlington, 22204, VA ~ 093300~XYZ '
select @strSQL ='select datalength('''+replace(@strData,'~','''),datalength(''') +''')'
--print @strSQL
exec (@strSQL)

Thank you!

View 4 Replies View Related

How To Give The Null Value In The Derived Column Expression Value

Aug 25, 2006

Hi I want to pas one Derived Column Value is "Null" Default . How to Give the Expression "

For Example"

The Derived Column i gave one Column Name



Derived Column Name Expression Data Type



Price ""



Any one give me the Solution

View 1 Replies View Related

Default Date In A Derived Column Expression

Nov 20, 2006

one of my SSIS packages use this expression to put todays date in if it is NULL:

(ISNULL(datejs)) ? GETDATE() : datejs

however, what I really want to do though is put a default date in like '2007-01-01' but I get syntax error because SSIS thinks it's a string, which it is I suppose.

Is it possible to do what I want it to?

Thanks

 

 

 

 

View 5 Replies View Related

How To Generate An Expression That Uses The Value From A Previous Column In A Matrix???

Sep 26, 2006

I have a RS Matrix element where I am attempting to genrate an expression that uses a value from a previous column. I have had no luck so far...

Here's a brief example of the matrix that I am using:

=Fields!MonthName (my one and only Column Group)

=Fields!ProductName =Sum(Fields!Count) =DifferenceExpressionInQuestion

I gotta use a Matrix since the number of months is dynamic.

In my test, I have 12 month columns. Under each Month column is a subordinate Count and a Difference column. In the Difference column, I want to calculate the difference in the Count columns relative to the previous month (if the Count for Jan is 15 and the Count for Feb is 27, then Feb's Difference column should be 12).

So, I am stuck trying to generate an expression for the Difference column. This expression needs to refer to the Count of the previous month.

RS's Previous() function cannot be used in a Matrix (because that's the compiler error that I get when I try to use it).

Does anybody know how to do this???

Do I have to use custom code to do this???

If I can't do this calculation in a matrix, then what good is it???

TIA!!!

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved