help on query

I need advice on how to approach this. To simplify with a sample, below is a
list of various calculations based from ITEMS table:
ITEM_NO FIELD_CALCULATION
123 cost*qty
111 cost-discount
222 sales*discount
333 cost-freight
etc...
There are 20 additional line items with different calculations.

From my SP, how do I incorporate the above to say:
SELECT ??
INTO #tmp1
FROM GL_ACCOUNT GL,
ITEMS I
WHERE GL.ITEM_NO = I.ITEM_NO

TIA!
BobHi

You could try something like:

SELECT I.Item_No,
CASE I.Item_No WHEN 123 THEN GL.cost*GL.qty
WHEN 111 THEN GL.cost-GL.discount
WHEN 222 THEN GL.sales*GL.discount
WHEN 333 THEN GL.cost-GL.freight
END AS [Calculation]
FROM GL_ACCOUNT GL,
JOIN ITEMS I ON GL.ITEM_NO = I.ITEM_NO

John

"B" <no_spam@.no_spam.com> wrote in message
news:ZrydnROKGqA3psrfRVn-uQ@.rcn.net...
>I need advice on how to approach this. To simplify with a sample, below is
>a
> list of various calculations based from ITEMS table:
> ITEM_NO FIELD_CALCULATION
> 123 cost*qty
> 111 cost-discount
> 222 sales*discount
> 333 cost-freight
> etc...
> There are 20 additional line items with different calculations.
> From my SP, how do I incorporate the above to say:
> SELECT ??
> INTO #tmp1
> FROM GL_ACCOUNT GL,
> ITEMS I
> WHERE GL.ITEM_NO = I.ITEM_NO
> TIA!
> Bob
>|||I was hoping to use the table created as a source without having to hardcode
since it will be used by other SP.

Thank you for your time.

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:42578d55$0$26738$db0fefd9@.news.zen.co.uk...
> Hi
> You could try something like:
> SELECT I.Item_No,
> CASE I.Item_No WHEN 123 THEN GL.cost*GL.qty
> WHEN 111 THEN GL.cost-GL.discount
> WHEN 222 THEN GL.sales*GL.discount
> WHEN 333 THEN GL.cost-GL.freight
> END AS [Calculation]
> FROM GL_ACCOUNT GL,
> JOIN ITEMS I ON GL.ITEM_NO = I.ITEM_NO
> John
> "B" <no_spam@.no_spam.com> wrote in message
> news:ZrydnROKGqA3psrfRVn-uQ@.rcn.net...
> >I need advice on how to approach this. To simplify with a sample, below
is
> >a
> > list of various calculations based from ITEMS table:
> > ITEM_NO FIELD_CALCULATION
> > 123 cost*qty
> > 111 cost-discount
> > 222 sales*discount
> > 333 cost-freight
> > etc...
> > There are 20 additional line items with different calculations.
> > From my SP, how do I incorporate the above to say:
> > SELECT ??
> > INTO #tmp1
> > FROM GL_ACCOUNT GL,
> > ITEMS I
> > WHERE GL.ITEM_NO = I.ITEM_NO
> > TIA!
> > Bob|||Hi

Creating a view would overcome that problem.

John

"B" <no_spam@.no_spam.com> wrote in message
news:JMSdnZXT2LmwK8rfRVn-iQ@.rcn.net...
>I was hoping to use the table created as a source without having to
>hardcode
> since it will be used by other SP.
> Thank you for your time.
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:42578d55$0$26738$db0fefd9@.news.zen.co.uk...
>> Hi
>>
>> You could try something like:
>>
>> SELECT I.Item_No,
>> CASE I.Item_No WHEN 123 THEN GL.cost*GL.qty
>> WHEN 111 THEN GL.cost-GL.discount
>> WHEN 222 THEN GL.sales*GL.discount
>> WHEN 333 THEN GL.cost-GL.freight
>> END AS [Calculation]
>> FROM GL_ACCOUNT GL,
>> JOIN ITEMS I ON GL.ITEM_NO = I.ITEM_NO
>>
>> John
>>
>> "B" <no_spam@.no_spam.com> wrote in message
>> news:ZrydnROKGqA3psrfRVn-uQ@.rcn.net...
>> >I need advice on how to approach this. To simplify with a sample, below
> is
>> >a
>> > list of various calculations based from ITEMS table:
>> > ITEM_NO FIELD_CALCULATION
>> > 123 cost*qty
>> > 111 cost-discount
>> > 222 sales*discount
>> > 333 cost-freight
>> > etc...
>> > There are 20 additional line items with different calculations.
>>> > From my SP, how do I incorporate the above to say:
>> > SELECT ??
>> > INTO #tmp1
>> > FROM GL_ACCOUNT GL,
>> > ITEMS I
>> > WHERE GL.ITEM_NO = I.ITEM_NO
>>> > TIA!
>> > Bob
>>>>>>>
>>|||Have you ever had a software engineering course? Probably not, because
this approach is a violaiton of the principle of cohesion and it
confuses data and code.

Help Needed in Approach for Error Output In SSIS

Hi i have a issue i am loading data from a flat file into a relational Database and i am loading the data without dropping the Primary and Foreign Key constraints and i am sending the error rows into a error table for each table. This is becoming large over head is there a way to Load the entire error Row as a single Column in one Error Table.

This is occuring coz the input is being parsed and its dividing into columns,

One approach would be to used Derived Column Transformation and Substring all columns but it makes to writing long substring statement as some tables have 80 columns ,

