Losing my margin!?

I am designing a report to output address labels (Avery 5160) and the
spacing of the data in the columns and the rows descrease as the print moves
down the page. By the final row of labels, the name line is in the row
preceeding row. I am using a list object and the margin settings are per
Avery's spec sheet.
Anyone else experience this and have a fix?
Thanks,
AndyWhat rendering output are you using? My guess is you'll have your best luck
with PDF or TIFF. HTML is pretty non-deterministic.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Andrew King" <acking@.cal.ameren.com> wrote in message
news:%23DfU0Z0IFHA.1176@.TK2MSFTNGP15.phx.gbl...
>I am designing a report to output address labels (Avery 5160) and the
>spacing of the data in the columns and the rows descrease as the print
>moves down the page. By the final row of labels, the name line is in the
>row preceeding row. I am using a list object and the margin settings are
>per Avery's spec sheet.
> Anyone else experience this and have a fix?
> Thanks,
> Andy
>|||I am using PDF. I have also tried to fix the size of the fields; unchecked
the "Can increase to accommodate contents".
Andy
"Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
news:%23869bC3IFHA.4028@.tk2msftngp13.phx.gbl...
> What rendering output are you using? My guess is you'll have your best
> luck with PDF or TIFF. HTML is pretty non-deterministic.
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "Andrew King" <acking@.cal.ameren.com> wrote in message
> news:%23DfU0Z0IFHA.1176@.TK2MSFTNGP15.phx.gbl...
>>I am designing a report to output address labels (Avery 5160) and the
>>spacing of the data in the columns and the rows descrease as the print
>>moves down the page. By the final row of labels, the name line is in the
>>row preceeding row. I am using a list object and the margin settings are
>>per Avery's spec sheet.
>> Anyone else experience this and have a fix?
>> Thanks,
>> Andy
>|||Solved! Placed the list object inside a rectangle to fix the size of the
label and removed the right and bottom padding from the field.
"Andrew King" <acking@.cal.ameren.com> wrote in message
news:e4hyBC$IFHA.2844@.TK2MSFTNGP10.phx.gbl...
>I am using PDF. I have also tried to fix the size of the fields; unchecked
>the "Can increase to accommodate contents".
> Andy
> "Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
> news:%23869bC3IFHA.4028@.tk2msftngp13.phx.gbl...
>> What rendering output are you using? My guess is you'll have your best
>> luck with PDF or TIFF. HTML is pretty non-deterministic.
>> --
>> Cheers,
>> '(' Jeff A. Stucker
>> \
>> Business Intelligence
>> www.criadvantage.com
>> ---
>> "Andrew King" <acking@.cal.ameren.com> wrote in message
>> news:%23DfU0Z0IFHA.1176@.TK2MSFTNGP15.phx.gbl...
>>I am designing a report to output address labels (Avery 5160) and the
>>spacing of the data in the columns and the rows descrease as the print
>>moves down the page. By the final row of labels, the name line is in the
>>row preceeding row. I am using a list object and the margin settings are
>>per Avery's spec sheet.
>> Anyone else experience this and have a fix?
>> Thanks,
>> Andy
>>
>

Losing Format When Exporting to PDF

I have a report with a table. Some of the table header columns are
multi-worded and wrap on to a second line. For the table header columns that
are not multi-worded, I want the text to appear on the second line and not
the first line which is the default.
If I use the vbscript function â'vbcrlfâ', it renders perfectly in HTML.
However, I want to render in PDF and for some reason when it renders in PDF
the renderer is ignoring that particular formatting and so the single-worded
column text appears on the first line.
Any ideaâ's?
--
MikeOn Dec 10, 1:49 pm, Mike DeYoung
<MikeDeYo...@.discussions.microsoft.com> wrote:
> I have a report with a table. Some of the table header columns are
> multi-worded and wrap on to a second line. For the table header columns that
> are not multi-worded, I want the text to appear on the second line and not
> the first line which is the default.
> If I use the vbscript function "vbcrlf", it renders perfectly in HTML.
> However, I want to render in PDF and for some reason when it renders in PDF
> the renderer is ignoring that particular formatting and so the single-worded
> column text appears on the first line.
> Any idea's?
> --
> Mike
If I'm understand you correctly, you should be able to set the
vertical alignment of the table header column via selecting the header
row on the left-hand-side of the table control, then select F4 (for
the Properties window) and to the right of 'Vertical Align' select
'Bottom.' Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Thank you Enrique.
--
Mike
"EMartinez" wrote:
> On Dec 10, 1:49 pm, Mike DeYoung
> <MikeDeYo...@.discussions.microsoft.com> wrote:
> > I have a report with a table. Some of the table header columns are
> > multi-worded and wrap on to a second line. For the table header columns that
> > are not multi-worded, I want the text to appear on the second line and not
> > the first line which is the default.
> >
> > If I use the vbscript function "vbcrlf", it renders perfectly in HTML.
> > However, I want to render in PDF and for some reason when it renders in PDF
> > the renderer is ignoring that particular formatting and so the single-worded
> > column text appears on the first line.
> >
> > Any idea's?
> >
> > --
> > Mike
>
> If I'm understand you correctly, you should be able to set the
> vertical alignment of the table header column via selecting the header
> row on the left-hand-side of the table control, then select F4 (for
> the Properties window) and to the right of 'Vertical Align' select
> 'Bottom.' Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||On Dec 11, 7:31 am, Mike DeYoung
<MikeDeYo...@.discussions.microsoft.com> wrote:
> Thank you Enrique.
> --
> Mike
> "EMartinez" wrote:
> > On Dec 10, 1:49 pm, Mike DeYoung
> > <MikeDeYo...@.discussions.microsoft.com> wrote:
> > > I have a report with a table. Some of the table header columns are
> > > multi-worded and wrap on to a second line. For the table header columns that
> > > are not multi-worded, I want the text to appear on the second line and not
> > > the first line which is the default.
> > > If I use the vbscript function "vbcrlf", it renders perfectly in HTML.
> > > However, I want to render in PDF and for some reason when it renders in PDF
> > > the renderer is ignoring that particular formatting and so the single-worded
> > > column text appears on the first line.
> > > Any idea's?
> > > --
> > > Mike
> > If I'm understand you correctly, you should be able to set the
> > vertical alignment of the table header column via selecting the header
> > row on the left-hand-side of the table control, then select F4 (for
> > the Properties window) and to the right of 'Vertical Align' select
> > 'Bottom.' Hope this helps.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant
You're welcome. Let me know if I can be of further assistance.
Regards,
Enrique Martinez
Sr. Software Consultant

Looping Through Excel Columns (256 columns)

Hello All,

I have a problem and i wish i can get the answers or advices to solve it.

i have like 20 excel files and in each file there is 1 sheet (Planning) . What i need to do is to loop on the on the 20 files (actually this is the easy part and i already done it) the hard part is while looping i need to open each excel file and loop on the 256 columns in it and extract the data from it to a SQL server Database.

Any help will be alot appreciated.

Does each sheet have the same number of columns and the same column names?

|||

Hi, thank you for ur reply, yes the sheets are all the same, same columns name and same column data and same number of columns.

|||

Perfect, then inside your loop you'll need a data flow task. Set the source excel connection manager through a variable and send the data to your destination.

|||

thats if i wanted to extract the whole excel sheet, wht i want to do is looping on the columns(256), loop on each column and extract it where i want.

|||

I don't quite understand. There is an interface to map your source columns to your destination columns.

|||

i found it thank you alot

sql

Looping through a recordser in a stored procedure

I have a table [myOrders] with three columns. One of the columns
contains text output data [myText] nvarchar(500), one of them contains
a filename [myFileName] nvarchar(50), one of the columns is a bit to
record if it has been output yet[isOutput] bit default value = 0.

I am creating a SQL Agent job that needs to look at a recordset of
[myOrders] where [isOutput] = 0 and create a seperate text file for
each row using [myFileName] as the filename.

Then I need to mark [isOutput] of each record in [myOrders] as 1.

Ok, so that's the task...

What I'm thinking is I construct a stored procedure that starts with a
select statement:

Create PROCEDURE JustDoIt
AS
set nocount on

SELECT
myText, myFileName
FROM
myOrders
WHERE
(isOutput = 0)

THEN I USE BCP to create the file looping through the recordset above.
THIS IS THE PART I AM CLUELESS ABOUT!

/* NEED TO LOOP HERE */

DECLARE @.ReturnCode int
DECLARE @.ExportCommand varchar(255)
DECLARE @.FileName nvarchar(50)
DECLARE @.FileText nvarchar (500)

SELECT @.FileName = myFileName

/*THIS SYNTAX IS PROBABLY TOTALLY OUTA WHACK:)

SET @.ExportCommand =
'BCP @.FileText queryout "c:\' +
@.FileName +
'" -T -c -S ' + @.@.SERVERNAME
EXEC @.ReturnCode = master..xp_cmdshell @.ExportCommand

/* NEED TO EXIT LOOP HERE */

Then I update all records in [myOrders] to 1

BEGIN TRANSACTION

UPDATE
myOrders
SET isOutput = 1
WHERE
(isOutput = 0)

/* err checking here */

COMMIT TRANSACTION

