query question

I have shopping cart and the items are stored in a
StoreCart table. I'm trying to write a query that
returns the items in the cart in certain way but I don't
know how to do it. Say for example that I have the
following items:
Product Qty
-- --
Toy_1 3
Toy_2 1
Toy_3 2
I want to write a query that will return the item the
number of times that the Qty field has.
Product
--
Toy_1
Toy_1
Toy_1
Toy_2
Toy_3
Toy_3
How can I do this?
TIA,
Vic"Vic" <vduran@.specpro-inc.com> wrote in message
news:0e4201c53fcc$350cb270$a501280a@.phx.gbl...
>I have shopping cart and the items are stored in a
> StoreCart table. I'm trying to write a query that
> returns the items in the cart in certain way but I don't
> know how to do it. Say for example that I have the
> following items:
> Product Qty
> -- --
> Toy_1 3
> Toy_2 1
> Toy_3 2
> I want to write a query that will return the item the
> number of times that the Qty field has.
> Product
> --
> Toy_1
> Toy_1
> Toy_1
> Toy_2
> Toy_3
> Toy_3
>
create table N(i int primary key)
go
set nocount on
declare @.i int
set @.i = 0
while @.i < 10000
begin
insert into N(i) values (@.i)
set @.i = @.i + 1
end
go
create table cart(product varchar(50) not null, Qty int not null, primary
key (Product,qty))
insert into cart(product,qty)values('Toy_1',3)
insert into cart(product,qty)values('Toy_2',1)
insert into cart(product,qty)values('Toy_3',2)
go
select product
from
cart
join N on n.i < cart.qty
order by product
David

Query Question

I have a query that I am not sure how to write.
I need to get the value of a column where that column's value happens more
than once AND a date column has the same date.
for instance:
ColA ColB
--
ValA 1/1/2003
ValA 1/1/2003
ValX 1/1/2003
I need to find all values from ColA where there is another row with the same
value in ColA and ColB (in this case the query should return ValA).
?SELECT DISTINCT colA
FROM Sometable
GROUP BY colA, colB
HAVING COUNT(*)>1
--
David Portas
--
Please reply only to the newsgroup
--|||Thanks David,
Makes sense and did the trick
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:-PWdnfOhm6CPriCi4p2dnA@.giganews.com...
> SELECT DISTINCT colA
> FROM Sometable
> GROUP BY colA, colB
> HAVING COUNT(*)>1
> --
> David Portas
> --
> Please reply only to the newsgroup
> --
>|||select cola from table_name
group by cola,colb
having count(*) >1
_______________________
"A" <agarrettbNOSPAM@.hotmail.com> wrote in message
news:OvXOcu6rDHA.1764@.TK2MSFTNGP10.phx.gbl...
> I have a query that I am not sure how to write.
> I need to get the value of a column where that column's value happens more
> than once AND a date column has the same date.
> for instance:
> ColA ColB
> --
> ValA 1/1/2003
> ValA 1/1/2003
> ValX 1/1/2003
> I need to find all values from ColA where there is another row with the
same
> value in ColA and ColB (in this case the query should return ValA).
> ?
>
>

Query Question

Greetings,
I am new to SQL Server and am trying to write a query for an application I
am working on. The table I am working with has three dollar amounts in
seperate columns that are prices on the same product from multiple
distributors. I am having trouble building a query that does a comparison on
these fields and returns a list of records based upon the lowest dollar amount
The results would be then used to populate a new table using only the lowest
price as returned from the SELECT statement
Is there someone who might be able to point me to some sample code that I
could use to help me figure out how this could be written
Thanks,
Joe.SELECT col1, col2, col3, ...
(SELECT MIN(price)
FROM
(SELECT price1 AS price UNION ALL
SELECT price2 UNION ALL
SELECT price3) AS T) AS min_price
FROM YourTable
The three price columns collectively represent a "repeating group". In
relational design this is a serious error and the difficulty you are having
is a consequence of the design problem. Hopefully your intention is to fix
this.
--
David Portas
SQL Server MVP
--|||David,
I assume that MIN will still work in the same manner if the price values
being compared are in seperate tables. I am working on different ways to get
rid of the repeating problem.
Thanks for your help
Joe.
"David Portas" wrote:
> SELECT col1, col2, col3, ...
> (SELECT MIN(price)
> FROM
> (SELECT price1 AS price UNION ALL
> SELECT price2 UNION ALL
> SELECT price3) AS T) AS min_price
> FROM YourTable
> The three price columns collectively represent a "repeating group". In
> relational design this is a serious error and the difficulty you are having
> is a consequence of the design problem. Hopefully your intention is to fix
> this.
> --
> David Portas
> SQL Server MVP
> --
>
>|||MIN retrieves the lowest non-NULL value of a set. If you can join the
additional table into the query then you should be able to make use of MIN.
My point about your design was that it would be easier and more efficient to
do this if your design was correctly normalized.
--
David Portas
SQL Server MVP
--

