Is this the correct syntax?

Hi,
Is this the correct syntax for this Access pass-thru query? I'm new to
TSQL and I have a book on order but I still need to move forward with
this project while I'm waiting for it.
I want to update a field in a SQLServer table,
BillingNumber(datatype=text) has one(1) to many leading zero's, I need
to strip one off so I had the dba create a new field BillingNumberNew
which I trying to update with BillingNumber minus one leading zero.
Would this be correct?
UPDATE tblPhysLineFinal
SET tblPhysLineFinal.BillingNumberNew =
Substing(tblPhysLineFinal.BillingNumber,
2,Len(tblPhysLineFinal.BillingNumber)-1)
thanks
bobh.Hi bobh,
pls try this
This will remove leading first from BillingNumber
UPDATE tblPhysLineFinal
SET tblPhysLineFinal.BillingNumberNew =
Substring(cast( BillingNumber as varchar),2,Len(cast (BillingNumber as
varchar))-1) from tblPhysLineFinal
Regards,
Amol Lembhe
"bobh" wrote:

> Hi,
> Is this the correct syntax for this Access pass-thru query? I'm new to
> TSQL and I have a book on order but I still need to move forward with
> this project while I'm waiting for it.
> I want to update a field in a SQLServer table,
> BillingNumber(datatype=text) has one(1) to many leading zero's, I need
> to strip one off so I had the dba create a new field BillingNumberNew
> which I trying to update with BillingNumber minus one leading zero.
> Would this be correct?
> UPDATE tblPhysLineFinal
> SET tblPhysLineFinal.BillingNumberNew =
> Substing(tblPhysLineFinal.BillingNumber,
> 2,Len(tblPhysLineFinal.BillingNumber)-1)
> thanks
> bobh.
>|||Thanks!
bobh.
On Nov 12, 1:27 pm, Amol Lembhe <amo...@.gmail.com> wrote:
> Hi bobh,
> pls try this
> This will remove leading first from BillingNumber
> UPDATE tblPhysLineFinal
> SET tblPhysLineFinal.BillingNumberNew =
> Substring(cast( BillingNumber as varchar),2,Len(cast (BillingNumber as
> varchar))-1) from tblPhysLineFinal
> Regards,
> Amol Lembhe
>
> "bobh" wrote:
>
>
>
>
> - Show quoted text -

Is this the correct syntax?

Hi,
Is this the correct syntax for this Access pass-thru query? I'm new to
TSQL and I have a book on order but I still need to move forward with
this project while I'm waiting for it.
I want to update a field in a SQLServer table,
BillingNumber(datatype=text) has one(1) to many leading zero's, I need
to strip one off so I had the dba create a new field BillingNumberNew
which I trying to update with BillingNumber minus one leading zero.
Would this be correct?
UPDATE tblPhysLineFinal
SET tblPhysLineFinal.BillingNumberNew =
Substing(tblPhysLineFinal.BillingNumber,
2,Len(tblPhysLineFinal.BillingNumber)-1)
thanks
bobh.
Hi bobh,
pls try this
This will remove leading first from BillingNumber
UPDATE tblPhysLineFinal
SET tblPhysLineFinal.BillingNumberNew =
Substring(cast( BillingNumber as varchar),2,Len(cast (BillingNumber as
varchar))-1) from tblPhysLineFinal
Regards,
Amol Lembhe
"bobh" wrote:

> Hi,
> Is this the correct syntax for this Access pass-thru query? I'm new to
> TSQL and I have a book on order but I still need to move forward with
> this project while I'm waiting for it.
> I want to update a field in a SQLServer table,
> BillingNumber(datatype=text) has one(1) to many leading zero's, I need
> to strip one off so I had the dba create a new field BillingNumberNew
> which I trying to update with BillingNumber minus one leading zero.
> Would this be correct?
> UPDATE tblPhysLineFinal
> SET tblPhysLineFinal.BillingNumberNew =
> Substing(tblPhysLineFinal.BillingNumber,
> 2,Len(tblPhysLineFinal.BillingNumber)-1)
> thanks
> bobh.
>
|||Thanks!
bobh.
On Nov 12, 1:27 pm, Amol Lembhe <amo...@.gmail.com> wrote:
> Hi bobh,
> pls try this
> This will remove leading first from BillingNumber
> UPDATE tblPhysLineFinal
> SET tblPhysLineFinal.BillingNumberNew =
> Substring(cast( BillingNumber as varchar),2,Len(cast (BillingNumber as
> varchar))-1) from tblPhysLineFinal
> Regards,
> Amol Lembhe
>
> "bobh" wrote:
>
>
> - Show quoted text -

