Configuration file location different in BIDS?

I've been working on package configurations here, mainly trying to figure out what works, and how. If I use an XML configuration, and and don't specify the path, ie just typing in test.dtsconfig, it creates the config file on my desktop. When I execute the packge in BIDS, it seems to be looking in that location. If however, I execute the package manually on the filesystem, test.dtsconfig must be in the same directory as the package (which is what I would expect). Why does this work differently in BIDS. Also, if the package is deployed to a SQL server, where would the configuration file need to be in this case? Thanks for the help.

Configuration file paths in SSIS are absolute, not relative. hence, use the full path when defining your file. i can't explain why behaviour is different between BIDS and whatever other method you are using to execute but to be honest that is irrelevant if you define the location with an absolute path.

If you don't like the thought of absolute paths then indirect configurations are what you want! http://blogs.conchango.com/jamiethomson/archive/2005/11/02/2342.aspx

Deployingn to SQL Server makes no differrence. you still need an absolute path.

-Jamie

Conditional Where Clause w/ Case Statement Possible?

Greetings,

After many hours search many forums and many failed experiments, I figure it's time to turn to the experts.

I need to execute a query that changes the returned data based upon a parameter's value. In my example below, the lob field contains both text values and nulls.

SELECT uniqueID, lob, xdate

FROM mytable

WHERE

CASE WHEN @.myparam = 'ALL'

THEN

xdate >= '2007-09-01'

ELSE

xdate >= '2007-09-01' or

lob = @.myparm

END

I've experimented with various forms of the LIKE function, checking for null/not null and keep coming up blank.

I thought about using an IF statement and creating different versions of the entire statement, however, in real-life I need to do this with four fields using four parameters (one for each field). The permutations are a little too much.

Any ideas?

Rob

Your query can be written this way, I think:

SELECT uniqueID, lob, xdate

FROM mytable

WHERE (xdate >= '20070901')

AND (

@.myparm = 'ALL'

OR

lob = @.myparm

)

In general, you can probably write:

WHERE

(@.p = 'Option1' AND (<option 1 condition>))

OR

(@.p = 'Option 2' AND (<option 2 condition>))

OR

...

Be careful where parentheses go, so AND and OR don't associate differently than you want.

In SQL, CASE .. END is an expression, by the way, so it can't be used as you hoped in your post.

Steve Kass

Drew University

http://www.stevekass.com

|||

This is really bad idea to write the logical expression to validate the variable’s value on the where clause, it might force the engine to use the index scan,

If .. Else is not harm to use in your query.. Don’t try to reduce the no of lines, check the performance..

Code Snippet

If @.myparam = 'ALL'

SELECT

uniqueID, lob, xdate

FROM

mytable

WHERE

xdate >= '2007-09-01'

ELSE

SELECT

uniqueID, lob, xdate

FROM

mytable

WHERE

xdate >= '2007-09-01' or lob = @.myparm

|||

Steve Kass wrote:

Your query can be written this way, I think:

SELECT uniqueID, lob, xdate

FROM mytable

WHERE (xdate >= '20070901')

AND (

@.myparm = 'ALL'

OR

lob = @.myparm

)

In SQL, CASE .. END is an expression, by the way, so it can't be used as you hoped in your post.

Steve - when @.myparm ='ALL', I want all of the records returned. The field (lob) does not actually contain the value 'ALL'.

I tried using a LIKE statement but found that nulls are not returned. For example, if @.myparm was populated with "%" and used in this query:

SELECT uniqueID, lob, xdate

FROM mytable

WHERE (xdate >= '20070901') and

lob LIKE @.myparm

then all records with a value in lob are returned but those will a null are not. I need to inlcude the records with a null.

Rob

|||

Manivannan.D.Sekaran wrote:

This is really bad idea to write the logical expression to validate the variable’s value on the where clause, it might force the engine to use the index scan,

If .. Else is not harm to use in your query.. Don’t try to reduce the no of lines, check the performance..

The problem with using the IF statement is the number of permutation when I have four variables.

IF @.myparm1 = 'ALL' and @.myparm2 <> 'ALL' and @.myparm3 <> 'ALL' and @.myparm4 <> 'ALL'

(query 1)

IF @.myparm1 <> 'ALL' and @.myparm2 = 'ALL' and @.myparm3 <> 'ALL' and @.myparm4 <> 'ALL'

(query 2)

