August 28, 2010 at 10:51 am
Greetings experts,
I am using the following code to so a user doesn't have to type fullname to get results.
Problem is, when I run the code, I get "no data found" message.
Here is the:
SELECT[NAME]
,[REL]
,[ESTNO]
,[DTFILD]
,[PUB]
,[TYPE OF DOCUMENT]
,[BTYP]
,[BKNO]
,[PGNO]
,[DISP]
,[DISPDT]
FROM [ProbateCourt].[dbo].[PCS60418_MTHLY_XREF]
WHERE NAME LIKE '*' + @Name+ '*'
If I run this version in query window, it works:
SELECT[NAME]
,[REL]
,[ESTNO]
,[DTFILD]
,[PUB]
,[TYPE OF DOCUMENT]
,[BTYP]
,[BKNO]
,[PGNO]
,[DISP]
,[DISPDT]
FROM [ProbateCourt].[dbo].[PCS60418_MTHLY_XREF]
WHERE NAME LIKE '% Name %'
What could be wrong?
Thanks in advance
August 28, 2010 at 11:21 am
Hi, are you setting up the parameter properly so you are sending the correct value through to the SQL Parameter in the where clause? You could drop the value into the report or you could use profiler to see what you are sending it.
August 28, 2010 at 1:54 pm
The asterisk '*' is not a wildcard character in SQL Server. The % is the wildcard that means match any characters.
Lookup LIKE in Books Online for the different wildcard characters you can use.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 28, 2010 at 6:47 pm
Right, I do KNOW that the % is the wildcard character in sql server but I am also told that the % doesn't work in SSRS, rather it accepts the asterik.
Either case, none of the 2 is working for me.
August 28, 2010 at 8:05 pm
I don't know where the * would be used in SSRS, unless you are using built-in functions.
As for the queries, the wildcard that you have won't match if the name you are looking for does not have a space before and after the name.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 28, 2010 at 10:31 pm
Jeff,
You are absolutely correct. The correct expression is the % wildcard.
I don't know where this guy got his from.
This is where I found the * wildcard:
http://blogs.sftsrc.com/stuart/archive/2009/05/08/154.aspx
As you can see from my original post, I used the % wildcard initially but it didn't work for some reason.
Now, it does.
Thank you very much.
Can I ask for one more question, please?
One of the fields in the query, this --> DTFILD has more than one value per record.
For instance, it looks like this:
NAME REL ESTNO DTFILD PUB TYPE OF DOCUMENT BTYP BKNO PGNO DISP DISPDT
John Doe 1 34 8122009 dd N M 00 09 No Run 292008
8122010
8122011
Notice how DTFILD has more than one value per record.
Do you know what needs to change in the query below so the layout looks similar to above sample code?
Thanks alot
SELECT[NAME]
,[REL]
,[ESTNO]
,[DTFILD]
,[PUB]
,[TYPE OF DOCUMENT]
,[BTYP]
,[BKNO]
,[PGNO]
,[DISP]
,[DISPDT]
FROM [ProbateCourt].[dbo].[PCS60418_MTHLY_XREF]
WHERE NAME LIKE '% Name %'
August 29, 2010 at 9:34 am
How is the data actually stored in that column and what is the data type of the column?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 29, 2010 at 1:06 pm
hi Jeff,
The data type is nvarchar(50) and it is stored one record per row.
However, rather than have the name appear, say 7 times ( or 7 rows), we want the name to appear once and the the associated records can appear as many times.
It is doable?
Thanks again.
August 29, 2010 at 2:03 pm
You do that in the report itself. There is an option on the column properties to suprress duplicates which is what you are looking for.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply