Showing posts with label fields. Show all posts
Showing posts with label fields. Show all posts

Friday, March 30, 2012

Hide Group Header Elements when Detail Visible

This seems like an easy request but I can't figure it out.
I have a table with multiple groups. Each group header contains subtotal
fields. I want to hide the contents of the subtotal fields on each group
header when that group is expanded but I want the field that contains the
name of the group to remain visible.
Thanks, JonI did this by adding an additional row to the group header.
Put the field that contains the name of the group on row 1 of your header,
and the subtotal field on row 2. Now you can select the field with the group
name to toggle Visibility on or off for both your Details and your Subtotal
field (or row). Leave the Subtotal field visible in the report definition
(Hidden=False), but hide the Details section.
Hth,
Flip
"Jon B" wrote:
> This seems like an easy request but I can't figure it out.
> I have a table with multiple groups. Each group header contains subtotal
> fields. I want to hide the contents of the subtotal fields on each group
> header when that group is expanded but I want the field that contains the
> name of the group to remain visible.
> Thanks, Jon

Hide From Print

Hi,
I would like to exclude some fields from printing, but they have to be
visible for the user in IE.
Is this possible?
tx,
Hippo
Greetz,
Hippo from [NL]
--
remove xxx from mailHippo, have you figured this out yet. I want to do the exact same thing but
have not been able to do it.
If you have please let me know how you did it.
Thank you,
Fred
"hippo" wrote:
> Hi,
> I would like to exclude some fields from printing, but they have to be
> visible for the user in IE.
> Is this possible?
> tx,
> Hippo
> Greetz,
> Hippo from [NL]
> --
> remove xxx from mail
>

Hide Find/Next, View report and some export formats in report view

