Having problems with update statement

Can someone help me with this update, I have exhausted
all my effort into resolving this -
update pfile set facility = (select max(a.facility_num)
from efile a
where p.Order_num = a.Order_num and
pfile.facility <> a.facility_num and
a.facility_num IS NOT NULL and
pfile.facility like '9999%'
and a.facility_num not like 'N/A%' group by a.Order_num)
I checked the corresponding select statement works
select a.Order_num, a.facility_num,a.oth_facility_num, b.Order_num,
b.facility
from efile a, pfile b
where a.Order_num = b.Order_num and a.facility_num <> b.facility
and b.facility like '9999%'
and a.facility_num not like 'N/A%' group by a.Order_num
This works with group by clause too.
When I run update statement, I get
"Cannot insert null values into column facility "
However, I checked there is no null value in the select
statement or existing data. Then I changed the column
to allow nulls, it put null value for all records.
I also tried the statement without max(facility_num)
as there isn't more than one record as of now.
Still I get "Cannot insert null value " error, any ideas
how to resolve this?
I am stuck, I have to meeet deadline.
Thanks for your help!
-MYou need to keep your aliasing straight. You were mixing a "p" alias and the
base table name without ever defining it. Also, when a correlated sub-query
does not return any results, its value is NULL. Your updatable column does
not allow NULLs; so, you have to code for that condition either by excluding
those updates--which I coded--or using something like a CASE statement or the
ISNULL function to provide a different value when NULL appears.
Here is my alternative:
UPDATE p
SET facility = (SELECT MAX(e.facility_num)
FROM efile AS e
WHERE e.Order_num = p.Order_num
AND e.facility_num <> p.facility
AND e.facility_num IS NOT NULL
AND e.facility_num NOT LIKE 'N/A%'
)
FROM pfile AS p
WHERE p.facility LIKE '9999%'
AND EXISTS(
SELECT MAX(e.facility_num)
FROM efile AS e
WHERE e.Order_num = p.Order_num
AND e.facility_num <> p.facility
AND e.facility_num IS NOT NULL
AND e.facility_num NOT LIKE 'N/A%'
)
Now, if you give it some thought, you should be able to upgrade this from a
correlated sub-query expression to a direct update using multiple table
joins. This would be preferrable because the statement above will be
sloooooooow.
Good luck.
Sincerely,
Anthony Thomas
"Me" wrote:
> Can someone help me with this update, I have exhausted
> all my effort into resolving this -
> update pfile set facility = (select max(a.facility_num)
> from efile a
> where p.Order_num = a.Order_num and
> pfile.facility <> a.facility_num and
> a.facility_num IS NOT NULL and
> pfile.facility like '9999%'
> and a.facility_num not like 'N/A%' group by a.Order_num)
>
> I checked the corresponding select statement works
> select a.Order_num, a.facility_num,a.oth_facility_num, b.Order_num,
> b.facility
> from efile a, pfile b
> where a.Order_num = b.Order_num and a.facility_num <> b.facility
> and b.facility like '9999%'
> and a.facility_num not like 'N/A%' group by a.Order_num
> This works with group by clause too.
> When I run update statement, I get
> "Cannot insert null values into column facility "
> However, I checked there is no null value in the select
> statement or existing data. Then I changed the column
> to allow nulls, it put null value for all records.
> I also tried the statement without max(facility_num)
> as there isn't more than one record as of now.
> Still I get "Cannot insert null value " error, any ideas
> how to resolve this?
> I am stuck, I have to meeet deadline.
> Thanks for your help!
> -M
>
>|||Anthony,
Thanks for the reply!
Still it didn't work, but I found a work around.
Appreciate your help!
-M
"AnthonyThomas" wrote:
> You need to keep your aliasing straight. You were mixing a "p" alias and the
> base table name without ever defining it. Also, when a correlated sub-query
> does not return any results, its value is NULL. Your updatable column does
> not allow NULLs; so, you have to code for that condition either by excluding
> those updates--which I coded--or using something like a CASE statement or the
> ISNULL function to provide a different value when NULL appears.
> Here is my alternative:
> UPDATE p
> SET facility => (SELECT MAX(e.facility_num)
> FROM efile AS e
> WHERE e.Order_num = p.Order_num
> AND e.facility_num <> p.facility
> AND e.facility_num IS NOT NULL
> AND e.facility_num NOT LIKE 'N/A%'
> )
> FROM pfile AS p
> WHERE p.facility LIKE '9999%'
> AND EXISTS(
> SELECT MAX(e.facility_num)
> FROM efile AS e
> WHERE e.Order_num = p.Order_num
> AND e.facility_num <> p.facility
> AND e.facility_num IS NOT NULL
> AND e.facility_num NOT LIKE 'N/A%'
> )
> Now, if you give it some thought, you should be able to upgrade this from a
> correlated sub-query expression to a direct update using multiple table
> joins. This would be preferrable because the statement above will be
> sloooooooow.
> Good luck.
> Sincerely,
>
> Anthony Thomas
>
> "Me" wrote:
> > Can someone help me with this update, I have exhausted
> > all my effort into resolving this -
> >
> > update pfile set facility = (select max(a.facility_num)
> > from efile a
> > where p.Order_num = a.Order_num and
> > pfile.facility <> a.facility_num and
> > a.facility_num IS NOT NULL and
> > pfile.facility like '9999%'
> > and a.facility_num not like 'N/A%' group by a.Order_num)
> >
> >
> > I checked the corresponding select statement works
> >
> > select a.Order_num, a.facility_num,a.oth_facility_num, b.Order_num,
> > b.facility
> > from efile a, pfile b
> > where a.Order_num = b.Order_num and a.facility_num <> b.facility
> > and b.facility like '9999%'
> > and a.facility_num not like 'N/A%' group by a.Order_num
> >
> > This works with group by clause too.
> >
> > When I run update statement, I get
> > "Cannot insert null values into column facility "
> >
> > However, I checked there is no null value in the select
> > statement or existing data. Then I changed the column
> > to allow nulls, it put null value for all records.
> >
> > I also tried the statement without max(facility_num)
> > as there isn't more than one record as of now.
> > Still I get "Cannot insert null value " error, any ideas
> > how to resolve this?
> >
> > I am stuck, I have to meeet deadline.
> >
> > Thanks for your help!
> > -M
> >
> >
> >
> >|||Um, you forgot your WHERE clause on your update statement. As written, it
would try to update every row in pfile.
And you don't have a table corresponding to your p alias.
Jeff
"Me" <Me@.discussions.microsoft.com> wrote in message
news:143D3A21-3743-4F66-8B2E-91764A750E62@.microsoft.com...
> Can someone help me with this update, I have exhausted
> all my effort into resolving this -
> update pfile set facility = (select max(a.facility_num)
> from efile a
> where p.Order_num = a.Order_num and
> pfile.facility <> a.facility_num and
> a.facility_num IS NOT NULL and
> pfile.facility like '9999%'
> and a.facility_num not like 'N/A%' group by a.Order_num)
>
> I checked the corresponding select statement works
> select a.Order_num, a.facility_num,a.oth_facility_num, b.Order_num,
> b.facility
> from efile a, pfile b
> where a.Order_num = b.Order_num and a.facility_num <> b.facility
> and b.facility like '9999%'
> and a.facility_num not like 'N/A%' group by a.Order_num
> This works with group by clause too.
> When I run update statement, I get
> "Cannot insert null values into column facility "
> However, I checked there is no null value in the select
> statement or existing data. Then I changed the column
> to allow nulls, it put null value for all records.
> I also tried the statement without max(facility_num)
> as there isn't more than one record as of now.
> Still I get "Cannot insert null value " error, any ideas
> how to resolve this?
> I am stuck, I have to meeet deadline.
> Thanks for your help!
> -M
>
>sql

