Showing posts with label stores. Show all posts
Showing posts with label stores. Show all posts

Monday, March 19, 2012

Conditional split with dependence?

I have setup a SSIS package that takes a flat file fixed width input, and stores it to two SQL server tables in the same database. The flat file contains two types of records, lets call them Type1 and Type2. The two types of records are formatted differently, and the first character determines what type the record is. I used a conditional split to send record type1 down one path, and type2 down the other. On each of those I use a derived column task to build all the fields and then store to the table with the OLE destination. I put any errors that occur (like truncation) into an error table by setting the "redirected row" feature vs "Fail Component". This all works well and I have no issues.

The dilema is as follows. Type1 is essentially a parent record and the Type2 record is a child. There is a shared primary key / foreign key relationship field. I want errors when processing type1 to cause the associated type2 to also be redirected to the error table vs being inserted.

If anyone has suggestions on how this could be done, reference articles, etc... please let me know.

Thanks.

Perhaps use a merge join on the error output of the Type1 flow together with the Type 2 data flow. Then use a conditional split to look for matches. If you have a match, you direct the Type 2 record (along with the Type1 record) down a separate error-handling flow. If you don't have a match, the Type2 records can be processed accordingly.|||

I'm trying your suggestion and I think it will work. But I am having an issue. I have my original flat file source, which I read into the SSIS package as just rows. So I do CRLF search to bring in as one column. I then send it to a derived column component after a conditional split to perform all the "substrings" to get the actual columns out of the data.

In order to do a merge join you must use sorted columns. I was able to set sorted column on the flat file data source and single column, which does me no good. I need to be able to set the sorted column on the derived columns after the data has been put into columns. Is there any way to set the sort column on a derived column? If I can do that it will solve my issues.

Thanks.

Thursday, March 8, 2012

Conditional 'Order By' statement

