Showing posts with label syntax. Show all posts
Showing posts with label syntax. Show all posts

Wednesday, March 28, 2012

How to use wildcards in WHERE clause

I have a SQL statement which is generated dynamically. I need to know what is the correct syntax for this

WHERE status = 'open' AND salesman = * AND dat = * AND customername = *

i.e. fetch everything WHERE status = 'open'

I know that simply WHERE status = 'open' would do the trick but I need it like the first example because of the way the statement is being generated i.e. this salesmen bit is like this.

If Salesman <> "*" Then
sql2 &= " AND salesman = '" & Salesman & "'"
Else
sql2 &= " AND salesman = *"
End If

Thanks

BenI presume you are saying that the salesman should be LIKE anything.

You should try:

WHERE status = 'open' AND salesman LIKE '%' AND dat LIKE '%' AND customername LIKE '%'

OleDb uses % as the wildcard character, and you need to use LIKE with wildcards.|||Yes, thats done the trick.

Thanks a lot.

Ben

Monday, March 26, 2012

How to use the TOP(N) Filter syntax?

Im tearing my hair out on this one.
Could anyone provide information on how to use the "TOP(N)" filter syntax? I
want to return the top 10 aggregate rows. Not sure if it can do this?
Please help!
Thanks
TazYou mean like this:
SELECT TOP 500 Client.CLI_LastName
from Client
"Tarun Mistry" <nospam@.nospam.com> wrote in message
news:%235obeed0GHA.1268@.TK2MSFTNGP02.phx.gbl...
> Im tearing my hair out on this one.
> Could anyone provide information on how to use the "TOP(N)" filter syntax?
> I want to return the top 10 aggregate rows. Not sure if it can do this?
> Please help!
> Thanks
> Taz
>|||Just try something like this in your dataset
SELECT TOP (10) SomeField, COUNT(*) AS count
FROM SomeTable
GROUP BY SomeField
ORDER BY count DESC
That will count the number or rows pertaining to that specific field and
then put them in order by the count, showing only the top 10
"Tarun Mistry" <nospam@.nospam.com> wrote in message
news:%235obeed0GHA.1268@.TK2MSFTNGP02.phx.gbl...
> Im tearing my hair out on this one.
> Could anyone provide information on how to use the "TOP(N)" filter syntax?
> I want to return the top 10 aggregate rows. Not sure if it can do this?
> Please help!
> Thanks
> Taz
>|||Hi guys, thanks for the responses.
I cannot change my query, therefore the filtering has to happen after the
dataset has been returned. This is because below my chart I will sumamrise
ALL of the informarion, however I only want the chart to display the top 10
entires (or whatever).
I thought i could do something like..
Count(Field!MyAggregate.Value) Top N 10
However this didnt do anything (that I could see).
Kind Regards
Taz|||another option is two have 2 datasets, make them the exact same except add
the top N statement to the dataset related to the chart and then have your
second dataset without the top N for the summary information. You would
still get the same info but from 2 different datasets.
"Tarun Mistry" <nospam@.nospam.com> wrote in message
news:%23xy8bre0GHA.4648@.TK2MSFTNGP04.phx.gbl...
> Hi guys, thanks for the responses.
> I cannot change my query, therefore the filtering has to happen after the
> dataset has been returned. This is because below my chart I will
> sumamrise ALL of the informarion, however I only want the chart to display
> the top 10 entires (or whatever).
> I thought i could do something like..
> Count(Field!MyAggregate.Value) Top N 10
> However this didnt do anything (that I could see).
> Kind Regards
> Taz
>|||Many thanks for your help Ben,
your previous post was the one I needed to make me realise where I was going
wrong.
Thanks again,
Taz
"Ben Watts" <ben.watts@.aaronnickellhomes.com> wrote in message
news:OG$qsue0GHA.4044@.TK2MSFTNGP04.phx.gbl...
> another option is two have 2 datasets, make them the exact same except add
> the top N statement to the dataset related to the chart and then have your
> second dataset without the top N for the summary information. You would
> still get the same info but from 2 different datasets.
> "Tarun Mistry" <nospam@.nospam.com> wrote in message
> news:%23xy8bre0GHA.4648@.TK2MSFTNGP04.phx.gbl...
>> Hi guys, thanks for the responses.
>> I cannot change my query, therefore the filtering has to happen after the
>> dataset has been returned. This is because below my chart I will
>> sumamrise ALL of the informarion, however I only want the chart to
>> display the top 10 entires (or whatever).
>> I thought i could do something like..
>> Count(Field!MyAggregate.Value) Top N 10
>> However this didnt do anything (that I could see).
>> Kind Regards
>> Taz
>