Having problems with update statement

Can someone help me with this update, I have exhausted
all my effort into resolving this -
update pfile set facility = (select max(a.facility_num)
from efile a
where p.Order_num = a.Order_num and
pfile.facility <> a.facility_num and
a.facility_num IS NOT NULL and
pfile.facility like '9999%'
and a.facility_num not like 'N/A%' group by a.Order_num)
I checked the corresponding select statement works
select a.Order_num, a.facility_num,a.oth_facility_num, b.Order_num,
b.facility
from efile a, pfile b
where a.Order_num = b.Order_num and a.facility_num <> b.facility
and b.facility like '9999%'
and a.facility_num not like 'N/A%' group by a.Order_num
This works with group by clause too.
When I run update statement, I get
"Cannot insert null values into column facility "
However, I checked there is no null value in the select
statement or existing data. Then I changed the column
to allow nulls, it put null value for all records.
I also tried the statement without max(facility_num)
as there isn't more than one record as of now.
Still I get "Cannot insert null value " error, any ideas
how to resolve this?
I am stuck, I have to meeet deadline.
Thanks for your help!
-M
You need to keep your aliasing straight. You were mixing a "p" alias and the
base table name without ever defining it. Also, when a correlated sub-query
does not return any results, its value is NULL. Your updatable column does
not allow NULLs; so, you have to code for that condition either by excluding
those updates--which I coded--or using something like a CASE statement or the
ISNULL function to provide a different value when NULL appears.
Here is my alternative:
UPDATE p
SET facility =
(SELECT MAX(e.facility_num)
FROM efile AS e
WHERE e.Order_num = p.Order_num
AND e.facility_num <> p.facility
AND e.facility_num IS NOT NULL
AND e.facility_num NOT LIKE 'N/A%'
)
FROM pfile AS p
WHERE p.facility LIKE '9999%'
AND EXISTS(
SELECT MAX(e.facility_num)
FROM efile AS e
WHERE e.Order_num = p.Order_num
AND e.facility_num <> p.facility
AND e.facility_num IS NOT NULL
AND e.facility_num NOT LIKE 'N/A%'
)
Now, if you give it some thought, you should be able to upgrade this from a
correlated sub-query expression to a direct update using multiple table
joins. This would be preferrable because the statement above will be
sloooooooow.
Good luck.
Sincerely,
Anthony Thomas
"Me" wrote:

> Can someone help me with this update, I have exhausted
> all my effort into resolving this -
> update pfile set facility = (select max(a.facility_num)
> from efile a
> where p.Order_num = a.Order_num and
> pfile.facility <> a.facility_num and
> a.facility_num IS NOT NULL and
> pfile.facility like '9999%'
> and a.facility_num not like 'N/A%' group by a.Order_num)
>
> I checked the corresponding select statement works
> select a.Order_num, a.facility_num,a.oth_facility_num, b.Order_num,
> b.facility
> from efile a, pfile b
> where a.Order_num = b.Order_num and a.facility_num <> b.facility
> and b.facility like '9999%'
> and a.facility_num not like 'N/A%' group by a.Order_num
> This works with group by clause too.
> When I run update statement, I get
> "Cannot insert null values into column facility "
> However, I checked there is no null value in the select
> statement or existing data. Then I changed the column
> to allow nulls, it put null value for all records.
> I also tried the statement without max(facility_num)
> as there isn't more than one record as of now.
> Still I get "Cannot insert null value " error, any ideas
> how to resolve this?
> I am stuck, I have to meeet deadline.
> Thanks for your help!
> -M
>
>
|||Anthony,
Thanks for the reply!
Still it didn't work, but I found a work around.
Appreciate your help!
-M
"AnthonyThomas" wrote:
[vbcol=seagreen]
> You need to keep your aliasing straight. You were mixing a "p" alias and the
> base table name without ever defining it. Also, when a correlated sub-query
> does not return any results, its value is NULL. Your updatable column does
> not allow NULLs; so, you have to code for that condition either by excluding
> those updates--which I coded--or using something like a CASE statement or the
> ISNULL function to provide a different value when NULL appears.
> Here is my alternative:
> UPDATE p
> SET facility =
> (SELECT MAX(e.facility_num)
> FROM efile AS e
> WHERE e.Order_num = p.Order_num
> AND e.facility_num <> p.facility
> AND e.facility_num IS NOT NULL
> AND e.facility_num NOT LIKE 'N/A%'
> )
> FROM pfile AS p
> WHERE p.facility LIKE '9999%'
> AND EXISTS(
> SELECT MAX(e.facility_num)
> FROM efile AS e
> WHERE e.Order_num = p.Order_num
> AND e.facility_num <> p.facility
> AND e.facility_num IS NOT NULL
> AND e.facility_num NOT LIKE 'N/A%'
> )
> Now, if you give it some thought, you should be able to upgrade this from a
> correlated sub-query expression to a direct update using multiple table
> joins. This would be preferrable because the statement above will be
> sloooooooow.
> Good luck.
> Sincerely,
>
> Anthony Thomas
>
> "Me" wrote:
|||Um, you forgot your WHERE clause on your update statement. As written, it
would try to update every row in pfile.
And you don't have a table corresponding to your p alias.
Jeff
"Me" <Me@.discussions.microsoft.com> wrote in message
news:143D3A21-3743-4F66-8B2E-91764A750E62@.microsoft.com...
> Can someone help me with this update, I have exhausted
> all my effort into resolving this -
> update pfile set facility = (select max(a.facility_num)
> from efile a
> where p.Order_num = a.Order_num and
> pfile.facility <> a.facility_num and
> a.facility_num IS NOT NULL and
> pfile.facility like '9999%'
> and a.facility_num not like 'N/A%' group by a.Order_num)
>
> I checked the corresponding select statement works
> select a.Order_num, a.facility_num,a.oth_facility_num, b.Order_num,
> b.facility
> from efile a, pfile b
> where a.Order_num = b.Order_num and a.facility_num <> b.facility
> and b.facility like '9999%'
> and a.facility_num not like 'N/A%' group by a.Order_num
> This works with group by clause too.
> When I run update statement, I get
> "Cannot insert null values into column facility "
> However, I checked there is no null value in the select
> statement or existing data. Then I changed the column
> to allow nulls, it put null value for all records.
> I also tried the statement without max(facility_num)
> as there isn't more than one record as of now.
> Still I get "Cannot insert null value " error, any ideas
> how to resolve this?
> I am stuck, I have to meeet deadline.
> Thanks for your help!
> -M
>
>