Query Question

Greetings,
I am new to SQL Server and am trying to write a query for an application I
am working on. The table I am working with has three dollar amounts in
seperate columns that are prices on the same product from multiple
distributors. I am having trouble building a query that does a comparison on
these fields and returns a list of records based upon the lowest dollar amou
nt
The results would be then used to populate a new table using only the lowest
price as returned from the SELECT statement
Is there someone who might be able to point me to some sample code that I
could use to help me figure out how this could be written
Thanks,
Joe.SELECT col1, col2, col3, ...
(SELECT MIN(price)
FROM
(SELECT price1 AS price UNION ALL
SELECT price2 UNION ALL
SELECT price3) AS T) AS min_price
FROM YourTable
The three price columns collectively represent a "repeating group". In
relational design this is a serious error and the difficulty you are having
is a consequence of the design problem. Hopefully your intention is to fix
this.
David Portas
SQL Server MVP
--|||David,
I assume that MIN will still work in the same manner if the price values
being compared are in seperate tables. I am working on different ways to get
rid of the repeating problem.
Thanks for your help
Joe.
"David Portas" wrote:

> SELECT col1, col2, col3, ...
> (SELECT MIN(price)
> FROM
> (SELECT price1 AS price UNION ALL
> SELECT price2 UNION ALL
> SELECT price3) AS T) AS min_price
> FROM YourTable
> The three price columns collectively represent a "repeating group". In
> relational design this is a serious error and the difficulty you are havin
g
> is a consequence of the design problem. Hopefully your intention is to fix
> this.
> --
> David Portas
> SQL Server MVP
> --
>
>|||MIN retrieves the lowest non-NULL value of a set. If you can join the
additional table into the query then you should be able to make use of MIN.
My point about your design was that it would be easier and more efficient to
do this if your design was correctly normalized.
David Portas
SQL Server MVP
--

query question

I have shopping cart and the items are stored in a
StoreCart table. I'm trying to write a query that
returns the items in the cart in certain way but I don't
know how to do it. Say for example that I have the
following items:
Product Qty
-- --
Toy_1 3
Toy_2 1
Toy_3 2
I want to write a query that will return the item the
number of times that the Qty field has.
Product
--
Toy_1
Toy_1
Toy_1
Toy_2
Toy_3
Toy_3
How can I do this?
TIA,
Vic"Vic" <vduran@.specpro-inc.com> wrote in message
news:0e4201c53fcc$350cb270$a501280a@.phx.gbl...
>I have shopping cart and the items are stored in a
> StoreCart table. I'm trying to write a query that
> returns the items in the cart in certain way but I don't
> know how to do it. Say for example that I have the
> following items:
> Product Qty
> -- --
> Toy_1 3
> Toy_2 1
> Toy_3 2
> I want to write a query that will return the item the
> number of times that the Qty field has.
> Product
> --
> Toy_1
> Toy_1
> Toy_1
> Toy_2
> Toy_3
> Toy_3
>
create table N(i int primary key)
go
set nocount on
declare @.i int
set @.i = 0
while @.i < 10000
begin
insert into N(i) values (@.i)
set @.i = @.i + 1
end
go
create table cart(product varchar(50) not null, Qty int not null, primary
key (Product,qty))
insert into cart(product,qty)values('Toy_1',3)
insert into cart(product,qty)values('Toy_2',1)
insert into cart(product,qty)values('Toy_3',2)
go
select product
from
cart
join N on n.i < cart.qty
order by product
David

Query Problems!