Hi,
1. As Chris G asked 9/7/2006 it is not possible to change text on "view
report" and "Find/Next", but is it possible to hide those fields (or at least
find/next) for all users. (this a language issue, better for us to hide
fields than show them in english).
2. Is it possible to hide som export formats to alla users, we only use PDF
and excel, and also change text "Select a format" ?
/JeromeYou only need to change in one place, rsreportserver.config (Save a copy of
the file before making any changes). Reboot after the change. The below
shows commenting out the existing CSV export.
<!--
<Extension Name="CSV"
Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering"/>
-->
The above shows how you comment out a section. If you do the above then CSV
would not be available. However, you might want to leave CSV in. Sometimes
for a large amout of data CSV is faster than Excel. If you comment out like
above and put this in:
<Extension Name="CSV"
Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering">
<Configuration>
<DeviceInfo>
<Encoding>ASCII</Encoding>
</DeviceInfo>
</Configuration>
</Extension>
It will change CSV export so it uses ASCII instead of Unicode. This allows
Excel to automatically open up properly for the CSV. With Unicode it all
gets put into one column and then you have to split the data.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"/jerome k" <jeromek@.discussions.microsoft.com> wrote in message
news:C1F26C7C-C276-4CD7-B9FD-D7D8B134DC80@.microsoft.com...
> Hi,
> 1. As Chris G asked 9/7/2006 it is not possible to change text on "view
> report" and "Find/Next", but is it possible to hide those fields (or at
> least
> find/next) for all users. (this a language issue, better for us to hide
> fields than show them in english).
> 2. Is it possible to hide som export formats to alla users, we only use
> PDF
> and excel, and also change text "Select a format" ?
> /Jerome|||Great, thank you...
Any ideas how to hide (or translate) the "View Report"-button,
"Find/Next"-fields and "Select a format"-text ? The Refresh icon or the
"Enter"-key is enough for refreshing, and Find/Next is a standard feature in
the browser menue.
/J
"Bruce L-C [MVP]" wrote:
> You only need to change in one place, rsreportserver.config (Save a copy of
> the file before making any changes). Reboot after the change. The below
> shows commenting out the existing CSV export.
> <!--
> <Extension Name="CSV"
> Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering"/>
> -->
> The above shows how you comment out a section. If you do the above then CSV
> would not be available. However, you might want to leave CSV in. Sometimes
> for a large amout of data CSV is faster than Excel. If you comment out like
> above and put this in:
> <Extension Name="CSV"
> Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering">
> <Configuration>
> <DeviceInfo>
> <Encoding>ASCII</Encoding>
> </DeviceInfo>
> </Configuration>
> </Extension>
> It will change CSV export so it uses ASCII instead of Unicode. This allows
> Excel to automatically open up properly for the CSV. With Unicode it all
> gets put into one column and then you have to split the data.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "/jerome k" <jeromek@.discussions.microsoft.com> wrote in message
> news:C1F26C7C-C276-4CD7-B9FD-D7D8B134DC80@.microsoft.com...
> > Hi,
> >
> > 1. As Chris G asked 9/7/2006 it is not possible to change text on "view
> > report" and "Find/Next", but is it possible to hide those fields (or at
> > least
> > find/next) for all users. (this a language issue, better for us to hide
> > fields than show them in english).
> > 2. Is it possible to hide som export formats to alla users, we only use
> > PDF
> > and excel, and also change text "Select a format" ?
> >
> > /Jerome
>
>|||Sorry, no.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"/jerome k" <jeromek@.discussions.microsoft.com> wrote in message
news:83377ADE-3AF9-4614-AB2D-9D9CDF9B747F@.microsoft.com...
> Great, thank you...
> Any ideas how to hide (or translate) the "View Report"-button,
> "Find/Next"-fields and "Select a format"-text ? The Refresh icon or the
> "Enter"-key is enough for refreshing, and Find/Next is a standard feature
> in
> the browser menue.
> /J
> "Bruce L-C [MVP]" wrote:
>> You only need to change in one place, rsreportserver.config (Save a copy
>> of
>> the file before making any changes). Reboot after the change. The below
>> shows commenting out the existing CSV export.
>> <!--
>> <Extension Name="CSV"
>> Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering"/>
>> -->
>> The above shows how you comment out a section. If you do the above then
>> CSV
>> would not be available. However, you might want to leave CSV in.
>> Sometimes
>> for a large amout of data CSV is faster than Excel. If you comment out
>> like
>> above and put this in:
>> <Extension Name="CSV"
>> Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering">
>> <Configuration>
>> <DeviceInfo>
>> <Encoding>ASCII</Encoding>
>> </DeviceInfo>
>> </Configuration>
>> </Extension>
>> It will change CSV export so it uses ASCII instead of Unicode. This
>> allows
>> Excel to automatically open up properly for the CSV. With Unicode it all
>> gets put into one column and then you have to split the data.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "/jerome k" <jeromek@.discussions.microsoft.com> wrote in message
>> news:C1F26C7C-C276-4CD7-B9FD-D7D8B134DC80@.microsoft.com...
>> > Hi,
>> >
>> > 1. As Chris G asked 9/7/2006 it is not possible to change text on
>> > "view
>> > report" and "Find/Next", but is it possible to hide those fields (or at
>> > least
>> > find/next) for all users. (this a language issue, better for us to hide
>> > fields than show them in english).
>> > 2. Is it possible to hide som export formats to alla users, we only use
>> > PDF
>> > and excel, and also change text "Select a format" ?
>> >
>> > /Jerome
>>

Hide fields with blank value

I am creataing a report with a Local Mode report and ReportViewer. Part of the report has an address:

[FName] [LName]
[Address1]
[Address2]
[City] [State] [ZIP]
[Phone]

So the data might come out on the report:

John Doe
123 Fake Street
Suite 32
Somewhere USA 00101
(800) 555-1212

Which is great, except if address2 is blank (as in most cases it is) it comes out:

John Doe
123 Fake Street

Somewhere USA 00101
(800) 555-1212

How can I make it Hide the [Address2] Field if it is blank, so that extrat line space is taken out?

Thanks

UPDATE** - As soon as i posted this site i realized when you use the expression for this, it should be =Field(Value) = "". I had =Field(Value) <> "". I was backwards. Sorry for the uncecessary post.

In the visibility property of textbox give the expression as

IIF(Len(Fields.Address2.value)>0,True,False)

Thanks

SubbuSmile

Hide Fields on Export