Having problems with update statement

Can someone help me with this update, I have exhausted
all my effort into resolving this -
update pfile set facility = (select max(a.facility_num)
from efile a
where p.Order_num = a.Order_num and
pfile.facility <> a.facility_num and
a.facility_num IS NOT NULL and
pfile.facility like '9999%'
and a.facility_num not like 'N/A%' group by a.Order_num)
I checked the corresponding select statement works
select a.Order_num, a.facility_num,a.oth_facility_num, b.Order_num,
b.facility
from efile a, pfile b
where a.Order_num = b.Order_num and a.facility_num <> b.facility
and b.facility like '9999%'
and a.facility_num not like 'N/A%' group by a.Order_num
This works with group by clause too.
When I run update statement, I get
"Cannot insert null values into column facility "
However, I checked there is no null value in the select
statement or existing data. Then I changed the column
to allow nulls, it put null value for all records.
I also tried the statement without max(facility_num)
as there isn't more than one record as of now.
Still I get "Cannot insert null value " error, any ideas
how to resolve this?
I am stuck, I have to meeet deadline.
Thanks for your help!
-MYou need to keep your aliasing straight. You were mixing a "p" alias and th
e
base table name without ever defining it. Also, when a correlated sub-query
does not return any results, its value is NULL. Your updatable column does
not allow NULLs; so, you have to code for that condition either by excluding
those updates--which I coded--or using something like a CASE statement or th
e
ISNULL function to provide a different value when NULL appears.
Here is my alternative:
UPDATE p
SET facility =
(SELECT MAX(e.facility_num)
FROM efile AS e
WHERE e.Order_num = p.Order_num
AND e.facility_num <> p.facility
AND e.facility_num IS NOT NULL
AND e.facility_num NOT LIKE 'N/A%'
)
FROM pfile AS p
WHERE p.facility LIKE '9999%'
AND EXISTS(
SELECT MAX(e.facility_num)
FROM efile AS e
WHERE e.Order_num = p.Order_num
AND e.facility_num <> p.facility
AND e.facility_num IS NOT NULL
AND e.facility_num NOT LIKE 'N/A%'
)
Now, if you give it some thought, you should be able to upgrade this from a
correlated sub-query expression to a direct update using multiple table
joins. This would be preferrable because the statement above will be
sloooooooow.
Good luck.
Sincerely,
Anthony Thomas
"Me" wrote:

> Can someone help me with this update, I have exhausted
> all my effort into resolving this -
> update pfile set facility = (select max(a.facility_num)
> from efile a
> where p.Order_num = a.Order_num and
> pfile.facility <> a.facility_num and
> a.facility_num IS NOT NULL and
> pfile.facility like '9999%'
> and a.facility_num not like 'N/A%' group by a.Order_num)
>
> I checked the corresponding select statement works
> select a.Order_num, a.facility_num,a.oth_facility_num, b.Order_num,
> b.facility
> from efile a, pfile b
> where a.Order_num = b.Order_num and a.facility_num <> b.facility
> and b.facility like '9999%'
> and a.facility_num not like 'N/A%' group by a.Order_num
> This works with group by clause too.
> When I run update statement, I get
> "Cannot insert null values into column facility "
> However, I checked there is no null value in the select
> statement or existing data. Then I changed the column
> to allow nulls, it put null value for all records.
> I also tried the statement without max(facility_num)
> as there isn't more than one record as of now.
> Still I get "Cannot insert null value " error, any ideas
> how to resolve this?
> I am stuck, I have to meeet deadline.
> Thanks for your help!
> -M
>
>|||Anthony,
Thanks for the reply!
Still it didn't work, but I found a work around.
Appreciate your help!
-M
"AnthonyThomas" wrote:
[vbcol=seagreen]
> You need to keep your aliasing straight. You were mixing a "p" alias and
the
> base table name without ever defining it. Also, when a correlated sub-que
ry
> does not return any results, its value is NULL. Your updatable column doe
s
> not allow NULLs; so, you have to code for that condition either by excludi
ng
> those updates--which I coded--or using something like a CASE statement or
the
> ISNULL function to provide a different value when NULL appears.
> Here is my alternative:
> UPDATE p
> SET facility =
> (SELECT MAX(e.facility_num)
> FROM efile AS e
> WHERE e.Order_num = p.Order_num
> AND e.facility_num <> p.facility
> AND e.facility_num IS NOT NULL
> AND e.facility_num NOT LIKE 'N/A%'
> )
> FROM pfile AS p
> WHERE p.facility LIKE '9999%'
> AND EXISTS(
> SELECT MAX(e.facility_num)
> FROM efile AS e
> WHERE e.Order_num = p.Order_num
> AND e.facility_num <> p.facility
> AND e.facility_num IS NOT NULL
> AND e.facility_num NOT LIKE 'N/A%'
> )
> Now, if you give it some thought, you should be able to upgrade this from
a
> correlated sub-query expression to a direct update using multiple table
> joins. This would be preferrable because the statement above will be
> sloooooooow.
> Good luck.
> Sincerely,
>
> Anthony Thomas
>
> "Me" wrote:
>|||Um, you forgot your WHERE clause on your update statement. As written, it
would try to update every row in pfile.
And you don't have a table corresponding to your p alias.
Jeff
"Me" <Me@.discussions.microsoft.com> wrote in message
news:143D3A21-3743-4F66-8B2E-91764A750E62@.microsoft.com...
> Can someone help me with this update, I have exhausted
> all my effort into resolving this -
> update pfile set facility = (select max(a.facility_num)
> from efile a
> where p.Order_num = a.Order_num and
> pfile.facility <> a.facility_num and
> a.facility_num IS NOT NULL and
> pfile.facility like '9999%'
> and a.facility_num not like 'N/A%' group by a.Order_num)
>
> I checked the corresponding select statement works
> select a.Order_num, a.facility_num,a.oth_facility_num, b.Order_num,
> b.facility
> from efile a, pfile b
> where a.Order_num = b.Order_num and a.facility_num <> b.facility
> and b.facility like '9999%'
> and a.facility_num not like 'N/A%' group by a.Order_num
> This works with group by clause too.
> When I run update statement, I get
> "Cannot insert null values into column facility "
> However, I checked there is no null value in the select
> statement or existing data. Then I changed the column
> to allow nulls, it put null value for all records.
> I also tried the statement without max(facility_num)
> as there isn't more than one record as of now.
> Still I get "Cannot insert null value " error, any ideas
> how to resolve this?
> I am stuck, I have to meeet deadline.
> Thanks for your help!
> -M
>
>

having problems with details selection

i have a packing slip report with 2 detail sections. can i select which details section to display/print as the line item based on whether the line item is a stock item (orderitem.type) =1 or a miscellaneous item (orderitem.type) = 3 ?? i don't want to use the suppress method, it needs to be one section or the other. thanksWhat's wrong with conditionally suppressing each detail sub-section as required? Would seem to be exactly what you want.

Having Problems with a Simple Query