I'm hoping someone can help me construct this.
Thanks,
lqLauren Quantrell (laurenquantrell@.hotmail.com) writes:
> I have a table [myOrders] with three columns. One of the columns
> contains text output data [myText] nvarchar(500), one of them contains
> a filename [myFileName] nvarchar(50), one of the columns is a bit to
> record if it has been output yet[isOutput] bit default value = 0.
> I am creating a SQL Agent job that needs to look at a recordset of
> [myOrders] where [isOutput] = 0 and create a seperate text file for
> each row using [myFileName] as the filename.

I'm glad to see that you are exploring Agent!

Did you ever consider of making it an Active-X job step? You could then
use VBscript for the task, and it may be easier to write files from
VBscript. (Then again, I have never used VB-script myself.) You could
also write a command-line program in whatever language you fancy, and
run the step as as CmdExec.

You could do this in T-SQL, by setting up a cursor, but since you
would have to fork out with xp_cmdshell for BCP for each file, there
may be a performance cost. VBscript (or whatever language) would be
more effective.

The cusror solution is fairly straightforward:

DECLARE your_cur INSENSITIVE CURSOR FOR
> SELECT
> myText, myFileName
, OrderID
> FROM
> myOrders
> WHERE
> (isOutput = 0)

OPEN your_cur

WHILE 1 = 1
BEGIN
FETCH your_cur INTO @.FileText, @.myFileName, @.orderID
IF @.@.fetch_status <> 0
BREAK

> SET @.ExportCommand =
> 'BCP @.FileText queryout "c:\' +
> @.FileName +
> '" -T -c -S ' + @.@.SERVERNAME
> EXEC @.ReturnCode = master..xp_cmdshell @.ExportCommand

IF @.ReturnCode = 0
UPDATE myOrders SET isOutput = 1 WHERE orderID= @.orderID

END

DEALLOCATE your_cur

Now, as it written above, it assumes that @.FileText is a query, but
from your narrative, I believe it is just a file. You could make it
a query by