I'm having a problem write a query that pulls the new updated data from tbl1. The fields are state_no and date_insp. the criteria for the search is 00054000 & 1/1/2003 until present time.
I want to compare that data to tbl2 to see if it has any pre-existing data such as tbl1.Owner name, tbl1.Owner_addy, tbl2Owner_name, tbl2.Owner_addy so that I'm not duplicating the date in tbl2 when i do the import.

If this isn't clear to your please write back and I will explain more.HERE'S A QUERY THAT i CAME UP WITH TO TRY AND SEE IF IT WORKS BUT I DON'T THINK THAT ITS CHECKING EARLIER STATE_NO:

SELECT DISTINCT STATE_NO, OWNER_NAME, BUSINESS_NAME
FROM TESTNEWIMPORTBOILER T, HBC_CONTACT C
where state_no >= '00054000' and date_inspection >= '1/1/2003'
AND
T.OWNER_NAME =
(SELECT BUSINESS_NAME
FROM HBC_CONTACT
WHERE BUSINESS_NAME = T.OWNER_NAME)

Originally posted by Bigced_21
I'm having a problem write a query that pulls the new updated data from tbl1. The fields are state_no and date_insp. the criteria for the search is 00054000 & 1/1/2003 until present time.
I want to compare that data to tbl2 to see if it has any pre-existing data such as tbl1.Owner name, tbl1.Owner_addy, tbl2Owner_name, tbl2.Owner_addy so that I'm not duplicating the date in tbl2 when i do the import.

If this isn't clear to your please write back and I will explain more.

Query problem

Hi all,

I am trying to write a query to do the following in SQL server but am
struggling:

I have a table with a int number field in it and I want to find out
the lowest unused number in the table. Unfortunately, the numbers are
not necesarily sequential (due to deletes). So I may have the
following records:

num
--
1
2
3
4
6
8
9
10

I want a query to return me the value 5 - i.e. the lowest unused int
in the table (assuming counting starts at 1). I know I could do this
using a temporary table containing a full sequence of possible numbers
(given some ceiling) using something like:
select min(num) from temptable where num not in (select num from
maintable)
This would however mean creating a temporary table which I am trying
to avoid. I could also use a cursor to itterate through the records
above to find the lowest unused number but it seems overkill.

I'm sure there must be a way of doing this using a simple (possibly
nested) query. Any ideas?

Thanks in advance

--JamesSELECT COALESCE(MIN(T1.num)+1,1)
FROM YourTable AS T1
LEFT JOIN YourTable AS T2
ON T1.num = T2.num - 1
WHERE T2.num IS NULL

--
David Portas
SQL Server MVP
--|||David,
Just what I needed - cheers - nice bit of SQL too!

Regards

--James|||SELECT MIN(F1.num + 1)
FROM Foobar AS F1
UNION
VALUE (0) -- needs to be SELECT 0 in dialect
WHERE (T1.num +1)
NOT IN (SELECT num FROM Foobar);

This will give you an answer on the high end if the sequence is
complete.sql

Query problem

Hi,

I've got a problem with the following query, i'd like to write this in SQL for reporting services.

if ($par == "Ok"){
$query .= ' and b.date is not null '
}