Hey guys,
I am trying to run a query that will return all rows where a column is
completely empty of data.
I try this query:
select * from patients_visitInsurers
where CompanyID is NULL
But it does not return anything for me. If I try the reverse
select * from patients_visitInsurers
where CompanyID is not NULL
It returns rows that contain CompanyId values and rows that are blank.
What am I missing here? Thanks!Do you have those blank values for CompanyID being NULL or just blank
string? Assuming CompanyID is character format, you can check:
SELECT <columns>
FROM patients_visitInsurers
WHERE COALESCE(CompanyID, '') = ''
Or
SELECT <columns>
FROM patients_visitInsurers
WHERE CompanyID IS NULL
OR CompanyID = ''
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||Is CompanyID a string? "Empty of data" and "blank" are two different
things, in my opinion. Empty of data means NULL (and you have the correct
syntax, of that were the case). Blank means an empty string, e.g. ''. In
which case,
WHERE CompanyID = ''
You may also be safer to trim the data first, since it could contain a
space...
WHERE RTRIM(CompanyID) = ''
However, you should not insert a blank string when you really meant NULL.
They are different in implementation, and they are different on a conceptual
level, as well.
<alvinstraight38@.hotmail.com> wrote in message
news:40bfbcdf-5531-4350-9889-dee8af80b160@.d1g2000hsg.googlegroups.com...
> Hey guys,
> I am trying to run a query that will return all rows where a column is
> completely empty of data.
> I try this query:
> select * from patients_visitInsurers
> where CompanyID is NULL
> But it does not return anything for me. If I try the reverse
> select * from patients_visitInsurers
> where CompanyID is not NULL
> It returns rows that contain CompanyId values and rows that are blank.
> What am I missing here? Thanks!|||Plamen Ratchev was thinking very hard :
> Do you have those blank values for CompanyID being NULL or just blank string?
> Assuming CompanyID is character format, you can check:
> SELECT <columns>
> FROM patients_visitInsurers
> WHERE COALESCE(CompanyID, '') = ''
> Or
> SELECT <columns>
> FROM patients_visitInsurers
> WHERE CompanyID IS NULL
> OR CompanyID = ''
>
and if that doesn't get it, then
OR LTRIM(RTRIM(CompanyID)) = ''
HTH,
Brad.|||On Apr 4, 11:07=A0am, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> Is CompanyID a string? =A0"Empty of data" and "blank" are two different
> things, in my opinion. =A0Empty of data means NULL (and you have the corre=ct
> syntax, of that were the case). =A0Blank means an empty string, e.g. ''. ==A0In
> which case,
> WHERE CompanyID =3D ''
> You may also be safer to trim the data first, since it could contain a
> space...
> WHERE RTRIM(CompanyID) =3D ''
> However, you should not insert a blank string when you really meant NULL.
> They are different in implementation, and they are different on a conceptu=al
> level, as well.
> <alvinstraigh...@.hotmail.com> wrote in message
> news:40bfbcdf-5531-4350-9889-dee8af80b160@.d1g2000hsg.googlegroups.com...
>
> > Hey guys,
> > I am trying to run a query that will return all rows where a column is
> > completely empty of data.
> > I try this query:
> > select * from patients_visitInsurers
> > where CompanyID is =A0NULL
> > But it does not return anything for me. =A0If I try the reverse
> > select * from patients_visitInsurers
> > where CompanyID is =A0not NULL
> > It returns rows that contain CompanyId values and rows that are blank.
> > What am I missing here? =A0 Thanks!- Hide quoted text -
> - Show quoted text -
I think that is where I am getting confused. I looked at the data
type and it says varchar and not null. If I look at the database,
nothing shows in the column for some rows, but I want to exclude rows
that do contain values in this field.
Thanks!|||>>
I think that is where I am getting confused. I looked at the data
type and it says varchar and not null. If I look at the database,
nothing shows in the column for some rows,
Right. So you need to have two concepts very clear:
NULL is the absence of any data whatsoever.
'' is an empty string. It is data, even if it is zero-length.
There is little point in setting this column to NOT NULL if you can stick an
empty string in there. In this specific situation, it is apparent that the
two concepts are interchangeable...sql

Having problem with sql create

The following is a create that I was trying to run in SQL/2000 SQL
Analyser:

CREATE TABLE tblxyz as (Select * from tblPlayers);

tblPlayers has 2 rows in it.

I continue to get the error

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'AS'.

I have tried it with and without the () and it works on Oracle but not
SQL/2K. Don't know why.

This has all developed because of another problem.

First I have select with a complex join all records with a certain
employee ID.

Then I have to select the top 20 (by date) records from the first
select.

From the second select results I have to select the top 10 by a
numeric field.

The only way I thought of doing it was to create temporary tables (see
original question).SELECT * INTO tblxyz FROM tblPlayers

if you want an empty table with only the schema of tblPlayers use a WHERE
1=0

"Jim R" <JimReid@.comcast.net> wrote in message
news:7eb6df4b.0407150949.15a9468c@.posting.google.c om...
> The following is a create that I was trying to run in SQL/2000 SQL
> Analyser:
> CREATE TABLE tblxyz as (Select * from tblPlayers);
> tblPlayers has 2 rows in it.
> I continue to get the error
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'AS'.
> I have tried it with and without the () and it works on Oracle but not
> SQL/2K. Don't know why.
> This has all developed because of another problem.
> First I have select with a complex join all records with a certain
> employee ID.
> Then I have to select the top 20 (by date) records from the first
> select.
> From the second select results I have to select the top 10 by a
> numeric field.
> The only way I thought of doing it was to create temporary tables (see
> original question).|||> CREATE TABLE tblxyz as (Select * from tblPlayers);

The SQL Server equivalent is

SELECT * INTO tblxyz FROM tblPlayers ;

> The only way I thought of doing it was to create temporary tables (see
> original question).

Without more of a spec (DDL for the tables, some INSERT statements of sample
data) it's impossible for us to know if that's really the best way to
achieve what you want.

--
David Portas
SQL Server MVP
--|||Thank you. That works great. Now I create tbltemp and from tbltemp I
create a tbltempa and then drop tbltemp. All in a stored procedure.
Then after I'm done with my selection of the recordset I'll drop the
tbltempa.

Thanks again.

Jim

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||SQL is a declarative language. To get the most out of SQL, start by writing
a query that will produce the end result you require rather than thinking of
procedural solutions like [Query 1] -> [Temp Table] -> [Query 2] -> etc.
From your description I doubt that temp tables are the most concise,
efficient or maintainable solution to your problem.

--
David Portas
SQL Server MVP
--|||David,

You are right. My problem was that I didn't know how to create a select
within a select. What I ended up with was just that and it is all in a
single stored procedure with no temporary tables. Works really well.

My biggest problem was all new things I was trying to do. I have always
done simple select statements before both with Views and Stored
Procedures.

Then I discovered that I needed to feed parameters to the first select
which knocked out Views. Then I needed only the top 20 by date and from
the top 20 the lowest 10(numerically) of those top 20.

It got very confusing real fast when I had a stored procedure that
select via param for the top 20. It would pick the top 20 first and
then the parms. Which wasn't the sequence I wanted.

