Conditional Split query

Hi,

I have the following table in MsAccess

EmployeesA

empId integer,

empName varchar(60),

empAge integer,

empStatus char(1) - can be N,D or S - New, Deleted or Shifted

and the following in Sql2005

EmployeesB

Id smallint,

Name varchar(60),

Age int,

Status char(1) - Bydefault 'N'

I have written a Foreach File package that populates the sql server tables (EmployeesB) from Access(EmployeesA). However i want to check for a condition now.

If empStatus = N in EmployeesA, then insert a new record in EmployeesB

If empStatus = D in EmployeesA, then search for that field in the EmployeesB by passing empname and age and if found, mark the Status field in EmployeesB as 'D'

If empStatus = S in EmployeesA, then search for that field in the EmployeesB by passing empname and age and if found, mark the Status as 'S' in EmployeesB and insert a new row.

How do I do it for each table each row in EmployeesA using a foreach file loop?

Thanks,

ron

If you are using a data flow inside your For Each, you can use the techniques shown in this thread (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1211340&SiteID=1) to determine whether the row should be inserted or updated. The thread is dicussing specifically whether the row already exists or not, so you may need to add a conditional split to your data flow.

|||

Hi,

thanks for the reply. I had already seen that link. I cannot do a look up as Employees B will already contain millions of rows.

I just want to know this step by step if you could explain. I am so new to this SSIS.

How will I specify conditions :

If empStatus = N in EmployeesA, then insert a new record in EmployeesB

If empStatus = D in EmployeesA, then search for that field in the EmployeesB by passing empname and age and if found, mark the Status field in EmployeesB as 'D'

If empStatus = S in EmployeesA, then search for that field in the EmployeesB by passing empname and age and if found, mark the Status as 'S' in EmployeesB and insert a new row.

Which control to use. Where to specify etc.

thanks,

|||

Ok,

I have figured out most of it through a friend. Just tell me this:

What i am doing is :

For status D, I do a Lookup and if found, I have to use an OLE DB Command tranform to do the update.

What query do I fire in the look up over here. If that row exists, after that what to do in the OLEDB command. ?How to pass the current row?

thanks.

|||Do you mean how to update the row that was matched in the lookup? Why can you not use the same fields you used in the lookup for the match and put them into the WHERE clause of your update? Sometimes it is cleaner to return a key column or two from the lookup, and them as basis for the WHERE clause.|||

Can you state an example. What should be in the Lookup and what in the OledbCommand based on my table.

Thanks

|||

An example, do you mean for this problem-

If empStatus = D in EmployeesA, then search for that field in the EmployeesB by passing empname and age and if found, mark the Status field in EmployeesB as 'D'

I would be tempted to skip the lookup. Use a Conditional Split to get a feed of all EmployeesA rows where empStatus = "D", then use a command to do the update. Set your connection, and end the SQL statement -

UPDATE EmployeesB

SET empStatus = 'D'

WHERE empname = ?

AND age = ?

Map the two input columns empname and age to the two parameters, to complete the OLD-DB Command setup.

This avoids the costs of a lookup, which may be faster overall. If there is no match, then no update happens, which is the same overall outcome as if the lookup had failed to find anything and the command was not run.

It may be faster to use a Lookup to help filter out the non-matches, it really depends on row counts and ratios of lookup hits to misses. Test both if you are worried about performance, but it is often faster to attempt and "fail" than to prevent the "fail" in the first place in SSIS.

|||

Darren,

you know what..that worked like a charm i removed the look up and did as you said..I will try the rest and if everything works, i will close this thread. thankuuuuuu.

If you could have a look at this thread too, I will be much obliged.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2178311&SiteID=1

thanks.

Conditional Displaying

Hi all,

I am using Crystal reports XI R1 . Here is the problem, I have an integer field (x), i need to display three more fields (a,b,c) based on that integer field (x) when it is maximum.

for example
when max(x) then i need to display the corresponding records of a, b, and c.

Any help would be greatly appreciated.

Thanks in advance
Sudharsan.What is the database?
I think you need to write the query

Select * from table
where x=(select max(x) from table)

and design the report using that query

Concatenation of integer data into text

I am a TSQL Newbie trying to concatenate two columns (DocumentNo & SequenceNo) that were created with a “smallint” data type constraint in a full-text search database.I want to end up with a column containing varchar data such as “5-2” where this row of data contains information about the 2nd document in a series for a person or group designated as 5.

If I could change the data type for the columns to varchar I think I could query them like this:

SELECT ("DocumentNo" + '-' + "SequenceNo") AS DocumentNoFull

FROM Full_Documents

ORDER BY DocumentNo, SequenceNo

When I try to concatenate with this query the result is a mathematical addition of the numbers, not what I am trying to achieve (which is to combine the two numbers to produce a text string).

Due to the full-text search parameters for the database I have not been able to modify the data type constraints on the two relevant columns.Is there a way to concatenate the two “smallint” columns and create a new column with text data (e.g., 5-2) for each row in the table?

My research suggests that “casting” could be used to convert between data types, but I have not been able to figure out how to apply it to my situation.Any help would be appreciated.

Casting should work.

It would be something like.

SELECT CAST(DocumentNo AS VARCHAR(5) )+ '-' + CAST(SequenceNo AS VARCHAR(5)) AS DocumentNoFull

FROM Full_Documents

ORDER BY DocumentNo, SequenceNo

|||

Hi Ryan: Thanks, that was so easy. Now I know how to cast.

How do I create a new column in the database into which the results of the query will automatically be inserted?

|||

I'm not sure exactly what you mean.

Do you want to add a column to your table and populate it for all existing rows using your query? With this approach you would have to change future inserts to the table to populate this field. (Or use something like a trigger to populate it, if you don't have control of the insert statements)

Or do you want a computed column that is added to the table and then calculated based on the values in the other fields?

Can I ask why you need to add this as a column at all? Why can't you just do the concatenation in SQL when you need it?

If you really need to do either the first option or second, I can point you toward how to do it.

|||

I think I want the first option. I don't foresee any additions to the database (which is based on historical records from a closed source).

I hope to be able to do full text searches in a VB application and possibly from a web form and am looking to keep things simple when I write those applications. As I get more experience I will surely become more confident in my ability to concatenate, etc. But at this point I just want to make sure I can get it to work. I can do full text searches easily from within SQL Management Studio, but have not yet been able to achieve it from Visual Basic. So I just want to eliminate as many possible sources of error until I know that I can do it all properly.

Also, I will learn to create a new column and insert data from a query (which could be useful as I progress in my TSQL education).

|||

Okay. If you really want the first option.

Do something like this. For the added column you either need to allow it to be NULL or give it a default value. I went with the NULL option

ALTER TABLE Full_Documents ADD concat_col VARCHAR(15) NULL

UPDATE Full_Documents SET concat_col = CAST(DocumentNo AS VARCHAR(5) )+ '-' + CAST(SequenceNo AS VARCHAR(5))

|||

Thanks Ryan. Exactly what I wanted in this instance.

Just so that I will understand my choice - would the second option have created a dynamic field that would have automatically been updated with the properly concatenated text when a new row was added? If not, what did I miss by choosing the first option?

|||

Yes, that is exactly the difference. You can use what is called a computed column. From a performance standpoint, it is not usually the best idea. But, you can declare that column using a function that returns the value that you want. With this column, the concat_col would always have values associated with the other 2 columns instead of needing it to be inserted with each row.

The typical way to do this is to declare the column with a type that references a function (instead of varchar). The function would return the value that you want based on the other values in your row.

|||Thanks again Ryan.

Concatenation Formula For Int Columns

Column A, Column B and Column C : All Integer.

I want a concatenation. For example A=111, B=222. C should be 111222 NOT 333. Is it possible? If it's possible, what is the formula?

Thanks in advance...

If you need to concatenate numeric values, you need toCAST them as character values first.

SELECTCAST(ColumnAAS varchar(10)) +CAST(ColumnBAS varchar(10))AS Column3FROM myTable

|||Thank you but i ask the formula to use in Formula Property of Column in SQL (Enterprise Manager). Isn't that possible?|||Yes. The formula would be exactly the same, without the AS clause. Did you try it?

CAST(ColumnA AS varchar(10)) + CAST(ColumnB AS Varchar(10))|||

Tried after my answer. You were right. Sorry and thank you.

|||Cool, I'm glad it worked.|||Out of subject and not so important but i wondered. I set C as Unique. When i insert record, if it's duplicate so rollback transaction but ID is increased. For example 4. record was duplicate so ID's like 1,2,3,5,6... Can i prevent this so how?|||

LacOniC:

Out of subject and not so important but i wondered. I set C as Unique. When i insert record, if it's duplicate so rollback transaction but ID is increased. For example 4. record was duplicate so ID's like 1,2,3,5,6... Can i prevent this so how?

You can't prevent this if you are using an Identity column, sorry. The only way to really prevent it is to have your own ID number table, read the next available value out of it, and assign that to your new record. All of that should be wrapped in the transaction.

Concatenating Numbers/Converting Dates to Integers

My ERP software stores all dates as integers. So originally, I wrote a T-SQL function to convert these integer dates to normal people dates in the query I use as the recordset for my report. Well...that worked fine on 1,000 rows, but NOT for 100,000. So I've figured out that if I convert my normal person date parameter to an integer date, then SQL only has to convert my 1 parameter instead of having to convert 100,000 fields, (actually, 300,000 because I have 3 date columns).

So my question is, what is the best way to do this? This is what I have so far:

SET @.Macola = Cast(Datepart(yy,@.MacolaDate) as varchar) + Cast(Datepart(mm,@.MacolaDate) as varchar) + Cast(Datepart(dd,@.MacolaDate) as varchar)

However, I want the leading zeros for the month and day. For example if I enter '1/1/2004' into this function, it returns 200411, but I need it to return 20040101.

Any suggestions would be greatly apprectiated. Thank you.just to be sure
what is the type of your variable @.Macola

doeas it HAVE to be integer ?|||Well, I am working with a database design that I cannot modify and it stores the dates in an int column with the format 20040101 and any conversions to the 100,000 values in the table takes too long, so I want to convert my date to an integer.|||i'm not sure if this is what you want

but create this user define function

CREATE function dbo.Date(@.Date DateTime)
returns Varchar(10) as
begin
return(
Cast(Datepart(yy,@.Date) as varchar) +
Replicate('0',2-len(Cast(Datepart(mm,@.Date) as varchar)))+Cast(Datepart(mm,@.Date) as varchar) +
Replicate('0',2-len(Cast(Datepart(dd,@.Date) as varchar)))+Cast(Datepart(dd,@.Date) as varchar)
)
end

then you'll be able to get easily

set @.Macola=dbo.date('1/1/2004')
set @.Macola=dbo.date(@.MacolaDate)

the Replicate+Len functions adds the 0 if necessary|||Thank you!|||is it what you needed ('cause i'm going offline)|||Yes it is, I appreciate your help.|||Cross post?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=32771

Anyway, what does your base data look like

Since you say you want it to be 20040101, are you assuming that that's the int value?

Where do you get 1/1/2004 from?

Some ddl might help

I wrote a T-SQL function to convert these integer dates to normal people dates in the query I use as the recordset for my report. Well...that worked fine on 1,000 rows, but NOT for 100,000. So I've figured out that if I convert my normal person date parameter to an integer date, then SQL only has to convert my 1 parameter instead of having to convert 100,000 fields,

That part confused me...|||Sorry for any confusion. Let me try this again.

I need to pass StartDate and EndDate parameters to the query I use as the record-source for several reports.

My database stores dates as integers in the format 20040101, but I don't want my end users to have to enter dates in that integer format, also, I figured it would be easier to convert the integer dates to datetimes in the query so that I wouldn't have to make this conversion on every report, it would already be done for me.

I wrote a T-SQL scalar function that converts from the integer format to a datetime format, and I just used that 'fnIntToDate(doc_date)' as the column returned in the header for the 3 date columns I have. This function looks like this, and works well:

Convert(Datetime,Left(Right(@.MacolaDate,4),2) + '/' + Right(@.MacolaDate,2) + '/' + Left(@.MacolaDate,4))

I do my development on a database with 1,000 rows, but our production database has over 100,000, and this date conversion (along with a few other things I have since fixed) caused the query to time-out. And according the the query analyzer, I can save 3-4 seconds PER COLUMN if I just leave the integers as integers and convert my date parameters to integers instead.

So I'm asking for help on a function that does the opposite of what my function does. I want to pass it a datetime value and have it return a date in the format 20040101. I had gotten all of it except the leading 0s, but I think the previous poster answered that for me, so i'm goign to go try that now.

Thank you.|||And what he told me worked great :). I just converted it to an integer and I'm good to go.

concatenating columns

I have two columns of type integer that I want to concatenate. I also want to put a "/ " in between the columns.

I have tried doing it in the same way as I would for varchar columns but that doesn't seem to work for integers.

Thanks

RupalDo the following
SELECT ComputedColumn = Cast(column1 as varchar(4)) + '/' + Cast(column2 as varchar(4))
FROM TableName|||

SELECT (CONVERT(varchar,t.int1) + '/' + CONVERT(varchar,t.int2)) AS concatedcolumn
FROM table1 t