if ($par == "NOK"{
$query = 'and b.date is null'
}

How should I do this? If my parameter is OK, the date should be filled in. Otherwise...

THX

You can do something like this

select .... where @.par = 'Ok' AND b.date is not null

union

select .... where @.par = 'NOK' AND b.date is null

I haven't tested it but it should work. The idea being that only one side of the union can have a result because only one side's where clause will evaluate to true.

Query plans

Hi all,
I read somewhere that you should write the fully qualified name for a table(db.dbo.table) in order to have a better chance to match the statement to a queryplan/execution plan..

Ive tested it to some of the tables in your db with different results, in some cases the query without the "complete path" was up to 3 times heavier to execute(relatively to the batch) than the same query with qualified path(i ran the two at the same time with show execution plan).

Sometimes there was no difference at all 50%-50% cost. And i wasent able to find any logic reason for the sometimes quicker execution with qualified path, sometimes a didnt even had any statistics created for the column in the where clause when the question was faster than the other one.

type of statements executed:
Select Col1,Col2 From db.dbo.table1
Select Col1,Col2 From table1

Does anyone know exactly how this works?

Any help would be mostly appreciated
JonasThe times that they were equal - were both queries on the fast side or slow side ?|||"The times that they were equal - were both queries on the fast side or slow side ?"

I really dont know..They where both executed with the same cost. My guess is that both queries where matched to an existing execution plan and should therefor been on the fast side.

Q2: Lets say that we have a stored proc that is compiled and sqlserver has generated a execution plan for it, right. Does the created execution plan uses other already created plans for the statements in the proc or does it creates a completely new one just for the statments in the proc?|||Take a look at Recompilng a Stored Procedure in bol. If you need more information or a better explanation, let me know.

query performance

The folling query works but is it the most efficient way to write it from a
performance aspect?
The parameter can be set with the values (0,1 and %) these can be changed if
needed for the final query version.
The Detail.Automated database field is a datatype of bit
Should I be writing this to avoid the LIKE keyword?
-- only used for testing
DECLARE @.Auto AS char (1)
SET @.Auto = '%'
-- end test
SELECT DISTINCT Status.ID,
Info.ID,
Info.Text,
Detail.Automated,
Status.Status
FROM Status INNER JOIN Info
ON Status.ID = Info.ID
INNER JOIN Detail
ON Info.ID = Detail.ID
WHERE (Status.ID = 4)
AND (Detail.Automated LIKE @.AutoJim Abel (JimAbel@.discussions.microsoft.com) writes:

> The folling query works but is it the most efficient way to write it
> from a performance aspect? The parameter can be set with the values (0,1
> and %) these can be changed if needed for the final query version.
> The Detail.Automated database field is a datatype of bit
> Should I be writing this to avoid the LIKE keyword?
Using LIKE with bit looks quite strange. I would rather write:
AND (Detail.Automated = @.Auto OR @.Auto IS NULL)
And of course @.Auto would be declared as bit.
Whether the query you have is the best from the point of view of
performance is impossible to say, as this requires knowledge about
the tables, indexes, and the amount and distribution of the data in
the tables.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

query out to XML

hi,
i need to write a query and export the result to an xml file. how would I
do that?
thanksHave a look at the 'FOR XML AUTO' extension to the select statement i.e.
select * from yourTable for xml auto
Peter
"Denial ain't just a river in Egypt."
Mark Twain
"Rafael Chemtob" wrote:

> hi,
> i need to write a query and export the result to an xml file. how would I
> do that?
> thanks
>
>

Query Optimization

Hello all,

Please bear with the long explanation of my scenario.

As I'm relatively new to the query world, I like to write my queries
using the visual toos such as the "View" option in SQL Server or in MS
Access. If I have a complicated query with sub-queries, I create a
query (view1) as (for example):

select ID,count(ID) as NumberOfUsers from tblContact
where Type > 3
GROUP BY ID

then create another query (view2) which uses the first view in another
select statement:

select count(NumberOfUsers) from view1 where NumberOfUsers > 1

What I've noted above is a very simple example but, you get the idea (I
hope).

My question, however, is regarding a very complex query with
cascading/nested views.

Scenario:

View1 is joining 5 tables

View2 is using a join between View1 and 3 more tables

View3 is using a join between View2 and 5 more tables

View4 is using a join between view3 and 2 more tables

When I run VIEW3, it executes within 10 seconds. When I run VIEW4, it
takes 4 hours! What I did to get around this problem was this:

1. I renamed View3 to something else - like View3_Test
2. I then exported the new View3_Test into a table called View3

After this modification, when I run View4, it executes in 15 seconds!

It seems to me that SQL Server is bundling the joins used in
View4...all as 1 view.

Is there any way, I can make the execution plan in such a way that it
executes (the original) view3 FIRST and then proceeds to execute the
rest of the joins in View4?

This would be extremely helpful for me because in the interim (sp?), I
will have to schedule a DTS package to export the View3_Test to the
table called View3 first...upon completion, run the rest of the report.
That's just cheating. I'd like to be able to provide a better
solution to my employer.

Any help will be much appreciated.

Thank you in advance for your expert advice,

-Umar Farooq.You can't really control how MSSQL processes the views, because the
query optimizer never 'sees' them - when you query a view, its name is
replaced by its definition, and the optimizer then finds a plan for the
expanded query. Unfortunately, it seems that in your case it isn't
finding a very good plan.

You can use Query Analyzer to check the query plan, and see which joins
or other operations are the 'heavy' ones. That may give you a clue as
to how to improve your queries.

Alternatively, if your views are used for reporting, and if the data in
the tables doesn't change very much, you could consider using indexed
views. These are more like tables, and they do store the data in the
view, so the optimizer doesn't need to go to the base tables. But this
will make changes to data much slower, so it's probably most useful in
a reporting scenario.

Simon|||Hello Simon,

Thanks for your reply.

I guess my next question would be "How do I set up an indexed view?" I
look in the Enterprise manager and when I right-click on the view,
under "All Tasks" it gives me "Manage Triggers" and "Permissions" but
"Manage Indexes" is grayed out.

I downloaded the Production.BAK file to the QA and restored it and ran
these views on it. Another point to note is that on the QA server, the
QA implementation of the same database, this query runs just
fine...max, 35 seconds. But on the Production database, it chokes.

I tried doing a DBCC "soft reindexing" on all the tables and then took
a SQL trace and ran the Query Optimizer on the Production database on
the QA server. Still no luck.

I'd like to learn more about the "Indexed Views" as that sounds like a
viable solution. You said the data will be stored in the view? I'm
not sure I understand how that works. It's a bit of an "overload" for
my Microsoft brain.

While on that subject, would anyone happen to know if I can set up
defaults in views? I know how to set it up on the table but I have a
scenario where I'd like for different people to see a table through a
view and on their "UserID" column, for example, I'd like to default
their UserID value on the view.

Thank you again for your help,

-Umar Farooq.|||"Umar Farooq" <UmarAlFarooq@.gmail.com> wrote in message
news:1112795648.697684.308450@.g14g2000cwa.googlegr oups.com...
> Hello Simon,
> Thanks for your reply.
> I guess my next question would be "How do I set up an indexed view?" I
> look in the Enterprise manager and when I right-click on the view,
> under "All Tasks" it gives me "Manage Triggers" and "Permissions" but
> "Manage Indexes" is grayed out.

I don't use EM much myself, so I don't know if it's possible to create an
indexed view from there. However, not all views are indexable, and you have
to make sure all the conditions are met, so it may simply be that your view
is not indexable without some changes - see "Creating an Indexed View" in
Books Online for a list of requirements.

> I downloaded the Production.BAK file to the QA and restored it and ran
> these views on it. Another point to note is that on the QA server, the
> QA implementation of the same database, this query runs just
> fine...max, 35 seconds. But on the Production database, it chokes.
> I tried doing a DBCC "soft reindexing" on all the tables and then took
> a SQL trace and ran the Query Optimizer on the Production database on
> the QA server. Still no luck.

I'm not really sure I follow this - you mean you copied your DB from
Production to QA, and the same query runs in 35 seconds in QA but 4 hours in
Production? If so, there should be something fairly obviously different in
the query plans to suggest what's going on.

> I'd like to learn more about the "Indexed Views" as that sounds like a
> viable solution. You said the data will be stored in the view? I'm
> not sure I understand how that works. It's a bit of an "overload" for
> my Microsoft brain.

See "Designing an Indexed View" in Books Online. Basically, you first create
a clustered index on the view (you can create additional nonclustered
indexes after that); since a clustered index always contains data in its
leaf level, the new index now has the view data in it. You can check Books
Online for more details.

An important point to remember with indexed views is that you can create
them in any edition of MSSQL, but only Enterprise Edition will automatically
use them - other editions will continue to expand the view and ignore the
indexes. To force them to use the indexes, you need to use the WITH
(NOEXPAND) hint in your queries - see the sections on hints under "FROM" in
BOL.

> While on that subject, would anyone happen to know if I can set up
> defaults in views? I know how to set it up on the table but I have a
> scenario where I'd like for different people to see a table through a
> view and on their "UserID" column, for example, I'd like to default
> their UserID value on the view.

I don't know what you mean by a default, but if you want to show different
users different data based on their login, then something like this may
work:

create view dbo.MyCustomers
as
select
CustID,
CustName,
...
from
dbo.Customers
where
CustomerAccountRep = system_user -- see BOL

> Thank you again for your help,
> -Umar Farooq.

You're welcome.

Simon|||/*
> I downloaded the Production.BAK file to the QA and restored it and
ran
> these views on it. Another point to note is that on the QA server,
the
> QA implementation of the same database, this query runs just
> fine...max, 35 seconds. But on the Production database, it chokes.

> I tried doing a DBCC "soft reindexing" on all the tables and then
took
> a SQL trace and ran the Query Optimizer on the Production database on

> the QA server. Still no luck.

I'm not really sure I follow this - you mean you copied your DB from
Production to QA, and the same query runs in 35 seconds in QA but 4
hours in
Production? If so, there should be something fairly obviously different
in
the query plans to suggest what's going on.
*/

I mean that I copied the DB from Production to QA and ran the query and
it took just as long as it did on production. However, the same tables
and structures and most of the data is in the QA DB on the QA server.
On that DB, the query runs super fast. Since the QA DB and the
Production DB are identical in structure, I thought re-indexing the
Production DB will make it run faster. No such luck. Not only that,
once I have the query running in 20 seconds on the QA DB, even if I
make a slight modification to the query (like adding some additional
fields, etc.) the query starts to bog down again.

I wish there was a way by which I could tell the query to treat the
steps I want as a self-contained query and not to combine the execution
plan with the other queries in the view.

By default, I mean for inserts. On a table, if I wanted to enter a
default date, for example, I'd just do a "getdate()" function on the
default property of the date field and it will insert a date in the
field by default. I'd like a view to to insert a default value in a
column just like the table default. For example:

tblSuggestion
ID, SuggestorID, Category, Suggestion, SuggestionDetails,
DateSubmitted, DateModified

Let's say there are 3 Suggestors in tblSuggestor: James, John and Mike

I want to create 3 views:

View1
ID, 1, Category, Suggestion, SuggestionDetails, DateSubmitted,
DateModified

View2
ID, 2, Category, Suggestion, SuggestionDetails, DateSubmitted,
DateModified

View3
ID, 3, Category, Suggestion, SuggestionDetails, DateSubmitted,
DateModified

I give James View1 which will automatically enter a 1 in the
SuggestorID field so he doesn't have to do it every time. All he has
to enter will be Category, Suggestion and SuggestionDetails.
DateSubmitted is a table default of "getdate()" and any changes to the
record will fire up a trigger to update DateModified with "getdate()"
value.

The problem is that the scenario I've listed above is only for Select
views and not for update views. I know I can accomplish this task with
a stored procedure which will accept values and hard-code the
information into the table but I wanted to do that via a view if
possible.

Thanks,

-Ashwin Sharma.|||Although it is generally not recommended, you could try to add the query
hint "OPTION (FORCE ORDER)", and see how that works for you.

HTH,
Gert-Jan

Umar Farooq wrote:
> Hello all,
> Please bear with the long explanation of my scenario.
> As I'm relatively new to the query world, I like to write my queries
> using the visual toos such as the "View" option in SQL Server or in MS
> Access. If I have a complicated query with sub-queries, I create a
> query (view1) as (for example):
> select ID,count(ID) as NumberOfUsers from tblContact
> where Type > 3
> GROUP BY ID
> then create another query (view2) which uses the first view in another
> select statement:
> select count(NumberOfUsers) from view1 where NumberOfUsers > 1
> What I've noted above is a very simple example but, you get the idea (I
> hope).
> My question, however, is regarding a very complex query with
> cascading/nested views.
> Scenario:
> View1 is joining 5 tables
> View2 is using a join between View1 and 3 more tables
> View3 is using a join between View2 and 5 more tables
> View4 is using a join between view3 and 2 more tables
> When I run VIEW3, it executes within 10 seconds. When I run VIEW4, it
> takes 4 hours! What I did to get around this problem was this:
> 1. I renamed View3 to something else - like View3_Test
> 2. I then exported the new View3_Test into a table called View3
> After this modification, when I run View4, it executes in 15 seconds!
> It seems to me that SQL Server is bundling the joins used in
> View4...all as 1 view.
> Is there any way, I can make the execution plan in such a way that it
> executes (the original) view3 FIRST and then proceeds to execute the
> rest of the joins in View4?
> This would be extremely helpful for me because in the interim (sp?), I
> will have to schedule a DTS package to export the View3_Test to the
> table called View3 first...upon completion, run the rest of the report.
> That's just cheating. I'd like to be able to provide a better
> solution to my employer.
> Any help will be much appreciated.
> Thank you in advance for your expert advice,
> -Umar Farooq.|||<snip
> I wish there was a way by which I could tell the query to treat the
> steps I want as a self-contained query and not to combine the execution
> plan with the other queries in the view.