SELECT @.filetext = 'SELECT ''' + replace(@.filetext, '''', ''') + ''''

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland,
Thanks very much for this code. Hopefully I can construct this from
this foundation.
Question though, can I avoid using cursors by taking advantage of BCP
parameters firstrow, lastrow and batchsize parameters so that I output
one row at a time of a variable row recordset? This would be my
first option.
Thanks,
LQ

Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns95316883662FYazorman@.127.0.0.1>...
> Lauren Quantrell (laurenquantrell@.hotmail.com) writes:
> > I have a table [myOrders] with three columns. One of the columns
> > contains text output data [myText] nvarchar(500), one of them contains
> > a filename [myFileName] nvarchar(50), one of the columns is a bit to
> > record if it has been output yet[isOutput] bit default value = 0.
> > I am creating a SQL Agent job that needs to look at a recordset of
> > [myOrders] where [isOutput] = 0 and create a seperate text file for
> > each row using [myFileName] as the filename.
> I'm glad to see that you are exploring Agent!
> Did you ever consider of making it an Active-X job step? You could then
> use VBscript for the task, and it may be easier to write files from
> VBscript. (Then again, I have never used VB-script myself.) You could
> also write a command-line program in whatever language you fancy, and
> run the step as as CmdExec.
> You could do this in T-SQL, by setting up a cursor, but since you
> would have to fork out with xp_cmdshell for BCP for each file, there
> may be a performance cost. VBscript (or whatever language) would be
> more effective.
> The cusror solution is fairly straightforward:
> DECLARE your_cur INSENSITIVE CURSOR FOR
> > SELECT
> > myText, myFileName
> , OrderID
> > FROM
> > myOrders
> > WHERE
> > (isOutput = 0)
> OPEN your_cur
> WHILE 1 = 1
> BEGIN
> FETCH your_cur INTO @.FileText, @.myFileName, @.orderID
> IF @.@.fetch_status <> 0
> BREAK
> > SET @.ExportCommand =
> > 'BCP @.FileText queryout "c:\' +
> > @.FileName +
> > '" -T -c -S ' + @.@.SERVERNAME
> > EXEC @.ReturnCode = master..xp_cmdshell @.ExportCommand
> IF @.ReturnCode = 0
> UPDATE myOrders SET isOutput = 1 WHERE orderID= @.orderID
> END
> DEALLOCATE your_cur
> Now, as it written above, it assumes that @.FileText is a query, but
> from your narrative, I believe it is just a file. You could make it
> a query by
> SELECT @.filetext = 'SELECT ''' + replace(@.filetext, '''', ''') + ''''|||Erland, Thanks again for your time.
I want to do this without using a cursor, instead use select top 1 of
the recordset and loop through BCP until there are no more records.

Looks something like

SELECT top1 OrderID, myText, myFileName from tblOrders where isOutput
= 0

>>WHatI need here is to figure out how to extract the value of
myFileName and myText and pass it to the BCP Utility<<

Do until there's no more records in select statement above:

SET @.ExportCommand = 'BCP myText queryout "c:\' + myFileName+ '" -T -c
-S ' + @.@.SERVERNAME
EXEC @.ReturnCode = master..xp_cmdshell @.ExportCommand

UPDATE myOrders SET isOutput = 1 WHERE orderID= @.orderID

Loop

Sorry for being so dumb about this. I have never used this sort of
construction before.

Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns95316883662FYazorman@.127.0.0.1>...
> Lauren Quantrell (laurenquantrell@.hotmail.com) writes:
> > I have a table [myOrders] with three columns. One of the columns
> > contains text output data [myText] nvarchar(500), one of them contains
> > a filename [myFileName] nvarchar(50), one of the columns is a bit to
> > record if it has been output yet[isOutput] bit default value = 0.
> > I am creating a SQL Agent job that needs to look at a recordset of
> > [myOrders] where [isOutput] = 0 and create a seperate text file for
> > each row using [myFileName] as the filename.
> I'm glad to see that you are exploring Agent!
> Did you ever consider of making it an Active-X job step? You could then
> use VBscript for the task, and it may be easier to write files from
> VBscript. (Then again, I have never used VB-script myself.) You could
> also write a command-line program in whatever language you fancy, and
> run the step as as CmdExec.
> You could do this in T-SQL, by setting up a cursor, but since you
> would have to fork out with xp_cmdshell for BCP for each file, there
> may be a performance cost. VBscript (or whatever language) would be
> more effective.
> The cusror solution is fairly straightforward:
> DECLARE your_cur INSENSITIVE CURSOR FOR
> > SELECT
> > myText, myFileName
> , OrderID
> > FROM
> > myOrders
> > WHERE
> > (isOutput = 0)
> OPEN your_cur
> WHILE 1 = 1
> BEGIN
> FETCH your_cur INTO @.FileText, @.myFileName, @.orderID
> IF @.@.fetch_status <> 0
> BREAK
> > SET @.ExportCommand =
> > 'BCP @.FileText queryout "c:\' +
> > @.FileName +
> > '" -T -c -S ' + @.@.SERVERNAME
> > EXEC @.ReturnCode = master..xp_cmdshell @.ExportCommand
> IF @.ReturnCode = 0
> UPDATE myOrders SET isOutput = 1 WHERE orderID= @.orderID
> END
> DEALLOCATE your_cur
> Now, as it written above, it assumes that @.FileText is a query, but
> from your narrative, I believe it is just a file. You could make it
> a query by
> SELECT @.filetext = 'SELECT ''' + replace(@.filetext, '''', ''') + ''''|||I realize there is a cost to using cursors, but since you're going
to launch the command shell for BCP on every record, the cost of the
cursor is probably insignificant. But if you insist...

> Erland, Thanks again for your time.
> I want to do this without using a cursor, instead use select top 1 of
> the recordset and loop through BCP until there are no more records.
> Looks something like

WHILE 1=1 BEGIN
SELECT TOP 1 @.OrderID=OrderID,@.MyText=myText,@.MyFileName=myFile Name
from tblOrders where isOutput = 0

IF @.@.ROWCOUNT = 0 BREAK
> SET @.ExportCommand = 'BCP '+@.myText+' queryout "c:\' + @.myFileName+ '" -T -c
> -S ' + @.@.SERVERNAME
> EXEC @.ReturnCode = master..xp_cmdshell @.ExportCommand
> UPDATE myOrders SET isOutput = 1 WHERE orderID= @.orderID
END -- end of loop|||[I'm answering to Jim's post, since Lauren's has not made it here yet. My
ISP reconfigured the news server and it took them two days to realize
that it was no longer working.]

Jim Geissman (jim_geissman@.countrywide.com) writes:
> I realize there is a cost to using cursors, but since you're going
> to launch the command shell for BCP on every record, the cost of the
> cursor is probably insignificant. But if you insist...

Why Laruen does not want to use a cursor I don't know, but since he
has to iterate anyway, cursor is the best solution for iteration anyway.

Say that you instead do:

> SELECT TOP 1 @.OrderID=OrderID,@.MyText=myText,@.MyFileName=myFile Name
> from tblOrders where isOutput = 0

If there is no index on isOutput (and one would not expect that),
and the table is huge, this can be very expensive. I have no benchmarks,
but I would suggest that for an iteration a cursor is the best way to
go, although it depends on the cursor type. FAST_FORWARD may be the
fastest, but I always use INSENSITIVE.

Anyway, Laruen should not do this in T-SQL at all, he should use VBscript
or similar as I suggested in my previous post. It will be easier to
program and execute faster.

Also, it occurred to me that if tblOrders.myText is the text that is
to be written to the file, the QueryOut thing will not work, since the
newlines in myText causes problem. Then again if the query for queryout is

'SELECT myText FROM tblOrders = ' + str(@.orderid)

that will work.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland (and Jim)
You guys have gone above and beyond the call of duty in your response
to my problem and because of your repsonses I have been able to roll
this out. Thanks a million, and I have only one correction for
Erland's post - substitute "she" for "he" and it's 100% correct!
Thanks,
lq

Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns9534EF744A28AYazorman@.127.0.0.1>...
> [I'm answering to Jim's post, since Lauren's has not made it here yet. My
> ISP reconfigured the news server and it took them two days to realize
> that it was no longer working.]
> Jim Geissman (jim_geissman@.countrywide.com) writes:
> > I realize there is a cost to using cursors, but since you're going
> > to launch the command shell for BCP on every record, the cost of the
> > cursor is probably insignificant. But if you insist...
> Why Laruen does not want to use a cursor I don't know, but since he
> has to iterate anyway, cursor is the best solution for iteration anyway.
> Say that you instead do:
> > SELECT TOP 1 @.OrderID=OrderID,@.MyText=myText,@.MyFileName=myFile Name
> > from tblOrders where isOutput = 0
> If there is no index on isOutput (and one would not expect that),
> and the table is huge, this can be very expensive. I have no benchmarks,
> but I would suggest that for an iteration a cursor is the best way to
> go, although it depends on the cursor type. FAST_FORWARD may be the
> fastest, but I always use INSENSITIVE.
> Anyway, Laruen should not do this in T-SQL at all, he should use VBscript
> or similar as I suggested in my previous post. It will be easier to
> program and execute faster.
> Also, it occurred to me that if tblOrders.myText is the text that is
> to be written to the file, the QueryOut thing will not work, since the
> newlines in myText causes problem. Then again if the query for queryout is
> 'SELECT myText FROM tblOrders = ' + str(@.orderid)
> that will work.|||Lauren Quantrell (laurenquantrell@.hotmail.com) writes:
> You guys have gone above and beyond the call of duty in your response
> to my problem and because of your repsonses I have been able to roll
> this out. Thanks a million, and I have only one correction for
> Erland's post - substitute "she" for "he" and it's 100% correct!

Glad to hear that you got it working! And my cheeks blossom in embarrassment
for calling you a man. I remember it to next time.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

Looping in a query

Hello Everybody,

I have a requirement where the query needs to looped.

I have a table f553002A. In this table i have iykit,iyitm as columns.

for every iykit,there can be a set of iyitm

like this

select KIT, ITM from F553002A where KIT = 11758

KIT ITM

11758 11494

11758 11705

11758 11713

11758 11868

Again for every ITM as KIT in the same table ,there can have set of values(ITM) like this

select KIT, ITM from F553002A where KIT = 11713 (parent’s ITM)

KIT ITM

11713.0 2917.0
11713.0 2910.0
11713.0 5013.0
11713.0 2890.0
11713.0 2873.0
11713.0 2843.0
11713.0 2856.0
11713.0 2836.0
11713.0 2879.0
11713.0 7974.0
11713.0 2789.0
11713.0 2842.0
11713.0 2915.0
11713.0 2885.0
11713.0 2908.0
11713.0 2858.0
11713.0 2871.0
11713.0 2841.0

the same with 11494,11705,11868.

Again 2917.0 (ITM) in the above example as KIT can have another set of ITM's in the same table

I want all the END ITM's along with there Grand parent KIT and Immediate Parent Kit returned in a result set.

For Ex

11758 11713.0 2917.0

....

...

..

.

11758 11494 ...........................

........

...

..

.

However i know i can achieve this with Stored procdeure. I want this to be implemented in SQL Query where i can embeed it in a VBscript

Please help me in writing the query for this. Thanks in advance

We are using sql server 2000. not 2005

here you go..

Code Snippet

Create Table #f553002a (

[KIT] int ,

[ITM] int

);

Insert Into #f553002a Values(1,2);

Insert Into #f553002a Values(1,3);

Insert Into #f553002a Values(1,4);

Insert Into #f553002a Values(1,5);

Insert Into #f553002a Values(2,6);

Insert Into #f553002a Values(3,8);

Insert Into #f553002a Values(4,9);

Insert Into #f553002a Values(5,10);

Insert Into #f553002a Values(6,11);

Insert Into #f553002a Values(7,12);

Insert Into #f553002a Values(8,13);

Insert Into #f553002a Values(9,14);

Insert Into #f553002a Values(10,2);

Insert Into #f553002a Values(11,3);

;With CTE(KIT,ITM,Level)

as

(

Select KIT,ITM,1 Level from #f553002a Where KIT = 3 --@.StartKitValue

Union ALL

Select Child.KIT,Child.ITM,Main.Level + 1 From CTE Main

Join #f553002a Child On Main.Itm = Child.Kit

)

Select KIT,ITM From CTE Order By Level

|||

Can any body through a light why the query below is returning syntax error

With CTE(IYKIT,IYITM,Level)

as

(

Select IYKIT,IYITM,1 Level from f553002a Where IYKIT = 11758 --@.StartKitValue

Union ALL

Select Child.IYKIT,Child.IYITM,Main.Level + 1 From CTE Main

Join f553002a Child On Main.IYItm = Child.IYKit

)

Select IYKIT,IYITM From CTE Order By Level

Msg 156, Level 15, State 1, Line 1

Incorrect syntax near the keyword 'With'.

I am running this on sql server 2005 Management studio.

How ever in my question,i requested for a solution which can work in SQL server 2000, but this works only in sql server 2005

|||

CTE only work with SQL Server 2005. You missed the semicolon ; before the WITH.

In 2000 you can use recursive sp call... let me try this..

sql

Looping columns in instead of trigger

I have the following view (vProcurementPlan)

SELECT dbo.tblProcurementPlan.*, dbo.tblRequisition.RequisitionID AS
ReqReqID, dbo.tblRequisition.ReqNo AS ReqNo, dbo.tblRequisition.Am AS Am,
dbo.tblRequisition.ROS AS ROS,
dbo.tblRequisition.ActivityID AS ActivityID, dbo.tblRequisition.ProjectID AS
ProjectID
FROM dbo.tblProcurementPlan INNER JOIN
dbo.tblRequisition ON
dbo.tblProcurementPlan.RequisitionID = dbo.tblRequisition.RequisitionID

If I try inserting a record from Access it complains about multiple base
tables, I'm happy to write an "instead of" trigger and handle the 5 columns
from tblRequisition but as it contains all columns from tblProcurementPlan I
don't want to have to list them separately in any insert or update
statement.

The idea is that a record will be inserted into both tables simultaneously
upon insert to the view.Trev@.Work (bouncer@.localhost) writes:
> I have the following view (vProcurementPlan)
> SELECT dbo.tblProcurementPlan.*, dbo.tblRequisition.RequisitionID AS
> ReqReqID, dbo.tblRequisition.ReqNo AS ReqNo, dbo.tblRequisition.Am AS Am,
> dbo.tblRequisition.ROS AS ROS,
> dbo.tblRequisition.ActivityID AS ActivityID, dbo.tblRequisition.ProjectID
> AS ProjectID
> FROM dbo.tblProcurementPlan INNER JOIN
> dbo.tblRequisition ON
> dbo.tblProcurementPlan.RequisitionID = dbo.tblRequisition.RequisitionID
> If I try inserting a record from Access it complains about multiple base
> tables, I'm happy to write an "instead of" trigger and handle the 5
> columns from tblRequisition but as it contains all columns from
> tblProcurementPlan I don't want to have to list them separately in any
> insert or update statement.

I am afraid you don't have much choice.

Besides, in my opinion SELECT * does not belong in production code.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Try not using the * and referencing all the column names explicitly.
It should insert without a problem.
eg
SELECT pp.RequisitionID ppreqid, pp.ReqNo ppReqNo, pp.Am AS Am, ppROS
AS ROS, pp.ActivityID ppAcID, pp.ProjectID ppProjID,rq.RequisitionID
rqReqId, rq.ReqNo rqReqNo, rq.Am Am, rqROS ROS, rq.ActivityID rqAcID,
rq.ProjectID rqProjID
FROM dbo.tblProcurementPlan pp INNER JOIN dbo.tblRequisition rq ON
pp.RequisitionID = rq.RequisitionID

Pachydermitis

"Trev@.Work" <bouncer@.localhost> wrote in message news:<3fddeed0$0$13894$afc38c87@.news.easynet.co.uk>...
> I have the following view (vProcurementPlan)
> SELECT dbo.tblProcurementPlan.*, dbo.tblRequisition.RequisitionID AS
> ReqReqID, dbo.tblRequisition.ReqNo AS ReqNo, dbo.tblRequisition.Am AS Am,
> dbo.tblRequisition.ROS AS ROS,
> dbo.tblRequisition.ActivityID AS ActivityID, dbo.tblRequisition.ProjectID AS
> ProjectID
> FROM dbo.tblProcurementPlan INNER JOIN
> dbo.tblRequisition ON
> dbo.tblProcurementPlan.RequisitionID = dbo.tblRequisition.RequisitionID
> If I try inserting a record from Access it complains about multiple base
> tables, I'm happy to write an "instead of" trigger and handle the 5 columns
> from tblRequisition but as it contains all columns from tblProcurementPlan I
> don't want to have to list them separately in any insert or update
> statement.
> The idea is that a record will be inserted into both tables simultaneously
> upon insert to the view.

Loop through Query

Out of one messed up table I need to create two related tables. I am stuck in trying to get 'some' of the columns from the messed up table to the new table.

How can I select only the columns I need and insert them in the new table?

Thanks in advance!

Hi There,

I done something similar to thi before and i found this article is helpful.

http://www.sql-server-performance.com/dp_no_cursors.asp

loop through each table of my DB

Is there a way to loop using a cursor in SQL-server so i can see if each columns of each tables that i loop through my DB have a specific string value and change it to something else, renaming the column if the match if correct.

any threads that i can read from or website..

thanx !!Won't you get there using:SELECT * FROM INFORMATION_SCHEMA.COLUMNS-PatP|||Table name = nMontantBilletTVQ
Table name = nMontantBilletTPS

here an example of what i try to find.

i would like to search the word 'TVQ' in my string 'nMontantBilletTVQ'

is there a string functions for that ?? just cant get one working

thanx|||Use LIKE.

-PatP

Loop through all tables and columns

Is there a way to change the collation against all the tables and columns within a database?Easy way? No. Best way is probably to select from the information_schema views, and generate a set of alter table commands. Save that script, in case you need it again. I don't have the syntax on hand, but I think

alter table blah alter column yadda varchar(nnn) collate SQL_Latin1...

In order to get the data type correct, you can disect the sp_help stored procedure to see how MS does it.|||Is there a way to change the collation against all the tables and columns within a database?

look this example query,This query will generate 'alter table' statement.u can specify ur filter condition in where clause for ur specfic requirment.Use this code by your own risk.

SELECT
'ALTER TABLE ' + TABLE_NAME +
' ALTER COLUMN ' + COLUMN_NAME +
' ' + DATA_TYPE +' '+
CASE WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL
THEN '('+(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH)+')' )
ELSE isnull(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH),' ')
END
+' COLLATE SQL_Latin1_General_CP850_BIN '+
CASE IS_NULLABLE
WHEN 'YES' THEN 'NULL'
WHEN 'No' THEN 'NOT NULL'
END

FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE IN ('varchar' ,'char','nvarchar','nchar')

loop through a tables columns

is there a way using t-sql to loop through a tables columns?
i want to build a long string with this type of logic
foreach (column in table.columns)
{
strValues += column.name + "=" + row.value
}
thank you for your helpAbraham,
Can you give me some sample data and desired results to work with?
Not exactly sure what you're asking for.
HTH
Jerry
"Abraham Andres Luna" <abe@.rdk.com> wrote in message
news:e8wfGkB0FHA.2460@.TK2MSFTNGP10.phx.gbl...
> is there a way using t-sql to loop through a tables columns?
> i want to build a long string with this type of logic
> foreach (column in table.columns)
> {
> strValues += column.name + "=" + row.value
> }
> thank you for your help
>|||What are you trying to accomplish? Is this supposed to be some kind of
data interchange format? If so, how about using SELECT ... FOR XML to
generate an XML fragment instead?
If you really want to do this dynamically then take a look at this proc
for inspiration:
http://vyaskn.tripod.com/code/generate_inserts.txt
David Portas
SQL Server MVP
--|||Abraham Andres Luna wrote:
> is there a way using t-sql to loop through a tables columns?
> i want to build a long string with this type of logic
> foreach (column in table.columns)
> {
> strValues += column.name + "=" + row.value
> }
>
No, you will need to use a client tool for this. The best you can do in
T-SQL is (air code):
select 'col1=' + cast(col1 as varchar) + ... + '; colN=' + colN from table
I know you want to avoid this exercise, but the only way would be to use a
client tool: for example: an ADO Recordset.
Oh, I suppose you could go to a great deal of trouble to create a dynamic
sql statement by looping through the result of querying the
information_schema.columns view, but I would not advise this.
Bob Barrows
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||ty for the answer, that proc was a big help
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1129225553.008794.38510@.g14g2000cwa.googlegroups.com...
> What are you trying to accomplish? Is this supposed to be some kind of
> data interchange format? If so, how about using SELECT ... FOR XML to
> generate an XML fragment instead?
> If you really want to do this dynamically then take a look at this proc
> for inspiration:
> http://vyaskn.tripod.com/code/generate_inserts.txt
> --
> David Portas
> SQL Server MVP
> --
>

Loop through a recordset to populate columns in a temp table

I want to take the contents from a table of appointments and insert the
appointments for any given month into a temp table where all the
appointments for each day are inserted into a single row with a column
for each day of the month.
Is there a simple way to do this?

I have a recordset that looks like:

SELECT
a.Date,
a.Client --contents: Joe, Frank, Fred, Pete, Oscar
FROM
dbo.tblAppointments a
WHERE
a.date between ...(first and last day of the selected month)

What I want to do is to create a temp table that has 31 columns
to hold appointments and insert into each column any appointments for
the date...

CREATE TABLE #Appointments (id int identity, Day1 nvarchar(500), Day2
nvarchar(500), Day3 nvarchar(500), etc...)

Then loop through the recordset above to insert into Day1, Day 2, Day3,
etc. all the appointments for that day, with multiple appointments
separated by a comma.

INSERT INTO
#Appointments(Day1)
SELECT
a.Client
FROM
dbo.tblAppointments a
WHERE
a.date = (...first day of the month)

(LOOP to Day31)

The results would look like
Day1 Day2 Day3 ...
Row1 Joe, Pete
Frank,
Fred

Maybe there's an even better way to handle this sort of situation?
Thanks,
lqYou can use a query to do reports like this without looping or temp tables.
As specified it seems a little strange to list the dates horizontally since
there is apparently no data on the vertical axis but the rest is really just
a matter of formatting once you have the basic query. Formatting is best
done client-side rather than in the database.

DECLARE @.dt DATETIME
/* First date of the month */
SET @.dt = '20050501'

SELECT
MAX(CASE WHEN DATEDIFF(DAY,@.dt,date)=0 THEN client END),
MAX(CASE WHEN DATEDIFF(DAY,@.dt,date)=1 THEN client END),
MAX(CASE WHEN DATEDIFF(DAY,@.dt,date)=2 THEN client END),
...
MAX(CASE WHEN DATEDIFF(DAY,@.dt,date)=30 THEN client END)
FROM tblAppointments
WHERE date >= @.dt
AND date < DATEADD(MONTH,1,@.dt)
GROUP BY client

--
David Portas
SQL Server MVP
--|||laurenq uantrell wrote:
> I want to take the contents from a table of appointments and insert the
> appointments for any given month into a temp table where all the
> appointments for each day are inserted into a single row with a column
> for each day of the month.
> Is there a simple way to do this?
> I have a recordset that looks like:
> SELECT
> a.Date,
> a.Client --contents: Joe, Frank, Fred, Pete, Oscar
> FROM
> dbo.tblAppointments a
> WHERE
> a.date between ...(first and last day of the selected month)
> What I want to do is to create a temp table that has 31 columns
> to hold appointments and insert into each column any appointments for
> the date...
> CREATE TABLE #Appointments (id int identity, Day1 nvarchar(500), Day2
> nvarchar(500), Day3 nvarchar(500), etc...)
> Then loop through the recordset above to insert into Day1, Day 2, Day3,
> etc. all the appointments for that day, with multiple appointments
> separated by a comma.
> INSERT INTO
> #Appointments(Day1)
> SELECT
> a.Client
> FROM
> dbo.tblAppointments a
> WHERE
> a.date = (...first day of the month)
> (LOOP to Day31)
>
> The results would look like
> Day1 Day2 Day3 ...
> Row1 Joe, Pete
> Frank,
> Fred
> Maybe there's an even better way to handle this sort of situation?
> Thanks,
> lq

You're talking about crosstab queries. Here's a page of links that may
be of use:

http://www.google.com/custom?q=cros...ID%3A1%3B&hl=en

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)|||laurenq uantrell (laurenquantrell@.hotmail.com) writes:
> I want to take the contents from a table of appointments and insert the
> appointments for any given month into a temp table where all the
> appointments for each day are inserted into a single row with a column
> for each day of the month.
> Is there a simple way to do this?
>...
> Then loop through the recordset above to insert into Day1, Day 2, Day3,
> etc. all the appointments for that day, with multiple appointments
> separated by a comma.

I'm a afraid that loop is what you will have to do. And write 31
UPDATE statements, one for each day of the month. There are ways to
build comma-separated lists with set-based statements, but the
methods used are unsupported and undefined, and cannot be trusted.
So the only way to build a CSV is to run a cursor.

OK, you don't really need 31 UPDATE statements. You could aggregate
data into a table with one per date, and then at then end run a
31-way self cross-join to produce the final result.

Certainly, a client program is much better apt to do this sort
of thing.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||David, thanks for that. I thought it must be pretty straignt forward.
The reason I want it all in one row and the days data in columns is
that I want to populate a sincle record on a form that resembles a
monthly calendar but cwhich in fact is simply 31 text boxes. Previously
I had a form that populated 31 different subforms by looping through
the dates on the client side and it is too slow.THanks,
lq|||David, thanks for that. I thought it must be pretty straight forward.
The reason I want it all in one row and the days data in columns is
that I want to populate a sincle record on a form that resembles a
monthly calendar but cwhich in fact is simply 31 text boxes. Previously
I had a form that populated 31 different subforms by looping through
the dates on the client side and it is too slow.THanks,
lq|||Erland,
My solution is to run a UDFwithin a View that creates the comma
separated list for each date of appointments. That part works fine on a
single date, and now I'm just figuring out how to loop through the
dates: first day of the month + 31 days.
Thanks.
lq|||Erland,
You got me in the right direction and the solution works very fast:

The stored procedure:

@.ClienID int,
@.dt datetime /* first day of the selected month */

AS

DECLARE @.dtEnd datetime
SET @.dtEnd = DATEADD(DAY,-1,DATEADD(MONTH,1,@.dt)) /* last day of the
selected month */

SELECT
dbo.fn_ClientSked(@.dt, @.ClientID) AS D1,
dbo.fn_ClientSked(DATEADD(DAY,1,@.dt), @.ClientID) AS D2,
dbo.fn_ClientSked(DATEADD(DAY,2,@.dt), @.ClientID) AS D3,
etc...(for D4-D28)
CASE WHEN DATEADD(DAY,28,@.dt) <= @.dtEnd THEN
dbo.fn_ClientSked(DATEADD(DAY,28,@.dt), @.ClientID) END AS D29,
CASE WHEN DATEADD(DAY,29,@.dt) <= @.dtEnd THEN
dbo.fn_ClientSked(DATEADD(DAY,29,@.dt), @.ClientID) END AS D30,
CASE WHEN DATEADD(DAY,30,@.dt) <= @.dtEnd THEN
dbo.fn_ClientSked(DATEADD(DAY,30,@.dt), @.ClientID) END AS D31

The UDF:

CREATE function dbo.fn_ClientSked(@.dtX as DateTime, @.ClientID as int)
returns
nvarchar(500)
AS
begin
declare @.ret_value nvarchar(500)
SET @.ret_value=''
Select @.ret_value=@.ret_value + '; ' + AppointmentTitle
FROM dbo.tblAppointments
WHERE
tblAppointments.ClientID = @.ClientID
AND
@.dtX Between tblAppointments.StartDate AND tblAppointments.EndDate
RETURN CASE WHEN LEN(@.ret_value)>0 THEN
RIGHT(@.ret_value,LEN(@.ret_value)-2) ELSE '' END
end

Note: This particular UDF returns all appointments by day of the month
for the same client to populate a monthly calendar. It can be easily
modified to show appointments by employee, etc.|||laurenq uantrell (laurenquantrell@.hotmail.com) writes:
> You got me in the right direction and the solution works very fast:
>...
> declare @.ret_value nvarchar(500)
> SET @.ret_value=''
> Select @.ret_value=@.ret_value + '; ' + AppointmentTitle
> FROM dbo.tblAppointments
> WHERE
> tblAppointments.ClientID = @.ClientID
> AND
> @.dtX Between tblAppointments.StartDate AND tblAppointments.EndDate
> RETURN CASE WHEN LEN(@.ret_value)>0 THEN
> RIGHT(@.ret_value,LEN(@.ret_value)-2) ELSE '' END
> end

While it may work and be fast, it relies on undefined behaviour. See
http://support.microsoft.com/default.aspx?scid=287515.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland,
Now you have me worried. Because I am using a UDF within the SELECT
statement? Isn't that what they're for?
I chose this solution because the other solutions I conceived required
31 server calls to populate 31 subforms (one for each day of the week.)
With the method above the data is all shoved down the pipe as one row.
Isn't that a preferable solution where a simple string output is all
that's required for each date?
lq|||laurenq uantrell (laurenquantrell@.hotmail.com) writes:
> Now you have me worried. Because I am using a UDF within the SELECT
> statement? Isn't that what they're for?

No, because of

Select @.ret_value=@.ret_value + '; ' + AppointmentTitle
FROM dbo.tblAppointments

This can give you want you want, or just one appointment. Or something
else. The result of this construct "concatentate aggregation" is not
defined.

If you want to play safe, write a cursor instead. (But you can still
do this in a UDF.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||OK, thanks for the info. I hate messing with cursors...
lqsql

Loop a select statement?

Hi,
I have a select statement which brings back several fields and several
columns. Within one of these columns is an email address. I want to be
able to cycle through each record in the select statement and email the
details attached to their email address. My SP is below:-
SELECT dbo.tbl_surveillance.s_id as REG_NO,
dbo.tbl_surveillance_dates.sd_urn as URN,
dbo.tbl_surveillance_dates.sd_reviewing_officer as OFFICER,
dbo.tbl_surveillance_dates.sd_renewal_date as
RENEWAL_DATE, dbo.tbl_email.e_officer_email AS OFFICER_EMAIL
FROM dbo.tbl_surveillance INNER JOIN
dbo.tbl_surveillance_dates ON
dbo.tbl_surveillance.s_id = dbo.tbl_surveillance_dates.sd_s_id LEFT OUTER
JOIN
dbo.tbl_email ON dbo.tbl_surveillance_dates.sd_e_id =
dbo.tbl_email.e_id
WHERE getdate() > dateadd(day, -7, sd_renewal_date)
I know you can use the following to send emails:-
@.sbj varchar(200),
@.msg varchar(2000),
@.recipient varchar(50)
exec master..xp_sendmail @.recipients= @.recipient, @.subject = @.sbj,
@.message=@.msg
but I want to incorporate that xp_sendmail with the select statement.
If anyone has ever done this before and can give me some pointers it would
be greatly appreciated.
Thanks
DamonI think you will need to use a cursor.
Here's some help with your SELECT statement, by the way. It's still largely
unreadable because of the superfluous prefixes and mismatched column names,
but it should be a little easier to tackle. Note that I changed the WHERE
clause to apply transformation to the constant, instead ofto the column, and
kept with the tradition of object-operator-value instead of
value-operator-object. getdate()>column+x is very difficult to process, at
least for me. If the table is huge, you may find an advantage in declaring
a variable of smalldatetime up front and calculating GETDATE()+7 and storing
it in a constant. However, if there is no index on sd_renewal_date, it's
probably all moot.
SELECT
Reg_No = s.s_id,
URN = d.sd_urn,
OFFICER = d.sd_reviewing_officer,
RENEWAL_DATE = d.sd_renewal_date,
OFFICER_EMAIL = e.e_officer_email
FROM
dbo.tbl_surveillance s
INNER JOIN
dbo.tbl_surveillance_dates d
ON
s.s_id = d.sd_s_id
LEFT OUTER JOIN
dbo.tbl_email e
ON
d.sd_e_id = e.e_id
WHERE
d.sd_renewal_date < GETDATE()+7;
If the cursor's only purpose is to send e-mail, then you probably want an
inner join against tbl_email. What is the point of getting rows where there
isn't a valid recipient?
I'd write the cursor for you, but it is entirely unclear to me how you are
deriving @.sbj and @.msg based on Reg_No, URN, OFFICER, and RENEWAL_DATE.
Please see http://www.aspfaq.com/5006
"Damon" <nonsense@.nononsense.com> wrote in message
news:O6Xnf.19494$8v6.12132@.newsfe6-gui.ntli.net...
> Hi,
> I have a select statement which brings back several fields and several
> columns. Within one of these columns is an email address. I want to be
> able to cycle through each record in the select statement and email the
> details attached to their email address. My SP is below:-
> SELECT dbo.tbl_surveillance.s_id as REG_NO,
> dbo.tbl_surveillance_dates.sd_urn as URN,
> dbo.tbl_surveillance_dates.sd_reviewing_officer as OFFICER,
> dbo.tbl_surveillance_dates.sd_renewal_date as
> RENEWAL_DATE, dbo.tbl_email.e_officer_email AS OFFICER_EMAIL
> FROM dbo.tbl_surveillance INNER JOIN
> dbo.tbl_surveillance_dates ON
> dbo.tbl_surveillance.s_id = dbo.tbl_surveillance_dates.sd_s_id LEFT OUTER
> JOIN
> dbo.tbl_email ON dbo.tbl_surveillance_dates.sd_e_id =
> dbo.tbl_email.e_id
> WHERE getdate() > dateadd(day, -7, sd_renewal_date)
> I know you can use the following to send emails:-
> @.sbj varchar(200),
> @.msg varchar(2000),
> @.recipient varchar(50)
> exec master..xp_sendmail @.recipients= @.recipient, @.subject = @.sbj,
> @.message=@.msg
> but I want to incorporate that xp_sendmail with the select statement.
> If anyone has ever done this before and can give me some pointers it would
> be greatly appreciated.
> Thanks
> Damon
>|||Thank you very much for your reply. I can see why you are a SQL Server MVP.
Unfortunately my works have not supplied me with SQL training so have had to
learn myself, thus the messy code.
The @.sbj would be the same everytime, something like "List of renewal
dates". @.msg would literally be a compilation of the other fields i.e.
Reg_no & ', ' & URN & ', ' & RENEWAL_DATE. This is just so the officer
being emailed can see the list of renewals that they have which are due up
within the next w.
I really appreciate your help. Need to get on to my works to send me on a
SQL course.
Thanks again
Damon.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OrdL3fMAGHA.532@.TK2MSFTNGP15.phx.gbl...
>I think you will need to use a cursor.
> Here's some help with your SELECT statement, by the way. It's still
> largely unreadable because of the superfluous prefixes and mismatched
> column names, but it should be a little easier to tackle. Note that I
> changed the WHERE clause to apply transformation to the constant, instead
> ofto the column, and kept with the tradition of object-operator-value
> instead of value-operator-object. getdate()>column+x is very difficult to
> process, at least for me. If the table is huge, you may find an advantage
> in declaring a variable of smalldatetime up front and calculating
> GETDATE()+7 and storing it in a constant. However, if there is no index
> on sd_renewal_date, it's probably all moot.
> SELECT
> Reg_No = s.s_id,
> URN = d.sd_urn,
> OFFICER = d.sd_reviewing_officer,
> RENEWAL_DATE = d.sd_renewal_date,
> OFFICER_EMAIL = e.e_officer_email
> FROM
> dbo.tbl_surveillance s
> INNER JOIN
> dbo.tbl_surveillance_dates d
> ON
> s.s_id = d.sd_s_id
> LEFT OUTER JOIN
> dbo.tbl_email e
> ON
> d.sd_e_id = e.e_id
> WHERE
> d.sd_renewal_date < GETDATE()+7;
> If the cursor's only purpose is to send e-mail, then you probably want an
> inner join against tbl_email. What is the point of getting rows where
> there isn't a valid recipient?
> I'd write the cursor for you, but it is entirely unclear to me how you are
> deriving @.sbj and @.msg based on Reg_No, URN, OFFICER, and RENEWAL_DATE.
> Please see http://www.aspfaq.com/5006
>
>
> "Damon" <nonsense@.nononsense.com> wrote in message
> news:O6Xnf.19494$8v6.12132@.newsfe6-gui.ntli.net...
>|||I've taken an example cursor from Books Online and adjusted it somewhat to
fit your situation. However, it's just a rough draft and you will need to
complete it. Basically, the cursor allows you to iternate through the query
result one row at a time, giving you the ability to populate variables and
exec the xp_sendmail call. Every column that you plan to reference will need
to be assigned a variable. I've also added the FAST_FORWARD option so it
should use less resources.
DECLARE surveillance_cursor CURSOR FAST_FORWARD FOR
SELECT dbo.tbl_surveillance.s_id as REG_NO,
dbo.tbl_surveillance_dates.sd_urn as URN,
dbo.tbl_surveillance_dates.sd_reviewing_officer as OFFICER,
dbo.tbl_surveillance_dates.sd_renewal_date as
RENEWAL_DATE, dbo.tbl_email.e_officer_email AS OFFICER_EMAIL
FROM dbo.tbl_surveillance INNER JOIN
dbo.tbl_surveillance_dates ON
dbo.tbl_surveillance.s_id = dbo.tbl_surveillance_dates.sd_s_id LEFT OUTER
JOIN
dbo.tbl_email ON dbo.tbl_surveillance_dates.sd_e_id =
dbo.tbl_email.e_id
WHERE getdate() > dateadd(day, -7, sd_renewal_date)
OPEN surveillance_cursor
-- Perform the first fetch and store the values in variables.
-- Note: The variables should be in the same order as the columns in the
SELECT statement.
FETCH NEXT FROM surveillance_cursor
INTO @.recipient, @.Reg_no, @.RENEWAL_DATE, etc.
-- Check @.@.FETCH_STATUS to see if there are any more rows to fetch.
WHILE @.@.FETCH_STATUS = 0
BEGIN
select @.sbj = ?
select @.msg = @.Reg_no + ', ' + @.URN + ', ' + @.RENEWAL_DATE + etc.
exec master..xp_sendmail @.recipients= @.recipient, @.subject = @.sbj,
@.message=@.msg
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM surveillance_cursor
INTO @.recipient, @.Reg_no, @.RENEWAL_DATE
END
CLOSE surveillance_cursor
DEALLOCATE surveillance_cursor
"Damon" <nonsense@.nononsense.com> wrote in message
news:cCXnf.28255$XZ6.26473@.newsfe1-gui.ntli.net...
> Thank you very much for your reply. I can see why you are a SQL Server
> MVP. Unfortunately my works have not supplied me with SQL training so have
> had to learn myself, thus the messy code.
> The @.sbj would be the same everytime, something like "List of renewal
> dates". @.msg would literally be a compilation of the other fields i.e.
> Reg_no & ', ' & URN & ', ' & RENEWAL_DATE. This is just so the officer
> being emailed can see the list of renewals that they have which are due up
> within the next w.
> I really appreciate your help. Need to get on to my works to send me on a
> SQL course.
> Thanks again
> Damon.
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:OrdL3fMAGHA.532@.TK2MSFTNGP15.phx.gbl...
>|||>> I have a select statement which brings back several fields and several co
lumns. <<
Interesting, since SQL does not have fields and columns are a totally
different concept.
More interesting, since SQL does not have records and rows are a
totally different concept. Tables are sets and not files;
sets by definition have no ordering, so cycles make no sense
whatsoever.
You need to use a cursor (explicit or hidden in a called procedure) to
convert the result table into a sequential structure that can have
loops. While you are catching up on the foundations of RM, you might
also want to learn ISO-11179 so that you stop using that silly "tbl-"
in your code, Standard SQL keywords, etc. You are writing SQL like a
procedural or OO programmer because you have not got the right mindset
yet.|||CELKO,
As I mentioned in my previous message, I have not had any official training
in SQL as my employer has not yet put me on a course, I have had to try and
learn this by myself so you will have to forgive my wording and code. I am
a VB programmer so most of the stuff I do I do in VB as I do not know SQL
very well and I find it easier to do in VB.
Thanks for your reply.
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1134664262.456898.41440@.g14g2000cwa.googlegroups.com...
> Interesting, since SQL does not have fields and columns are a totally
> different concept.
>
> More interesting, since SQL does not have records and rows are a
> totally different concept. Tables are sets and not files;
> sets by definition have no ordering, so cycles make no sense
> whatsoever.
> You need to use a cursor (explicit or hidden in a called procedure) to
> convert the result table into a sequential structure that can have
> loops. While you are catching up on the foundations of RM, you might
> also want to learn ISO-11179 so that you stop using that silly "tbl-"
> in your code, Standard SQL keywords, etc. You are writing SQL like a
> procedural or OO programmer because you have not got the right mindset
> yet.
>|||JT,
Thank you very much for your reply. I really appreciate your time in
replying in such detail.
I will have a crack @. this today.
Thanks again.
"JT" <someone@.microsoft.com> wrote in message
news:eNhERNZAGHA.2560@.TK2MSFTNGP12.phx.gbl...
> I've taken an example cursor from Books Online and adjusted it somewhat to
> fit your situation. However, it's just a rough draft and you will need to
> complete it. Basically, the cursor allows you to iternate through the
> query result one row at a time, giving you the ability to populate
> variables and exec the xp_sendmail call. Every column that you plan to
> reference will need to be assigned a variable. I've also added the
> FAST_FORWARD option so it should use less resources.
> DECLARE surveillance_cursor CURSOR FAST_FORWARD FOR
> SELECT dbo.tbl_surveillance.s_id as REG_NO,
> dbo.tbl_surveillance_dates.sd_urn as URN,
> dbo.tbl_surveillance_dates.sd_reviewing_officer as OFFICER,
> dbo.tbl_surveillance_dates.sd_renewal_date as
> RENEWAL_DATE, dbo.tbl_email.e_officer_email AS OFFICER_EMAIL
> FROM dbo.tbl_surveillance INNER JOIN
> dbo.tbl_surveillance_dates ON
> dbo.tbl_surveillance.s_id = dbo.tbl_surveillance_dates.sd_s_id LEFT OUTER
> JOIN
> dbo.tbl_email ON dbo.tbl_surveillance_dates.sd_e_id =
> dbo.tbl_email.e_id
> WHERE getdate() > dateadd(day, -7, sd_renewal_date)
>
> OPEN surveillance_cursor
> -- Perform the first fetch and store the values in variables.
> -- Note: The variables should be in the same order as the columns in the
> SELECT statement.
> FETCH NEXT FROM surveillance_cursor
> INTO @.recipient, @.Reg_no, @.RENEWAL_DATE, etc.
> -- Check @.@.FETCH_STATUS to see if there are any more rows to fetch.
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> select @.sbj = ?
> select @.msg = @.Reg_no + ', ' + @.URN + ', ' + @.RENEWAL_DATE + etc.
> exec master..xp_sendmail @.recipients= @.recipient, @.subject = @.sbj,
> @.message=@.msg
> -- This is executed as long as the previous fetch succeeds.
> FETCH NEXT FROM surveillance_cursor
> INTO @.recipient, @.Reg_no, @.RENEWAL_DATE
> END
> CLOSE surveillance_cursor
> DEALLOCATE surveillance_cursor
>
> "Damon" <nonsense@.nononsense.com> wrote in message
> news:cCXnf.28255$XZ6.26473@.newsfe1-gui.ntli.net...
>

LookupCube: Unexpected Results with StrToSet

Hi.
SELECT {[Customers].[All Customers].[Canada]} ON COLUMNS,
{StrToSet(CStr(LookupCube("Sales", "SetToStr({[Product].Member
s})")))} ON
ROWS
FROM [Sales]
Does anyone have any idea why the above query fails with an "Unknown
internal error"? When I replace the "[Product].Members" string with
"[Time].Members", it works fine.
The following query, which I think is quite similar, works fine:
SELECT {[Customers].[All Customers].[Canada]} ON COLUMNS,
{StrToSet("[Product].Members")} ON ROWS
FROM [Sales]
Thanks.I believe this happens because the list of product members exceeds the
maximum string length that can be handled by "SetToStr", "LookupCube", or
both.
This is supported by testing your query replacing [Product].Members by
Head([Product].Members, n).
Up to n = 344 this works, then it breaks.
At this point , the string length should be over 32K, which is a likely limi
t.
HTH,
Brian
www.geocities.com/brianaltmann/olap.html
"John" wrote:

> Hi.
> SELECT {[Customers].[All Customers].[Canada]} ON COLUMNS,
> {StrToSet(CStr(LookupCube("Sales", "SetToStr({[Product].Memb
ers})")))} ON
> ROWS
> FROM [Sales]
> Does anyone have any idea why the above query fails with an "Unknown
> internal error"? When I replace the "[Product].Members" string with
> "[Time].Members", it works fine.
> The following query, which I think is quite similar, works fine:
> SELECT {[Customers].[All Customers].[Canada]} ON COLUMNS,
> {StrToSet("[Product].Members")} ON ROWS
> FROM [Sales]
> Thanks.
>
>

Lookup with more possibilities?

How can I do a lookup which doens't directly link two columns but uses another statement?

I tried in advanced with:

Code Snippet

select * from
(select * from [dbo].[Employees]) as refTable
where [refTable].[EM_ID] = ? and [refTable].[EM_From] <= ? and
([refTable].[EM_Until] > ? or [refTable].[EM_Until] IS NULL)

and adding 2 parameters.

Error 1 Validation error. Fill Planning: Lookup [2376]: An input column with the lineage ID 1760, referenced in the ParameterMap custom property with the parameter on position number 1, cannot be found in the input columns collection. Package.dtsx 0 0

So I guess that's not the way to go. Any other way to tackle this?

Aren't you missing a ")" at the end? I have sucessfully tried using the advanced tab to input parameters into the lookup transform in the past....

Here is a decent article on this type of action: http://www.julian-kuiters.id.au/article.php/ssis-lookup-with-range

|||Nope no ) missing. I got 2 ( and 2 ) so that's all good. Strange, it should work, I'll play some more with it. Strange thing, it works when I don't do the advanced stuff so something must be wrong there.|||

Just thought of something:

Is this ok to do:

I have:

Code Snippet

Parameter0 EM_ID

Parameter1 PL_Date

Parameter2 PL_Date

Is it ok to use PL_Date twice?

|||

rept wrote:

Just thought of something:

Is this ok to do:

I have:

Code Snippet

Parameter0 EM_ID

Parameter1 PL_Date

Parameter2 PL_Date

Is it ok to use PL_Date twice?

Sure.|||

yes.

Notice that parameter 1 and 2 of the julian kuiters article are both "modifydate".

|||

Just curious, why are you doing the select * from (select * from table) as reftable ? Why not just select * from table as reftable?

|||

" Just curious, why are you doing the select * from (select * from table) as reftable ? Why not just select * from table as reftable?"

I have the same question. It looks like that SQL is more complex that it should be; and I know for sure that SSIS is not very good at finding the parameter in subqueries. Give it a try without using that in-line-view and see if that fix the problem.

|||

Thanks for all the replies!

I just extended what SSIS had by default (same as in Julian Kuiters article as well BTW). I replaced it now but no difference however.

|||

Finally figured it out.

You need to make sure that every parameter that you use in the query is also connected graphically in the columns tab! It doesn't matter if the relation you draw doesn't make sence, you need to for SSIS to be able to find the input column! Hope this will save someone a lot of time someday

Thanks for all who replied!

Lookup with more possibilities?

How can I do a lookup which doens't directly link two columns but uses another statement?

I tried in advanced with:

Code Snippet

select * from
(select * from [dbo].[Employees]) as refTable
where [refTable].[EM_ID] = ? and [refTable].[EM_From] <= ? and
([refTable].[EM_Until] > ? or [refTable].[EM_Until] IS NULL)

and adding 2 parameters.

Error 1 Validation error. Fill Planning: Lookup [2376]: An input column with the lineage ID 1760, referenced in the ParameterMap custom property with the parameter on position number 1, cannot be found in the input columns collection. Package.dtsx 0 0

So I guess that's not the way to go. Any other way to tackle this?

Aren't you missing a ")" at the end? I have sucessfully tried using the advanced tab to input parameters into the lookup transform in the past....

Here is a decent article on this type of action: http://www.julian-kuiters.id.au/article.php/ssis-lookup-with-range

|||Nope no ) missing. I got 2 ( and 2 ) so that's all good. Strange, it should work, I'll play some more with it. Strange thing, it works when I don't do the advanced stuff so something must be wrong there.|||

Just thought of something:

Is this ok to do:

I have:

Code Snippet

Parameter0 EM_ID

Parameter1 PL_Date

Parameter2 PL_Date

Is it ok to use PL_Date twice?

|||

rept wrote:

Just thought of something:

Is this ok to do:

I have:

Code Snippet

Parameter0 EM_ID

Parameter1 PL_Date

Parameter2 PL_Date

Is it ok to use PL_Date twice?

Sure.|||

yes.

Notice that parameter 1 and 2 of the julian kuiters article are both "modifydate".

|||

Just curious, why are you doing the select * from (select * from table) as reftable ? Why not just select * from table as reftable?

|||

" Just curious, why are you doing the select * from (select * from table) as reftable ? Why not just select * from table as reftable?"

I have the same question. It looks like that SQL is more complex that it should be; and I know for sure that SSIS is not very good at finding the parameter in subqueries. Give it a try without using that in-line-view and see if that fix the problem.

|||

Thanks for all the replies!

I just extended what SSIS had by default (same as in Julian Kuiters article as well BTW). I replaced it now but no difference however.

|||

Finally figured it out.

You need to make sure that every parameter that you use in the query is also connected graphically in the columns tab! It doesn't matter if the relation you draw doesn't make sence, you need to for SSIS to be able to find the input column! Hope this will save someone a lot of time someday

Thanks for all who replied!

Lookup Transformation Join Column Types

Does anyone have a clue as to why DT_R4, DT_R8 are not allowed as join columns? This means I cannot join tables on AccountNumbers, InvoiceNumbers, etc. What a pain...

We didn't include support for floating point joins, because of the inherent inability of computers to accurately compare two floating point numbers.

To make the comparisons consistent, we would need to introduce some sort of rounding and user specified precision. If we did not, you may might be now posting a question about why 1.75 is not equal to 1.75 J

I hardly every see people store invoice or account numbers as floating point, I would be interested to know why you made this choice.

|||

>>inability of computers to accurately compare two floating point numbers

I had forgotten about that little fact.

I do not store account numbers and other similar keys as a floating point data type. However, the access database that I am importing with SSIS was created (by my predecessor) using double as the type for AccountNumber, rather than long integer. From here on out, I will do a data conversion before I do the look up and that should take care of my problem.

Thanks!

lookup to check values in 13 columns with same values

hi,

it is my first post on this forum, please be patient if i miss any important bit of information.

i am transporting data from a legacy system into mssql 2k5 using SSIS.
among those column of a dataset there are 13 columns, all necessary for operational reasons, that i need to ensure data consistance.

i believe i could do this check using the lookup data flow item, but surely there must be a way to do it in a more streamlined fashion.

since column names contain numbers to distinguish the version, eg; col01, col02, col03 .. col13.

i thought i could include the lookup within a loop and use a couple of variables to do this trick, but since i have not done it before i am asking for some sort of guidance from a guru among you folks.

please let me know if further clarification is necessary.

regards,

nicolas

If you are trying to confirm that the 13 values for each row in the source match the corresponding row in the destination, then a lookup is the way to go. Using a single lookup with the 13 columns all mapped makes sense. Trying to do something dynamic is not going to work, and even if it did this would mean 13 individual lookup calls, so probably a lot slower due to the extra overhead.

Another method when you have lots of columns to compare is to use a hash or checksum across the columns. You can compute this checksum in the pipeline on the incoming source, and compare that against the checksum stored in the destination. This means the lookup is only one column, so for wide column data and lots of them it will be faster due to the reduced data being transferred, and it is easier to just select columns for checksum generation compared to mapping in the lookup. This does mean that your destination needs to be expaned to store the checksum.

A checksum transform is available here (http://www.sqlis.com/default.aspx?21) or you can implement your own in the Script Component.

|||darren,

i understand your point about looking through 13 columns been slow, but i am not certain if i understand how to hash all those columns to compare their values.

for clarification, i need to map or rather insure that those values in all 13 columns will comply with a foreign key constraint, thus i am ensuring that only values listed in my reference table exist in those columns.

would this scenario allow me to use hash for comparing values?

many thanks|||

I may have misunderstood this. If the 13 columns are the key for one table, then the hash concept will work. the hash serves as a compund key for those 13 columns,. If they are 13 different reference tables, then i messed up, you woudl use 13 lookups, one to check each table, probably what you meant first time around. There is no shortcut for these 13 lookups if that is what you require.

I digress a bit, but a good strategy can be to just load the data and let it fail on the FK violation. Set the component to re-direct any failed rows to the error output. You may then need to do your lookups or whatever you want to do when you have missing reference data. This can be much faster than lookups for every row. If 100 rows are loaded, but only 1 is a new key, the error output will get 1 row. This means 1 lookup (fix work) as opposed to 100.

|||

Hi Darren,

In the example - you have assumed that the base tables contain the Foreign Key column values - What if we need to lookup on a secondary table, Obtain the primary key from the secondary table and populate them in the base table column.

Is it beneficial to handle these relations in SQL JOIN Statements while fetching records from the source tables rather than doing the lookup using LookUP Components in the SSIS pipeline (which I believe works at record level - meaning a bulk lookup is impossible.)

Please share your comments.

Thanks,
Loonysan

|||Ok,

since i have similar situation with 4 other dataset, i would like to probe this idea further as well as maek sure that i clarify it so we understand this situation properly.

this source dataset that i extracted from our legacy data source has these 13 columns (col1..col13) among many.

in on our destination system, i created a table (lookup table) which will enable me to map and transform legacy codes into relevant new codes in our destination table.

the dataset is intented to be inserted into a table that has 13 foreign keys (one for each of those 13 columns) which references another table, thus ensuring that only new codes will be inserted in any of those 13 columns.

having said that, you believe that i should join the lookup table, try to insert into the destination table & catch any error?

many thanks

Lookup tables - one or many

So i am designing a new database that currently has several tables 'look up tables' that are used just to limit the values of columns in other tables.

my question is what is the best way to do this?

1. multiple tables - one for each set of values (ex: JobType, Position, PayGrade)
2. One large table that holds all the lookup values - has a 'Category' field to group them
3. put constraints on the columns of the tables that are 'looking up' and get rid of the lookup tables.

thanks
lucas

Lucas, well you want lookup values in a table of SOME sort, so option #3 would be out for me. Much better design to have dynamic data changes, then hard-coding constraints.

Now the real choice... probably get half of the people using one way and the other half, the other... A lot of this depends on thigns like... what is going in your lookup tables. If it's a case where you have a relatively small # of values, and they are basically all the same (an ID, Code, Name Desc), then I prefer 2 tables, the Lookup and the LookupCategory tables. Doing it that way, the main issue is that you want a constraint back to the Lookup table, so you have to either have a 2 column PK there that you FK back to, or could use triggers to maintain the "FK" constraint, ehhh.. or just key back to the single PK column and have checks in place that they are FK'ing back to one of the values for the specific Category....

But if it's a small amount of Lookup tables, or they have more attributes then ID,Code,Name,Desc... then separate tables per Lookup works ok. If you start having 50, 70, 100+ of these 5 row type tables, that gets annoying.... but a lot of people can't deal with the object-oriented stlye, so it all depends on the developers and whoever will touch the tables, to see which of those 2 options to go with.... You'll probably get more people want to use one table per lookup, than the generic approach.... Bruce

|||Right now i have about 20 small lookup tables (just one field) with around 10 records in each.

i'm the developer as well and i am just planing on using these lookup tables to populate drop down lists in the application.

i'm starting to think i should have one large lookup table with a category table; like this:

LookUp
LookUpId (pk)
Category (fk)
Value

LookUpCategory
Category (pk)

look good?
|||

In that 2 table method, I'd have the columns for ID, Code, Name and Desc.... not just the value, at least some description field, if not NAME and DESC fields. On the Category, same thing, an ID, Code, Name, Desc.... If you just have a Category, unless it's a clear varchar code, then you won't know what it is exactly, especailly if teh model grows to 30,40,50 lookups... and, since you're FKing back to this table, might be better to call it tTypeCode and tTypeCategory... then your base tables would have fields called like propertyTypeID ( as opposed to propertyLookupID)... just clearer to call them type then lookups...

Other columns you MAY want to have in the type table are a Seq# and a defaultFlag....

FKing back to the Type table, is ok like that, but you could have a phoneTypeID value be stored that is really an ID for a propertyTypeID, unless you put some check in place to enforce the selection of type ID's by category...

Bruce

|||

If you just want to validate that a column

only contains a defined set of values don't need to do things like populate drop downs the set of valid values do not change

Lookup table, extracting values

I need some help with the following...

My data source has some columns I have to 'translate' first and then insert into my destination table.

Example Source data:

key size height

1 'Small' 'Tall'

2 'Big' 'Short'

has to become

1 'Y' 'P'

2 'N' 'D'

I thought of creating a lookup table (I'm talking about the real table, not a lookup transformation table) that would have these columns: column name, value_source, value_dest

Example:

col_name vl_source vl_dest

size 'Small' 'Y'

size 'Big' 'N'

height 'Tall' 'P'

... and so on, I believe you get the point

How would you extract the needed values? Can I use a select statement in a derived column expression? Any ideas? I'm not really fond of the idea to create n lookups, one for each column I have to translate, is there a slicker solution?You either need to do it in your source when you do the query, or do n lookups or embed the information in a derived column component.

Unfortunately there is no Multi lookup component.
|||

what do you exactly mean by 'embed the information in a derived column component'? Could you elaborate?
Thank you

|||

He's saying that you could either do it in source extract query like this

Code Blockselect

....
,
case

when col1='small' then 's'

when col1='medium' then 'm'

end

from
....

|||

or embeded in the derived column component like this

Code Blockcol1==""small"" ? ""s"" : col1 || col1==""medium"" ? ""m"" : col1

|||You can use multiple lookup transformations in a row to get what you need. Create that physical table and then it'll work. Add a lookup for each column you want to translate.

Then in each lookup, you'll filter your SQL query against "col_name". So for exampe, "select vl_source, vl_dest from conversion_table where col_name = 'size'")
|||

Thank you, Phil, that's exactly what I've done.

It'd be great if derived column could 'take' select statements so it could be used instead of n lookups in this case...

|||

Sara4 wrote:
Thank you, Phil, that's exactly what I've done.

It'd be great if derived column could 'take' select statements so it could be used instead of n lookups in this case...

Per the example above, you can do CASE logic inside the derived column, but then it'll be harder to maintain that your table approach.

|||

Anthony Martin wrote:

or embeded in the derived column component like this

Code Blockcol1==""small"" ? ""s"" : col1 || col1==""medium"" ? ""m"" : col1

Thank you for the explanation. We need to keep our decodifying dynamic (in the future small may become smallx and some new values could be entered - storing the values in a derived column would mean modifying the SSIS too).

It seems that for now the only (quick) solution is creating as many lookups as there are columns that need to be decodified.

Thank you all!

|||Sara,
You can unpivot your data as well and run it through one lookup.

So your data:

key size height

1 'Small' 'Tall'

2 'Big' 'Short'

Would become:
key column value
1 'size' 'Small'
1 'height' 'Tall'
2 'size' 'Big'
2 'height' 'Short'

This would allow you to use one lookup table. Then you'd need to pivot your data back, using the returned values from the lookup.

Takes more time to setup, but it could be worth it for you.

Lookup table, extracting values

I need some help with the following...

My data source has some columns I have to 'translate' first and then insert into my destination table.

Example Source data:

key size height

1 'Small' 'Tall'

2 'Big' 'Short'

has to become

1 'Y' 'P'

2 'N' 'D'

I thought of creating a lookup table (I'm talking about the real table, not a lookup transformation table) that would have these columns: column name, value_source, value_dest

Example:

col_name vl_source vl_dest

size 'Small' 'Y'

size 'Big' 'N'

height 'Tall' 'P'

... and so on, I believe you get the point

How would you extract the needed values? Can I use a select statement in a derived column expression? Any ideas? I'm not really fond of the idea to create n lookups, one for each column I have to translate, is there a slicker solution?

You either need to do it in your source when you do the query, or do n lookups or embed the information in a derived column component.

Unfortunately there is no Multi lookup component.

|||

what do you exactly mean by 'embed the information in a derived column component'? Could you elaborate?

Thank you

|||

He's saying that you could either do it in source extract query like this

Code Block

select

....

,

case

when col1='small' then 's'

when col1='medium' then 'm'

end

from

....

or embeded in the derived column component like this

Code Block

col1=="small" ? "s" : col1 || col1=="medium" ? "m" : col1

|||You can use multiple lookup transformations in a row to get what you need. Create that physical table and then it'll work. Add a lookup for each column you want to translate.

Then in each lookup, you'll filter your SQL query against "col_name". So for exampe, "select vl_source, vl_dest from conversion_table where col_name = 'size'")|||

Thank you, Phil, that's exactly what I've done.

It'd be great if derived column could 'take' select statements so it could be used instead of n lookups in this case...

|||

Sara4 wrote:

Thank you, Phil, that's exactly what I've done.

It'd be great if derived column could 'take' select statements so it could be used instead of n lookups in this case...

Per the example above, you can do CASE logic inside the derived column, but then it'll be harder to maintain that your table approach.|||

Anthony Martin wrote:

or embeded in the derived column component like this

Code Block

col1=="small" ? "s" : col1 || col1=="medium" ? "m" : col1

Thank you for the explanation. We need to keep our decodifying dynamic (in the future small may become smallx and some new values could be entered - storing the values in a derived column would mean modifying the SSIS too).

It seems that for now the only (quick) solution is creating as many lookups as there are columns that need to be decodified.

Thank you all!

|||Sara,
You can unpivot your data as well and run it through one lookup.

So your data:

key size height

1 'Small' 'Tall'

2 'Big' 'Short'

Would become:
key column value
1 'size' 'Small'
1 'height' 'Tall'
2 'size' 'Big'
2 'height' 'Short'

This would allow you to use one lookup table. Then you'd need to pivot your data back, using the returned values from the lookup.

Takes more time to setup, but it could be worth it for you.|||

I'll try it out and let you know how it went!