When I export to a file, say excel, I want certain textboxes to not show up.
How can I hide them on export?
bradSorry, this is not possible. You can only hide based on e.g. report
parameters.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"bradtm" <bradtm@.discussions.microsoft.com> wrote in message
news:9145ACFB-54E5-4BFC-82B4-0368B7AFA0FE@.microsoft.com...
> When I export to a file, say excel, I want certain textboxes to not show
> up.
> How can I hide them on export?
> brad|||Don't forget, though, if you use your own "export" button through a
custom interface, setting the rs:FORMAT=PDF or similar, you can set a
report parameter that can be referenced to show or hide selected fields.

hide field from user

I have an app that connects to sqlepress. When run the field are loaded from the db. There are some fields that I don't want some of the users to see. Is there a way to do this without hardcoding the 'username' in the applicaton ?

I looked at the explicit permissions for username for the particular table, but I don't understand some of the terms listed.

Alter

Control

References

View definition

Normally you do this by creating a View that exposes just the columns that you want to show to the users. I don't know of any way to control column level access by user, particularly if you don't tell SQL who the users is. I would say that you create specific groups to control access level, then build Views with the right set of columns for each group. Then you can call the appropriate View based on the group membership.

Mike

sql

Friday, March 23, 2012

hidden fields rendering on server only!

I have a report with a matrix in which I conditionally hide one of the data
columns. This is working properly in preview on my development studio but
when I deploy to the report server it is showing these hidden data columns
when it is not supposed to - if I export the rendered report from the server
... it exports to pdf and excel with the data columns properly hidden - so
how can I fix this ... I cant understand what is happening. Please help as
release date is approaching. Thank youUpdate on this - I put the same conditional expressions on the border
property of the columns I want hidden and also the background color of the
column that gets conditionally colored - this seems to work although it
doesnt explain why it worked w/out that in dev studio AND why it exported
correctly - the only problem being on the server in html format. Still would
like to know why because if I had known that then I could have saved a lot of
time. Thanks
"MJT" wrote:
> I have a report with a matrix in which I conditionally hide one of the data
> columns. This is working properly in preview on my development studio but
> when I deploy to the report server it is showing these hidden data columns
> when it is not supposed to - if I export the rendered report from the server
> ... it exports to pdf and excel with the data columns properly hidden - so
> how can I fix this ... I cant understand what is happening. Please help as
> release date is approaching. Thank you

Wednesday, March 21, 2012

Hi need help to decide the indexers

Hello,

I have a situation here...

where we are inserting data of candidate...which includes fields...

1. First Name, Middle Name, Last Name ... (varchar(50))

2. Passport ID (no duplicates allowed but may be null )

3. Telephone number... ( may be null but no duplicates allowed )

4. Date of Birth ( mandatory )

5. Email id .......(no duplicates allowed but may be null )

The problem is that we already have about 1 lakh enteries in the database..

and it is expected to grow with time...

We need to define indexers such that it doesnot take time for searching or while inserting a new datafield into the database.

I hope that i am clear.

since, i suspect that that as it grows it will become more and more slow...!

This is database to store "resumes of different candidates".

So have decided...that

1. name 2. Passport no. 3. Email id: 4. Contact number

be the fields to prevent duplicacy of candidate data...

This involves checking for candidates existence every time anybody tries to make a new entry into database.

Please help to decide over the indexers...

Any good suggestions are welcomed...

Thanks for the reply...

\With regards,

Girish R. Pawar

You could easily have dupicates for Name. I would most certainly break the name into at least LastName and FirstName.

Two candidates 'could' be sharing a contact telephone number, so your 'no duplicates' constraint may be a problem.