Gert-Jan's FORCE ORDER suggestion is probably the closest thing there is
without using indexed views - generally you don't want to impose a certain
plan on the optimizer, but sometimes it just gets it wrong. You might also
want to make sure you have the latest servicepack installed, as they may
include changes to the optimizer.

> By default, I mean for inserts. On a table, if I wanted to enter a
> default date, for example, I'd just do a "getdate()" function on the
> default property of the date field and it will insert a date in the
> field by default. I'd like a view to to insert a default value in a
> column just like the table default. For example:

<snip
> The problem is that the scenario I've listed above is only for Select
> views and not for update views. I know I can accomplish this task with
> a stored procedure which will accept values and hard-code the
> information into the table but I wanted to do that via a view if
> possible.

A stored procedure is usually a better way to modify data than a view, since
it gives you more flexibility in terms of validating input, performing other
actions, cascading changes to other tables, applying procedural logic and so
on.

In your example, I don't really see why you need a view - you could just
SELECT/INSERT on the table directly. Unless tblSuggestion is in fact a view
and not a table? One view per user is not a very scalable solution anyway,
and maintenance would be an issue.

If you can replace SuggestorID with a user's login name, then you can simply
make SYSTEM_USER the default for that column, as you've already done with
GETDATE() elsewhere. If that isn't possible, then I would use a stored
procedure; if you absolutely have to use a view for some reason, then you
could create an INSTEAD OF trigger on the view. That assumes you have
SQL2000 - I don't think you've mentioned which version you have.