I have a table that stores three columns of data, namely
1. Order_ID
2. Exam_Start_Date
3. Order_Received_Date
I want to order the records as follows:
If the Exam_Start_Date is within the next 10 days or past, then order by the
exam_start_date. Otherwise order by the order_Received_Date. Therefore the
result set should display records where the exam_start_date is within next 10
days first, then display all other records.
I have tried the following SQL but it doesnt appear to work (i.e. Order of
the records is not exam_start_date (if within next 10 days), otherwise
Order_Received_Date.
select order_id, order_received_date, exam_start_date
from orders
ORDER BY CASE WHEN Exam_Start_date < '20050118' THEN Exam_Start_Date ELSE
Order_Received_Date END
Any suggestions greatly appreciated!
Wes.
On Sat, 7 Jan 2006 18:51:02 -0800, Wez wrote:

>I have a table that stores three columns of data, namely
>1. Order_ID
>2. Exam_Start_Date
>3. Order_Received_Date
>I want to order the records as follows:
>If the Exam_Start_Date is within the next 10 days or past, then order by the
>exam_start_date. Otherwise order by the order_Received_Date. Therefore the
>result set should display records where the exam_start_date is within next 10
>days first, then display all other records.
>I have tried the following SQL but it doesnt appear to work (i.e. Order of
>the records is not exam_start_date (if within next 10 days), otherwise
>Order_Received_Date.
>select order_id, order_received_date, exam_start_date
>from orders
>ORDER BY CASE WHEN Exam_Start_date < '20050118' THEN Exam_Start_Date ELSE
>Order_Received_Date END
>Any suggestions greatly appreciated!
Hi Wes,
Try if this works better:
ORDER BY CASE WHEN Exam_Start_date < '20050118'
THEN Exam_Start_Date
ELSE '20050118'
END DESC,
Order_Received_Date DESC
Or, a more generic version:
ORDER BY CASE WHEN Exam_Start_date < DATEADD(day,-10, CURRENT_TIMESTAMP)
THEN Exam_Start_Date
ELSE DATEADD(day,-10, CURRENT_TIMESTAMP)
END DESC,
Order_Received_Date DESC
Hugo Kornelis, SQL Server MVP

Conditional Join

Hello,
I have the following problem...
I am musing ADO.NET with SQL Server. I have a table that stores events
that are linked to different tables.. for example, lets suppose that I
have the following tables : Airplanes, Cars, Trains. Each table is
different, however, events produced by any of these are stored on a
single Event Table with the following fields:
EventID, OwnerTable, OwnerID, EventTime, Description.
Where OwnerTable might be Airplanes, Cars or Trains and OwnerID is the
unique ID within the given table. I now want to create a SELECT
statement that shows all events and joins the particular row to the
correct record based on the OwnerTable and OwnerID to show common fields
among all. For example, assume that all three tables have a COLOR
field, the resulting query should produce the following:
Event Table
Event ID: 1
OwnerTable: Airplanes
OwnerID: 1
Color : Red ( This is the COLOR field on the Airplanes table with
AirplaneID = 1)
Event Table
Event ID: 2
OwnerTable: Trains
OwnerID: 1
Color : Blue ( This is the COLOR field on the Trains table with
TrainID = 1)
Thanks,
Jeronimo BertranHi J,
As per the info you have porvided I have created tables and inserted the
data.
The query you require is at the end.
Create table plane (objectID Int Primary Key Identity,Color Varchar(1000))
go
Create table car (objectID Int Primary Key Identity,Color Varchar(1000))
go
Create table train (objectID Int Primary Key Identity,Color Varchar(1000))
go
Create table Events (EventID Int Primary Key Identity, OwnerTable
Varchar(1000),
OwnerID Int, EventTime DateTime, Description Varchar(1000))
go
Insert Into plane values ('Blue')
go
Insert Into plane values ('Blue1')
go
Insert Into plane values ('Blue2')
go
Insert Into car values ('Red')
go
Insert Into car values ('Red1')
go
Insert Into car values ('Red2')
go
Insert Into train values ('Yellow')
go
Insert Into train values ('Yellow1')
go
Insert Into train values ('Yellow2')
go
Insert into Events Select 'Plane',ObjectID,GetDate(),'Planes has added this'
from plane
go
Insert into Events
Select 'Train',ObjectID,GetDate(),'Trains has added this' from train
Union
Select 'Car',ObjectID,GetDate(),'Cars has added this' from Car
go
--This is your query
Select Events.* , Case Events.OwnerTable
When 'Plane' Then Plane.Color
When 'Train' Then Train.Color
When 'Car' Then Car.Color
Else '{Blank}'
End
From Events Left Outer Join Plane on Events.OwnerId = Plane.ObjectID And
Events.OwnerTable = 'Plane'
Left Outer Join Train on Events.OwnerId = Train.ObjectID And
Events.OwnerTable = 'Train'
Left Outer Join Car on Events.OwnerId = Car.ObjectID And Events.OwnerTable
= 'Car'
Please respond if it solves your problem
Thanks,
Vishal Khajuria
Sungard SCT India
"Jeronimo Bertran" wrote:

> Hello,
> I have the following problem...
> I am musing ADO.NET with SQL Server. I have a table that stores events
> that are linked to different tables.. for example, lets suppose that I
> have the following tables : Airplanes, Cars, Trains. Each table is
> different, however, events produced by any of these are stored on a
> single Event Table with the following fields:
> EventID, OwnerTable, OwnerID, EventTime, Description.
> Where OwnerTable might be Airplanes, Cars or Trains and OwnerID is the
> unique ID within the given table. I now want to create a SELECT
> statement that shows all events and joins the particular row to the
> correct record based on the OwnerTable and OwnerID to show common fields
> among all. For example, assume that all three tables have a COLOR
> field, the resulting query should produce the following:
> Event Table
> Event ID: 1
> OwnerTable: Airplanes
> OwnerID: 1
> Color : Red ( This is the COLOR field on the Airplanes table with
> AirplaneID = 1)
> Event Table
> Event ID: 2
> OwnerTable: Trains
> OwnerID: 1
> Color : Blue ( This is the COLOR field on the Trains table with
> TrainID = 1)
> Thanks,
> Jeronimo Bertran
>|||Hi Jeronimo Bertran,
As per the info you have provided, I have created table and populated them
with data. The query you are looking for is at end. I am giving you all the
scripts so that there is no misinterpertation.
Create table plane (objectID Int Primary Key Identity,Color Varchar(1000))
GO
Create table car (objectID Int Primary Key Identity,Color Varchar(1000))
GO
Create table train (objectID Int Primary Key Identity,Color Varchar(1000))
GO
Create table Events (EventID Int Primary Key Identity, OwnerTable
Varchar(1000), OwnerID Int, EventTime DateTime, Description Varchar(1000))
GO
Insert Into plane values ('Blue')
GO
Insert Into plane values ('Blue1')
GO
Insert Into plane values ('Blue2')
GO
Insert Into car values ('Red')
GO
Insert Into car values ('Red1')
GO
Insert Into car values ('Red2')
GO
Insert Into train values ('Yellow')
GO
Insert Into train values ('Yellow1')
GO
Insert Into train values ('Yellow2')
GO
Insert into Events Select 'Plane',ObjectID,GetDate(),'Planes has added this'
from plane
GO
Insert into Events
Select 'Train',ObjectID,GetDate(),'Trains has added this' from train
Union
Select 'Car',ObjectID,GetDate(),'Cars has added this' from Car
GO
--This is the query you are lookin for
Select Events.* , Case Events.OwnerTable
When 'Plane' Then Plane.Color
When 'Train' Then Train.Color
When 'Car' Then Car.Color
Else '{Blank}'
End
From Events Left Outer Join Plane on Events.OwnerId = Plane.ObjectID And
Events.OwnerTable = 'Plane'
Left Outer Join Train on Events.OwnerId = Train.ObjectID And
Events.OwnerTable = 'Train'
Left Outer Join Car on Events.OwnerId = Car.ObjectID And Events.OwnerTable
= 'Car'
Please respond if it solves your problem.
Regard ,
Vishal Khajuria
Sungard SCT India
"Jeronimo Bertran" wrote:

> Hello,
> I have the following problem...
> I am musing ADO.NET with SQL Server. I have a table that stores events
> that are linked to different tables.. for example, lets suppose that I
> have the following tables : Airplanes, Cars, Trains. Each table is
> different, however, events produced by any of these are stored on a
> single Event Table with the following fields:
> EventID, OwnerTable, OwnerID, EventTime, Description.
> Where OwnerTable might be Airplanes, Cars or Trains and OwnerID is the
> unique ID within the given table. I now want to create a SELECT
> statement that shows all events and joins the particular row to the
> correct record based on the OwnerTable and OwnerID to show common fields
> among all. For example, assume that all three tables have a COLOR
> field, the resulting query should produce the following:
> Event Table
> Event ID: 1
> OwnerTable: Airplanes
> OwnerID: 1
> Color : Red ( This is the COLOR field on the Airplanes table with
> AirplaneID = 1)
> Event Table
> Event ID: 2
> OwnerTable: Trains
> OwnerID: 1
> Color : Blue ( This is the COLOR field on the Trains table with
> TrainID = 1)
> Thanks,
> Jeronimo Bertran
>|||Jeronimo
SELECT <column lists> FROM Airplanes A JOIN Events E
ON A.EventId=E.EventId AND A.OwnerID=E.OwnerID
If it does not help please post DDL+ sample data + expected result
"Jeronimo Bertran" <jeronimo.bertran@.newsgroup.nospam> wrote in message
news:eexdtw7FFHA.1476@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I have the following problem...
> I am musing ADO.NET with SQL Server. I have a table that stores events
> that are linked to different tables.. for example, lets suppose that I
> have the following tables : Airplanes, Cars, Trains. Each table is
> different, however, events produced by any of these are stored on a
> single Event Table with the following fields:
> EventID, OwnerTable, OwnerID, EventTime, Description.
> Where OwnerTable might be Airplanes, Cars or Trains and OwnerID is the
> unique ID within the given table. I now want to create a SELECT
> statement that shows all events and joins the particular row to the
> correct record based on the OwnerTable and OwnerID to show common fields
> among all. For example, assume that all three tables have a COLOR
> field, the resulting query should produce the following:
> Event Table
> Event ID: 1
> OwnerTable: Airplanes
> OwnerID: 1
> Color : Red ( This is the COLOR field on the Airplanes table with
> AirplaneID = 1)
> Event Table
> Event ID: 2
> OwnerTable: Trains
> OwnerID: 1
> Color : Blue ( This is the COLOR field on the Trains table with
> TrainID = 1)
> Thanks,
> Jeronimo Bertran|||Try this:
SELECT E.eventid, E.ownertable, E.ownerid,
COALESCE(A.color, T.color) AS color
FROM Events AS E
LEFT JOIN AirPlanes AS A
ON E.ownerid = A.airplaneid
AND E.ownertable = 'Airplanes'
LEFT JOIN Trains AS T
ON E.ownerid = T.trainid
AND E.ownertable = 'Trains'
Alternatively, why not put those common columns in a single table
across all the types of transport and in the separate tables just have
columns that are specific to that subtype - that would greatly simplify
this type of query.
If Event is some kind of system-maintained audit trail then its maybe
reasonable to hold the applicable table name in there but otherwise I
would suggest that the table name is probably a poor way to identify
the entity. Don't you have identifying codes for the types "Airplanes",
"Trains", etc? In general its best not to mix data and metadata in a
table - doing so suggests that there may be something missing from the
data model.
David Portas
SQL Server MVP
--|||Thanks David,
The COALESCE will do the job. Yes I do have identifying codes for the
different types whcih I can use instead of the table name.|||Thanks Vishal,
Especially for being so thorough. Yor implementation solved my problem.

Friday, February 10, 2012

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.