Is this the correct syntax?

Hi,
Is this the correct syntax for this Access pass-thru query? I'm new to
TSQL and I have a book on order but I still need to move forward with
this project while I'm waiting for it.
I want to update a field in a SQLServer table,
BillingNumber(datatype=text) has one(1) to many leading zero's, I need
to strip one off so I had the dba create a new field BillingNumberNew
which I trying to update with BillingNumber minus one leading zero.
Would this be correct?
UPDATE tblPhysLineFinal
SET tblPhysLineFinal.BillingNumberNew = Substing(tblPhysLineFinal.BillingNumber,
2,Len(tblPhysLineFinal.BillingNumber)-1)
thanks
bobh.Hi bobh,
pls try this
This will remove leading first from BillingNumber
UPDATE tblPhysLineFinal
SET tblPhysLineFinal.BillingNumberNew =Substring(cast( BillingNumber as varchar),2,Len(cast (BillingNumber as
varchar))-1) from tblPhysLineFinal
Regards,
Amol Lembhe
"bobh" wrote:
> Hi,
> Is this the correct syntax for this Access pass-thru query? I'm new to
> TSQL and I have a book on order but I still need to move forward with
> this project while I'm waiting for it.
> I want to update a field in a SQLServer table,
> BillingNumber(datatype=text) has one(1) to many leading zero's, I need
> to strip one off so I had the dba create a new field BillingNumberNew
> which I trying to update with BillingNumber minus one leading zero.
> Would this be correct?
> UPDATE tblPhysLineFinal
> SET tblPhysLineFinal.BillingNumberNew => Substing(tblPhysLineFinal.BillingNumber,
> 2,Len(tblPhysLineFinal.BillingNumber)-1)
> thanks
> bobh.
>|||Thanks!
bobh.
On Nov 12, 1:27 pm, Amol Lembhe <amo...@.gmail.com> wrote:
> Hi bobh,
> pls try this
> This will remove leading first from BillingNumber
> UPDATE tblPhysLineFinal
> SET tblPhysLineFinal.BillingNumberNew => Substring(cast( BillingNumber as varchar),2,Len(cast (BillingNumber as
> varchar))-1) from tblPhysLineFinal
> Regards,
> Amol Lembhe
>
> "bobh" wrote:
> > Hi,
> > Is this the correct syntax for this Access pass-thru query? I'm new to
> > TSQL and I have a book on order but I still need to move forward with
> > this project while I'm waiting for it.
> > I want to update a field in a SQLServer table,
> > BillingNumber(datatype=text) has one(1) to many leading zero's, I need
> > to strip one off so I had the dba create a new field BillingNumberNew
> > which I trying to update with BillingNumber minus one leading zero.
> > Would this be correct?
> > UPDATE tblPhysLineFinal
> > SET tblPhysLineFinal.BillingNumberNew => > Substing(tblPhysLineFinal.BillingNumber,
> > 2,Len(tblPhysLineFinal.BillingNumber)-1)
> > thanks
> > bobh.- Hide quoted text -
> - Show quoted text -