Friday, March 9, 2012

How to use IIF function (or similar syntax) in SELECT clause

I need to create a simple view containing a driverID that must be converted
to numeric.
IF isnumeric(driverID) = 0 then 0
I tried to use IIF(isnumeric(driverID), driver, 0) but it's not accepted in
regular query.
Please help!
Thanks
BillYou can use a CASE expression; For example:
SELECT
CASE WHEN ISNUMERIC(DriverID) = 1 THEN DriverID ELSE 0 END
FROM MyTable
Note that ISNUMERIC can return 1 for values you might not want to consider
as numeric (e.g. '1E0') so you might want to substitute LIKE:
SELECT
CASE WHEN DriverID NOT LIKE '%[^0-9]%' THEN DriverID ELSE 0 END
FROM MyTable
Hope this helps.
Dan Guzman
SQL Server MVP
"Bill nguyen" <billn_nospam_please@.jaco.com> wrote in message
news:ekpb7A%231FHA.3660@.TK2MSFTNGP15.phx.gbl...
>I need to create a simple view containing a driverID that must be converted
>to numeric.
> IF isnumeric(driverID) = 0 then 0
> I tried to use IIF(isnumeric(driverID), driver, 0) but it's not accepted
> in regular query.
> Please help!
> Thanks
> Bill
>

How to use EXEC

Hi,
How can I run EXEC conditionally?
The EXEC syntax woks on its own but not when it's in a query.
TIA
Mike
select
'name','surname', case when datediff (dd, getdate(), getdate()+2) between 0
and 2
then EXEC master..xp_sendmail 'mike', 'The master database is full.' else ''
end as testYou need an IF statement. Also, your SELECT needs a FROM clause.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Mike_T" <nospam@.yahoo.com> wrote in message
news:uXYK4ZMZGHA.4060@.TK2MSFTNGP02.phx.gbl...
Hi,
How can I run EXEC conditionally?
The EXEC syntax woks on its own but not when it's in a query.
TIA
Mike
select
'name','surname', case when datediff (dd, getdate(), getdate()+2) between 0
and 2
then EXEC master..xp_sendmail 'mike', 'The master database is full.' else ''
end as test|||Try
If exists(select
'name','surname', case when datediff (dd, getdate(), getdate()+2)
between 0
and 2 from table)
EXEC master..xp_sendmail 'mike', 'The master database is full.'
else
EXEC master..xp_sendmail 'mike', ''
Madhivanan|||Hi Madhivanan,
I tried the following using pubs
--
If exists(select
'au_fname','au_lname', case when datediff (dd, getdate(), getdate()+2)
between 0 and 2 from authors)
EXEC master..xp_sendmail 'mike', 'The master database is full.'
else
EXEC master..xp_sendmail 'mike', ''
--
but I get this error:
Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'from'.
Server: Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'else'.
I understood the idea but not the error
Thank you.
"Madhivanan" <madhivanan2001@.gmail.com> escribi en el mensaje
news:1145615715.694251.14190@.t31g2000cwb.googlegroups.com...
> Try
> If exists(select
> 'name','surname', case when datediff (dd, getdate(), getdate()+2)
> between 0
> and 2 from table)
> EXEC master..xp_sendmail 'mike', 'The master database is full.'
> else
> EXEC master..xp_sendmail 'mike', ''
> Madhivanan
>|||On Fri, 21 Apr 2006 22:35:46 +0200, Mike_T wrote:

>Hi Madhivanan,
>I tried the following using pubs
>--
>If exists(select
> 'au_fname','au_lname', case when datediff (dd, getdate(), getdate()+2)
>between 0 and 2 from authors)
>EXEC master..xp_sendmail 'mike', 'The master database is full.'
>else
>EXEC master..xp_sendmail 'mike', ''
>--
>but I get this error:
>Server: Msg 156, Level 15, State 1, Line 3
>Incorrect syntax near the keyword 'from'.
>Server: Msg 156, Level 15, State 1, Line 5
>Incorrect syntax near the keyword 'else'.
>I understood the idea but not the error
Hi Mike,
The error is caused because the CASE expression is not closed. You need
to add at least one THEN clause and an END.
Also, a SELECT list is irrelevant in an EXISTS subquery; the database
will only look for matching rows and disregard the column list yoou
specified. So you could rewrite your code as
IF EXISTS (SELECT * FROM authors)
EXEC master..xp_sendmail 'mike', 'The master database is full.'
ELSE
EXEC master..xp_sendmail 'mike', ''
However, I doubt that this does what you want. Maybe you should explain
what you try to accomplish instead of posting just a part of your code
and have us guess...
Hugo Kornelis, SQL Server MVP|||Try
If exists(select
'name','surname' from table where datediff (dd, getdate(),
getdate()+2) between 0 and 2)
EXEC master..xp_sendmail 'mike', 'The master database is full.'
else
EXEC master..xp_sendmail 'mike', ''
Madhivanan

Wednesday, March 7, 2012

how to use DBCC WritePage command

The syntax of the command is

DBCC writepage ({ dbid, 'dbname' }, fileid, pageid, offset, length, data)

what I only know is that this command can change the structure of data page,it replace data in data page,

and it may cause exception when i scan the table after executed dbcc writepage command

the error message is:Could not continue scan with NOLOCK due to data movement

I want to know what is the purpose that sqlserver provide "DBCC WRITEPAGE" command and how to use it.

could anyone give me a particular introduction to dbcc wirtepage command?

I can't find any information about it in internet.

Thanks.

It's an undocument command that you should only use when you know what you are doing, as you can very easily corrupt the database when you use it incorrectly.

Thanks,

How to use comma separated lists with parameters in sqldatasource?

I'm new (very new) to asp.net and now at 3am after maybe 12 straight hours of trying to go through examples and understand the syntax, I have a somewhat working program... basically a query parameters screen that upon a click generates a report. The query parameter screen basically is a HTML form with a procedure that puts the field values into session variables. The report uses <asp:sessionparameters> in the sqldatasource to apply aforementioned session variables to filter the SELECT statement's results. They are then displayed using the gridview control. It actually works!

Now I'm trying to figure out: if I need/want my users to be able to enter comma-separated list of values into the HTML form field and somehow get these into the SELECT statement's WHERE clause, is there a way to do this? I'm not very experienced with asp nor sql server and have been struggling with this new challenge. I really could use pointers as to whether there's an easy way to handle this. In other languages in the past I would have had to parse the comma-separated list and use those to construct my SELECT statement programmatically. I get the impression that with all this ASP.NET fanciness, there has to be a better way?

If it matters, I'm using ASP.NET 2.0 with SQL Server 2005 Express and Visual Studio Web 2005 Express.

Thanks in advance for any replies, they would be GREATLY appreciated :)

Hi,

you either generate the SQL query programmatically based on the CSV, e.g split the CSV string with the delimiter, loop the array returned by String.Split and then buold the SQl string dynamically. Other way is to create a stored procedure to the Db and use functions to split CSVs for example:http://www.sqlteam.com/item.asp?ItemID=11499

Friday, February 24, 2012

How to use C# syntax in report expressions