Since I was only running on SQL/2k I ended up with the following stored
procedure:
CREATE PROCEDURE [SelectUSGA1]]
@.PlayerID integer
AS
SELECT TOP 10 WITH TIES dbo.tblScores.[Date], dbo.tblScores.Player,
dbo.tblScores.HandiDiff, dbo.tblScores.Tees, dbo.tblScores.H1,
dbo.tblScores.H2, dbo.tblScores.H3, dbo.tblScores.Course,
dbo.tblCourse.CourseID, dbo.tblCourse.CourseName, dbo.tblCourse.Hole1,
dbo.tblCourse.Hole2, dbo.tblCourse.Hole3, dbo.tblCourse.Hole4,
dbo.tblCourse.Hole5, dbo.tblCourse.Hole6, dbo.tblCourse.Hole7,
dbo.tblCourse.Hole8, dbo.tblCourse.Hole9, dbo.tblCourse.Hole10,
dbo.tblCourse.Hole11, dbo.tblCourse.Hole12, dbo.tblCourse.Hole13,
dbo.tblCourse.Hole14, dbo.tblCourse.Hole15, dbo.tblCourse.Hole16,
dbo.tblCourse.Hole17, dbo.tblCourse.Hole18, dbo.tblCourse.T,
dbo.tblCourse.Rating, dbo.tblCourse.Slope, dbo.tblPlayers.Player_ID,
dbo.tblPlayers.PlayerLastName, dbo.tblPlayers.PlayerFirstName,
dbo.tblPlayers.PlayerNickName, dbo.tblScores.H4, dbo.tblScores.H5,
dbo.tblScores.H6, dbo.tblScores.H7, dbo.tblScores.H8, dbo.tblScores.H9,
dbo.tblScores.H10, dbo.tblScores.H11, dbo.tblScores.H12,
dbo.tblScores.H13, dbo.tblScores.H14, dbo.tblScores.H15,
dbo.tblScores.H16, dbo.tblScores.H17, dbo.tblScores.H18,
dbo.tblScores.B9, dbo.tblScores.T18, dbo.tblScores.P1, dbo.tblScores.F9,
dbo.tblScores.P2, dbo.tblScores.P3, dbo.tblScores.P4, dbo.tblScores.P5,
dbo.tblScores.P6,
dbo.tblScores.P7, dbo.tblScores.P8, dbo.tblScores.P9, dbo.tblScores.P10,
dbo.tblScores.P11, dbo.tblScores.P12, dbo.tblScores.P13,
dbo.tblScores.P15, dbo.tblScores.P14,
dbo.tblScores.P16, dbo.tblScores.P17, dbo.tblScores.P18,
dbo.tblScores.FW1, dbo.tblScores.FW2, dbo.tblScores.FW3,
dbo.tblScores.FW4, dbo.tblScores.FW5, dbo.tblScores.FW6,
dbo.tblScores.FW7, dbo.tblScores.FW8, dbo.tblScores.FW9,
dbo.tblScores.FW10, dbo.tblScores.FW11, dbo.tblScores.FW12,
dbo.tblScores.FW13, dbo.tblScores.FW14, dbo.tblScores.FW15,
dbo.tblScores.FW16, dbo.tblScores.FW17,
dbo.tblScores.FW18, dbo.tblScores.FWT, dbo.tblScores.TotalPutts,
dbo.tblScores.ActualDiff, dbo.tblScores.TotPars, dbo.tblScores.TotBirds,
dbo.tblScores.TotEagles, dbo.tblScores.TotBogys,
dbo.tblScores.Tot2Bogys, dbo.tblScores.AvgPutts
FROM dbo.tblScores
LEFT OUTER JOIN
dbo.tblCourse ON dbo.tblScores.Course = dbo.tblCourse.CourseID
LEFT OUTER JOIN
dbo.tblPlayers ON dbo.tblScores.Player =
dbo.tblPlayers.Player_ID
WHERE tblPlayers.Player_ID=@.PlayerID
order by date, HandiDiff;
GO

Works really well now.

Jim

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Looks like you just need date in DESCending order (the default is
ascending).

SELECT TOP 10 WITH TIES
...
ORDER BY date DESC, handidiff

Note that you may or may not want the WITH TIES option. Without it, TOP may
give non-deterministic results - if there are values where the sort columns
are equal (tied) then only some subset of the rows may be shown if the
result would otherwise give more than 10 rows.

--
David Portas
SQL Server MVP
--

> I posted another message because with your solution it gave me 10 rows by
> date which isn't exactly what I was looking for.

> I have to selection for a row of golf scores, the most current 20 rows by
> date for a particular player.

> Then I have to select the lowest 10 scores from that subset.

> Then I use those 10 to calculate the USGA Handicap Index.

> What I'm getting is the newest 10 by date which may not be the best scores|||Still have a problem with SQL. I have tried the following:

Select top 10 with ties, date, score
From tblScores
Where playerID = 3
Order by date desc, score;

The problem is that it selects the top 10 scores by date when in fact I
want the most current top 20 scores and from the most current top 20 I
want to pick the top 10 scores by lowest score.

So if I had 100 rows I would get the most current 20. From that group I
would select by score the lowest 10.

I'm not sure how TIES works but it's not doing what I wanted.

Any help would be appreciated.

Jim

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Try this:

SELECT TOP 10 WITH TIES *
FROM
(SELECT TOP 20 WITH TIES
S.[date], S.player, S.handidiff, S.tees, S.h1, S.h2, S.h3, S.course,
C.courseid, C.coursename, C.hole1, C.hole2, C.hole3, C.hole4, C.hole5,
C.hole6, C.hole7, C.hole8, C.hole9, C.hole10, C.hole11, C.hole12,
C.hole13, C.hole14, C.hole15, C.hole16, C.hole17, C.hole18, C.t,
C.rating, C.slope, P.player_id, P.playerlastname, P.playerfirstname,
P.playernickname, S.h4, S.h5, S.h6, S.h7, S.h8, S.h9, S.h10, S.h11,
S.h12, S.h13, S.h14, S.h15, S.h16, S.h17, S.h18, S.b9, S.t18,
S.p1, S.f9, S.p2, S.p3, S.p4, S.p5, S.p6, S.p7, S.p8, S.p9, S.p10,
S.p11, S.p12, S.p13, S.p15, S.p14, S.p16, S.p17, S.p18, S.fw1, S.fw2,
S.fw3, S.fw4, S.fw5, S.fw6, S.fw7, S.fw8, S.fw9, S.fw10, S.fw11,
S.fw12, S.fw13, S.fw14, S.fw15, S.fw16, S.fw17, S.fw18, S.fwt,
S.totalputts, S.actualdiff, S.totpars, S.totbirds, S.toteagles,
S.totbogys, S.tot2bogys, S.avgputts
FROM dbo.tblScores AS S
LEFT OUTER JOIN
dbo.tblCourse AS C
ON S.course = C.courseid
LEFT OUTER JOIN
dbo.tblPlayers AS P
ON S.player = P.player_id
WHERE P.player_id = @.PlayerID
ORDER BY S.[date] DESC, S.handidiff) AS T
ORDER BY handidiff

--
David Portas
SQL Server MVP
--sql

HAVING clause?

Hi all,

How would I add:

WHERE Year(tblDetails.DateAdded)=#2003#

