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


ADVERTISEMENT

Updating Multiple Rows With An Expression

Dec 26, 2006

OK, I am trying to update a particular column with a numerical number. Here is the query I am using.

UPDATE viewerblock SET dummycat = (?) WHERE dummyproduct = 0

I am trying to number dummycat row to a certain number for example


dummycat-----------------dummyproduct
1--------------------------0
2--------------------------0
3--------------------------0
4--------------------------0
5--------------------------0
6--------------------------0


do you see what i am trying to do? I am simply trying to number the dummycat column where ever dummyproduct = 0.

is this possible to do?

View 3 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

Updating The Column Defined As 'Text' Column

Oct 31, 2002

Hi,

I have a table with col_noteText defined as 'Text' datatype column.

I want to search a pattern 'Lawyer' and replace with 'Attorney' in the column col_noteText.

Does anyone know how to do this for 'Text' datatype column.

Thanks in advance.

jfk

View 1 Replies View Related

Updating A Column With A Column From Another Table

Apr 1, 2004

Hi, I am kinda new to this so here it goes:

table name: USERS
field names : LName, FName, EmpID


other table name:PERSONNEL
field names : (same as USERS)

The EmpID column in my USERS table is blank (I have 30 records in the table.)

I would like to update the USERS.EmpID table with the PERSONNEL.EmpID data

how would I do this or what would the code be?

sanctos

View 2 Replies View Related

Updating Max(column) And Between_dates Column

Apr 26, 2008



Hi,

I have dataset which has max(column) and between_sale_dates columns So I would like to
update those columns. CustomerNo and Sales_date are important . This group by for these 2
colums. I can write a sproc but How can I do with SSIS?

thanks

View 5 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

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

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

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

Problem With Expression Window In Derived Column

Jan 10, 2007

Hi,

i am facing problem to dervie one column value from another column using either if and case statements in expression window of dervived columns transformation.

let me give the exapmle. i get 1 column from source system name as "col a" and i want to insert 2 columns into my destination as col A and col B. based on the values of col A i want to derive the values of col B,like if col A value is 0 then col B value is Good else BAD.

Can any one asssit in this regard how to achive it?? and is it possible to use IF and CASE statement in this dervived column tranformation???





Sreenivas

View 1 Replies View Related

How To Set Empty A Space From A Derived Column Expression?

May 30, 2006

hi,

This field comes from a flat file and sometimes own zero and sometimes own a empty position (after 'EUR'):

2006053000499236000005307700108287457080200408287452006052953990000000010000EUR
2006053000499236004414989200101423426004400501423422006052953990000000010000EUR0

[Column 12] == "0" ? [Column 12] : ?????????



TIA

View 7 Replies View Related

Updating A Column In SQL

Dec 13, 2006

I am trying to update a users status from Pending to either Approved or Rejected.  I created the following handers to update me db by I keep getting a syntax error. What am I doing wrong? public partial class admin_beta : System.Web.UI.Page{    protected void ApproveButton_Click(object sender, EventArgs e)    {        SqlConnection conn = new SqlConnection("Data Source=TECATE;Initial Catalog=subscribe_mainSQL; User Id=maindb Password=$$ricardo; Integrated Security=SSPI");        SqlCommand cmd = new SqlCommand("UPDATE [main] ([status]) VALUES (@status)", conn);        conn.Open();        cmd.Parameters.AddWithValue("@status", "Approved");        int i = cmd.ExecuteNonQuery();        conn.Dispose();    }    protected void DenyButton_Click(object sender, EventArgs e)    {        SqlConnection conn = new SqlConnection("Data Source=TECATE;Initial Catalog=subscribe_mainSQL; User Id=maindb Password=$$ricardo; Integrated Security=SSPI");        SqlCommand cmd = new SqlCommand("UPDATE [main] ([status]) VALUES (@status)", conn);        conn.Open();        cmd.Parameters.AddWithValue("@status", "Rejected");        int i = cmd.ExecuteNonQuery();        conn.Dispose();    }}

View 4 Replies View Related

Updating A Column By The Value Of '1'

Nov 6, 2007

Hi,Its probably simple but.. How do I update a column by just '1'..for example - heres my code:    protected void Button1_Click(object sender, EventArgs e)    {        SqlConnection con = new SqlConnection();        con.ConnectionString = "HiddenConnection";        con.Open();        SqlCommand command = new SqlCommand();        command.Connection = con;        Label productIDLabel = (Label)DataList1.FindControl("productIDLabel");        command.CommandText = "UPDATE Items SET numberclickedin = numberclickedin + 1 WHERE productID=@productID";        command.Parameters.Add("@productID", productIDLabel.Text);        command.ExecuteNonQuery();        con.Close();        command.Dispose();    } As you can see in the bold text, I want to add 1 to the numberclickedin column.. and in my primative way Ive just typed +1What should I use instead?Thanks in advance,Jon 

View 1 Replies View Related

HELP Updating Column

Nov 15, 2006

How can I create an update statement that will allow me to fill in a column if the previous column already has data in it? I am trying to do and UPDATE/SET command that allows me to extract information and populate columns within a table. However, the UPDATE/SET has to make sure that it is not overwriting information that is already in the column and if there is, to populate the column next to it, and so on until all the columns are populated.

Here is my table:


Code:

create table #add_diags(
add_diag_1 varchar(10) null,
add_diag_2 varchar(10) null,
add_diag_3 varchar(10) null,
add_diag_4 varchar(10) null,
add_diag_5 varchar(10) null,
add_diag_6 varchar(10) null,
add_diag_7 varchar(10) null,
add_diag_8 varchar(10) null
)



In my UPDATE/SET I am pulling data from another table to populate into the 'add_diag' columns however, I'm not sure how to write in SQL the ability to monitor all the columns, and if for example the first three are full to then populate into the fourth and so on.

Here is my UPDATE/SET statement (while involved, I think I need something in my WHERE clause in order for this to be resolved):


Code:

update ad
set add_diag_1=dsm_code
from #add_diags ad
join Doc_Entity de
on ad.patient_id=de.patient_id
and ad.episode_id=de.episode_id
and doc_code = 'DCDIAG'
join Patient_Assignment pa
on de.patient_assignment_id = pa.patient_assignment_id
and convert(char(8),de.effective_date,112) = convert(char(8),pa.date_discharged,112)
left outer join Doc_Diag_Axis_I_III dx1
on de.doc_session_no=dx1.doc_session_no
and de.current_version_no=dx1.version_no
where dx1.sequence_no=2
and de.is_locked = 'Y'
and dx1.rule_out = 'N'
and is_billable = 'Y'
and dx1.axis_type IN ('1','2')
and de.status in ('CO' , 'SA')



Any ideas? Thanks!

View 7 Replies View Related

SSIS - Derived Column Transformation Editor Expression

Aug 23, 2007

I am trying to put the following as an expression in the SSIS Derived Column Transformation Editor.

DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)

It is not allowing it. This works fine in a regular SQL statement.

Does anyone know how I can get this to work?

View 14 Replies View Related

Integration Services :: SSIS Derived Column Expression

Nov 23, 2015

I am trying to figure out my expression here with derived column 

source

Unavailable/Planned

output

Planned(which is after "/".

I achieved this in sql but not finding solution in SSIS Derived column transformation.

View 3 Replies View Related







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