I want to use C# syntax in report expressions.
I use WinForms ReportViewer in local mode.
How to use C# instead of VB.NET syntax ?
I'm thinking about the following approaches:
1. Create pre-processor which converts expressions in RDL file from C# to
VB. NET
2. Replace all expressions with a method which takes C# expression as string
parameter, evaluates it and returns result.
Any idea how to implement this ?
Where to find any samples of C# expression evaluator or C# VB .NET
expression converter ?
Andrus.On Mar 27, 4:34 am, "Andrus" <kobrule...@.hot.ee> wrote:
> I want to use C# syntax in report expressions.
> I use WinForms ReportViewer in local mode.
> How to use C# instead of VB.NET syntax ?
> I'm thinking about the following approaches:
> 1. Create pre-processor which converts expressions in RDL file from C# to
> VB. NET
> 2. Replace all expressions with a method which takes C# expression as string
> parameter, evaluates it and returns result.
> Any idea how to implement this ?
> Where to find any samples of C# expression evaluator or C# VB .NET
> expression converter ?
> Andrus.
I, myself, come from a C# background and needed to learn some basic
VB.NET for expressions in order to use SSRS 2005. Aside from basic
syntax, the two are highly similar. It doesn't seem like the time to
become familiar w/the report expressions, etc in VB.NET would be as
much of a trade off when compared to a custom C# to VB.NET expression
converting application. That said, here's one I found:
http://www.tangiblesoftwaresolutions.com/Product_Details/Instant_VB/Instant_VB.htm
Also, several are mentioned here:
http://groups.google.com/group/microsoft.public.dotnet.languages.vb/browse_thread/thread/b4787e2e70c0b9df/6ec64748dea02d40?lnk=st&q=C%23+to+vb.net+conversion&rnum=4#6ec64748dea02d40
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||> It doesn't seem like the time to
> become familiar w/the report expressions, etc in VB.NET would be as
> much of a trade off when compared to a custom C# to VB.NET expression
> converting application.
I noticed that I must write code in <Code> tags in VB .NET also.
So using reporting services requires learning full VB .NET language, not
only expressions.
I have read everywhere that .NET allows to select between C# and VB .NET
When this is fixed so that C# language can be used in RDLC files ?
Andrus.|||> I noticed that I must write code in <Code> tags in VB .NET also.
>So using reporting services requires learning full VB .NET language, not
>only expressions.
Really not true, or at least certainly not a big deal, if you put your code
in custom DLLs instead of embedding it in the report. Which you would
probably want to do for non-trivial code anyway. So put your code in custom
DLLs in C#, and go as crazy as you want creating custom functions (in C#)
which you can call in your report for anything where the built-in VB
functions seem non-intuitive or not effective from your POV.
>L<
"Andrus" <kobruleht2@.hot.ee> wrote in message
news:OWlLZNHcHHA.2120@.TK2MSFTNGP03.phx.gbl...
>> It doesn't seem like the time to
>> become familiar w/the report expressions, etc in VB.NET would be as
>> much of a trade off when compared to a custom C# to VB.NET expression
>> converting application.
> I noticed that I must write code in <Code> tags in VB .NET also.
> So using reporting services requires learning full VB .NET language, not
> only expressions.
> I have read everywhere that .NET allows to select between C# and VB .NET
> When this is fixed so that C# language can be used in RDLC files ?
> Andrus.

Sunday, February 19, 2012

How to use "N" syntax for unicode parameters

Hi all,

I had two Nvarchar(255) columns & retreive it like this

DIM CN1 AS SQLCONNECTION
DIM DR1 AS SQLDATAADAPTER
DIM DTABLE AS DATATABLE
DIM DTSET AS DATASET

DTSET=NEW DATASET()
DR1=NEW SQLDATAADAPTER("SP_PRO1",CN1)
DR1.SELECTCOMMAND.COMMANDTYPE=COMMANDTYPE.STOREDPROCEDURE
DR1.SELECTCOMMAND.PARAMETERS.ADD("@.TYPE1", REQUEST.QUERYSTRING("PID"))
DR1.SELECTCOMMAND.PARAMETERS.ADD("@.TYPE1", REQUEST.QUERYSTRING("UID"))
DR1.FILL(DTSET,CINT(INTCURRINDEX.TEXT),CINT (INTPAGESIZE.TEXT),"SP_PRO1")
DL1.DATASOURCE=DTSET.TABLES(0).DEFAULTVIEW
DL1.DATABIND()
CN1.CLOSE()
END SUB

How to insert "N" National Code to parameter? I try it N"@.TYPE1" or "N@.TYPE1" with failed. Any ideas or advise? Thanks a lot.

Arisnot used to reading everything in caps.. feels like someone screaming..
anyway, do you want to add the character 'N" to the value of the parameter?

if so, try

.Add("@.Type1", "N" & Request.Querystring("PID"))

now in the code I see that there are TWO @.Type1 params.. it's probably a typo.|||You shouldn't need to, .net only uses Unicode.