Concatenating fields: select

Showing posts with label select. Show all posts

Tuesday, March 27, 2012

Configuration Manager ?

Each time you start a SSIS project you have Solutions Configuration showing "Development". In the drop down box you can select "Configuration Manager..."

Can someone tell me the use of this, and is it related to the package configurations somehow. I've tried to create new Solution Configurations like "Test" and "Production" with the purpose of binding different configuration files and deplymentfolders to each Solution Configuration but still it seems like this isn't the way i should be used...

This is something inherent to Visual Studio rather than BIDS or SSIS. It doesn't have any relevance to package configurations that we know and love (!!!).

I don't know how this VS feature is supposed to be used so I say whether it would be useful for SSIS or not. I'd like to know though!!!

-Jamie

|||

The only thing i could see any use of this was to create 3 enviroments - Development, Test and Production.

I each of those i'll set the outputpath to bin\Development, bin\Test, bin\Production

So when i build the package i can choose the enviroment to build it to so that i can keep those files sepperate....

|||

Is there a way to reference the current value within a variable ?

This would be helpfull for having the servername depend on the selected configuration.

Thanks in advance,

Geert

|||

What you are asking and what cpgl described seems totally unnecessary if you only use the built in Configuration support for SSIS. Things like file paths are server names should be changed through configurations, not coded into the package during "build".

I would expect each environment to have the required configiration resources in place, so there should be nothing to change in the package to prepare it for an environment. If you do change the package between environments it just defeats the purpose of having a test environment as what you tested is not what you will then promote to production. It may be a minor change, but being strict, no changes should be allowed.

The only exception I can see is when environments are combined on machines, and for that I'd recomend passing in a parameter through the execution host, e.g. DTEXEC /SET

Tuesday, March 20, 2012

Conditional XQuery: How to select a desirable node when it occurs multiple times

I would very much appreicate if someone could help me with the following

Return CountryCodes node based on the following rules:

(1) Ignore <AlternativeState> completely

(2) When <CurrentEvent>MarketSize</CurrentEvent> get CountryCodes from <MarketSize> node only

(3) When <CurrentEvent>MarketShare</CurrentEvent> get CountryCodes from <OtherEvents> node only

(4) When <CurrentEvent> doesn't exist then xml would have only one CountryCodes; get that node

I have come up with the following so far which is far from what is desirable