... to the SQL statement below?

"SELECT tblDetails.ProductID, tblProducts.ShortDesc, Count(tblDetails.ProductID) AS ProductCount FROM (tblDetails INNER JOIN tblProducts ON tblDetails.ProductID = tblProducts.ProductID) GROUP BY tblDetails.ProductID, tblProducts.ShortDesc ORDER BY Count(tblDetails.ProductID) DESC"

Cheers,
Davidi think the order is

select...
from...
where...
group by ...
having...
order by...

sql

Having clause without GROUP BY clause?

Hi,

What is HAVING clause equivalent in the following oracle query, without the combination of "GROUP BY" clause ?

eg :

SELECT SUM(col1) from test HAVING col2 < 5

SELECT SUM(col1) from test WHERE x=y AND HAVING col2 < 5

I want the equivalent query in MSSQLServer for the above Oracle query.

Also, does the aggregate function in Select column(here the SUM(col1)) affect in anyway the presence of HAVING clause?.

Thanks,
Gopi.those queries actually run in oracle? i rather doubt it

without a GROUP BY, the entire table is considered a single group

the individual col2 values would not necessarily all be the same, therefore the HAVING condition in the first query would not necessarily give you the results you want, assuming it even runs, which i doubt

in the second query you will surely get a syntax error even in oracle

perhaps what you want for the two queries is:

SELECT SUM(col1) from test where col2 < 5

SELECT SUM(col1) from test WHERE x=y AND col2 < 5|||Sorry for the typos. Actually the queries are as follows.

SELECT SUM(col1) from test HAVING SUM(col2) < 5

SELECT SUM(col1) from test WHERE x=y HAVING SUM(col2) < 5

Thanks,
Gopi.|||do you have sql server? if so, why don't you test those queries and see what you get

HAVING clause is a case statement??

i have wrote a query which compares two tables, returning anywhere the qty is not the same in each table:

(simple ex)

Select order_no
from table1
inner join table2
on table1.order_no = table2.order_no
group by order_no
having table1.Qty<> table2.Qty

BUT... I need to add a table3, where there maybe (or may not be enters - thus a left join). If there is an entry in table3 then use qty in table3 and not table1... so having becomes:

CASE WHEN table1.Qty<> table3.Qty
THEN table3.Qty<> table2.Qty
ELSE table1.Qty<> table2.Qty END

but how do i actually write this?perhaps if you would care to explain what you're doing?

are you comparing individual Qty values, or the SUMs?

because the HAVING clause may refer only to columns in the GROUP BY or to aggregate functions|||perhaps if you would care to explain what you're doing?

are you comparing individual Qty values, or the SUMs?

because the HAVING clause may refer only to columns in the GROUP BY or to aggregate functions

Sorry I am trying to compare Sum(qty) for each product in an order (product maybe in the order more than 1ce) I am trying to retrieve any product lines where Sum qties in table1 and table2 are not the same.

However, if stock was not found, then an allocated qty is recorded in table 3...so in this case I want to compare qtyies in table3 and table2
?|||select t1.order_no
, t1.sumqty
, t2.order_no
, t2.sumqty
from (
select order_no
, sum(qty) as sumqty
from table1
group
by order_no
) as t1
full outer
join (
select order_no
, sum(qty) as sumqty
from table2
group
by order_no
) as t2
on t2.order_no = t1.order_no
and t2.sumqty <> t1.sumqtythat's the general strategy -- do your sums in derived tables

for table 3, you're on your own :)

having a select statement with no output

i have the following select query "select myCol from myTbl"

i cant modify the query but i can add to it;
what i want is to cancel the output of this query so it has no output( just like an update or insert query). a way for doing this is using 'select into' a #temp table but i cant do this becoz adding the 'into' at the end wont work after the 'from'.

i can use 'union all' to add another select statement but i cant use 'into' in the second statement becoz it has to be in the first one.

adding 'where 1=2' is not what i want becoz it still gives an empty output

any way to do this ?

thxCan you only add to the end of it? If you just add "--" to the front of it it won't execute at all, and you won't get any output.

This is certainly one of the strange requests I've seen on this forum. Why do you wan't a select statements with no output? Are you doing debugging or some sort of iterative process automation?

blindman|||thx for ur reply

i cant add to the front becoz it's a written query that i cant modify but can only add a string to the end before it is executed.

my prob is solved now after i found my source files and now i can modify the query..

thx|||I STILL have no idea...

WOAC radio

HAVING (COUNT(category) > 1) , not only 1 row

i am using this code :

SELECT MAX(user) AS lastuser, category
FROM journal
GROUP BY category
HAVING (COUNT(category) > 1)

it works but returns 1 line by category >1

i need all the user (all the rows) HAVING (COUNT(category) > 1) , not only 1

if 1 category has only 1 user i must not keep it

i am not shure to be clear :-)

thank you for helpingIf you need all the users fulfilling the requirements, why are you using the "max" argument. By its nature, the max (maximum) will return the largest value. Get rid of max, add user to your group by and try again.|||you mean

SELECT user, category
FROM journal
GROUP BY user,category
HAVING (COUNT(category) > 1)

i dont get it in that way

I dont need the lines : A|B where count(B) = 1
i need only the lines : A|B. C|B, D|B count(B) > 1

with my first code I get D|B (the last one)|||I don't think that your query will work it would return
where a user had the same category more than once
not differeent categories for the same user.

SELECT user
FROM journal
GROUP BY user
HAVING (COUNT(category) > 1)|||i dont get it in that way
i need for exemple 3 lines

john tennis
pierre tennis
paul tennis

3 lines if count(category) > 1|||SSELECT user_name,
category
FROM journal
WHERE category IN (SELECT category
FROM journal
GROUP BY category
HAVING COUNT(*) > 1)|||i'll try

thank you

Have lost the facility to select a Report Model DataSource Type?

I am trying to create a report from a Report Model, but when I try to select
the Datasource Type I find the Report Model DataSource Type is no longer
available to select. It definitely used to be there.
I have recently been experimenting with the Custom Report Items and modified
the configs etc as per instructions. The Polygons example appeared to work
eventually but at around the same time as experimenting with that, the Report
Model DataSource Type dissapeared. I have restored the config files back to
what they were, but still lost my DataSource Types. I suspect the only real
answer is to reinstall, however, I need to understand how to fix, and how I
have broken it. Where are the datasource types drop downs picked from? and
how best to fix this without reinstall. Any experts out there please help!Unsure how on earth lost the Report Model DataSource Type and the only way I
could find of restoring it was to uninstall all of SQL and reinstall it
again. Lost my Report Server Database, since cannot be restored.
Custom Report Items Health warning - tread carefully... suspect the
RSReportDesigner.config was the one that was clobbered in the process -
losing the RS Data extension. Unclear if it could be recreated somehow
without all this pain. Maybe a repair facility on Business Intelligence
Studio WOULD HAVE HELPED!
"Steve Giergiel" wrote:
> I am trying to create a report from a Report Model, but when I try to select
> the Datasource Type I find the Report Model DataSource Type is no longer
> available to select. It definitely used to be there.
> I have recently been experimenting with the Custom Report Items and modified
> the configs etc as per instructions. The Polygons example appeared to work
> eventually but at around the same time as experimenting with that, the Report
> Model DataSource Type dissapeared. I have restored the config files back to
> what they were, but still lost my DataSource Types. I suspect the only real
> answer is to reinstall, however, I need to understand how to fix, and how I
> have broken it. Where are the datasource types drop downs picked from? and
> how best to fix this without reinstall. Any experts out there please help!