Is this syntax correct: (',' + @country_code + ',' like '%,' + rtrim(member_country)

I need some help in trying to build this SQL
select count(*)
from
tMember
where
(@.country_code is null
or (',' + @.country_code + ',' like '%,' + rtrim(member_country) + ',%'))
There is 1 record in tMember with member_country column's value = US.
@.country_code varchar(5)
1. This Returns 0 Count (Incorrect)
--
@.country_code = 'FR, US'
2. This Returns 0 Count (Incorrect)
--
@.country_code = 'FR,US,UK'
3. This Returns 0 Count (Incorrect)
--
@.country_code = 'FR,UK,US'
4. This Returns 1 Count (CORRECT)
--
@.country_code = 'US,FR'
How can I make the query return the right count even if US is not the first
element.
Oddly, if I hard code the values 'FR,US' in the SQL it returns the correct c
ount.
select member_id, member_country
from
tMember
where ',' + 'FR,US'+ ',' like '%,' + rtrim(member_country) + ',%'
TIA,
RajanYou say that @.country_code is varchar(5). Every one of the examples
you describe as Incorrect has a value for @.country_code longer than 5
characters. Perhaps making @.country_code long enough to hold the data
would help?
Also, given the embedded blank in the first example, consider removing
blanks using REPLACE:
or (',' + REPLACE(@.country_code,' ','') + ',' like '%,' +
rtrim(member_country) + ',%'))
Roy
On Sat, 25 Feb 2006 18:41:03 -0800, "Rajan" <roger@.yahoo.com> wrote:

>I need some help in trying to build this SQL
>select count(*)
>from
>tMember
>where
>(@.country_code is null
> or (',' + @.country_code + ',' like '%,' + rtrim(member_country) + ',%'))
>
>There is 1 record in tMember with member_country column's value = US.
>@.country_code varchar(5)
>
>1. This Returns 0 Count (Incorrect)
>--
>@.country_code = 'FR, US'
>
>2. This Returns 0 Count (Incorrect)
>--
>@.country_code = 'FR,US,UK'
>
>3. This Returns 0 Count (Incorrect)
>--
>@.country_code = 'FR,UK,US'
>
>4. This Returns 1 Count (CORRECT)
>--
>@.country_code = 'US,FR'
>
>How can I make the query return the right count even if US is not the first
element.
>
>Oddly, if I hard code the values 'FR,US' in the SQL it returns the correct
count.
>select member_id, member_country
>from
>tMember
>where ',' + 'FR,US'+ ',' like '%,' + rtrim(member_country) + ',%'
>TIA,
>Rajan|||>>
Perhaps making @.country_code long enough to hold the data
would help?
Yes Roy, that was it. I made it varchar(255) and it fixed the problem.
Thanks very much for your help.
Sincerely,
Rajan
"Roy Harvey" <roy_harvey@.snet.net> wrote in message news:9k6202lum5vp1cklkg1hqbspmapd168km9
@.4ax.com...
> You say that @.country_code is varchar(5). Every one of the examples
> you describe as Incorrect has a value for @.country_code longer than 5
> characters. Perhaps making @.country_code long enough to hold the data
> would help?
>
> Also, given the embedded blank in the first example, consider removing
> blanks using REPLACE:
>
> or (',' + REPLACE(@.country_code,' ','') + ',' like '%,' +
> rtrim(member_country) + ',%'))
>
> Roy
>
>
> On Sat, 25 Feb 2006 18:41:03 -0800, "Rajan" <roger@.yahoo.com> wrote:
>

Is this query possible in Sql Server ?