I will often have an 'Age' field on the input form (BUT it isn't saved to the database) -it acts as a 'cross-check' against the DOB. DOB is so critical in some situations, we want to catch the obvious 'transpositions' immediately.

Your choices of indexes seem reasonable.

Friday, March 9, 2012

HelpTotals within Totals

I have a report with say two fields, market, amount. I need a report to present like this, but to do totals after line item presentation...example report looks like this. Question is, how do I do the totals part seperate from the line items??

Market amount
-- --
Texas 10.00
Texas 20.00
Texas 30.00
Boston 10.00
Boston 20.00
Boston 90.00

Totals Texas 60.00
Totals Boston 120.00You can set it up like the following by adding a Group on Market and adding a Running Total Field which Sums Amount to the Group Footer.

Market amount
-- --
Texas 10.00
Texas 20.00
Texas 30.00

Totals Texas 60.00

Boston 10.00
Boston 20.00
Boston 90.00

Totals Boston 120.00

- or -

If you know what Market is going to be, you can set up Formula Fields with the following Formula:

NumberVar intTotal
IF {Market} = 'Texas' THEN
NumberVar := NumberVar + {Amount}

- or -

There's a Formula that looks like: Sum(fld, condFld, cond). I have never used it, but I assume you can set it up simialr to:

Sum ( {Amount}, {Market}, 'Texas' )

(I didn't test this code, so you may have to tweak it a bit to work out any bugs.)|||Thanks malleyo but there's a subtle difference in the report...the footer has and entries for each market. The list of markets is dynamic (a group by field)...thus, option 1 and 2-3 aren't quite it...I have a feeling I have to use subreports.

Let me know if you have any other thoughts and thanks for your help.|||Hi Mallego
How to sum amount large than 10 ?|||ReportDude,
I guess you are right there is no option otherthan subreport, I had the same issue before and I used sub report.
But if you find better solution other than SubReport please post the solution.

Thanks,
Gragi

Help-Name Standardization Problem

Is there an application out there that will allow us to cleanse/merge-purge
the name fields for a consumer as well as for a commercial data warehouse?
In other words, we want to take, for example, Walmart, Wal-Mart, Walmart
Corp, etc and boil it down to a single WalMart Corporation entity.
An example of the consumer side would be Jim Smith, James E. Smith, Jimmy
Smith, etc.
Thanks!Welcome to the world of data scrubbing. There is no corporate lookup
function, you will have to do some string manipulation and parsing to clean
the data. If you can convince the users/business units to do this, the
better.
Ray Higdon MCSE, MCDBA, CCNA
--
"Paul Stanial" <p_stanial@.webfirst.com> wrote in message
news:102lb9shn77u392@.corp.supernews.com...
> Is there an application out there that will allow us to
cleanse/merge-purge
> the name fields for a consumer as well as for a commercial data warehouse?
> In other words, we want to take, for example, Walmart, Wal-Mart, Walmart
> Corp, etc and boil it down to a single WalMart Corporation entity.
> An example of the consumer side would be Jim Smith, James E. Smith, Jimmy
> Smith, etc.
>
> Thanks!
>
>|||Thanks for the response. I guess I'm thinking that if we could go against a
table with all registered U.S. corporations (e.g. D&B?), then we could
relate the names in the records, along with their addresses and City to the
correct corporate entity and then aggregate the relevent fields from each
into the new "master record."
Thanks again...
"Ray Higdon" <sqlhigdon@.nospam.yahoo.com> wrote in message
news:OpSpUiR8DHA.360@.TK2MSFTNGP12.phx.gbl...
> Welcome to the world of data scrubbing. There is no corporate lookup
> function, you will have to do some string manipulation and parsing to
clean
> the data. If you can convince the users/business units to do this, the
> better.
> --
> Ray Higdon MCSE, MCDBA, CCNA
> --
> "Paul Stanial" <p_stanial@.webfirst.com> wrote in message
> news:102lb9shn77u392@.corp.supernews.com...
> cleanse/merge-purge
warehouse?
Jimmy
>|||I don't think it exists or at least published anywhere, I could be wrong.
Ray Higdon MCSE, MCDBA, CCNA
--
"Paul Stanial" <p_stanial@.webfirst.com> wrote in message
news:102nk04oc8h1q91@.corp.supernews.com...
> Thanks for the response. I guess I'm thinking that if we could go against
a
> table with all registered U.S. corporations (e.g. D&B?), then we could
> relate the names in the records, along with their addresses and City to
the
> correct corporate entity and then aggregate the relevent fields from each
> into the new "master record."
> Thanks again...
> "Ray Higdon" <sqlhigdon@.nospam.yahoo.com> wrote in message
> news:OpSpUiR8DHA.360@.TK2MSFTNGP12.phx.gbl...
> clean
> warehouse?
Walmart
> Jimmy
>

Wednesday, March 7, 2012

Help: UNION vs. CONTAINSTABLE

I have n Tables: T_1, T_2, ... T_n that all have the same exact fields/columns.

Ultimately, I want to search through ALL n tables and return a single table of relevant results from all tables, arranged in order by rank.

I'm not sure if this is possible. So far, I have:

1SELECT RANK, field_1, field_2, ..., field_mFROM2(3SELECT RANK, field_1, field_2, ..., field_mFROM T_1,4CONTAINSTABLE(T_1, field_i,@.searchText) searchTable5WHERE KEY = T_1.field_i6UNION7SELECT RANK, field_1, field_2, ..., field_mFROM T_2,8CONTAINSTABLE(T_2, field_i,@.searchText) searchTable9WHERE KEY = T_2.field_i10UNION11.12.13.14UNION15SELECT RANK, field_1, field_2, ... field_mFROM T_n,16CONTAINSTABLE(T_n, field_i,@.searchText) searchTable17WHERE KEY = T_n.field_i18)19ORDER BY RANKDESC

I haven't tried it yet, but it seems wrong. How do we actually do this?

You should first create a CTE (Common Table Expression) or a VIEW:http://www.singingeels.com/Articles/Understanding_SQL_Complex_Queries.aspx

You would do something like this:

WITH MyTables (ColA, ColB etc...) AS ( SELECT * FROM T_1 UNION SELECT * FROM T_2)

now.. you do your query with "MyTables" as the table name...

|||

I'm now getting an error that says:

Cannot use a CONTAINS or FREETEXT predicate on object 'MyTables' because it is not full-text indexed.

That's true, though. The table we created isn't full-text enabled.

----------

My other option is to do a query for each table I want searched, then merge them all into a DataSet, then reorder the DataSet by rank. How tedious, though, especially if I want pagination of large tables.

|||

I read somewhere that SQL Server 2005 does not support free text searching over multiple tables. There's something called SQLONE that actually allows FTS over multiple tables.

Friday, February 24, 2012

help: Passing values to formula fields of subreport from the code in vb.net

hi,
i'm new in CR, i made a subreports(ReCaisse) in a main report(rptCaisseAll) and i established many formula fields in it, i want to fill the formula fields from the code in vb.net.
this is the code:
***********************************************
Dim SoCaisse As New ClsCaisse() //object caisse
Dim Report As New rptCaisseAll //main report
Dim r1 As New ReCaisse //Subreport

//
SoCaisse.GetRecord("Exchrate,AmountUS,AmountFc,s.Notes,s.DbCr,AccName,s.AccNo", "EntryDate=convert(datetime,''" & Me.txtDate.Text & "'',103)", "s.DbCr") //GetRecord establish sql statement

SoCaisse.SelectspecialMethod() //stored procedure

Report.Database.Tables.Item(0).SetDataSource(SoCaisse.CaisseSet.Tables(0))
Report.DataDefinition.FormulaFields.Item("Date").Text = "totext('" & Me.txtDate.Text & "')" // Date is a formula fields in rptCaisseAll-main report
Report.DataDefinition.FormulaFields.Item("SoldeUS").Text = "totext('" & Me.txtCUs.Text & "')" //SoldeUS is a formula fields in ReCaisse-subreport
**********************************************************
it passed the value to "Date" formula coz "Date" is a formula in the main report, but it not passed the value to "SoldeUS" formula fields; It appears blank.
so i tried:
***********************************
r1.DataDefinition.FormulaFields.Item("SoldeUS").Text = "totext('" & Me.txtCUs.Text & "')"
***********************************
but also not passed.

any help plz...
thanks.If Me.txtCUs.Text is a date then, format it

r1.DataDefinition.FormulaFields.Item("SoldeUS").Text = "totext('" & format(Me.txtCUs.Text,"dd-mmm-yyyy") & "')"

Sunday, February 19, 2012

Help: About ms sql query, how can i check if a part string exists in a string?

Hello to all,

I have a problem with ms sql query. I hope that somebody can help me.

i have a table "Relationships". There are two Fields (IDMember und RelationshipIDs) in this table. IDMember is the Owner ID (type: integer) und RelationshipIDs saves all partners of this Owner ( type: varchar(1000)). Example Datas for Table Relationships: IDMember Relationships .

3387 (2345, 2388,4567,...)

4567 (8990, 7865, 3387...)

i wirte a query to check if there is Relationship between two members.

Query:

Declare @.IDM int; Declare @.IDO int; Set @.IDM = 3387, @.IDO = 4567;

select *

from Relationship

where(IDMember= @.IDM)and( cast(@.ID0 as char(100)) in

(select Relationship.[RelationshipIDs]from Relationshipwhere IDMember= @.IDM))

But I get nothing by this query.

Can Someone tell me where is the problem? Thanks

Best Regards

Pinsha

Use the PATINDEX function to check if a one part is there in a string or not and you can write your query like this

Select * from RelationShip

Where (IDMember=@.IDM)

AND ( PATINDEX(@.IDO, RelationshipIDs) > 0 ) -- If the string is found this will return greater than 0 the starting of the string

This should help you

|||

try this, you have to tweak it a little to work correctly with small and big numbers ( like it will return 312 as connected to 12 but you can try to fix it your self for example by including separators between related ids)

createTable #Relationships

(IDMemberint,

Relationshipsvarchar(1000))

insertinto #Relationships

select' 3387','(2345, 2388,4567,...)'

insertinto #Relationships

select'4567','(8990, 7865, 3387...)'

Declare @.IDMint,

@.IDOint

Set @.IDM= 3387

SET @.IDO= 4567;

select*

from #Relationships

where(IDMember= @.IDM)and(charindex(cast(@.IDOasvarchar(100)),cast(Relationshipsasvarchar(1000)))>0)

droptable #Relationships

|||

Hello satya_tanwar,

i have tried to use the PATINDEX function, but if i used a variable in PATINDEX(@.IDO, RelationshipIDs) and (@.IDO = '3456', i doesn't work. If i use a constant in PATINDEX('3456', RelationshipIDs), it works.

I use ms sql 2005 to execute this query. Maybe can you tell me where is the problem?

Thank you very much

Best Regards

Pinsha

|||

Hallo japzgier,

I have also tried to use your example. But the variable incharindex function doesn't work. If i use constant incharindex function and it works.

i don't know whycharindex doesn't work with variable.

Maybe can you help me?

Thank you very much

Best Regards

Pinsha

|||

What is the variable type Pinsha...Could you please send the code to me coz i have tried to execute the query and its working. Whats result you are getting ?

or just try to modify the PatIndex part like this

(PATINDEX('%'+ @.IDO+'%', Relationships)> 0)

Will work for sure..

|||

I tested code I passed on my SQL server and I have no problems with it, I use SQL 2005 which version are you using?

|||

Hello to japzgier,

I found the straight problem. The problem is false data type. i habe defined a variable as char type and another variable as varchar type.

i use ms sql 2005.

Thanks very much

But i have now another problem, Maybe could you help me?

In my tablewtcomValidRelationships

IDMember RelationshipIDS

1 2

2 3

3 4

4 5

5 6

declare @.ENDPATH varchar(1000);

declare @.IDMint;

declare @.IDO varchar(100);

set @.IDM= 1;

set @.IDO='6'

declare @.Path3 varchar(1000);

set @.Path3=(selecttop 1convert(varchar(100),A.IDMember)+'-'+convert(varchar(100),B.IDMember)+'-'+convert(varchar(100),C.IDMember)+'-'+convert(varchar(100),D.IDMember)+'-'+convert(varchar(100),E.IDMember)

from wtcomValidRelationshipsas A,

wtcomValidRelationshipsas B,

wtcomValidRelationshipsas C,

wtcomValidRelationshipsas D,

wtcomValidRelationshipsas E

where A.IDMember= @.IDMandcharindex(convert(varchar(100),B.IDMember),A.RelationshipIDS)> 0

andcharindex(convert(varchar(100),C.IDMember),B.RelationshipIDs)> 0

andcharindex(convert(varchar(100),D.IDMember), C.RelationshipIDs)> 0

andcharindex(convert(varchar(100),E.IDMember),D.RelationshipIDs)> 0

andcharindex(@.IDO,E.RelationshipIDs)> 0);

if(len(@.Path3)> 0)

begin

set @.ENDPATH= @.Path3;

end

else

begin

set @.ENDPATH='No Relationship';

end

print @.ENDPATH

Problem is that Query Excuting need to 38 Secends ?

Thanks

Best Regards

Pinsha

|||

Hi sasha,

Have you tried this

(PATINDEX('%'+ @.IDO+'%', Relationships)> 0)

|||

Hello satya,

i have tried this (PATINDEX('%'+ @.IDO+'%', Relationships)> 0) , it works. Thanks

but now i have another problem.

I need to search the relationships from level 1 to level 6.

so i need write aIF ELSE sql query. But when i useIF ELSE, the query excuting is too slow. without if else, the query excuting is rash.

But i must useIF ELSE to get my result. Maybe Could you tell me other methods, how can i check the first level query, if it has no elements and then go to excute next level , and continue do this compare. My complete Codes are here. I hope that you can understand what i mean. Because my english is poor . sorry

For Example

wtcomValidRelationships

IDMember RelationshipIDs

1 2

2 3

3 4

4 5

5 6

Without IF ELSE 5 select Query together excuted under 1 Seconds:

declare @.ENDPATH varchar(1000);

declare @.IDMint;

declare @.IDO varchar(100);

set @.IDM= 1;

set @.IDO='6'

select IDMember

from wtcomValidRelationships

where wtcomValidRelationships.[IDMember]= @.IDM

andPATINDEX('%'+@.IDO+'%',(select RelationshipIDsfrom wtcomValidRelationshipswhere IDMember= @.IDM))> 0

selecttop 1convert(varchar(100),A.IDMember)+'-'+convert(varchar(100),B.IDMember)

from wtcomValidRelationshipsas A, wtcomValidRelationshipsas B

where A.IDMember= @.IDMandcharindex(convert(varchar(100),B.IDMember),A.RelationshipIDS)> 0

andPATINDEX('%'+@.IDO+'%',B.RelationshipIDs)> 0

selecttop 1convert(varchar(100),A.IDMember)+'-'+convert(varchar(100),B.IDMember)+'-'+convert(varchar(100),C.IDMember)

from wtcomValidRelationshipsas A, wtcomValidRelationshipsas B, wtcomValidRelationshipsas C

where A.IDMember= @.IDMandcharindex(convert(varchar(100),B.IDMember),A.RelationshipIDS)> 0

andcharindex(convert(varchar(100),C.IDMember),B.RelationshipIDs)> 0andPATINDEX('%'+@.IDO+'%',C.RelationshipIDs)> 0

selecttop 1convert(varchar(100),A.IDMember)+'-'+convert(varchar(100),B.IDMember)+'-'+convert(varchar(100),C.IDMember)+'-'+convert(varchar(100),D.IDMember)

from wtcomValidRelationshipsas A, wtcomValidRelationshipsas B, wtcomValidRelationshipsas C, wtcomValidRelationshipsas D

where A.IDMember= @.IDMandcharindex(convert(varchar(100),B.IDMember),A.RelationshipIDS)> 0

andcharindex(convert(varchar(100),C.IDMember),B.RelationshipIDs)> 0andcharindex(convert(varchar(100),D.IDMember), C.RelationshipIDs)> 0

andPATINDEX('%'+@.IDO+'%',D.RelationshipIDs)> 0

selecttop 1convert(varchar(100),A.IDMember)+'-'+convert(varchar(100),B.IDMember)+'-'+convert(varchar(100),C.IDMember)+'-'+convert(varchar(100),D.IDMember)+'-'+convert(varchar(100),E.IDMember)

from wtcomValidRelationshipsas A, wtcomValidRelationshipsas B, wtcomValidRelationshipsas C, wtcomValidRelationshipsas D, wtcomValidRelationshipsas E

where A.IDMember= @.IDMandcharindex(convert(varchar(100),B.IDMember),A.RelationshipIDS)> 0

andcharindex(convert(varchar(100),C.IDMember),B.RelationshipIDs)> 0andcharindex(convert(varchar(100),D.IDMember), C.RelationshipIDs)> 0

andcharindex(convert(varchar(100),E.IDMember),D.RelationshipIDs)> 0andPATINDEX('%'+@.IDO+'%',E.RelationshipIDs)> 0

But With IF ELSE query excuted more than 30 seconds

declare @.ENDPATH varchar(1000);

declare @.IDMint;

declare @.IDO varchar(100);

declare @.Level int;

set @.IDM= 3450;

set @.IDO='4269'

declare @.Path varchar(1000);

set @.Path='';

set @.Path=(select IDMember

from wtcomValidRelationships

where wtcomValidRelationships.[IDMember]= @.IDM

andcharindex( @.IDO,(select RelationshipIDsfrom wtcomValidRelationshipswhere IDMember= @.IDM))> 0);

if(len(@.Path)> 0)

begin

set @.ENDPATH= @.Path;

end

else

begin

declare @.Path1 varchar(1000);

set @.Path1=(selecttop 1convert(varchar(100),A.IDMember)+'-'+convert(varchar(100),B.IDMember)

from wtcomValidRelationshipsas A, wtcomValidRelationshipsas B

where A.IDMember= @.IDMandcharindex(convert(varchar(100),B.IDMember),A.RelationshipIDS)> 0

andcharindex(@.IDO,B.RelationshipIDs)> 0);

if(len(@.Path1)> 0)

begin

set @.ENDPATH= @.Path1;

end

else

begin

declare @.Path5 varchar(1000);

set @.Path5=(selecttop 1convert(varchar(100),A.IDMember)+'-'+convert(varchar(100),B.IDMember)+'-'+convert(varchar(100),C.IDMember)

from wtcomValidRelationshipsas A, wtcomValidRelationshipsas B, wtcomValidRelationshipsas C

where A.IDMember= @.IDMandcharindex(convert(varchar(100),B.IDMember),A.RelationshipIDS)> 0

andcharindex(convert(varchar(100),C.IDMember),B.RelationshipIDs)> 0andcharindex(@.IDO,C.RelationshipIDs)> 0);

if(len(@.Path5)> 0)

begin

set @.ENDPATH= @.Path5;

end

else

begin

declare @.Path2 varchar(1000);

set @.Path2=(selecttop 1convert(varchar(100),A.IDMember)+'-'+convert(varchar(100),B.IDMember)+'-'+convert(varchar(100),C.IDMember)+'-'+convert(varchar(100),D.IDMember)

from wtcomValidRelationshipsas A, wtcomValidRelationshipsas B, wtcomValidRelationshipsas C, wtcomValidRelationshipsas D

where A.IDMember= @.IDMandcharindex(convert(varchar(100),B.IDMember),A.RelationshipIDS)> 0

andcharindex(convert(varchar(100),C.IDMember),B.RelationshipIDs)> 0andcharindex(convert(varchar(100),D.IDMember), C.RelationshipIDs)> 0

andcharindex(@.IDO,D.RelationshipIDs)> 0);

if(len(@.Path2)> 0)

begin

set @.ENDPATH= @.Path2;

end

else

begin

declare @.Path3 varchar(1000);

set @.Path3=(selecttop 1convert(varchar(100),A.IDMember)+'-'+convert(varchar(100),B.IDMember)+'-'+convert(varchar(100),C.IDMember)+'-'+convert(varchar(100),D.IDMember)+'-'+convert(varchar(100),E.IDMember)

from wtcomValidRelationshipsas A, wtcomValidRelationshipsas B, wtcomValidRelationshipsas C, wtcomValidRelationshipsas D, wtcomValidRelationshipsas E

where A.IDMember= @.IDMandcharindex(convert(varchar(100),B.IDMember),A.RelationshipIDS)> 0

andcharindex(convert(varchar(100),C.IDMember),B.RelationshipIDs)> 0andcharindex(convert(varchar(100),D.IDMember), C.RelationshipIDs)> 0

andcharindex(convert(varchar(100),E.IDMember),D.RelationshipIDs)> 0andcharindex(@.IDO,E.RelationshipIDs)> 0);

if(len(@.Path3)> 0)

begin

set @.ENDPATH= @.Path3;

end

else

begin

set @.ENDPATH='No Relationship'

end

end

end

end

end

print @.ENDPATH