Hash join

Can any one explain me this "Option [hash join]"...
what that Option [hash join] will effect in this query.
select * from [group] inner join patientgroup on PA_PatientID = PG_PatientID
Option [hash join]
Thanks
Noor
Noor wrote:
> Can any one explain me this "Option [hash join]"...
> what that Option [hash join] will effect in this query.
> select * from [group] inner join patientgroup on PA_PatientID =
> PG_PatientID Option [hash join]
>
> Thanks
> Noor
It will force SQL Server to use a hash join on the tables instead of a
nested loop join or a merge join. Generally, you want to let SQL Server
make up its own mind about what the best join operation is. Essentially,
a hash join forces SQL Server to create hash values on both tables in
order to determine the matching values.
To see how it affects your query test it in Query Analyzer with and
without the hint.
See Understanding Hash Joins in the BOL.
David G.

Has "Select All" option been checked for Multi-Valued Parameter?

Greetings.

I have downloaded the patch to SP1 that restores the Select All choice for MVP. I am now wondering if there is a clean way (property?) to know if the Select All box is checked. My reason for needing this is that I want to selectively print a block near the top of the report that shows all of the parameters used to create the report. There are many blocks of parameters and each can have 5-10 possible choices. It would be much cleaner if I could print "all" rather than list all of the possible values, if in fact that category was not really used to filter the rows.

Perhaps I need to build a dynamic where clause but I would rather not go to that trouble if I can use a built in feature or property.

Thanks in advance. I hope this makes sense.

No, this is by design. All values will be passed as a comma separated string.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||One possibility that you may use is to add an 'All' option to your selection list. Then in your SQL you could use specify WHERE ((FieldName IN (@.Parameter)) OR ('All' = @.Parameter)). Ugly but functional.

Hardening SQL Accounts

Hi I have some SQL accounts used by Word Mailmerge I would like to
limit what these accounts can do as much as possible.
I have granted them SELECT on the appropriate Views and Revoked SELECT
on sysobjects but when I log into enterprise manager using one of the
accounts I can still get a list of the SQL Accounts,database diagrams
and even stop the SQL Server.
Is there any way to prevent these permissions?
Thanks
hals_left> and even stop the SQL Server? Is this the SA login account? Check the
> membership in the Fixed Server Roles (primarily SYSADMIN) and other Fixed
> Database Roles/User-Defined database roles. You really do not need to
> remove SELECT to system objects.
Also, I'd recommend using Windows Authentication if possible - more secure.
HTH
Jerry
<cc900630@.ntu.ac.uk> wrote in message
news:1128616525.643700.173250@.f14g2000cwb.googlegroups.com...
> Hi I have some SQL accounts used by Word Mailmerge I would like to
> limit what these accounts can do as much as possible.
> I have granted them SELECT on the appropriate Views and Revoked SELECT
> on sysobjects but when I log into enterprise manager using one of the
> accounts I can still get a list of the SQL Accounts,database diagrams
> and even stop the SQL Server.
> Is there any way to prevent these permissions?
> Thanks
> hals_left
>|||Remove them from the sysadmin and or serveradmin fixed server roles. Only
members of those 2 roles can shutdown the SQL server.
Jacco Schalkwijk
SQL Server MVP
<cc900630@.ntu.ac.uk> wrote in message
news:1128616525.643700.173250@.f14g2000cwb.googlegroups.com...
> Hi I have some SQL accounts used by Word Mailmerge I would like to
> limit what these accounts can do as much as possible.
> I have granted them SELECT on the appropriate Views and Revoked SELECT
> on sysobjects but when I log into enterprise manager using one of the
> accounts I can still get a list of the SQL Accounts,database diagrams
> and even stop the SQL Server.
> Is there any way to prevent these permissions?
> Thanks
> hals_left
>|||Hi I have checked & the account its not a member of any server roles.
The only server role with members is System Administrators with members
sa and BUILTIN/Administrators. It is also not a member of any daatbase
roles.
I have only given this account SELECT on 2 views no other permissions
at all.
The acount is Type=Standard, Server Access=Permit.
I just conneced using the Servers IP Address and the user/pass for the
account and can still browse much of the server including creating and
removing backup devices, stopping & starting the server & agent and
viewing other account names.
I cant use Windows authentication for the account because the
connections coming in from other domains.

Hardening SQL Accounts

Hi I have some SQL accounts used by Word Mailmerge I would like to
limit what these accounts can do as much as possible.
I have granted them SELECT on the appropriate Views and Revoked SELECT
on sysobjects but when I log into enterprise manager using one of the
accounts I can still get a list of the SQL Accounts,database diagrams
and even stop the SQL Server.
Is there any way to prevent these permissions?
Thanks
hals_left
> and even stop the SQL Server? Is this the SA login account? Check the
> membership in the Fixed Server Roles (primarily SYSADMIN) and other Fixed
> Database Roles/User-Defined database roles. You really do not need to
> remove SELECT to system objects.
Also, I'd recommend using Windows Authentication if possible - more secure.
HTH
Jerry
<cc900630@.ntu.ac.uk> wrote in message
news:1128616525.643700.173250@.f14g2000cwb.googlegr oups.com...
> Hi I have some SQL accounts used by Word Mailmerge I would like to
> limit what these accounts can do as much as possible.
> I have granted them SELECT on the appropriate Views and Revoked SELECT
> on sysobjects but when I log into enterprise manager using one of the
> accounts I can still get a list of the SQL Accounts,database diagrams
> and even stop the SQL Server.
> Is there any way to prevent these permissions?
> Thanks
> hals_left
>
|||Remove them from the sysadmin and or serveradmin fixed server roles. Only
members of those 2 roles can shutdown the SQL server.
Jacco Schalkwijk
SQL Server MVP
<cc900630@.ntu.ac.uk> wrote in message
news:1128616525.643700.173250@.f14g2000cwb.googlegr oups.com...
> Hi I have some SQL accounts used by Word Mailmerge I would like to
> limit what these accounts can do as much as possible.
> I have granted them SELECT on the appropriate Views and Revoked SELECT
> on sysobjects but when I log into enterprise manager using one of the
> accounts I can still get a list of the SQL Accounts,database diagrams
> and even stop the SQL Server.
> Is there any way to prevent these permissions?
> Thanks
> hals_left
>
|||Hi I have checked & the account its not a member of any server roles.
The only server role with members is System Administrators with members
sa and BUILTIN/Administrators. It is also not a member of any daatbase
roles.
I have only given this account SELECT on 2 views no other permissions
at all.
The acount is Type=Standard, Server Access=Permit.
I just conneced using the Servers IP Address and the user/pass for the
account and can still browse much of the server including creating and
removing backup devices, stopping & starting the server & agent and
viewing other account names.
I cant use Windows authentication for the account because the
connections coming in from other domains.