I use this query in Oracle, is such query possible in Sql Server, if so,
what is the sql server syntax.
Select * from Contract
where (cn,rev) in
(select cn,rev from Invoice)
Best Regards,
Luqmanthe possibility to use the query in SQL server is
Select * from Contract
where cn in (select cn from Invoice),rev) and rev in (select rev from
Invoice)
"Luqman" <pearlsoft@.cyber.net.pk> wrote in message
news:OLC$eypCFHA.1292@.TK2MSFTNGP10.phx.gbl...
> I use this query in Oracle, is such query possible in Sql Server, if so,
> what is the sql server syntax.
> Select * from Contract
> where (cn,rev) in
> (select cn,rev from Invoice)
> Best Regards,
> Luqman
>
>
>|||the query is
Select * from Contract
where cn in (select cn from Invoice) and rev in (select rev from Invoice)
"Devinder Singh" <devinder79@.hotmail.com> wrote in message
news:eddin1pCFHA.2540@.TK2MSFTNGP09.phx.gbl...
> the possibility to use the query in SQL server is
> Select * from Contract
> where cn in (select cn from Invoice),rev) and rev in (select rev from
> Invoice)
>
> "Luqman" <pearlsoft@.cyber.net.pk> wrote in message
> news:OLC$eypCFHA.1292@.TK2MSFTNGP10.phx.gbl...
>|||the query is
Select * from Contract where cn in (select cn from Invoice) and rev in
(select rev from Invoice)|||Select * from Contract C
where EXISTS
(select 1 from Invoice I
WHERE I.cn = C.Cn AND I.rev = C.rev)
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Luqman" <pearlsoft@.cyber.net.pk> wrote in message
news:OLC$eypCFHA.1292@.TK2MSFTNGP10.phx.gbl...
>I use this query in Oracle, is such query possible in Sql Server, if so,
> what is the sql server syntax.
> Select * from Contract
> where (cn,rev) in
> (select cn,rev from Invoice)
> Best Regards,
> Luqman
>
>
>|||a simple inner join would probably do here.
dean
"Luqman" <pearlsoft@.cyber.net.pk> wrote in message
news:OLC$eypCFHA.1292@.TK2MSFTNGP10.phx.gbl...
> I use this query in Oracle, is such query possible in Sql Server, if so,
> what is the sql server syntax.
> Select * from Contract
> where (cn,rev) in
> (select cn,rev from Invoice)
> Best Regards,
> Luqman
>
>
>|||On Fri, 4 Feb 2005 15:15:49 +0530, Devinder Singh wrote:

>the query is
>
>Select * from Contract where cn in (select cn from Invoice) and rev in
>(select rev from Invoice)
Hi Devinder,
This won't produce the same results as the original query posted by
Luqman. Luqman wants to know if there is one row in Invoice with matching
cn and rev values - you are testing if there is a row with matching cn and
a row (not necessarily the same) with matching rev.
Roji's solution is correct.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||On Fri, 4 Feb 2005 11:04:03 +0100, Dean wrote:

>a simple inner join would probably do here.
Hi Dean,
Only if the combination of cn + rev is unique in the Invoice table. If it
isn't, you'll get duplicates and you'll have to use a derived table
(SELECT DISTINCT cn, rev FROM Invoice) AS D in your join.
I suggest using Roji's solution, which will work regardless of duplicates
in Invoice, without the need for an expensive DISTINCT operator.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||I checked out and I confirm Roji's query meets my requirement.
Thanks to all of you.
Best Regards,
Luqman
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:lhq601hjmukcmbtbedkh8euf6q337ijva3@.
4ax.com...
> On Fri, 4 Feb 2005 11:04:03 +0100, Dean wrote:
>
> Hi Dean,
> Only if the combination of cn + rev is unique in the Invoice table. If it
> isn't, you'll get duplicates and you'll have to use a derived table
> (SELECT DISTINCT cn, rev FROM Invoice) AS D in your join.
> I suggest using Roji's solution, which will work regardless of duplicates
> in Invoice, without the need for an expensive DISTINCT operator.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)sql

is this basic syntax correct?

Hi all,
I am trying to perform what would seem to be a very simple conditional check
but it never works!
I always end up with an empty value.
=SUM(IIF(Fields!Ticket.Value = 'Student', Fields!Quantity.Value =0,
Fields!Quantity.Value))
What I am hoping for in the result is that if there is a 'Student' in the
result set, when totalling, it should set that Quantity value to 0, but
continue to sum up the other values.
Any help appreciated.
ImmyTry
=SUM(IIF(Fields!Ticket.Value = "Student", 0,
Fields!Quantity.Value))
or
create a calculated field with
=IIF(Fields!Ticket.Value = "Student", 0,
Fields!Quantity.Value) as the expression and then sum the calculated
field.
Enter make sure to use double quotes SQLRS is real picky.

