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 guaranteed: SELECT TOP 1 FROM ... ORDER BY Field1, Field2, Field3

SELECT TOP 1 FROM ... ORDER BY Field1, Field2, Field3
Will the statement below, always return the first record of the same
stand-alone SELECT statement as below:
SELECT * FROM ... ORDER BY Field1, Field2, Field3
Thanks,
JayYes, assuming you use the ORDER BY clause and the data remains constant.
Insert a new row, and it may be the new top result.
"Jay" <jay6447@.hotmail.com> wrote in message
news:1131529297.922481.160800@.g49g2000cwa.googlegroups.com...
> SELECT TOP 1 FROM ... ORDER BY Field1, Field2, Field3
> Will the statement below, always return the first record of the same
> stand-alone SELECT statement as below:
> SELECT * FROM ... ORDER BY Field1, Field2, Field3
>
> Thanks,
> Jay
>|||Didn't you see the contrary example that Razvan posted?
http://groups.google.com/group/micr...3752b9548322706
David Portas
SQL Server MVP
--|||In SQL Server 2005, we are a bit more consistent with TOP + ORDER BY
semantics than perhaps some previous releases.
Here are the basic rules:
1. ORDER BY determines the presentation order for the _output_ of a query.
2. Within the same select block, an ORDER BY implies that TOP returns the
TOP N rows (not necessarily in a specific order).
3. ORDER BY in subselects or views does *not* guarantee the output of a
containing query.
So, for TOP N... ORDER BY ... with no containing select block, both the set
and the order are guaranted.
Within a subquery, TOP N ... ORDER BY guarantees the set but not the output
order (you need a top-level ORDER BY to guarantee output order).
Conor Cunningham
SQL Server Query Optimization Team
"Jay" <jay6447@.hotmail.com> wrote in message
news:1131529297.922481.160800@.g49g2000cwa.googlegroups.com...
> SELECT TOP 1 FROM ... ORDER BY Field1, Field2, Field3
> Will the statement below, always return the first record of the same
> stand-alone SELECT statement as below:
> SELECT * FROM ... ORDER BY Field1, Field2, Field3
>
> Thanks,
> Jay
>

Is this guaranteed: SELECT TOP 1 FROM ... ORDER BY Field1, Fie

Many thanks David and Razvan, I've rated your posts as helpful.
--
Adam J Warne, MCDBA
"David Portas" wrote:

> Not necessarily. We don't know what other columns are involved or what
> the keys are. You can only guarantee that you'll get the same
> particular row from the top of the second query if the three ORDER BY
> clolumns are UNIQUE. Use TOP 1 WITH TIES if the ORDER BY criteria isn't
> unique.
> --
> David Portas
> SQL Server MVP
> --
>>The WITH TIES clause is new to me should be
There is even a web log named toponewithties :)
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Jay" <jay6447@.hotmail.com> wrote in message
news:1131539036.434912.103710@.g44g2000cwa.googlegroups.com...
> Thanks for the replies. The WITH TIES clause is new to me should be
> very helpful. Thanks.
>

Is this a "stored procedure" situation?

We have 2 SQL tables being accessed through an Access form. The tables are an ORDER table and an ORDER-DETAIL table comprised of data regarding the Parts in any given Order. (Yes -- the classic Order-Entry situation.) The Access form is used to view/create new Orders, and shows ORDER data in fields, plus has a large field which presents a "spreadsheet"-like view of the related records from the ORDER-DETAIL table.

The users enter and modify data in the ORDER-DETAIL table directly through this "spreadsheet" in the Access form. However, because there is no PARTS table yet (that's part of what I'm working on), they have to enter part numbers and descriptions *manually* in each ORDER.

So... here's my question:

After I implement a PARTS table, I would like for users to be able to open an ORDER in the Access form, type in a Part # in a row of the ORDER-DETAIL "spreadsheet", and then have the rest of the row populate with the appropriate Part description and other data from the PARTS table. How do I go about making that a reality? Some kind of stored procedure triggered by a change in the Part # field? Ha ha if so, I am clueless as to how to make that happen. ANY information would greatly appreciated!

Thanks!
whill96205 the Noob :confused:You'll want a few stored procedures for this probably. :) You don't want to bind the datagrid to the order-detail table. You'll need to populate it, then after they enter a part number, you will want to have an ON UPDATE action that:

1. Gets the part information and updates the ORDER-DETAIL table.
2. Refreshed the datagrid.|||[QUOTE=derrickleggett]You don't want to bind the datagrid to the order-detail table.QUOTE]

I think I understand what you mean by "bind" -- that the datagrid is like a *direct* window into the ORDER DETAIL table, right?