Hardening SQL Accounts

Hi I have some SQL accounts used by Word Mailmerge I would like to
limit what these accounts can do as much as possible.
I have granted them SELECT on the appropriate Views and Revoked SELECT
on sysobjects but when I log into enterprise manager using one of the
accounts I can still get a list of the SQL Accounts,database diagrams
and even stop the SQL Server.
Is there any way to prevent these permissions?
Thanks
hals_left> and even stop the SQL Server? Is this the SA login account? Check the
> membership in the Fixed Server Roles (primarily SYSADMIN) and other Fixed
> Database Roles/User-Defined database roles. You really do not need to
> remove SELECT to system objects.
Also, I'd recommend using Windows Authentication if possible - more secure.
HTH
Jerry
<cc900630@.ntu.ac.uk> wrote in message
news:1128616525.643700.173250@.f14g2000cwb.googlegroups.com...
> Hi I have some SQL accounts used by Word Mailmerge I would like to
> limit what these accounts can do as much as possible.
> I have granted them SELECT on the appropriate Views and Revoked SELECT
> on sysobjects but when I log into enterprise manager using one of the
> accounts I can still get a list of the SQL Accounts,database diagrams
> and even stop the SQL Server.
> Is there any way to prevent these permissions?
> Thanks
> hals_left
>|||Remove them from the sysadmin and or serveradmin fixed server roles. Only
members of those 2 roles can shutdown the SQL server.
--
Jacco Schalkwijk
SQL Server MVP
<cc900630@.ntu.ac.uk> wrote in message
news:1128616525.643700.173250@.f14g2000cwb.googlegroups.com...
> Hi I have some SQL accounts used by Word Mailmerge I would like to
> limit what these accounts can do as much as possible.
> I have granted them SELECT on the appropriate Views and Revoked SELECT
> on sysobjects but when I log into enterprise manager using one of the
> accounts I can still get a list of the SQL Accounts,database diagrams
> and even stop the SQL Server.
> Is there any way to prevent these permissions?
> Thanks
> hals_left
>|||Hi I have checked & the account its not a member of any server roles.
The only server role with members is System Administrators with members
sa and BUILTIN/Administrators. It is also not a member of any daatbase
roles.
I have only given this account SELECT on 2 views no other permissions
at all.
The acount is Type=Standard, Server Access=Permit.
I just conneced using the Servers IP Address and the user/pass for the
account and can still browse much of the server including creating and
removing backup devices, stopping & starting the server & agent and
viewing other account names.
I cant use Windows authentication for the account because the
connections coming in from other domains.

HARD TOP SQL PROBLEM PLEASE HELP

Hi,

I need to use a top and a join in the same sql. To get 10 top refnr from orders_refnr. That works fine to I use this:

SQL = "SELECT TOP 10 refnr, antal = COUNT(refnr) FROM orders_refnr INNER JOIN produkter ON (orders_refnr.refnr = produkter.referensnummer) GROUP BY refnr ORDER BY antal DESC"

But I need to be able to get information from more fields than the field refnr. How can I specify more fields? I need to get other fields from produkter.

Please helt I′m really stucked.

Using the Northwind sample database, here's how you could do it using the Max (or Min) function:
select top 10 orders.customerId, count(orders.customerId), Max(CompanyName)
from orders
inner join customers on orders.customerId = customers.customerId
group by orders.customerID
order by count(orders.customerId) desc
|||

thanks for you answer.

But the problem is that I want more fields in my rs than just orders.customerId. How can I specify more fields?

|||Try it like this:

SELECT

    refnr,

    antal,

    produkter.column1,

    produkter.column2

FROM

( 

SELECT TOP 10

    refnr,

    COUNT(refnr) AS antal

FROM

    orders_refnr

GROUP BY

    refnr

ORDER BY

    2 DESC     

) AS subquery

INNER JOIN

    produkter ON (subquery.refnr = produkter.referensnummer)

ORDER BY

    antal DESC

|||Add the fields you want to the select list and also add them to the group by list.

Handling Dataset in Stored Proc.

Hi,

I want to create and populate a dataset from store procedure with following to querires & return the dataset as a result.

Select * from billmain where billno = 12

Select * from billdetails where billno = 12

I am currently performing this task aa a resultset. Now I want to use Dataset. Anybody can send me sample sp which returns dataset as a execution of the sp.

Nilkanth Desai

A simple select statement would do. Call the stored procedure from your ADO.NET code and store the results of this stored procedure in a DataSet object.

CREATE PROCEDURE SelectTable @.billNo int

AS

SELECT columnName FROM Table WHERE billno=@.billNo

|||

Hi,

Thanks fpr your Reply. This is working fine when I call it from my ADO.NET Code. But If I want to trf. Dataset as a return result of the said stored proc. where I will be doing multiple select in more then one table. So if I call it only once I can complete all task in a single call. In addition I want to marshal the job to server. So, In this case now tell me what should I do.I don't want to use CLR-Integrated stored procedure. In this case can How can I handle Dataset in above mentioned manner in a standard T-SQL stored procedure?

Nilkanth Desai

|||

You can return more than one SQL Server resultsets from a single stored procedure, see the example below.

Chris

CREATE PROCEDURE SelectTable @.billNo int

AS

SELECT columnName

FROM BillMain WHERE billno=@.billNo

SELECT columnName

FROM BillDetails WHERE billno=@.billNo

GO

Handling a Null datetime column

can anybody tell me how to do a select query on a datetime field where if i have a null value in that column, i need to display a some character.

ISNULL is a lovely function useful for doing just that.

ISNULL(MyDateColumn, 'ITS NULL!')

returns ITS NULL if column MyDateColumn's value is NULL

|||

select donor_id,isnull(check_date,'No Value') from donors where check_date is null

If the column check_date consists the null value U will get the value No Value.

Thank u

Baba

Please remember to click "Mark as Answer" on this post if it helped you.

|||I would say it depends what is the type of your field. If your filed is data time you shoudl convert it to string for output to not have problems with data type.Looks that ISNULL is trying to convert default for null value to the same type like tested value so you you would like to use syntax like this:ISNULL(DateField, 'Null date')It can not work because SQL will try to convert you "NUll Date" to be datetime and will fail. So you have to use different syntax:ISNULL(convert(varchar(20),DateField), 'Null date')and this should work but returned column will be varchar(20) not datetime.In case you need datetime column just left nulls inside or use first valid date :ISNULL(DateField, '00:00')it will replace all nulls by date '01/01/1900 00:00' Bu I think that keeping null is better.|||

ISNULL wont work with datetime a column if we r replacing with some characters. so in this case whatjpazgiermentioned is right.but in that too there is a flaw. what i got here is we need to check for each part of the datetime value for NULL.like dd/mm/yyyy, then hh:mm:ss,then am/pm.

so the query will be like

SELECT column1,Isnull(
(convert(varchar(20),columnDate,101) + ' ' + convert(varchar(20),columnDate,108) + ' ' + right(convert(varchar(20),columnDate),2)),'-') column2 from table1

im not sure if the above mentioned is the best solution possible.if anyone have any easy method other than this please reply.