Is there any way better than XP_CMDSHELL?

Hi ..
I want to Write in files or read from files
for example i have My_File.txt . i need a syntax and i want to call this syntax in my Store procedure and this syntax write forexample " Hello Word " in My_File.txt .
and i want another syntax that read from My_File.txt forexample "Word" from My_File.txt . what are those syntaxes do that ??

Is there any way better than XP_CMDSHELL for writing in or reading from MyFile.txt ??

thanksYou could BCP the SQL file into a temporary table and then execute the code through dynamic SQL, but that is pretty round-a-bout.

Is there any syntax like this in SQL Server ?

Query :
select * from Item1 a (NOLOCK, Index(ItemIn1))
join Item2 b (NOLOCK, Index(ItemData1))
Please explain the above nolock etc after the alias in the given query. What it does ?
Sam.They are table hints (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_backcomp_6rzt.asp), basically user supplied suggestions for how the optimizer ought to process the table.

NOLOCK requests that no new locks be placed, and existing locks be ignored. In my opinion, NOLOCK is a simple way to get wrong answers quickly.

Index(foo) requests that the optimizer select the index named foo on this table for processing the query. This is almost always a sign of poorly maintained database statistics or a brute force attempt to speed up a particular query. Maybe one instance in 500 is actually needed/helpful because of oddly skewed data that the developer understands but that the optimizer isn't smart enough to recognize.

As you've probably guessed, I don't think highly of table hints. There were times that they were absolutely crucial to getting work done. There are still times that they are helpful. In the VAST majority of cases with SQL-2000, while they might help in a specific circumstance, they are actually counter-productive in the long run.

-PatP|||Well, from what I've seen (quite a bit actually) about 90% of all SELECTs in a business system are or should be done with the lowest possible transaction isolation level. Reading COMMITTED ONLY data is absolutely required in situations where the most recent data vs. data being altered makes the most sense. But what is the true definition of "the most recent data"? Is it when the users clicks on OK button? If that's the case, than the result that could have been produced a split of a second earlier is different from the result that was actually acquired which in turn may be different from the result that would have been acquired if the user waited for another second...I don't believe in dogmatic approach to isolation levels, it all needs to be carefully tailored to the business needs and the way of application usage.|||See Pat...I thought this was a test or interview question...

especially since it didn't have a real example...

So how would they have know to get it syntactically correct?|||Well, from what I've seen (quite a bit actually) about 90% of all SELECTs in a business system are or should be done with the lowest possible transaction isolation level.Granted, but it should be done at the spid level using SET TRANSACTION ISOLATION LEVEL (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_set-set_74bw.asp), not for one query in a batch or even worse than that for one table within a query.

I think that you and I are on the "same page" here, but that is a tough concept to explain to someone asking what NOLOCK means. As long as the isolation level is applied consistantly for a transaction, it can be done safely. I don't agree that reading uncommitted data is anywhere near safe, but that's because I've spent literally months proving out statements that were corrupted due to the big rush at the end of the year where people were frantically entering and querying data... Those kind of memories tend to stick with me!

I feel very strongly that changing the isolation level for a single table within a query is a receipe for disaster. Every time I've found it, it has been a culprit.

-PatP|||Please explain to me how the example below may be used as a "receipe for disaster"? And activities like this one are the most typical for cut-over manufacturing+sales analysis for the day ;)

select o.order_id, o.order_date, o.customer_id, c.customer_name, d.item_id, im.item_description
from orders o (/*!!!*/TABLOCK/*!!!*/)
inner join customers c (/*!!!*/NOLOCK/*!!!*/)
on o.customer_id = c.customer_id
inner join order_detail d (/*!!!*/NOLOCK/*!!!*/)
on o.order_id = d.order_id
inner join item_master im (/*!!!*/NOLOCK/*!!!*/)
on d.item_id = im.item_id
where ship_date = convert(char(10), getdate(), 101)|||The easiest way to imagine a disaster is to think about how this kind of query is normally used. The "batch rollover" typically happens at a point in time when there is very little or no user interaction with the system... Other batch updates are often in progress. This transaction takes a full table lock on the orders table, but it does not take or respect any other locks.