is there a better way to handle these errors so that they can be looked at and Changes can be made to the Data ,

Also one more question is is there a way to Load in case of Duplicates load only the Recent Row By Date Column.

and send the old row according to Date into Error output.

Please suggest me what approach should i go for

Not really

You are best to create individual error tables. It means its easier for the user to see what the data is because it will be correctly split up.

|||

For your first question - why not use a error table with "row number" and "table name" columns and then rather than dumping the whole row into the error table -- log the row number and table name so that you can go back to the exact row and find the data you are having problem with. As the error table now has a standard structure (2 columns), you can use the same error table for logging errors for all the tables you are loading.

For you second question - You are trying to clean up the data (De-Duplication) so its better to stage the tables you are having duplicates in and then load it into the database after deleting the duplicate rows. This is hard to be done in the data-flow task because the order of the data coming in might not be based on date.

|||Another approach to de-duping rows is to use the Rank function - You can find an example using T-SQL here: http://rafael-salas.blogspot.com/2007/04/remove-duplicates-using-t-sql-rank.html and one with a custom component here: http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Rank-Transform.aspx

On the single column to capture an error, I have seen this implemented by using a text or xml column and generically creating the string to insert based on the row data. However, as Simon noted, this isn't typically very readable, and may require a seperate application to support reviewing the errors.

|||

is there way to log the record number in the Flatfile where the error is occuring and is there a way to log the Error Code and Error Description . Can u please guide me regarding this.

|||I need to capture the Database, Table, Input File Name, Input File Record Number; Key Data (does not need to be entire record), Error Code, Error Desc, Date and Time, SSIS Package/Program|||Basically you need to generate a rowNumber column in the dataflow. There are some samples:

http://support.microsoft.com/kb/908460

http://www.sqlis.com/93.aspx|||Thanks for the information it was very helpful i was thinking to capture the sorce filename and Destination table name too into the error table is there a way to capture this information.|||If you store those values in variables, you can use a derived column transform to bring them into the error flow.|||I am aware of this approach but is there way to store these values into variables , my connection managers are Dynamic i.e from a configurations table in sql server. is there way to capture the src and output names atleast in variable s to write it to error table|||

Dev2624 wrote:

Thanks for the information it was very helpful i was thinking to capture the sorce filename and Destination table name too into the error table is there a way to capture this information.

Why are you creating new threads on this topic? http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2195648&SiteID=1|||Phil's suggestion in the other thread is the same thing I was going to recommend.

Help Needed in Approach for Error Output In SSIS

Hi i have a issue i am loading data from a flat file into a relational Database and i am loading the data without dropping the Primary and Foreign Key constraints and i am sending the error rows into a error table for each table. This is becoming large over head is there a way to Load the entire error Row as a single Column in one Error Table.

This is occuring coz the input is being parsed and its dividing into columns,

One approach would be to used Derived Column Transformation and Substring all columns but it makes to writing long substring statement as some tables have 80 columns ,

is there a better way to handle these errors so that they can be looked at and Changes can be made to the Data ,

Also one more question is is there a way to Load in case of Duplicates load only the Recent Row By Date Column.

and send the old row according to Date into Error output.

Please suggest me what approach should i go for

Not really

You are best to create individual error tables. It means its easier for the user to see what the data is because it will be correctly split up.

|||

For your first question - why not use a error table with "row number" and "table name" columns and then rather than dumping the whole row into the error table -- log the row number and table name so that you can go back to the exact row and find the data you are having problem with. As the error table now has a standard structure (2 columns), you can use the same error table for logging errors for all the tables you are loading.

For you second question - You are trying to clean up the data (De-Duplication) so its better to stage the tables you are having duplicates in and then load it into the database after deleting the duplicate rows. This is hard to be done in the data-flow task because the order of the data coming in might not be based on date.

|||Another approach to de-duping rows is to use the Rank function - You can find an example using T-SQL here: http://rafael-salas.blogspot.com/2007/04/remove-duplicates-using-t-sql-rank.html and one with a custom component here: http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Rank-Transform.aspx

On the single column to capture an error, I have seen this implemented by using a text or xml column and generically creating the string to insert based on the row data. However, as Simon noted, this isn't typically very readable, and may require a seperate application to support reviewing the errors.

|||

is there way to log the record number in the Flatfile where the error is occuring and is there a way to log the Error Code and Error Description . Can u please guide me regarding this.

|||I need to capture the Database, Table, Input File Name, Input File Record Number; Key Data (does not need to be entire record), Error Code, Error Desc, Date and Time, SSIS Package/Program|||Basically you need to generate a rowNumber column in the dataflow. There are some samples:

http://support.microsoft.com/kb/908460

http://www.sqlis.com/93.aspx|||Thanks for the information it was very helpful i was thinking to capture the sorce filename and Destination table name too into the error table is there a way to capture this information.|||If you store those values in variables, you can use a derived column transform to bring them into the error flow.|||I am aware of this approach but is there way to store these values into variables , my connection managers are Dynamic i.e from a configurations table in sql server. is there way to capture the src and output names atleast in variable s to write it to error table|||

Dev2624 wrote:

Thanks for the information it was very helpful i was thinking to capture the sorce filename and Destination table name too into the error table is there a way to capture this information.

Why are you creating new threads on this topic? http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2195648&SiteID=1|||Phil's suggestion in the other thread is the same thing I was going to recommend.