IF @.myparm1 <> 'ALL' and @.myparm2 <> 'ALL' and @.myparm3 = 'ALL' and @.myparm4 <> 'ALL'

(query 3)

...

I think I'd wind up with 16 IF statements. While it should work, it would be very difficult to support.

Rob

|||

In your case the best solution is using the dynamic sql... Buliding the Where clause expression on the fly..

There are few limitations there while using the dynamic sql.. If you really need a best performance there is no compromise..

example,

Code Snippet

Declare @.SQL as Varchar(8000);

Declare @.Name as Varchar(8000);

Declare @.Type as Varchar(8000);

Declare @.Where as varchar(8000);

Set @.Name = null;

Set @.Type ='U'

Set @.SQL = 'Select * from Sysobjects Where 1=1'

Set @.Where = ''

IF @.Name is not NULL

Set @.Where = ' And Name=''' + @.Name + ''''

IF @.Type is NOT NULL

Set @.Where = @.Where + ' And Type=''' + @.Type + ''''

Exec (@.SQL + @.Where)

Code Snippet

Declare @.SQL as NVarchar(4000);

Declare @.Name as Varchar(8000);

Declare @.Type as Varchar(8000);

Declare @.Where as varchar(8000);

Set @.Name = NULL;

Set @.Type ='P'

Set @.SQL = 'Select * from Sysobjects Where 1=1'

Set @.Where = ''

IF @.Name is not NULL

Set @.Where = ' And Name=@.Name'

IF @.Type is NOT NULL

Set @.Where = @.Where + ' And Type=@.Type'

Set @.SQL = @.SQL + @.Where

Exec sp_executesql @.SQL, N'@.name varchar(100), @.type varchar(100)', @.name, @.type

|||Sorry, Rob. I misread your original intent, so let me try again. I think you want

1. If 'ALL' is passed: every row with xdate >= '20070901'
2. If 'ALL' is not passed: every row with xdate >= '20070901', as well as rows with lob = @.myparm, regardless of date.

First, a solution that assumes @.myparm is never NULL:

select uniqueID, lob, xdate
from mytable
where
(
@.myparm = 'ALL'
and
(xdate >= '20070901')
) or (
@.myparm <> 'ALL'
and
(@.myparm = lob or xdate >= '20070901')
)

Because you use NULL as a value for [lob], and you use @.myparm = NULL to select those rows, you can't rely on @.myparm = lob, which is not true when @.myparm and lob are both NULL. Unfortunately, you have to handle this separately, because T-SQL has no IS NOT DISTINCT FROM operator that means "are both equal or are both null". Whether you treat it as a third case or in the second case is up to you:

where
(
@.myparm = 'ALL'
and
(xdate >= '20070901')
) or (
@.myparm <> 'ALL'
and
(@.myparm = lob or xdate >= '20070901')
) or (
@.myparm IS NULL
and
(lob IS NULL or xdate >= '20070901')
)

So this is like what a CASE statement would be. The structure is

where
you are in case 1 and the where clause for that case holds
or
you are in case 2 and the where clause for that case holds
...

The problem with NULL is because a CASE expression would allow an OTHERWISE clause to handle both @.myparm <> 'ALL' and @.myparm IS NULL at once.

Note that I wrote the date without hyphens. Unfortunately, if xdate is [datetime] or [smalldatetime], SQL Server installations that use European and many other non-US localizations will interpret the date as you wrote it to mean January 9, 2007. I doubt you ever want that, and you can avoid a surprise by using the format I provided, or the other "safe" format '2007-09-01T00:00:00' (the T is required).

SK

|||

Steve Kass wrote:

select uniqueID, lob, xdate
from mytable
where
(
@.myparm = 'ALL'
and
(xdate >= '20070901')
) or (
@.myparm <> 'ALL'
and
(@.myparm = lob or xdate >= '20070901')
)

SK

Steve,

Thank you very much. This did the trick. It's amazing what a few AND & OR statements can do with the correct paranthesis.

I'm actually using these queres in SSRS as a data source. It took a couple of edits to get SSRS to take the paranthesis correctly as it want to "fix" them for you.

Rob

Conditional Where Clause w/ Case Statement Possible?

Greetings,

After many hours search many forums and many failed experiments, I figure it's time to turn to the experts.

I need to execute a query that changes the returned data based upon a parameter's value. In my example below, the lob field contains both text values and nulls.

SELECT uniqueID, lob, xdate

FROM mytable

WHERE

CASE WHEN @.myparam = 'ALL'

THEN

xdate >= '2007-09-01'

ELSE

xdate >= '2007-09-01' or

lob = @.myparm

END

I've experimented with various forms of the LIKE function, checking for null/not null and keep coming up blank.

I thought about using an IF statement and creating different versions of the entire statement, however, in real-life I need to do this with four fields using four parameters (one for each field). The permutations are a little too much.

Any ideas?

Rob

Your query can be written this way, I think:

SELECT uniqueID, lob, xdate

FROM mytable

WHERE (xdate >= '20070901')

AND (

@.myparm = 'ALL'

OR

lob = @.myparm

)

In general, you can probably write:

WHERE

(@.p = 'Option1' AND (<option 1 condition>))

OR

(@.p = 'Option 2' AND (<option 2 condition>))

OR

...

Be careful where parentheses go, so AND and OR don't associate differently than you want.

In SQL, CASE .. END is an expression, by the way, so it can't be used as you hoped in your post.

Steve Kass

Drew University

http://www.stevekass.com

|||

This is really bad idea to write the logical expression to validate the variable’s value on the where clause, it might force the engine to use the index scan,

If .. Else is not harm to use in your query.. Don’t try to reduce the no of lines, check the performance..

Code Snippet

If @.myparam = 'ALL'

SELECT

uniqueID, lob, xdate

FROM

mytable

WHERE

xdate >= '2007-09-01'

ELSE

SELECT

uniqueID, lob, xdate

FROM

mytable

WHERE

xdate >= '2007-09-01' or lob = @.myparm

|||

Steve Kass wrote:

Your query can be written this way, I think:

SELECT uniqueID, lob, xdate

FROM mytable

WHERE (xdate >= '20070901')

AND (

@.myparm = 'ALL'

OR

lob = @.myparm

)

In SQL, CASE .. END is an expression, by the way, so it can't be used as you hoped in your post.

Steve - when @.myparm ='ALL', I want all of the records returned. The field (lob) does not actually contain the value 'ALL'.

I tried using a LIKE statement but found that nulls are not returned. For example, if @.myparm was populated with "%" and used in this query:

SELECT uniqueID, lob, xdate

FROM mytable

WHERE (xdate >= '20070901') and

lob LIKE @.myparm

then all records with a value in lob are returned but those will a null are not. I need to inlcude the records with a null.

Rob

|||

Manivannan.D.Sekaran wrote:

This is really bad idea to write the logical expression to validate the variable’s value on the where clause, it might force the engine to use the index scan,

If .. Else is not harm to use in your query.. Don’t try to reduce the no of lines, check the performance..

The problem with using the IF statement is the number of permutation when I have four variables.

IF @.myparm1 = 'ALL' and @.myparm2 <> 'ALL' and @.myparm3 <> 'ALL' and @.myparm4 <> 'ALL'

(query 1)

IF @.myparm1 <> 'ALL' and @.myparm2 = 'ALL' and @.myparm3 <> 'ALL' and @.myparm4 <> 'ALL'

(query 2)

IF @.myparm1 <> 'ALL' and @.myparm2 <> 'ALL' and @.myparm3 = 'ALL' and @.myparm4 <> 'ALL'

(query 3)

...

I think I'd wind up with 16 IF statements. While it should work, it would be very difficult to support.

Rob

|||

In your case the best solution is using the dynamic sql... Buliding the Where clause expression on the fly..

There are few limitations there while using the dynamic sql.. If you really need a best performance there is no compromise..

example,

Code Snippet

Declare @.SQL as Varchar(8000);

Declare @.Name as Varchar(8000);

Declare @.Type as Varchar(8000);

Declare @.Where as varchar(8000);

Set @.Name = null;

Set @.Type ='U'

Set @.SQL = 'Select * from Sysobjects Where 1=1'

Set @.Where = ''

IF @.Name is not NULL

Set @.Where = ' And Name=''' + @.Name + ''''

IF @.Type is NOT NULL

Set @.Where = @.Where + ' And Type=''' + @.Type + ''''

Exec (@.SQL + @.Where)

Code Snippet

Declare @.SQL as NVarchar(4000);

Declare @.Name as Varchar(8000);

Declare @.Type as Varchar(8000);

Declare @.Where as varchar(8000);

Set @.Name = NULL;

Set @.Type ='P'

Set @.SQL = 'Select * from Sysobjects Where 1=1'

Set @.Where = ''

IF @.Name is not NULL

Set @.Where = ' And Name=@.Name'

IF @.Type is NOT NULL

Set @.Where = @.Where + ' And Type=@.Type'

Set @.SQL = @.SQL + @.Where

Exec sp_executesql @.SQL, N'@.name varchar(100), @.type varchar(100)', @.name, @.type

|||Sorry, Rob. I misread your original intent, so let me try again. I think you want

1. If 'ALL' is passed: every row with xdate >= '20070901'
2. If 'ALL' is not passed: every row with xdate >= '20070901', as well as rows with lob = @.myparm, regardless of date.

First, a solution that assumes @.myparm is never NULL:

select uniqueID, lob, xdate
from mytable
where
(
@.myparm = 'ALL'
and
(xdate >= '20070901')
) or (
@.myparm <> 'ALL'
and
(@.myparm = lob or xdate >= '20070901')
)

Because you use NULL as a value for [lob], and you use @.myparm = NULL to select those rows, you can't rely on @.myparm = lob, which is not true when @.myparm and lob are both NULL. Unfortunately, you have to handle this separately, because T-SQL has no IS NOT DISTINCT FROM operator that means "are both equal or are both null". Whether you treat it as a third case or in the second case is up to you:

where
(
@.myparm = 'ALL'
and
(xdate >= '20070901')
) or (
@.myparm <> 'ALL'
and
(@.myparm = lob or xdate >= '20070901')
) or (
@.myparm IS NULL
and
(lob IS NULL or xdate >= '20070901')
)

So this is like what a CASE statement would be. The structure is

where
you are in case 1 and the where clause for that case holds
or
you are in case 2 and the where clause for that case holds
...

The problem with NULL is because a CASE expression would allow an OTHERWISE clause to handle both @.myparm <> 'ALL' and @.myparm IS NULL at once.

Note that I wrote the date without hyphens. Unfortunately, if xdate is [datetime] or [smalldatetime], SQL Server installations that use European and many other non-US localizations will interpret the date as you wrote it to mean January 9, 2007. I doubt you ever want that, and you can avoid a surprise by using the format I provided, or the other "safe" format '2007-09-01T00:00:00' (the T is required).

SK

|||

Steve Kass wrote:

select uniqueID, lob, xdate
from mytable
where
(
@.myparm = 'ALL'
and
(xdate >= '20070901')
) or (
@.myparm <> 'ALL'
and
(@.myparm = lob or xdate >= '20070901')
)

SK

Steve,

Thank you very much. This did the trick. It's amazing what a few AND & OR statements can do with the correct paranthesis.

I'm actually using these queres in SSRS as a data source. It took a couple of edits to get SSRS to take the paranthesis correctly as it want to "fix" them for you.

Rob

|||in t-sql you can have conditional where clauses
sample:

select * from mytable
where
mycol = case when @.i = 1 then 1 else 2 end

Conditional Sum trouble

Can someone help me figure this out? I'm trying to total up 4 columns which
have been X'd to indicate inventory types: RAWX, NSFGX, BUYX and SFGX.
I've tried:
=iif( Fields!RAWX.Value = "X", Sum(Fields!BKIC_PROD_AVGC.Value *
Fields!BKIC_PROD_UOH.Value), 0)
=iif( Fields!NSFGX.Value = "X", Sum(Fields!BKIC_PROD_AVGC.Value *
Fields!BKIC_PROD_UOH.Value), 0)
=iif( Fields!BUYX.Value = "X", Sum(Fields!BKIC_PROD_AVGC.Value *
Fields!BKIC_PROD_UOH.Value), 0)
=iif( Fields!SFGX.Value = "X", Sum(Fields!BKIC_PROD_AVGC.Value *
Fields!BKIC_PROD_UOH.Value), 0)
The reoport runs without errors, but I get a Grand Total for RAWX and 0 for
the other three columns.
If I change the expressions to:
=Sum(iif( Fields!RAWX.Value = "X", (Fields!BKIC_PROD_AVGC.Value *
Fields!BKIC_PROD_UOH.Value), 0))
=Sum(iif( Fields!NSFGX.Value = "X", (Fields!BKIC_PROD_AVGC.Value *
Fields!BKIC_PROD_UOH.Value), 0))
=Sum(iif( Fields!BUYX.Value = "X", (Fields!BKIC_PROD_AVGC.Value *
Fields!BKIC_PROD_UOH.Value), 0))
=Sum(iif( Fields!SFGX.Value = "X", (Fields!BKIC_PROD_AVGC.Value *
Fields!BKIC_PROD_UOH.Value), 0))
I get #ERROR in all columns and a warning for each field:
"The value expression for the textbox â'RAWXâ' uses an aggregate function on
data of varying data types. Aggregate functions other than First, Last,
Previous, Count, and CountDistinct can only aggregate data of a single data
type.
BKIC_PROD_AVGC and BKIC_PROD_UOH are the same data type.
What is this message trying to tell me?Did you try this for the RAWX column?
=iif( Fields!RAWX.Value = "X", Sum(Fields!BKIC_PROD_AVGC.Value) *
Sum(Fields!BKIC_PROD_UOH.Value), 0)
--
I would also try to display the conditional value for the other three
to verify that those fields equal your "X" value
For example (for the NSFGX column), just put
= (Fields!NSFGX.Value = "X")
and see if it returns 'True' or 'False'
that may lead you in the right direction.
--
Also, I'm not sure how your report is set up, but did you notice that
for each of your columns, you're referencing the same values (
Fields!BKIC_PROD_AVGC.Value and Fields!BKIC_PROD_UOH.Value )?

conditional split on field in csv file

I know this should be simple but I can't figure it out. I am reading in a csv file to a conditional split task, all I want to do is split the file based on a field. Some values in field will have a suffix say ABCD while others wont. So my conditional split says Right(FieldA,4)=="ABCD" which then splits file in two directions or at least it's meant to. Problem is that it does not work. I think it has something to do with the field type in the csv file although I have tried using a Data Conversion task but to no avail all the field values with ABCD suffix are ignored by my conditional split and head off the same way as other values. Funny thing is is that if I manually add a value to the file with a suffix of ABCD and run task again then the conditional split works on the manually added row and all rows with suffix of ABCD. It's like it does not recognise previous values as string until one is added manually.

Thanks

Might there be trailing spaces on the file?

Try:

Right(Trim([FieldA]),4)=="ABCD"

|||

Thanks fo reply, yes I tried that one but it doesn't have trailing spaces. I think it has something to do with fields in Input file.

When I look at the file in notepad values are

"123","somevalue"

"123ABCD","somevalue"

I have seen some posts regarding text qualifier as a problem, mine is set to " but conditional split still fails.

|||Is it throwing any type of error message?|||

No the file processes fine.

If I add another row to file and put in the suffix then the conditional split works but I think this is because the text qualifiers are removed once I had row to file eg

Original file

"123","somevalue"

"123ABCD","somevalue"

goes thru data flow task with no errors but conditional split doesn't work

Modified file will look like

123,somevalue

123ABCD,somevalue

456ABCD,somevalue

and will work for both rows with suffix.

|||

Could you put a data viewer before the conditional split and see what data is getting in?

Thanks.

|||

Have put data viewer in and first riow looks correct but rows following look like they have another delimiter shown below

123, somevalue

o “456”, “somevalue”

o “123ABCD”, “somevalue”

Row delimiter is set to {CR}. Column 1 delimiter is set to {CR} if I change this to {CR}{LF} then the process fails with error delimeter for column 1 not found.

Thanks

|||

Got it to work in the Columns tab

Row Delimeter is {LF}

Column delimiter is Comma {,}

Text qualifier is "

in advanced tab

Coliumn delimeter of 2nd column is {LF}

which ended up making data come in as

123, somevalue"

456, somvalue"

123ABCD, somevalue"

which the conditional split was happy with.

Thanks for your help

Conditional Parameter in Where Clause

I'm trying to figure out a way to filter a dataset using a parameter only when the user enters a value for the parameter and to not apply the filter if the parameter is left blank (or null) by the user. I would like to do this within the WHERE clause of the SELECT statement to minimize the size of the dataset whenever possible. Is there such a thing as a default parameter value that equates to "any value"?

Nothing I have tried works (but I'm new to SQL, Report Server and the Visual Basic Development Environment).

Thanks in advance,

Chris

Rather than leaving the parameter unselected, you need to add an option with a value of NULL and text that matches your scenario e.g. blank, "All", "N/A", "Unspecified" etc. To do this you'll need to modify the query for the paramter dataset to:

SELECT id = NULL, name = 'All'
UNION ALL
SELECT id, name
FROM param_table

Then update your main query with the following WHERE clause

WHERE id = ISNULL(@.param, id)

so when the null option is selected the WHERE clause equates to id=id which is always true and hence all rows are returned.

Hope this helps.

|||

Thanks Adam,

I was not familiar with ISNULL. I got it to work sort of like I wanted it to by checking the "Allow Null Value" checkbox and making the default value NULL in the Report Parameters dialog box and then putting this in the WHERE clause:

WHERE LITEM.SIZE = ISNULL(@.Input_Size, LITEM.SIZE)

However, I could not figure out where to put the following statement (everything I tried resulted in an error - but I'm probably missing something obvious):

SELECT id = NULL, name = 'All'
UNION ALL
SELECT id, name
FROM param_table

...and therefore, the user must uncheck the NULL checkbox in order to enter a filter value and it's not real obvious that when NULL is checked, that the filter is not applied.

Thanks again for pointing me in the right direction!

Chris

|||

By your response it seems like your parameter is a textbox the user types into, is that correct?

My prerred way is to present the user a list of options i.e. a dropdown. In that case you don't get a null checkbox. The options in the dropdown can either be typed in on the paramter screen or can come from a dataset. The SELECT statement I provided is meant as an example of query used to populate such a dataset i.e. it includes a NULL option.

If you wish to use a textbox then you could alter your SQL query and rather than using ISNULL you could use an OR in your WHERE clause as follows

WHERE LITEM.SIZE = @.Input_Size
OR @.Input_Size = '' -- empty string

If LITEM.SIZE and @.Input_Size are integers then it gets a little more complicated. You'll need to experiment.

|||

Adam,

Thanks! It's now working just the way I wanted it to!

Chris Heitman

Conditional JOIN

Hi,

I am trying to change an SP from dynamic SQL to proper SQL but I can't
figure a way to conditionally add extra parts to the statement. How
can I do the equivalent of the following?

DECLARE @.arg NVARCHAR(10)

SELECT a.i, a.x
FROM aTable a
IF LEN(@.arg)
BEGIN
INNER JOIN bTable b ON a.[id] = b.[id]
END

Conditionally adding the INNER JOIN is very easy when building up a SQL
string but I can't see how to do it in pure SQL?

Thanks.What do you intend by a conditional join? The purpose of a join is
usually to bring back some extra columns from additional tables. Static
queries also have static metadata (in other words always the same set
of columns are returned every time) so a "conditional" join such as you
have posted is really just a selection:

SELECT a.i, a.x
FROM aTable AS a
WHERE EXISTS
(SELECT *
FROM bTable AS b
WHERE b.id = a.id)
OR @.arg = ''

(I'm assuming ID is unique in B otherwise your original query might
return duplicate rows).

In general you can use OR to implement optional criteria but this often
leads to sub-optimal query plans. You should consider using IF
statements to choose from a set of possible queries or just break up
the different queries into separate SPs to be called independently.
Either approach is usually preferable to dynamic SQL.

--
David Portas
SQL Server MVP
--|||I possibly gave a less than clear example. Insert an additional join
on aTable:

SELECT a.i, a.x
FROM aTable a
INNER JOIN bTable b on a.[id] = b.[id]
IF (LEN(@.arg) > 0)
BEGIN
INNER JOIN cTable c ON a.[id] = c.[id]
END

The condition is that if the parameter @.arg is of length 0 then I do
not want to join to cTable but if it is greater than 0 then I do. It's
essentially a switch to impose additional restrictions upon the
recordset being returned. I am not changing the structure of what is
being selected, just _conditionally_ adding an extra filter on the
data.

Thanks.|||SELECT a.i, a.x
FROM aTable a
INNER JOIN bTable b
ON a.[id] = b.[id]
WHERE EXISTS
(SELECT *
FROM cTable c
WHERE c.[id] = a.[id])
OR @.arg = ''

Again, assuming ID is unique in C this is equivalent to an INNER JOIN.

--
David Portas
SQL Server MVP
--|||(chandy@.totalise.co.uk) writes:
> I possibly gave a less than clear example. Insert an additional join
> on aTable:
> SELECT a.i, a.x
> FROM aTable a
> INNER JOIN bTable b on a.[id] = b.[id]
> IF (LEN(@.arg) > 0)
> BEGIN
> INNER JOIN cTable c ON a.[id] = c.[id]
> END
> The condition is that if the parameter @.arg is of length 0 then I do
> not want to join to cTable but if it is greater than 0 then I do. It's
> essentially a switch to impose additional restrictions upon the
> recordset being returned. I am not changing the structure of what is
> being selected, just _conditionally_ adding an extra filter on the
> data.

And that is exactly what David's query achieved. He hinted that his query
might not get a good query plan, but in fact

SELECT a.i, a.x
FROM aTable AS a
WHERE EXISTS
(SELECT *
FROM bTable AS b
WHERE b.id = a.id)
OR @.arg = ''

should perform very well when @.arg is ''. SQL Server will understand
that it does have to access bTable at all. It will appear in the plan,
but a so-called startup expression prevents it from being accessed when
there is no need to. (Interested readers can find more details on
this in http://www.sommarskog.se/dyn-search...mplexconditions.)

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Ahh, I get it now, hadn't fully comprehended what the WHERE EXISTS was
doing. That seems to work nicely.

Thanks!

Conditional AND statement in stored procedure Select query?

This should be simple but I can't figure it out. So I have a select query an
d
I want to run a conditional AND if a value is not NULL. Here is the type of
statment I want to run.
-- Declared values not shown.
SELECT * FROM MyTABLE
WHERE city = @.city
AND department = @.department
IF @.statusCode is NOT NULL
AND status IN (SELECT Item FROM TsqlSplit(@.statusCode) TsqlSplit)
END
-- The TsqlSplit function simply takes in a string of statusCodes "1,4,6"
and splits them out for the IN clause. It works fine if a string is actually
passed but I simply want to execute the IN clause if the @.statusCode value
passed to the procedure is not NULL.
I looked at the WHEN THEN clause but that only seems to work for equalities
AND status = WHEN @.statusCode IS NULL THEN ...
Thanks folks.SQL will short-circuit when the first part of an OR condition fails, so
this should work (performance may suffer though)
...
and (@.statusCode is null
or status in (select item from dbo.tsqlSplit(@.statusCode))
another option is to default the @.statusCode variable to the list of
possible status codes. then you won't have to check if @.statusCode is null.
Ramez wrote:
> This should be simple but I can't figure it out. So I have a select query
and
> I want to run a conditional AND if a value is not NULL. Here is the type o
f
> statment I want to run.
> -- Declared values not shown.
> SELECT * FROM MyTABLE
> WHERE city = @.city
> AND department = @.department
> IF @.statusCode is NOT NULL
> AND status IN (SELECT Item FROM TsqlSplit(@.statusCode) TsqlSplit)
> END
> -- The TsqlSplit function simply takes in a string of statusCodes "1,4,6"
> and splits them out for the IN clause. It works fine if a string is actual
ly
> passed but I simply want to execute the IN clause if the @.statusCode value
> passed to the procedure is not NULL.
> I looked at the WHEN THEN clause but that only seems to work for equalitie
s
> AND status = WHEN @.statusCode IS NULL THEN ...
> Thanks folks.

Concatination of empty field and removal of "" symbols

Hello...

I am going crazy trying to figure out how to do this. I have a flat file which I am massaging the data and loading into a table here is an example of a line out of the flat file:

"ABC NUTRITIONAL PRODUCTS","550","","","N","FAIR OAKS","","","COLORADO SPRINGS","C0","","","","","","","","A","","",""

My problem is that I have one field which is this address in a concatinated form. The fields that do not apply to this entry are suite#, floor# and other columns which are designated by the "" characters. The final concatinated addres field looks like this:

"550""""N""FAIR OAKS"""

I would like to remove the "" characters in the concatinated string. I just don't know the best way to do this? I was told DTS had a way of removing the "" from the flat file source. Since I have not used DTS extensively I am not sure if this is true. I was wondering how in SSIS I could go about removing the "" marks without removing the "" say if someone is quoted eg. John said "This is only a test". Removing the quotation marks in this instance would be changing the data. I am not sure how to do this and any help or advice is greatly appreciated!

Thank you...

SSIS has a powerful expression language that allows you to carry out many operations, including text manipulation.

In your case, you can use this expression language in a Derived Column Transform.

-Jamie