SELECT UsageID, Countries.Code.query('

for $CountryCode in .

return data($CountryCode)

') AS CountryCodes

FROM UsageAnalysis

CROSS APPLY xmlState.nodes('//*[not(self::AlternativeState)]/*/CountryCodes') AS Countries(Code)

Please keep in mind xml comes from a table column.

The following are three possible simplified cases

Case 1

<State>

<StatsState>

<CurrentState>

<MarketSize>

<CountryCodes>KT,LC,VG,SU,TT,UY,VE</CountryCodes>

</MarketSize>
<CurrentEvent>MarketSize</CurrentEvent>
</CurrentState>

</StatsState>

</State>

Case 2

<State>

<DefinitionState>

<CountryCodes>BR</CountryCodes>

</DefinitionState>

</State>

Case 3

<State>

<CountryCodes>FR</CountryCodes>

<AlternativeState>

<OtherEvents>

<CountryCodes>FR</CountryCodes>

</OtherEvents>

<MarketSize>

<CountryCodes>FR,FP,FG</CountryCodes>

</MarketSize>

<CurrentEvent>MarketShare</CurrentEvent>

</AlternativeState>

</OtherEvents>

<CurrentEvent>MarketShare</CurrentEvent>

<CountryCodes>,FR</CountryCodes>

</MarketSize>

</CurrentState>

</StatsState>

</State>

Hope this solve your problem:

Code Snippet

declare @.x xml

set @.x =

'<State>

</MarketSize>

<CurrentEvent>MarketSize</CurrentEvent>

</CurrentState>

</StatsState>

</State>

<State>

</DefinitionState>

</State>

<State>

</OtherEvents>

</MarketSize>

<CurrentEvent>MarketShare</CurrentEvent>

</AlternativeState>

</OtherEvents>

<CurrentEvent>MarketShare</CurrentEvent>

</MarketSize>

</CurrentState>

</StatsState>

</State>'

select @.x.query('

for $s in /State

return

if (data(($s/StatsState/CurrentState/CurrentEvent)[1]) = "MarketSize")

then $s/StatsState/CurrentState/MarketSize/CountryCodes

else (

if (data(($s/StatsState/CurrentState/CurrentEvent)[1]) = "MarketShare")

then $s/StatsState/CurrentState/OtherEvents/CountryCodes

else $s//CountryCodes

)

|||

Should this also be returned?

<CountryCodes>,FR</CountryCodes>

Please excuse me because I am rather new to the XML sector. I am confused by the question and the answer. I coded this up:

declare @.x xml
set @.x =
'<State>
<StatsState>
<CurrentState>
<MarketSize>
<CountryCodes>KT,LC,VG,SU,TT,UY,VE</CountryCodes>
</MarketSize>
<CurrentEvent>MarketSize</CurrentEvent>
</CurrentState>
</StatsState>
</State>
<State>
<DefinitionState>
<CountryCodes>BR</CountryCodes>
</DefinitionState>
</State>
<State>
<StatsState>
<CurrentState>
<OtherEvents>
<CountryCodes>FR</CountryCodes>
<AlternativeState>
<OtherEvents>
<CountryCodes>FR</CountryCodes>
</OtherEvents>
<MarketSize>
<CountryCodes>FR,FP,FG</CountryCodes>
</MarketSize>
<CurrentEvent>MarketShare</CurrentEvent>
</AlternativeState>
</OtherEvents>
<CurrentEvent>MarketShare</CurrentEvent>
<MarketSize>
<CountryCodes>,FR</CountryCodes>
</MarketSize>
</CurrentState>
</StatsState>
</State>'

select coalesce (
nullif(t.c.query('./StatsState/CurrentState/MarketSize/CountryCodes').value('.','varchar(20)'), ''),
nullif(t.c.query('./StatsState/CurrentState/OtherEvents/CountryCodes').value('.','varchar(20)'),''),
t.c.query('./DefinitionState/CountryCodes').value('.','varchar(20)')
)
as CountryCodes
from @.x.nodes('State') t(c)

and received this result:

/*
CountryCodes
--
KT,LC,VG,SU,TT,UY,VE
BR
,FR
*/

Do the correct results need to include the markup such that the results should look more like this:

/*
CountryCodes
--
<CountryCodes>KT,LC,VG,SU,TT,UY,VE</CountryCodes><CountryCodes>BR</CountryCodes><CountryCodes>,FR</CountryCodes>
*/

(Trying to learn what is going on -- and I'm a bit confused.)

I appreciate the help.

|||

Jinghao, thanks very much. Your provided snippet does exactly what I have been trying to achieve. The only change I decided to introduce is to use data() so that I could get the scalar values for country codes as follows:

select @.x.query('

for $s in /State

return

if (data(($s/StatsState/CurrentState/CurrentEvent)[1]) = "MarketSize")

then data($s/StatsState/CurrentState/MarketSize/CountryCodes)

else (

if (data(($s/StatsState/CurrentState/CurrentEvent)[1]) = "MarketShare")

then data($s/StatsState/CurrentState/OtherEvents/CountryCodes)

else data($s//CountryCodes)

)

Result set from your query:

Results after introducing data()

KT,LC,VG,SU,TT,UY,VE BR FR

Now I could use a function call to return a list of country codes.

Thanks again for your help.

|||

Kent,

I must say that it took me a while to fully understand the solution you suggested by clever use of COALESCE. It did exactly what I was trying to achieve. i.e get a list of selected country codes.

KT,LC,VG,SU,TT,UY,VE

I just wanted to have a list of countries, without having any markups. i.e. just the scalar values of <countryCodes>

Your response has shown me another use of COALESCE function and I very much appreciate your help

Conditional Where using a Parameter

How do I construct a select with a conditional where:

If DefaultWH is not blank I want to add a "AND part".

SELECT DISTINCT Name
FROM Warehouse
WHERE (Cono = @.Company)

CASE WHEN

@.DefaultWH' <> ' ' THEN

AND (@.DefaultWH = whseid)

END

Here is something that I have done. Rather than use a blank, set it to null and use an isnull.

Example:

If @.DefaultWh = ''
Begin
Set @.DefaultWh = NULL
End

Select Distinct Name
from Warehouse
Where (Cono = @.Company)
and isnull(@.DefaultWH, WhseID) = WhseID

Hope that helps!

BobP

|||

Try :

SELECT DISTINCT Name
FROM Warehouse
WHERE ((Cono = @.Company) and (@.DefaultWH='')) or ((Cono = @.Company) and (@.DefaultWH=whseid))

Hope it work's...

|||

Neith of the examples worked. I need to have the query run in 1 of 2 formats

Parameter 1 = select company

Parameter 2 = select DefaultWH

Parameter 3 =

SELECT DISTINCT Name
FROM Warehouse
WHERE (Cono = @.Company) < === from Parameter 1

SELECT DISTINCT Name
FROM Warehouse
WHERE (Cono = @.Company) AND (@.DefaultWH = whseid) <===From Parameter 1 and 2

I need to be able to drop or include the "AND part" based on the value of Parameter 2

Parameter 1 runs and I pick a company#

Parameter 2 runs and it returns DefafultWH as a blank (access to all warehouses) or a value (limited to that warehouse) based on the Company# enter for Parameter 1.

If Parameter 2 is blank then run Parameter 3 -- Select without the AND

If Parameter 2 is not blank then run Parameter 3 -- Select with the AND part

It just cant be that hard to to. I just have not been that this that long to figure it out.

|||

My example would work in both scenarios.

This is what it does:

The ISNULL syntax returns the value of @.DefaultWH if it is NOT null, and the value of WhseID if it is. So, If parameter2 is blank, change it to null, and the AND clause will look like this:

Parameter1 = 'ABC Company'

Parameter2 = blank (Unselected)

Select Distinct Name
from Warehouse
Where Cono = 'ABC Company'
and WhseID = WhseID

So this effectively removes the AND. The only caveat to this: If WhseID can be NULL, then you would need to add one more statement.

Select Distinct Name
From Warehouse
Where Cono = @.Company
And (WhseID = isnull(@.DefaultWH,WhseID) or WhseID is null)

You have to add the "WhseID is null" clause because null does not "=" null, it only IS null.

Feel free to email me at bobp1339
at
yahoo

BobP

|||

Where does the code go

If I insert on the generic query screeen is says that @.DefaultWH is not declared or defined

If I insert the following into the generic code screen a

=Code.GetSQL()

and then insert the code in the Report code section I get errors there to.

Monday, March 19, 2012

Conditional Union!

Conditional UNION!

Hi all,
I have a query that if it returns data i want to perform a union on it.
IE: Select * FROM myTable WHERE myColumn = 'a' ORDER BY NEWID()
If (? Above query returns rows)
UNION
SELECT * FROM myTable 2 WHERE myColumn = 'b' ORDER BY NEWID
Is this kind of thing possible'
A basic example would be great!!
Cheers,
Adam."Adam Knight" <adam@.pertrain.com.au> wrote in message
news:OKNz$V9wFHA.3644@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> I have a query that if it returns data i want to perform a union on it.
> IE: Select * FROM myTable WHERE myColumn = 'a' ORDER BY NEWID()
> If (? Above query returns rows)
> UNION
> SELECT * FROM myTable 2 WHERE myColumn = 'b' ORDER BY NEWID
> Is this kind of thing possible'
>
Would this work?
Select *
FROM myTable
WHERE myColumn = 'a'
UNION
SELECT *
FROM myTable
WHERE myColumn = 'b'
and exists(
Select *
FROM myTable
WHERE myColumn = 'a')
Regards,
John|||Adam
Is there any reason to use UNION instead of UNION ALL? Do you want to
eliminate duplications?
"Adam Knight" <adam@.pertrain.com.au> wrote in message
news:OKNz$V9wFHA.3644@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> I have a query that if it returns data i want to perform a union on it.
> IE: Select * FROM myTable WHERE myColumn = 'a' ORDER BY NEWID()
> If (? Above query returns rows)
> UNION
> SELECT * FROM myTable 2 WHERE myColumn = 'b' ORDER BY NEWID
> Is this kind of thing possible'
> A basic example would be great!!
> Cheers,
> Adam.
>
>|||Yes!
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:edBylb%23wFHA.3000@.TK2MSFTNGP12.phx.gbl...
> Adam
> Is there any reason to use UNION instead of UNION ALL? Do you want to
> eliminate duplications?
>
> "Adam Knight" <adam@.pertrain.com.au> wrote in message
> news:OKNz$V9wFHA.3644@.TK2MSFTNGP11.phx.gbl...
>|||Try:
SELECT DISTINCT *
FROM MyTable
WHERE mycolumn IN ('A','B')
AND EXISTS
(SELECT *
FROM MyTable
WHERE mycolumn = 'A') ;
ORDER BY NEWID() fails under UNION or DISTINCT unless you also add NEWID()
to the SELECT list (in which case duplicates would not be eliminated).
Apparently your table doesn't have a key. I suggest you fix that problem
first but I don't see how this query helps you do that.
If the above doesn't help, please post DDL, sample data and required results
as suggested here:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--

Conditional Union!

Sunday, March 11, 2012

Conditional Split - DatTime Condition

Hi,

How do I make a condition for a DateTime field?

The SQL that I use for it is:

select..

from..

where...anddatePart(hh, myDateTimefield)> 10

Thank you!!

If you look in BOL for Expressions in SSIS you'll see it is much the same.|||BOL?|||BOL = Books on Line. SQL's help file.

For a Conditional split, in the condition you would put your condition. Example: Col1 == 1

This would cause any column with a value of 1 to go to that output. Anything not matching any one of the conditions would go to the default output.

Options are:
== (Double =)
<=
>=
!=

See BOL for Conditional Split help.

Conditional sorting in order by clause

Hi,
I have a query as

select name, age, address from employee order by name

Now i want to do sorting as ASC or DESC in order by clause dynamically.

I tried something like this :-

declare @.Order int
set @.Order = 1

select name, age, address from employee
order by name
CASE
WHEN @.Order = 0 THEN ASC
WHEN @.Order = 1 THEN DESC
END

But its giving me error, Is it correct or is there any other way to do conditional sorting?

order by is usually the last statement in a query and u cant do it this way...simple way is use if-else ...

if(@.order=1)

select ...order by name desc

else

select ...order bu name asc

u may try to use dynamic sql and achive it too, but its not adviseable...

|||

Use the following query it is a conditional sorting...

Declare @.Order int
Set @.Order = 1

Selecct name, age, address from employee
Order By
CASE WHEN @.Order = 0 THEN Name End ASC,
CASE WHEN @.Order = 1 THEN Name End DESC

Conditional Select Statement

Hello dbForumers,

Yet another puzzling question. I remember I saw somewhere a particular syntax to select a column based on a conditional predicate w/o using a user defined function. What I want to accomplish is this : SELECT (if column colA is empty then colB else colA) as colC from SomeTable. Possible ? Not possible? Have I hallucinated ?

Thank You!possible.
select (case colA when ='' then colB else colA end) as colC

Originally posted by Rollmops
Hello dbForumers,

Yet another puzzling question. I remember I saw somewhere a particular syntax to select a column based on a conditional predicate w/o using a user defined function. What I want to accomplish is this : SELECT (if column colA is empty then colB else colA) as colC from SomeTable. Possible ? Not possible? Have I hallucinated ?

Thank You!|||Yay, right on target.

But now I have some difficulties testing the NULL state... the syntax: ...(CASE VTE1 WHEN NULL THEN ACHN ELSE VTE1 END) AS COND_ACHN... won't throw any errors but wont work as excepted since it always sends the ELSE case no matter what...|||select isnull(vte1,achn) as COND_ACHN
or

select (CASE WHEN VTE1 is NULL THEN ACHN ELSE VTE1 END) AS COND_ACHN

Originally posted by Rollmops
Yay, right on target.

But now I have some difficulties testing the NULL state... the syntax: ...(CASE VTE1 WHEN NULL THEN ACHN ELSE VTE1 END) AS COND_ACHN... won't throw any errors but wont work as excepted since it always sends the ELSE case no matter what...|||Yay, right on target.

But now I have some difficulties testing the NULL state... the syntax: ...(CASE VTE1 WHEN NULL THEN ACHN ELSE VTE1 END) AS COND_ACHN... won't throw any errors but wont work as excepted since it always sends the ELSE case no matter what...|||To determine if an expression is NULL, use IS NULL or IS NOT NULL rather than comparison operators (such as = or !=).
follow the code of my previous message.It should work for u.

Originally posted by Rollmops
Yay, right on target.

But now I have some difficulties testing the NULL state... the syntax: ...(CASE VTE1 WHEN NULL THEN ACHN ELSE VTE1 END) AS COND_ACHN... won't throw any errors but wont work as excepted since it always sends the ELSE case no matter what...|||I just had to remove the 'VTE1' in ...(CASE VTE1... for the predicate to work accordingly =) anyways thanks a lot it works just fine now =)

Conditional select in view

Greetings all,
I have three databases dmart, dmart_a and dmart_b. The first being a pointer database, has lookup table that one has to query to know which one of the two databases (dmart_a and dmart_b) is online. I want to create a view which can dynamically select the data from the database that is online . Following is the SQL which I was able to write however I am not able to precede any further b'cause of error Sub-query returns more than one row. All your suggestions are welcomed.

SELECT CASE
WHEN dbname = 'dmart_a'
THEN (select count(*) from dmart_a.upload.person_data)
ELSE (select count(*) from dmart_b.upload.person_data)
END --AS 'Database to point'
FROM dmart_db_pointerAs you have posted a question in the SQL server Article section it is being moved to SQL Server Forum.

MODERATOR.|||

Quote:

Originally Posted by VirDesi

SELECT CASE
WHEN dbname = 'dmart_a'
THEN (select count(*) from dmart_a.upload.person_data)
ELSE (select count(*) from dmart_b.upload.person_data)
END --AS 'Database to point'
FROM dmart_db_pointer

try:

select dmart_db_pointer.dbname, cnt_a, cnt_b from
dmart_db_pointer left join
(select 'dmart_a' as dbname, count(*) as cnt _afrom dmart_a.upload.person_data) dmart_a on dmart_a.dbname = dmart_db_pointer.dbname
left join (select 'dmart_b' as dbname, count(*) as cnt_b from dmart_b.upload.person_data) dmart_b on dmart_b.dbname = dmart_db_pointer.dbname

Conditional SELECT

Dear Group

I'm having trouble with the statement below. I tried CASE and IF
without success. What I'm trying to do:
There is a field in the database called Business_TelNo. If the field
has some value, I would like to return a generated field
(LaBusinessTelNo), which is the label of Busines_TelNo, reading
'Phone:'
If Business_TelNo has no value, the label should be set to ''.

Something like this:
SELECT i2b_vw_contact.Business_TelNo AS Business_TelNo,
IF (LEN(Business_TelNo) > 0) BEGIN SELECT 'Phone: ' AS LaBusinessTelNo
END ELSE BEGIN SELECT '' AS LaBusinessTelNo END
FROM i2b_vw_contact

This is working:
SELECT i2b_vw_contact.Business_TelNo AS Business_TelNo,
'Phone: ' AS LaBusinessTelNo
FROM i2b_vw_contact

PS: I know it would be much easier to add some logic in the
application but need to do this in SQL.

Thanks very much for your time and efforts!

MartinSELECT business_telno,
CASE WHEN business_telno>'' THEN 'Phone: ' ELSE '' END AS labusiness_telno
FROM i2b_vw_contact

You can find the CASE and IF syntax in Books Online but understand that CASE
is an *expression* whereas IF is a *statement* and therefore IF can't be
used as part of a query.

--
David Portas
SQL Server MVP
--|||Thanks David!
Have a nice day :-)

conditional reading of data ...

I have a need to allow users select permission depending upon
a flag and userid. Is it possible to do it on the server instead of on clien
t?
Because using 'Access front end', I am unable to restrict table view to
secured records. However, I am able to restrict the view using forms.
Thanks for your help in advance!
-MeConsider creating SQL Server views with option VIEW_METADATA. This will
limit users to only data exposed by the views and Access won't try to access
the underlying tables directly.
Hope this helps.
Dan Guzman
SQL Server MVP
"Me" <Me@.discussions.microsoft.com> wrote in message
news:B781CADF-9100-4295-937D-87383BA4954B@.microsoft.com...
>I have a need to allow users select permission depending upon
> a flag and userid. Is it possible to do it on the server instead of on
> client?
> Because using 'Access front end', I am unable to restrict table view to
> secured records. However, I am able to restrict the view using forms.
> Thanks for your help in advance!
> -Me
>|||Dan,
Its a great idea, I will implement it in some cases. However, what do I do
when I have to add/modify the data? If I don't link the table directly, I
won't be able to save data.
Here is my situation. I have a table say 'Tally', only admin/s are allowed
to create records in this table. It has a flag indicating if a particular
record in 'Tally' is confidential. If so, all users aren't allowed to view
this record. Only users associated with particular 'Tally' can view/modify
records. Information about who can access is stored in another table which i
s
linked to 'Tally' with a key.
As far as viewing of the data is concerned your idea of creating a view will
work
fine. But if users have to modify the record, they will need access to the
table.
Any ideas?
Appreciate your help!
-Me
"Dan Guzman" wrote:

> Consider creating SQL Server views with option VIEW_METADATA. This will
> limit users to only data exposed by the views and Access won't try to acce
ss
> the underlying tables directly.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Me" <Me@.discussions.microsoft.com> wrote in message
> news:B781CADF-9100-4295-937D-87383BA4954B@.microsoft.com...
>
>|||> As far as viewing of the data is concerned your idea of creating a view
> will
> work
> fine. But if users have to modify the record, they will need access to the
> table.
> Any ideas?
You ought to be able to modify data via the view as long as only one base
table is affected. For modifications via Access, I believe you also need to
identify the unique column(s) to Access so that it can construct the needed
SQL.
Hope this helps.
Dan Guzman
SQL Server MVP
"Me" <Me@.discussions.microsoft.com> wrote in message
news:5861EBE1-670C-4687-B8DF-13D264443643@.microsoft.com...[vbcol=seagreen]
> Dan,
> Its a great idea, I will implement it in some cases. However, what do I do
> when I have to add/modify the data? If I don't link the table directly, I
> won't be able to save data.
> Here is my situation. I have a table say 'Tally', only admin/s are
> allowed
> to create records in this table. It has a flag indicating if a particular
> record in 'Tally' is confidential. If so, all users aren't allowed to view
> this record. Only users associated with particular 'Tally' can view/modify
> records. Information about who can access is stored in another table which
> is
> linked to 'Tally' with a key.
> As far as viewing of the data is concerned your idea of creating a view
> will
> work
> fine. But if users have to modify the record, they will need access to the
> table.
> Any ideas?
> Appreciate your help!
> -Me
> "Dan Guzman" wrote:
>

Thursday, March 8, 2012

Conditional Parameters

Hi all
I cant get my head round conditional parameters. I have a report with two
parameters and I would like the user to be able to select either one or both
of the parameters.
IE.
Param 1 Firstname
Param 2 Lastname
The user should be able to enter into the first parameter, or the second
parameter or both. How do I acheive this and construct the SQL accordingly.
Thanks
Dave.David,
I used similar code in one of my reports:
where FirstName like (case when IsNull(@.FName, '') = '' then '%' else '%' +
@.FName end)
and LastName like (case when IsNull(@.LName, '') = '' then '%' else '%' +
@.LName end)
HTH,
Andrei.
"David Hines" <DavidHines@.discussions.microsoft.com> wrote in message
news:249315AC-DE87-4AF6-8013-025A9CA4EA81@.microsoft.com...
> Hi all
> I cant get my head round conditional parameters. I have a report with two
> parameters and I would like the user to be able to select either one or
both
> of the parameters.
> IE.
> Param 1 Firstname
> Param 2 Lastname
> The user should be able to enter into the first parameter, or the second
> parameter or both. How do I acheive this and construct the SQL
accordingly.
> Thanks
> Dave.|||Hi Andrei
You pointed me in just the right direction Thankyou.
Finished SQL was
WHERE (FirstName LIKE '%' + (CASE WHEN IsNull(@.Fname, '') = '' THEN '%'
ELSE @.Fname END) + '%') AND (LastName LIKE '%' + (CASE WHEN IsNull(@.LName,
'') = '' THEN '%' ELSE + @.Lname END) + '%') AND (LEN(@.Lname) +
LEN(@.OwnerCode) > 0)
Again Many Thanks for your speedy response.
Dave.
"andrei" wrote:
> David,
> I used similar code in one of my reports:
> where FirstName like (case when IsNull(@.FName, '') = '' then '%' else '%' +
> @.FName end)
> and LastName like (case when IsNull(@.LName, '') = '' then '%' else '%' +
> @.LName end)
> HTH,
> Andrei.
>
> "David Hines" <DavidHines@.discussions.microsoft.com> wrote in message
> news:249315AC-DE87-4AF6-8013-025A9CA4EA81@.microsoft.com...
> > Hi all
> > I cant get my head round conditional parameters. I have a report with two
> > parameters and I would like the user to be able to select either one or
> both
> > of the parameters.
> >
> > IE.
> >
> > Param 1 Firstname
> > Param 2 Lastname
> >
> > The user should be able to enter into the first parameter, or the second
> > parameter or both. How do I acheive this and construct the SQL
> accordingly.
> >
> > Thanks
> >
> > Dave.
>
>|||Another solution - I just don't like using LIKE...
--
WHERE (FirstName = @.Fname OR @.Fname IS NULL)
AND (LastName = @.Lname OR @.Lname IS NULL)
AND ...
--
This works very well when you have "<ALL>" as an option for a parameter.
Fred
"David Hines" wrote:
> Hi Andrei
> You pointed me in just the right direction Thankyou.
> Finished SQL was
> WHERE (FirstName LIKE '%' + (CASE WHEN IsNull(@.Fname, '') = '' THEN '%'
> ELSE @.Fname END) + '%') AND (LastName LIKE '%' + (CASE WHEN IsNull(@.LName,
> '') = '' THEN '%' ELSE + @.Lname END) + '%') AND (LEN(@.Lname) +
> LEN(@.OwnerCode) > 0)
> Again Many Thanks for your speedy response.
> Dave.
>
> "andrei" wrote:
> > David,
> >
> > I used similar code in one of my reports:
> >
> > where FirstName like (case when IsNull(@.FName, '') = '' then '%' else '%' +
> > @.FName end)
> > and LastName like (case when IsNull(@.LName, '') = '' then '%' else '%' +
> > @.LName end)
> >
> > HTH,
> > Andrei.
> >
> >
> > "David Hines" <DavidHines@.discussions.microsoft.com> wrote in message
> > news:249315AC-DE87-4AF6-8013-025A9CA4EA81@.microsoft.com...
> > > Hi all
> > > I cant get my head round conditional parameters. I have a report with two
> > > parameters and I would like the user to be able to select either one or
> > both
> > > of the parameters.
> > >
> > > IE.
> > >
> > > Param 1 Firstname
> > > Param 2 Lastname
> > >
> > > The user should be able to enter into the first parameter, or the second
> > > parameter or both. How do I acheive this and construct the SQL
> > accordingly.
> > >
> > > Thanks
> > >
> > > Dave.
> >
> >
> >

Conditional Insert

I need to do a conditional insert. This is what I have tried, it does not work.
What am I doing incorrectly?

IF (SELECT COUNT(*) FROM TBL1 INNER JOIN
TBL2 ON TBL1.MODEL_ID = TBL2.MODEL_ID INNER JOIN
TBL3 ON TBL1.PRO_TYPE = TBL3.TYPE INNER JOIN
TBL4 ON TBL1.PRO_SITE = TBL4.WHDESC) > 0

INSERT INTO TBL5
SELECT TBL1.PRO_SITE, TBL1.MODEL_ID,
TBL1.NUM_SLOTS, TBL1.TARGET_DAYS, GETDATE() AS Expr1,
TBL3.TYPE_ID, NULL AS Expr2, TBL1.NUM_SLOTS AS Expr3, NULL AS Expr4, NULL
AS Expr5, 0 AS RAMP
FROM TBL1 INNER JOIN
TBL2 ON TBL1.MODEL_ID = TBL2.MODEL_ID INNER JOIN
TBL3 ON TBL1.PRO_TYPE = TBL3.TYPE INNER JOIN
TBL4 ON TBL1.PRO_SITE = TBL4.WHDESC

UPDATE TBL5
SET TEAMS=0
GO
UPDATE TBL5
SET TEAMS = (SELECT NUM_SLOTS
FROM TBL6 R1
WHERE (R1.TEAM_ID = TBL5.TEAM_ID)
)
WHERE (TEAM_ID =
(SELECT TEAM_ID
FROM TBL6 R3
WHERE (R3.TEAM_ID = TBL5.TEAM_ID)))
GO
UPDATE TBL5
SET TOTALTEAMS = TEAMS + RAMP
GO

I need to do a conditional because sometimes the select that returns data contains no records.

Thanks...Try this:

IF EXISTS
(SELECT 1
FROM TBL1
INNER JOIN TBL2 ON TBL1.MODEL_ID = TBL2.MODEL_ID
INNER JOIN TBL3 ON TBL1.PRO_TYPE = TBL3.TYPE
INNER JOIN TBL4 ON TBL1.PRO_SITE = TBL4.WHDESC)
BEGIN
UPDATE <stuff here>
END
ELSE
BEGIN
INSERT <stuff here>
END

Wednesday, March 7, 2012

Conditional if within a Select Where statement?

Update #tempResourceMetrics
Set RequestsStartPeriod = (Select Count(Distinct ProjectID) From #tempResourceAllocation
Where #tempResourceAllocation.ParentDepartmentID = #tempResourceMetrics.ProjectDivisionID
And (Month(#tempResourceAllocation.StartDate) = Month(GETDATE()) - 1)
And #tempResourceAllocation.ProjectStatusID In (1, 2, 3, 4)
And #tempResourceAllocation.ProjectCategoryID = 1333)

In the second condition I'm using Month() to ensure that the totals I get for this column are calculated from the entries created in the preceeding month. The problem appears in January when the preceeding month becomes 12 as opposed to 1(what my code would think) and also the year changes.

How can I modify my select or update statements so that this logic would be included correctly?

Try:

> And (Month(#tempResourceAllocation.StartDate) = Month(GETDATE()) - 1)

And (

#tempResourceAllocation.StartDate >= convert(varchar(6) , dateadd(month, -1, getdate()), 112) + '01'

and

#tempResourceAllocation.StartDate < convert(varchar(6) , getdate()) + '01'

)

AMB

|||

This should work also, AND has the advantage that it will use any indexing on StartDate:

Code Snippet

AND ( #tempResourceAllocation.StartDate >= dateadd( month, datediff( month , 0, getdate() ) -1 , 0 )
AND #tempResourceAllocation.StartDate < dateadd( month, datediff( month, 0, getdate() ), 0 )

)

|||
Thanks for your help guys! I'll try out both solutions.

conditional IF

Hello. I have a query whice look like this:
select a,b,c,d from table1;
Now - when pressing a cell in the first column we are jumping to
another report with the value as parameter (called- p_param);.
In the other report the query is:
select * from table1 where a=::p_param.
What I want to do is taht in the first report I'll will check if the
second query return any result and if so to leave it as a link. If the
second query does't return any result (zero rows) to remove the link so
the user won't go to an empty report.
So first i need to know how to check in the first report what will be
the result of the second query.
Can u help me?
Thanks in advance,
Roy.What if you use some code that calls a stored procedure and it will return a
0 or a 1. Then you can return a link or a javascript alert indicating there
was no data to return so the report would be empty.
--
"Everyone knows something you don't know"
"nicknack" wrote:
> Hello. I have a query whice look like this:
> select a,b,c,d from table1;
> Now - when pressing a cell in the first column we are jumping to
> another report with the value as parameter (called- p_param);.
> In the other report the query is:
> select * from table1 where a=::p_param.
> What I want to do is taht in the first report I'll will check if the
> second query return any result and if so to leave it as a link. If the
> second query does't return any result (zero rows) to remove the link so
> the user won't go to an empty report.
> So first i need to know how to check in the first report what will be
> the result of the second query.
> Can u help me?
> Thanks in advance,
> Roy.
>|||What if you use some code that calls a stored procedure and it will return a
0 or a 1. Then you can return a link or a javascript alert indicating there
was no data to return so the report would be empty.
--
"Everyone knows something you don't know"
"nicknack" wrote:
> Hello. I have a query whice look like this:
> select a,b,c,d from table1;
> Now - when pressing a cell in the first column we are jumping to
> another report with the value as parameter (called- p_param);.
> In the other report the query is:
> select * from table1 where a=::p_param.
> What I want to do is taht in the first report I'll will check if the
> second query return any result and if so to leave it as a link. If the
> second query does't return any result (zero rows) to remove the link so
> the user won't go to an empty report.
> So first i need to know how to check in the first report what will be
> the result of the second query.
> Can u help me?
> Thanks in advance,
> Roy.
>

conditional IF

Hello. I have a query whice look like this:
select a,b,c,d from table1;
Now - when pressing a cell in the first column we are jumping to another report with the value as parameter (called- p_param);.
In the other report the query is:
select * from table1 where a=::p_param.
What I want to do is taht in the first report I'll will check if the second query return any result and if so to leave it as a link. If the second query does't return any result (zero rows) to remove the link so the user won't go to an empty report.
So first i need to know how to check in the first report what will be the result of the second query.
Can u help me?
Thanks in advance,
Roy.
From http://www.developmentnow.com/g/115_0_0_0_0_0/sql-server-reporting-services.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.comWhat if you use some code that calls a stored procedure and it will return a
0 or a 1. Then you can return a link or a javascript alert indicating there
was no data to return so the report would be empty.
--
"Everyone knows something you don't know"
"roy mm" wrote:
> Hello. I have a query whice look like this:
> select a,b,c,d from table1;
> Now - when pressing a cell in the first column we are jumping to another report with the value as parameter (called- p_param);.
> In the other report the query is:
> select * from table1 where a=::p_param.
> What I want to do is taht in the first report I'll will check if the second query return any result and if so to leave it as a link. If the second query does't return any result (zero rows) to remove the link so the user won't go to an empty report.
> So first i need to know how to check in the first report what will be the result of the second query.
> Can u help me?
> Thanks in advance,
> Roy.
>
> From http://www.developmentnow.com/g/115_0_0_0_0_0/sql-server-reporting-services.htm
> Posted via DevelopmentNow.com Groups
> http://www.developmentnow.com
>|||What if you use some code that calls a stored procedure and it will return a
0 or a 1. Then you can return a link or a javascript alert indicating there
was no data to return so the report would be empty.
--
"Everyone knows something you don't know"
"roy mm" wrote:
> Hello. I have a query whice look like this:
> select a,b,c,d from table1;
> Now - when pressing a cell in the first column we are jumping to another report with the value as parameter (called- p_param);.
> In the other report the query is:
> select * from table1 where a=::p_param.
> What I want to do is taht in the first report I'll will check if the second query return any result and if so to leave it as a link. If the second query does't return any result (zero rows) to remove the link so the user won't go to an empty report.
> So first i need to know how to check in the first report what will be the result of the second query.
> Can u help me?
> Thanks in advance,
> Roy.
>
> From http://www.developmentnow.com/g/115_0_0_0_0_0/sql-server-reporting-services.htm
> Posted via DevelopmentNow.com Groups
> http://www.developmentnow.com
>

Conditional group by

Hi,

Can anyone help me in writing this sql query, i want to group my select statement depending on the parameter user is passing.

Say when @.group='Cell' I want to group by CellID otherwise different conditions, something like below query but it is not working. I know we can't use case directly in where but please let me know if there is any other work around.

I don't want to use dynamic query and also this is big SP so i dont want to break sp in four conditions.

declare @.group varchar(10)

set @.group='Cell'

select cellid,sum(count)

FROM CellImpressionFact

WHERE ImpressionTypeLevelId = 2

AND ImpressionTypeId = 4

group by

case when group='Cell' then GROUP BY CellId

else group by activityID

end

This is not a good idea really. I would use dynamic SQL to provide this kind of capability if you really need to. It is possible (see code) but I would be very concerned about performance.

create table test
(
grouper int,
grouper2 int,
value decimal(10,5)
)
go
insert into test
select 1,1,10
union all
select 1,2,10
union all
select 1,3,10
union all
select 2,1,10
go
declare @.groupby varchar(10)
set @.groupBy = 'grouper2'

select max(grouper) as grouper,
max(grouper2) as grouper2,
sum(value) as valueSum
from test
group by case when @.groupBy = 'grouper' then grouper else grouper2 end

Note that the grouper2 column is of any value when you group by grouper, and vice versa (say it five times fast.)

Conditional Full-text search

Is there any way to use full-text search conditionally? For example, I have a query:

Code Snippet

select
Search.Rank,
Items.*
from
Items
inner join
freetexttable(Items, *, 'blablabla') as Search on
Items.ItemId = Search.[Key]
where
Items.ItemId = 1
and
Items.Type >= 0
order by
Search.Rank DESC

How can I put where clause to optimize my query? I want where clause to be executed before freetexttable, for more perfomance. It's obvious, that full-text search is not optimized well in my case, because where is executed after full-text search.

Is there any way to do it? Thank you in advance.

And if I have a query

select
Search.Rank,
Items.*
from
Items
inner join
freetexttable(Items, *, 'blablabla') as Search on
Items.ItemId = Search.[Key]

Is there any way to force freetexttable return only first 10 results, but not all of them? (I know, I can do it after freetexttable with 'select top 10', but I want inside freetexttable, to minimize the load of my SQL Server).
|||Read
"Items.ItemId = 1"
like
"Items.ItemId in (1, 2, 3, 4, 5, 10"
|||Unfortunately in this version of SQL Server, freetext search and SQL Server is separated from each other. Therefore the query optimizer is not able to optimize both queries within one step. You will need to use the optional top parameters provided for freetext search. Look in the BOL:

top_n_by_rank

When an integer value, n, is specified, FREETEXTTABLE returns only the top n matches, ordered by rank.

If filtering is performed in addition to the FREETEXTTABLE predicate, the filter is applied to the top n rows and fewer than top_n_by_rank rows will be returned. Enabling the precompute rank option in the sp_configure stored procedure can increase the prerformance of FREETEXTTABLE queries that use the top_n_by_rank parameter. For more information, see sp_configure (Transact-SQL) and sp_fulltext_service (Transact-SQL).

Jens K. Suessmeyer.

-
http://www.sqlserver2005.de
-|||

Hi Zhuravl,

As said by Jean, you can use top_n_by_rank to limit resultset. This is in the BOL:

" Limiting Result Sets to Return the Most Relevant Results

In many full-text queries, the number of items matching the search condition is very large. To prevent queries from returning too many matches, use the optional argument, top_n_by_rank, in CONTAINSTABLE and FREETEXTTABLE to specify the number of matches according to rank you want returned.

Note:

Using the top_n_by_rank argument returns a subset of rows that satisfy the full-text query. If top_n_by_rank is combined with other predicates, the query could return fewer rows than the number of rows that actually match all the predicates.
With this information, Microsoft SQL Server orders the matches by rank and returns only up to the specified number. This choice can result in a dramatic increase in performance. For example, a query that would normally return 100,000 rows from a table of one million rows are processed more quickly if only the top 100 rows are requested.

If you want only the top 3 matches returned on an earlier example using CONTAINSTABLE, the query looks like the following:

USE Northwind; GO SELECT K.RANK, CompanyName, ContactName, Address FROM Customers AS C INNER JOIN CONTAINSTABLE(Customers,Address, 'ISABOUT ("des*", Rue WEIGHT(0.5), Bouchers WEIGHT(0.9))', 3) AS K ON C.CustomerID = K.[KEY]; GO

Here is the result set:

RANK CompanyName ContactName address
- -- -
123 Bon app' Laurence Lebihan 12, rue des Bouchers 65 Du monde entier Janine Labrune 67, rue des Cinquante Otages 15 France restauration Carine Schmitt 54, rue Royale

This example returns the description and category name of the top 10 food categories where the Description column contains the words "sweet and savory" near either the word "sauces" or the word "candies."

SELECT FT_TBL.Description, FT_TBL.CategoryName, KEY_TBL.RANK FROM Categories AS FT_TBL INNER JOIN CONTAINSTABLE (Categories, Description, '("sweet and savory" NEAR sauces) OR ("sweet and savory" NEAR candies)' , 10 ) AS KEY_TBL ON FT_TBL.CategoryID = KEY_TBL.[KEY]; GO
"

Other references:

http://www.developmentnow.com/blog/SQL+Server+2005+Full+Text+Search+On+HTML+Documents.aspx

Regards

Nilton Pinheiro

www.mcdbabrasil.com.br

Tuesday, March 27, 2012

Configuration Manager ?

Tuesday, March 20, 2012

Conditional XQuery: How to select a desirable node when it occurs multiple times

Conditional Where using a Parameter

Monday, March 19, 2012

Conditional Union!

Conditional UNION!

Conditional Union!

Conditional Union!

Sunday, March 11, 2012

Conditional Split - DatTime Condition

Conditional sorting in order by clause

Conditional Select Statement

Conditional select in view

Conditional SELECT

conditional reading of data ...

Thursday, March 8, 2012

Conditional Parameters

Conditional Insert

Wednesday, March 7, 2012

Conditional if within a Select Where statement?

conditional IF

conditional IF

Conditional group by

Conditional Full-text search

Concatenating fields

Blog Archive

About Me