problem in pattern matching.. RSS

• • Reply
  

  closeguy2005

  Participant

  887 Points

  1746 Posts

  problem in pattern matching..

  Sep 14, 2008 01:18 PM|LINK

   I store jobs in table and display them as per conditions selected by user.

  my table has 4 columns.

  company       role              Location    postdate

  abc               Fresher            Ny             9/10/2008 12:00:00 AM

  xyz             Vp-operations ,   LA             9/10/2008 12:03:00 AM

   

  Now,  this is my select query

  SELECT Companyname, role, Location, date FROM callcenter where Location Like '%' + @city + '%'  and DATEDIFF(Day, postdate, getdate( ) ) <= @datediff and Role Like '%' + @role + '%'   

   

  my problem is , for searching jobs, i allow user to select multiple roles. I get results when choose just single role. But if i select multiple roles, I don't get any results. Is there something wrong with

  Role Like '%' + @role + '%'    ???

   I have defined @role varchar(100) and I am passing value to it as

  cmd.Parameters.AddWithValue("@role", moreabout) 

  If i select 2 roles, Freshers and Vp-operations , variable moreabout holds "Fresher,Vp-operations"

  then where is the problem ? ( I am using pattern matching , so it should show results. It shows result

  if i select just Vp-operations or just Freshers )
  

  Please Mark the post as "ANSWER" if it helps you
  
• • Reply
  

  anonymouswri...

  Contributor

  2340 Points

  424 Posts

  Re: problem in pattern matching..

  Sep 14, 2008 01:34 PM|LINK

   SELECT Companyname, role, Location, date FROM callcenter where Location Like '%' + @city + '%'  and DATEDIFF(Day, postdate, getdate( ) ) <= @datediff and Role IN (Fresher,Vp-operations)

• • Reply
  

  closeguy2005

  Participant

  887 Points

  1746 Posts

  Re: problem in pattern matching..

  Sep 14, 2008 01:48 PM|LINK

  anonymouswrites

   SELECT Companyname, role, Location, date FROM callcenter where Location Like '%' + @city + '%'  and DATEDIFF(Day, postdate, getdate( ) ) <= @datediff and Role IN (Fresher,Vp-operations)

   

  But there are not only 2 roles viz. Fresher and  Vp-operations . There are more than 10 roles.

  and i cannot determine which roles user will select , so passing those values to stored procedure is again difficult task.

  But whats wrong with my query? I am using pattern matching ..Do i have to modify pattern matching?
  

  Please Mark the post as "ANSWER" if it helps you
  
• • Reply
  

  ramireddyind...

  All-Star

  31358 Points

  4579 Posts

  Re: problem in pattern matching..

  Sep 14, 2008 02:05 PM|LINK

   in that table, in 1 row there will be only role or multiple roles.

  if it is one role i am giving query now. if there will be multiple roles, tomorrow i will give you solution as i am not at my system now.

  if you have split userdefined function fn_split(), then we can do like

  select * from (select value,c.* from company c cross join fn_split(@Roles,','))t where charindex (value,role) > 0
  

   

   

  Give a man a fish and you feed him for a day. Teach a man to fish and you feed him forever.
  
• • Reply
  

  closeguy2005

  Participant

  887 Points

  1746 Posts

  Re: problem in pattern matching..

  Sep 14, 2008 02:29 PM|LINK

  ramireddyindia

   in that table, in 1 row there will be only role or multiple roles.

  if it is one role i am giving query now. if there will be multiple roles, tomorrow i will give you solution as i am not at my system now.

  if you have split userdefined function fn_split(), then we can do like

  select * from (select value,c.* from company c cross join fn_split(@Roles,','))t where charindex (value,role) > 0
  

   

   

  Thanks rami reddy ..

  Each row of table holds just single role...

  Please modify my original query if possible as I want to use it in stored procedure..

  Also, fn_split() is in-built function?
  

   
  

  Please Mark the post as "ANSWER" if it helps you
  
  • Edited by closeguy2005 on Sep 14, 2008 02:47 PM