Okay, so I DON'T want to bind them. How can I tell if the datagrid that is currently in use is bound or not?

--William|||>> DerrickLeggett said:
>>You don't want to bind the datagrid to the order-detail table. You'll
>>need to populate it, then after they enter a part number, you will
>>want to have an ON UPDATE action that:
>> 1. Gets the part information and updates the ORDER-DETAIL table.
>> 2. Refreshed the datagrid.

The "datagrid" is a subform. Currently, I am using a View as the datasource for the subform, and the View is comprised of a join from the ORDER table and the PART table, and displays the PARTs that are already associated with the ORDER being viewed on the main form. There are two issues I'm trying to nail down:
1) To do what Derrick suggested (above), so that entering a PartNum value into a row of the subform causes the rest of the row to update with other data from the PART table (part description, etc.); and
2) To also allow a user to actually create a *new* entry in the PART table by entering a new PartNum into a row of the subform.

SO, I'd like the subform to recognize if a PartNum being entered into it is new or not. Is that possible? And, if so, how do I do that? PLEASE be explicit - this is all very new to me... :)

Is there an easy way to switch a table from "ANSI NULLS OFF" to "ANSI NULLS OFF"

We have some tables with quite a lot of data (in the order of tens of
millions of rows) which were created with ANSI NULLS OFF. Now that we
want to create some indexed views we need these tables to have ANSI
NULLS ON.
We can create new temp tables, bulk copy the data over and recreate all
cosntraints and indices.
But is there an easier way?
Thanks,
AnilA table doesn't care about this setting. It is the connection that is working against this table
which need to have the correct setting.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<sqlpractitioner@.gmail.com> wrote in message
news:1160615076.764380.293820@.i3g2000cwc.googlegroups.com...
> We have some tables with quite a lot of data (in the order of tens of
> millions of rows) which were created with ANSI NULLS OFF. Now that we
> want to create some indexed views we need these tables to have ANSI
> NULLS ON.
> We can create new temp tables, bulk copy the data over and recreate all
> cosntraints and indices.
> But is there an easier way?
> Thanks,
> Anil
>|||Tibor Karaszi wrote:
> A table doesn't care about this setting. It is the connection that is working against this table
> which need to have the correct setting.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
The setting at table-level does matter if you create computed columns
or if you need to create an indexed view.
Unfortunately, the only way I know of to change it is to recreate the
table.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||> The setting at table-level does matter if you create computed columns
> or if you need to create an indexed view.
Indeed, I just tried with an index over a computed columns.
Thanks, David. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1160645954.605837.156410@.e3g2000cwe.googlegroups.com...
> Tibor Karaszi wrote:
>> A table doesn't care about this setting. It is the connection that is working against this table
>> which need to have the correct setting.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
> The setting at table-level does matter if you create computed columns
> or if you need to create an indexed view.
> Unfortunately, the only way I know of to change it is to recreate the
> table.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||> The setting at table-level does matter if you create computed columns
> or if you need to create an indexed view.
> Unfortunately, the only way I know of to change it is to recreate the
> table.
>
Indexed views is the reason we are looking at this. Do you know if
there is an easier option in SQL 2005? We are on SQL 2000 now.
Thanks,
Anil|||<sqlpractitioner@.gmail.com> wrote in message
news:1160670946.200564.266370@.m73g2000cwd.googlegroups.com...
>> The setting at table-level does matter if you create computed columns
>> or if you need to create an indexed view.
>> Unfortunately, the only way I know of to change it is to recreate the
>> table.
> Indexed views is the reason we are looking at this. Do you know if
> there is an easier option in SQL 2005? We are on SQL 2000 now.
> Thanks,
> Anil
>
There is no change in 2005 that I know of. ANSI NULLS ON has been the
preferred option for so long, maybe supporting the OFF setting isn't a high
priority for MS. If you want to change that you could post a suggestion at:
http://connect.microsoft.com/SQLServer/feedback/
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||On Oct 12, 5:35 pm, sqlpractitio...@.gmail.com wrote:
> > The setting at table-level does matter if you create computed columns
> > or if you need to create an indexed view.
> > Unfortunately, the only way I know of to change it is to recreate the
> > table.Indexed views is the reason we are looking at this. Do you know if
> there is an easier option in SQL 2005? We are on SQL 2000 now.
> Thanks,
> Anil
There is no change in 2005 that I know of. ANSI NULLS ON has been the
preferred option for so long, maybe supporting the OFF setting isn't a
high priority for MS. If you want to change that you could post a
suggestion at:
http://connect.microsoft.com/SQLServer/feedback/
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--