Several customers that were input today got their names switched during the entry process. Two vendors sent updated product descriptions that need to be incorporated into your inventory master. As all these queries are running, you get everything absolutely correct from the orders table, but only some of the customer and item master information is corrupted... This can take months to find, but it destroys the user's faith in the system.

A more technical and more insideous problem is the "phantom deadlock"... If one of these queries is run by a scheduled task (with no UI) and another is run by a user task (with a UI) and both of them have existing locks on different tables that this query accesses with NOLOCK, even though it shouldn't produce a deadlock, on multiple CPU machines it can still produce one (unless you set MAXDOP to 1). This can cause a spooky error that is nearly impossible to resolve because of its timing sensitivity.

There are more ways to hurt yourself with this kind of programming practice, but they get progressively more difficult to reproduce.

Using NOLOCK with UPDATE statements is actually a larger problem, because it can cause code to break in ways that you can't reproduce. This is more visible to the average user, so it draws more attention and further deteriorates user faith in your application.

I don't mind running low isolation levels for "yardstick" queries. When you need a quick guestimate of today's volume, they are great. When you need to balance, to the penny, with absolutely accurate data, they are just problems waiting to happen. Think about how you'd feel about your bank if they promised you statements that were "pretty close, most of the time"!

-PatP|||Sorry, can't write such large posts, very little memory and information retention. But just to pick the bone, - phantom deadlock?? How can it happen with tables that don't have any footprint in syslocks? It's called PARANOIA, Pat. Relax, it must be something else that you're mistaking for the reasult of TABLOCK/NOLOCK combination ;)|||That's what the first two MS-PSS folks thought... My TAM was confident enough in me and my observations to push the issue until we reached a developer. After a LOT of work, we figured out how it could happen and what they had to change so we could remove the MAXDOP hint and have the code work without "assistance".

-PatP

Is there an MS SQL Limit function?

MySQL has a convenient syntax for paging data that looks like this:
SELECT * FROM MyTable LIMIT 10, 20
That would select 10 records, starting from record 20, so that it returns records 20 - 30. This is convenient way to page data, without returning anymore rows than than you need.

However, MS SQL doesn't appear to support that syntax. What is the equivalent sql code to select any N rows from an arbitrary starting point, without having to create a stored procedure?

Thanks in advance :)it's a fiasco

SQL Server has the TOP keyword, but it takes only one parameter

see this article -- http://rosca.net/writing/articles/serverside_paging.asp|||How can is start at row 20 when you have not specified an ORDER BY clause?|||How can is start at row 20 when you have not specified an ORDER BY clause?

You can't

Read here

http://weblogs.sqlteam.com/jeffs/category/162.aspx|||http://weblogs.sqlteam.com/jeffs/category/162.aspxjeepers, i took a look at one of the two articles posted there, and boy, that sql is inefficient

brett, did you read the article i posted?|||Which one? I thought the server side paging was pretty good...|||Andrew's code is very,,,need to compare the 2|||i originally read the second one, and it has issues

i just now went back and read the first one, and all it is is a dynamic-ization of the second one

i remain unimpressed

now, did you read the article i posted?|||Yes I did, and it's elegant...but I'd have to test it for performace against some major tables

Is there an equivalent syntax to TOP IN SQL?

Hi everyone, I am new to SQL, and would really appreciate help with this.

I have a database with the following fields:
IDNumber: sequential running from 1 to approx 50000
SURNAME: Surname
FNAME: Forename.

I want to return the last 100 IDNUmbers and return the surname and fname associated with the IDNumbers.
When I try TOP it gives me IDNumbers 1 to 100, is there an equilvant for the bottom 100 numbers.
Please help if you can.
Thanks
ScottReturn the top 100, but just order by IDNumbers DESC|||Thank you so much, I would never have worked that out.
And DESC stands for descending !? Fab!
Thanks again, I owe you a beer.

Scott