• • Reply
  

  ramireddyind...

  All-Star

  31358 Points

  4579 Posts

  Re: problem in pattern matching..

  Sep 14, 2008 02:36 PM|LINK

  I hope you already have fn_split() user defined function. you need that function before you run this query. if it is not at you i will give you tomorrow as i don't have development environment now.
  

   

  select  Companyname, role, Location, date from (SELECT Companyname, role, Location, date,value FROM callcenter cross join fn_split(@role,',')) t where Location Like '%' + @city + '%'  and DATEDIFF(Day, postdate, getdate( ) ) <= @datediff and charindex(value,role) > 0
  

  Give a man a fish and you feed him for a day. Teach a man to fish and you feed him forever.
  
• • Reply
  

  vijayakrishn...

  Participant

  1288 Points

  225 Posts

  Re: problem in pattern matching..

  Sep 14, 2008 02:40 PM|LINK

  Create the comma separated string of role from the client side and use dynamic sql to achieve this.

  Let us assume your roles string contains the value 'VP-Operations,Fresher,Administrator'

  DECLARE @SQL NVARCHAR(1000)

  SET @SQL = 'SELECT Companyname, role, Location, date FROM callcenter where Location Like '''%''' + @city + '''%'''  and DATEDIFF(Day, postdate, getdate( ) ) <= @datediff and Role in ('  + @role + ')'

  EXEC SP_EXECUTESQL @SQL

  Vijaya Krishna Paruchuri
  Please remember to click “Mark as Answer” on the post that helped you. This credits that member, earns you a point and marks your thread as resolved.
• • Reply
  

  closeguy2005

  Participant

  887 Points

  1746 Posts

  Re: problem in pattern matching..

  Sep 14, 2008 02:50 PM|LINK

  ramireddyindia

  I hope you already have fn_split() user defined function.

   

  Ok..i will wait..as I don't know whats that...In fact I am hearing it for the first time..

  Thanks anyways..
  

  Please Mark the post as "ANSWER" if it helps you
  
• • Reply
  

  closeguy2005

  Participant

  887 Points

  1746 Posts

  Re: problem in pattern matching..

  Sep 14, 2008 03:01 PM|LINK

  vijayakrishna

  Create the comma separated string of role from the client side and use dynamic sql to achieve this.

  Let us assume your roles string contains the value 'VP-Operations,Fresher,Administrator'

  DECLARE @SQL NVARCHAR(1000)

  SET @SQL = 'SELECT Companyname, role, Location, date FROM callcenter where Location Like '''%''' + @city + '''%'''  and DATEDIFF(Day, postdate, getdate( ) ) <= @datediff and Role in ('  + @role + ')'

  EXEC SP_EXECUTESQL @SQL

   

  Actually I am using this query to bind datalist dynamically. In such case I dont know from where to call

   EXEC SP_EXECUTESQL @SQL

   

  Dim cmd As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand("dbo.bporesults")
          cmd.CommandType = Data.CommandType.StoredProcedure
  
          cmd.Parameters.AddWithValue("@datediff", beforedays)
          cmd.Parameters.AddWithValue("@city", city1)
          cmd.Parameters.AddWithValue("@role", moreabout)
  
          cmd.Connection = objConn
  
  
          Dim ds As New System.Data.DataSet
          Dim da As System.Data.SqlClient.SqlDataAdapter = New System.Data.SqlClient.SqlDataAdapter(cmd)
  
          da.Fill(ds, "callcenter")
          DataList1.DataSource = ds
          DataList1.DataBind()
  

  Please Mark the post as "ANSWER" if it helps you
  
• • Reply
  

  closeguy2005

  Participant

  887 Points

  1746 Posts

  Re: problem in pattern matching..

  Sep 15, 2008 06:02 PM|LINK

   anybody know this?...
  

  Please Mark the post as "ANSWER" if it helps you