If this isn't helpful, I suggest you post CREATE TABLE and INSERT statements
to show exactly what you're trying to achieve.

http://www.aspfaq.com/etiquette.asp?id=5006

Simon

Query on large record

First thing I am new to write a query. I have two tables that I need
information from. The first table has the user, date and time. The second
table has the user, date, time and a record that contains 132 characters.
What I need to do is match the user, Date and time along with 7 characters
that are placed 7 positions in the record.
This is the begining of the record looks like and I only need the 0685043,
is this possible?
OVRIDE 0685043
Thanks in advance for any help.
I don't know what you're matching the 0685043 with, but
you will probably need the SUBSTRING function, which you
can learn about from Books Online, to extract that from the
rest of the 132 character string.
Generally, if substrings of a column have meaning of their own,
it is better to keep that information in a separate column of the
table.
Steve Kass
Drew University
Daniell wrote:

>First thing I am new to write a query. I have two tables that I need
>information from. The first table has the user, date and time. The second
>table has the user, date, time and a record that contains 132 characters.
>What I need to do is match the user, Date and time along with 7 characters
>that are placed 7 positions in the record.
>This is the begining of the record looks like and I only need the 0685043,
>is this possible?
>OVRIDE 0685043
>Thanks in advance for any help.
>
>
|||Thanks Steve I guess I should have explained a little better. I will give
the SUBSTRING a try.
"Steve Kass" wrote:

> I don't know what you're matching the 0685043 with, but
> you will probably need the SUBSTRING function, which you
> can learn about from Books Online, to extract that from the
> rest of the 132 character string.
> Generally, if substrings of a column have meaning of their own,
> it is better to keep that information in a separate column of the
> table.
> Steve Kass
> Drew University
> Daniell wrote:
>

Query on 2 tables

Hello all,
If I write this query :

SELECT [Client].[CLI_NAME], [Client].[CLI_PRENOM] FROM Client, Commandes

the return is good.

But If I the query is :

SELECT [Client].[CLI_NOM] & " " & [Commandes].[CMD_DATE_ORDER] As Ordered FROM Client, Commandes

I have an error "Invalid column name.

The CLI_NOM field is on Client Table and CMD_DATE_ORDER is on the other Order table.
How to make a good query with an aliased column without error ?

Thanks for reply.
Regards.
PAB.Try changing this:
& " " &
to this:
& ' ' &|||Also the & should be replaced with +

hth|||Also the
&
should be replaced with
+.

hth|||Hello,
Thanks all. I try that solutions.
PAB.