How to exclude zero values when sorting?

I have a datagrid with a "sort" field I want to use to sort the rows in ascending order. However, I want values with a 0 or NULL value to be displayed last. I can't figure out how to do a sort (preferably in the SQL) that returns the empty values last. Is this possible?This isn't pretty but why don't you give it a go:
SELECT * FROM mytable WHERE myfield > 0 ORDER BY myfield ASC
UNION SELECT * FROM mytable WHERE myfield = 0

Regards
Fredr!k|||Unfortunately, this isn't valid syntax becuase ORDER BY must be at the end of the query. I get "Incorrect syntax near the keyword 'UNION'." when I try

SELECT * FROM Photo WHERE PhotoOrder > 0 ORDER BY PhotoOrder ASC UNION SELECT * FROM Photo WHERE PhotoOrder = 0|||To use the UNION operator in SQL Server all your Data types must be the same and the same order in both tables, but UNION is restrictive because it performs an Implicit DISTINCT by eliminating DUPLICATES. So if eliminating duplicates is not important try UNION ALL, if it still fails the it is INNER JOIN if both tables are equal or OUTER JOIN if they are not equal. Hope this helps.

Kind regards,
Gift Peddie|||I'm confused - how is this relevant to my question?|||I got what I wanted with
"ORDER BY IsNull(PhotoORDER, 1000)"

Unfortunately, zeroes will still sort first, but I can NULLify them on data entry|||You might try:

ORDER BY
 CASE WHEN ISNULL(PhotoOrder,0) = 0 THEN 2 ELSE 1 END,
 CASE WHEN ISNULL(PhotoOrder,0) <> 0 THEN PhotoOrder

Terri|||I was only replying your UNION error not your original post. I will try to be clear in the future.

Kind regards,
Gift Peddiesql

How to ensure column uniqueness

Hi,
Coming from an Oracle background, I'm used to being able
to create a unique key on a column that allows many null
values, ie if a value exists it must be unique, otherwise
it can be null.
It appears as though SQL Server allows only 1 null value
in the same situation, ie create a unique constraint on a
column and once you try to insert a 2nd row with a null
value I get a constraint violation. [Thx to those that
replied to my last question on this]
I don't really want to write triggers testing for such
conditions. Is there some form of constraint that can do
this for me?
TIA,
SJTYour observations are correct. You can create a view conatining all rows but the NULL. Then create a
unique index (or possible a unique constraint) on that index.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"SJT" <scott.taylor@.pwcs.com.au> wrote in message news:054601c36ad9$46dd1f90$a501280a@.phx.gbl...
> Hi,
> Coming from an Oracle background, I'm used to being able
> to create a unique key on a column that allows many null
> values, ie if a value exists it must be unique, otherwise
> it can be null.
> It appears as though SQL Server allows only 1 null value
> in the same situation, ie create a unique constraint on a
> column and once you try to insert a 2nd row with a null
> value I get a constraint violation. [Thx to those that
> replied to my last question on this]
> I don't really want to write triggers testing for such
> conditions. Is there some form of constraint that can do
> this for me?
>
> TIA,
> SJT|||You can use an indexed view to enforce uniqueness only for non-NULL values:
CREATE TABLE Sometable (keycol INTEGER PRIMARY KEY, colx INTEGER NULL)
GO
CREATE VIEW Sometable_Unique_Non_NULL
WITH SCHEMABINDING
AS SELECT colx FROM dbo.Sometable WHERE colx IS NOT NULL
GO
CREATE UNIQUE CLUSTERED INDEX uclcolx ON Sometable_Unique_Non_NULL (colx)
INSERT INTO Sometable VALUES (1,1)
INSERT INTO Sometable VALUES (2,NULL)
INSERT INTO Sometable VALUES (3,NULL)
--
David Portas
--
Please reply only to the newsgroup
--

How to eliminate the NULL field values

I am importing an Access .mdb file into MS SQL server, and empty fields where the default value is "", change into NULL. This is a problem when I re-export a result set and have to apply a procedure to clean these values. Is there a way to eliminate this? . . . . and what have I missed?Check 'allow nulls' and 'default value' settings in the access tables.|||and to solve for the export problem...

Use a query to export the table, and use
Select ISNULL(yourfield,"") AS FIELD1...sql

How to eliminate the commas from the end in sql query when the column value is null

HI

I have three different columns as email1,email2 , email3.I am concatinating these columns into one i.e EMail like

select ISNULL(dbo.tblperson.Email1, N'') +';'+ISNULL(dbo.tblperson.Email2, N'') +';'+ISNULL(dbo.tblperson.Email3, N'')ASEmail from tablename.

One eg of the output of the above query when email2,email3 are having null values in the table is :

jacky_foo@.mfa.gov.sg;;

means it is inserting semicoluns whenever there is a null value in the particular column. I want to remove this extra semicolumn whenever there is null value in the column.

Please let me know how can i do this

If you just change SQL a bit you have the answer, see below

select ISNULL(dbo.tblperson.Email1+ ';', N'') + ISNULL(dbo.tblperson.Email2 + ';', N'') + ISNULL(dbo.tblperson.Email3 + ';', N'')ASEmail from tablename.

|||

I tried this it worked a bit but not completely.Now I am getting the semicolumn at the end if there is null for the third column or you can say for the last column.

|||

There is probably a quick easy way to do it, but this will work:

select ISNULL(dbo.tblperson.Email1, N'') + ISNULL(CASE WHEN dbo.tblperson.Email1 IS NOT NULL THEN ';' ELSE '' END+dbo.tblperson.Email2, N'') + ISNULL(CASE WHEN dbo.tblperson.Email1 IS NOT NULL OR dbo.tblperson.Email2 IS NOT NULL THEN ';' ELSE '' END+dbo.tblperson.Email3, N'')ASEmail from tablename.

|||

Another way is to first normalize your data:

SELECT Email1 As Email FROM dbo.tblPerson
UNION ALL
SELECT Email2 As Email FROM dbo.tblPerson
UNION ALL
SELECT Email3 As Email FROM dbo.tblPerson

Now use the normalized data in a query like:

DECLARE @.Email varchar(max)

SELECT @.Email=ISNULL(@.Email+';','')+Email

FROM (

SELECT Email1 As Email FROM dbo.tblPerson
UNION ALL
SELECT Email2 As Email FROM dbo.tblPerson
UNION ALL
SELECT Email3 As Email FROM dbo.tblPerson

) t1

SELECT @.Email AS Email

|||

Another way is to use one of the many string concatenation techniques once your data has been normalized.

There is a CONCATENATE aggregation function that you can install that will do the trick. (Microsoft supplies one somewhere, google "T-SQL string concatenation aggregate").

There is another technique using the FOR XML/PATH to do the same thing, but it's also kind of messy.

|||

I appreciate your reply. This query worked for me.

How to eliminate Space entries...?

Hey,
I have some field values entries in my database.. that are spaces like ' '. i wanna eliminate them.
When i use IS NOT NULL in query it only eliminates the rows with NULL values so how could i modify the query to eliminate the rows with spaces in the field value..

Thx in advance..where COALESCE(somecolumn,' ')<>' '|||Thank you very much sir..|||SELECT * FROM Table1 WHERE Col1 > ' '|||WHERE NULLIF(somecolumn,' ') IS NOT NULL

How to Eliminate Nodes with Null values?

I need to shred the xml data to retrieve BrandIDs based on the following business rules.

/**************************************************************************************************************************

(1) Not every instance of xml would contain BrandIDs node

(2) Ignore BrandIDs whenever its a descendant of AlternativeState

(3) We are interested in the data stored under MarketSize whenever the CurrentEvent node is MarketSize.

(4) We are interested in the data stored under OtherEvent whenever the CurrentEvent node is not MarketSize.

****************************************************************************************************************************/

While shredding the xml data, I have noticed that out of 200,000 xml rows there are only 1000 BrandIDs nodes that do actually have data in them (e.g. <BrandIDs> 123, 234</BrandIDs>. Others are just blank in the form of </BrandIDs>. I would like to modify XQuery given below so that I could filter out such rows where even though BrandIDs node exist but it has no scalar value for me to retrieve. From the sample query result given at the end you would notice that the third row is empty. I would like to avoid such rows in result set.

I am open to any suggestions here if anyone out there could come up with a better solution.

declare @.xml xml

set @.xml =

'

<State>

<StatsState>

<CurrentState>

<BrandIDs>2698741</BrandIDs>

</CurrentState>

</StatsState>

</State>

<State>

<StatsState>

<CurrentState>

<OtherEvents>

<BrandIDs>160603,160737</BrandIDs>

</OtherEvents>

<CurrentEvent>BrandShare</CurrentEvent>

</CurrentState>

</StatsState>

</State>

<State>

<StatsState>

<CurrentState>

<MarketSize>

<BrandIDs />

<AlternativeState>

<CurrentEvent>None</CurrentEvent>

<BrandIDs>25630,8956201</BrandIDs>

</AlternativeState>

<CompanyIDs />

</MarketSize>

<CurrentEvent>MarketSize</CurrentEvent>

</CurrentState>

</StatsState>

</State>

<State>

<StatsState>

<CurrentState>

<OtherEvents>

<BrandIDs>2001,2002,2003,2004,2005,2006</BrandIDs>

</OtherEvents>

<CurrentEvent>BrandShare</CurrentEvent>

<MarketSize>

<BrandIDs>40666,71788,201225</BrandIDs>

</MarketSize>

</CurrentState>

</StatsState>

</State>

'

SELECT

Element.Val.query(

'for $s in self::node()

where $s//*/BrandIDs[not(parent::AlternativeState)]

return

if (data(($s/StatsState/CurrentState/CurrentEvent)[1]) = "MarketSize")

then $s/StatsState/CurrentState/MarketSize/BrandIDs/text()

else (

if (data(($s/StatsState/CurrentState/CurrentEvent)[1]) != "MarketSize")

then $s/StatsState/CurrentState/OtherEvents/BrandIDs/text()

else $s//BrandIDs/text())

') AS BrandIDs

FROM @.xml.nodes('/State') AS Element(Val)

GO

BrandIDs

-

2698741

160603,160737

2001,2002,2003,2004,2005,2006

If an element is empty then it does not have any child nodes meaning you can check with e.g. BrandIDs[node()] for BrandIDs elements that are not empty.

So your query could be written as

Code Snippet

SELECT

Element.Val.query(

'for $s in self::node()

return

if (data(($s/StatsState/CurrentState/CurrentEvent)[1]) = "MarketSize")

then $s/StatsState/CurrentState/MarketSize/BrandIDs/text()

else (

if (data(($s/StatsState/CurrentState/CurrentEvent)[1]) != "MarketSize")

then $s/StatsState/CurrentState/OtherEvents/BrandIDs/text()

else $s//BrandIDs/text())

') AS BrandIDs

FROM @.xml.nodes('/State[.//*/BrandIDs[node() and not(parent::AlternativeState)]]') AS Element(Val)

|||

Hi marton,

Thanks once more for helping me out here. Your proposed solution does solve my problem. Is there any way that perhaps you could use the 'where' clause in FLWOR to apply the same condition? I actually have a requirement to use @.xml.nodes('/State'). I do have different set of FLWOR queries to read values for different nodes and for each the root node is always /State and I would need to combine all of them in one statement. So preferably I would like to keep the nodes clause pointing to root.

Hope you get my point.

thanks again

|||The problem is that the nodes methods shreds the xml variable into rows that you then query with the query method. So for the original example you got four rows in the result set as the nodes method yields four rows, independent of the query applied later to the each row. If you want to eliminate nodes to not yield rows at all then I think it as to be done with the nodes method.|||

Hi Martin,

Thanks for clarification. I understand your point.

I actually works with xml where each of the node goes to its own relational table and I wanted to have only one select statement where each xml row is read only once and I get all of the values for nodes by specifying any business rules within FLWOR there. Hence I hesitate to specify any specific node such as BrandIDs in nodes() because it wouldn't leave an option for me to work with other nodes. I think I would have to use function for each node and call them from my select statement.

Many thanks

How to drop a column that has a DEFAULT clause

Hi,
I have a table with a column:
rv smallint default 1 not null
I want to drop it:
alter table myTable
drop column rv
But I get an error:
Server: Msg 5074, Level 16, State 1, Line 1
The object 'DF__ccAstPublica__rv__27C3E46E' is dependent on column 'rv'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN rv failed because one or more objects access this
column.
The 'object' mentioned above is the default constraint.
How do I just drop the column (without having to know waht constraints it
has)?
Thanks
MichaelYou first have to drop the default constraint
alter table myTable
drop constraint DF__ccAstPublica__rv__27C3E46E
Then drop the cilumn
"Mic" <micspam@.jadegroup.co.uk> wrote in message
news:7E45448B-E30D-4D47-9A03-75EE568E6928@.microsoft.com...
> Hi,
> I have a table with a column:
> rv smallint default 1 not null
> I want to drop it:
> alter table myTable
> drop column rv
> But I get an error:
> Server: Msg 5074, Level 16, State 1, Line 1
> The object 'DF__ccAstPublica__rv__27C3E46E' is dependent on column 'rv'.
> Server: Msg 4922, Level 16, State 1, Line 1
> ALTER TABLE DROP COLUMN rv failed because one or more objects access this
> column.
> The 'object' mentioned above is the default constraint.
> How do I just drop the column (without having to know waht constraints it
> has)?
> Thanks
> Michael
>|||Armand,
Thanks. Yes, I know I have to drop the constraint but...
This is in a script and I don't know the auto generated name of the
constraint, so I can't readily drop it.
Is there a way to find out the constraint name for the DEFAULT for that
column?
That way, I could fid the name then frop the constraint programmatically.
BTW, this is against SQL 7.0, so I can't use and INFORMATION_SCHEMA
Thanks
Michael
"Armando Prato" wrote:
> You first have to drop the default constraint
> alter table myTable
> drop constraint DF__ccAstPublica__rv__27C3E46E
> Then drop the cilumn
> "Mic" <micspam@.jadegroup.co.uk> wrote in message
> news:7E45448B-E30D-4D47-9A03-75EE568E6928@.microsoft.com...
> > Hi,
> > I have a table with a column:
> > rv smallint default 1 not null
> >
> > I want to drop it:
> > alter table myTable
> > drop column rv
> >
> > But I get an error:
> > Server: Msg 5074, Level 16, State 1, Line 1
> > The object 'DF__ccAstPublica__rv__27C3E46E' is dependent on column 'rv'.
> > Server: Msg 4922, Level 16, State 1, Line 1
> > ALTER TABLE DROP COLUMN rv failed because one or more objects access this
> > column.
> >
> > The 'object' mentioned above is the default constraint.
> > How do I just drop the column (without having to know waht constraints it
> > has)?
> > Thanks
> > Michael
> >
>
>|||Sure
What you can do is query the sysconstraints table in your db
and join it to syscolumns
select s2.name, object_name(s1.constid)
from sysconstraints s1
inner join syscolumns s2 on (s1.id = object_id('mytable') and s1.id = s2.id
and s1.colid = s2.colid and s1.status = 133141)
"Mic" <micspam@.jadegroup.co.uk> wrote in message
news:D9C9F97B-2660-47D8-BCC7-43076688F5D6@.microsoft.com...
> Armand,
> Thanks. Yes, I know I have to drop the constraint but...
> This is in a script and I don't know the auto generated name of the
> constraint, so I can't readily drop it.
> Is there a way to find out the constraint name for the DEFAULT for that
> column?
> That way, I could fid the name then frop the constraint programmatically.
> BTW, this is against SQL 7.0, so I can't use and INFORMATION_SCHEMA
> Thanks
> Michael
> "Armando Prato" wrote:
> > You first have to drop the default constraint
> >
> > alter table myTable
> > drop constraint DF__ccAstPublica__rv__27C3E46E
> >
> > Then drop the cilumn
> >
> > "Mic" <micspam@.jadegroup.co.uk> wrote in message
> > news:7E45448B-E30D-4D47-9A03-75EE568E6928@.microsoft.com...
> > > Hi,
> > > I have a table with a column:
> > > rv smallint default 1 not null
> > >
> > > I want to drop it:
> > > alter table myTable
> > > drop column rv
> > >
> > > But I get an error:
> > > Server: Msg 5074, Level 16, State 1, Line 1
> > > The object 'DF__ccAstPublica__rv__27C3E46E' is dependent on column
'rv'.
> > > Server: Msg 4922, Level 16, State 1, Line 1
> > > ALTER TABLE DROP COLUMN rv failed because one or more objects access
this
> > > column.
> > >
> > > The 'object' mentioned above is the default constraint.
> > > How do I just drop the column (without having to know waht constraints
it
> > > has)?
> > > Thanks
> > > Michael
> > >
> >
> >
> >|||you can run sp_help tablename or sp_helpconstraint tablename to view the
default name.
"Mic" <micspam@.jadegroup.co.uk> wrote in message
news:D9C9F97B-2660-47D8-BCC7-43076688F5D6@.microsoft.com...
> Armand,
> Thanks. Yes, I know I have to drop the constraint but...
> This is in a script and I don't know the auto generated name of the
> constraint, so I can't readily drop it.
> Is there a way to find out the constraint name for the DEFAULT for that
> column?
> That way, I could fid the name then frop the constraint programmatically.
> BTW, this is against SQL 7.0, so I can't use and INFORMATION_SCHEMA
> Thanks
> Michael
> "Armando Prato" wrote:
>> You first have to drop the default constraint
>> alter table myTable
>> drop constraint DF__ccAstPublica__rv__27C3E46E
>> Then drop the cilumn
>> "Mic" <micspam@.jadegroup.co.uk> wrote in message
>> news:7E45448B-E30D-4D47-9A03-75EE568E6928@.microsoft.com...
>> > Hi,
>> > I have a table with a column:
>> > rv smallint default 1 not null
>> >
>> > I want to drop it:
>> > alter table myTable
>> > drop column rv
>> >
>> > But I get an error:
>> > Server: Msg 5074, Level 16, State 1, Line 1
>> > The object 'DF__ccAstPublica__rv__27C3E46E' is dependent on column
>> > 'rv'.
>> > Server: Msg 4922, Level 16, State 1, Line 1
>> > ALTER TABLE DROP COLUMN rv failed because one or more objects access
>> > this
>> > column.
>> >
>> > The 'object' mentioned above is the default constraint.
>> > How do I just drop the column (without having to know waht constraints
>> > it
>> > has)?
>> > Thanks
>> > Michael
>> >
>>|||Armando,
Thanks. What does status 133141 mean?
"Armando Prato" wrote:
> Sure
> What you can do is query the sysconstraints table in your db
> and join it to syscolumns
>
> select s2.name, object_name(s1.constid)
> from sysconstraints s1
> inner join syscolumns s2 on (s1.id = object_id('mytable') and s1.id = s2.id
> and s1.colid = s2.colid and s1.status = 133141)
>
> "Mic" <micspam@.jadegroup.co.uk> wrote in message
> news:D9C9F97B-2660-47D8-BCC7-43076688F5D6@.microsoft.com...
> > Armand,
> > Thanks. Yes, I know I have to drop the constraint but...
> > This is in a script and I don't know the auto generated name of the
> > constraint, so I can't readily drop it.
> > Is there a way to find out the constraint name for the DEFAULT for that
> > column?
> > That way, I could fid the name then frop the constraint programmatically.
> > BTW, this is against SQL 7.0, so I can't use and INFORMATION_SCHEMA
> > Thanks
> > Michael
> >
> > "Armando Prato" wrote:
> >
> > > You first have to drop the default constraint
> > >
> > > alter table myTable
> > > drop constraint DF__ccAstPublica__rv__27C3E46E
> > >
> > > Then drop the cilumn
> > >
> > > "Mic" <micspam@.jadegroup.co.uk> wrote in message
> > > news:7E45448B-E30D-4D47-9A03-75EE568E6928@.microsoft.com...
> > > > Hi,
> > > > I have a table with a column:
> > > > rv smallint default 1 not null
> > > >
> > > > I want to drop it:
> > > > alter table myTable
> > > > drop column rv
> > > >
> > > > But I get an error:
> > > > Server: Msg 5074, Level 16, State 1, Line 1
> > > > The object 'DF__ccAstPublica__rv__27C3E46E' is dependent on column
> 'rv'.
> > > > Server: Msg 4922, Level 16, State 1, Line 1
> > > > ALTER TABLE DROP COLUMN rv failed because one or more objects access
> this
> > > > column.
> > > >
> > > > The 'object' mentioned above is the default constraint.
> > > > How do I just drop the column (without having to know waht constraints
> it
> > > > has)?
> > > > Thanks
> > > > Michael
> > > >
> > >
> > >
> > >
>
>|||Richard,
I assume this would be helpful interactively, but not if I want to do this
in a script?
"Richard Ding" wrote:
> you can run sp_help tablename or sp_helpconstraint tablename to view the
> default name.
>
> "Mic" <micspam@.jadegroup.co.uk> wrote in message
> news:D9C9F97B-2660-47D8-BCC7-43076688F5D6@.microsoft.com...
> > Armand,
> > Thanks. Yes, I know I have to drop the constraint but...
> > This is in a script and I don't know the auto generated name of the
> > constraint, so I can't readily drop it.
> > Is there a way to find out the constraint name for the DEFAULT for that
> > column?
> > That way, I could fid the name then frop the constraint programmatically.
> > BTW, this is against SQL 7.0, so I can't use and INFORMATION_SCHEMA
> > Thanks
> > Michael
> >
> > "Armando Prato" wrote:
> >
> >> You first have to drop the default constraint
> >>
> >> alter table myTable
> >> drop constraint DF__ccAstPublica__rv__27C3E46E
> >>
> >> Then drop the cilumn
> >>
> >> "Mic" <micspam@.jadegroup.co.uk> wrote in message
> >> news:7E45448B-E30D-4D47-9A03-75EE568E6928@.microsoft.com...
> >> > Hi,
> >> > I have a table with a column:
> >> > rv smallint default 1 not null
> >> >
> >> > I want to drop it:
> >> > alter table myTable
> >> > drop column rv
> >> >
> >> > But I get an error:
> >> > Server: Msg 5074, Level 16, State 1, Line 1
> >> > The object 'DF__ccAstPublica__rv__27C3E46E' is dependent on column
> >> > 'rv'.
> >> > Server: Msg 4922, Level 16, State 1, Line 1
> >> > ALTER TABLE DROP COLUMN rv failed because one or more objects access
> >> > this
> >> > column.
> >> >
> >> > The 'object' mentioned above is the default constraint.
> >> > How do I just drop the column (without having to know waht constraints
> >> > it
> >> > has)?
> >> > Thanks
> >> > Michael
> >> >
> >>
> >>
> >>
>
>|||It means that the particular row represents a DEFAULT constraint. It's a
bitmap, actually.
I have notes that say the status = 5 for defaults but my SQL Server
represents it
as 133141 bitmap
"Mic" <micspam@.jadegroup.co.uk> wrote in message
news:2118DFBB-8625-452C-BAB3-D6CA804E905F@.microsoft.com...
> Armando,
> Thanks. What does status 133141 mean?
> "Armando Prato" wrote:
> > Sure
> >
> > What you can do is query the sysconstraints table in your db
> > and join it to syscolumns
> >
> >
> > select s2.name, object_name(s1.constid)
> > from sysconstraints s1
> > inner join syscolumns s2 on (s1.id = object_id('mytable') and s1.id =s2.id
> > and s1.colid = s2.colid and s1.status = 133141)
> >
> >
> > "Mic" <micspam@.jadegroup.co.uk> wrote in message
> > news:D9C9F97B-2660-47D8-BCC7-43076688F5D6@.microsoft.com...
> > > Armand,
> > > Thanks. Yes, I know I have to drop the constraint but...
> > > This is in a script and I don't know the auto generated name of the
> > > constraint, so I can't readily drop it.
> > > Is there a way to find out the constraint name for the DEFAULT for
that
> > > column?
> > > That way, I could fid the name then frop the constraint
programmatically.
> > > BTW, this is against SQL 7.0, so I can't use and INFORMATION_SCHEMA
> > > Thanks
> > > Michael
> > >
> > > "Armando Prato" wrote:
> > >
> > > > You first have to drop the default constraint
> > > >
> > > > alter table myTable
> > > > drop constraint DF__ccAstPublica__rv__27C3E46E
> > > >
> > > > Then drop the cilumn
> > > >
> > > > "Mic" <micspam@.jadegroup.co.uk> wrote in message
> > > > news:7E45448B-E30D-4D47-9A03-75EE568E6928@.microsoft.com...
> > > > > Hi,
> > > > > I have a table with a column:
> > > > > rv smallint default 1 not null
> > > > >
> > > > > I want to drop it:
> > > > > alter table myTable
> > > > > drop column rv
> > > > >
> > > > > But I get an error:
> > > > > Server: Msg 5074, Level 16, State 1, Line 1
> > > > > The object 'DF__ccAstPublica__rv__27C3E46E' is dependent on column
> > 'rv'.
> > > > > Server: Msg 4922, Level 16, State 1, Line 1
> > > > > ALTER TABLE DROP COLUMN rv failed because one or more objects
access
> > this
> > > > > column.
> > > > >
> > > > > The 'object' mentioned above is the default constraint.
> > > > > How do I just drop the column (without having to know waht
constraints
> > it
> > > > > has)?
> > > > > Thanks
> > > > > Michael
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >|||> This is in a script and I don't know the auto generated name of the
> constraint, so I can't readily drop it.
This is why you should name your constraint in the first place... :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Mic" <micspam@.jadegroup.co.uk> wrote in message
news:D9C9F97B-2660-47D8-BCC7-43076688F5D6@.microsoft.com...
> Armand,
> Thanks. Yes, I know I have to drop the constraint but...
> This is in a script and I don't know the auto generated name of the
> constraint, so I can't readily drop it.
> Is there a way to find out the constraint name for the DEFAULT for that
> column?
> That way, I could fid the name then frop the constraint programmatically.
> BTW, this is against SQL 7.0, so I can't use and INFORMATION_SCHEMA
> Thanks
> Michael
> "Armando Prato" wrote:
>> You first have to drop the default constraint
>> alter table myTable
>> drop constraint DF__ccAstPublica__rv__27C3E46E
>> Then drop the cilumn
>> "Mic" <micspam@.jadegroup.co.uk> wrote in message
>> news:7E45448B-E30D-4D47-9A03-75EE568E6928@.microsoft.com...
>> > Hi,
>> > I have a table with a column:
>> > rv smallint default 1 not null
>> >
>> > I want to drop it:
>> > alter table myTable
>> > drop column rv
>> >
>> > But I get an error:
>> > Server: Msg 5074, Level 16, State 1, Line 1
>> > The object 'DF__ccAstPublica__rv__27C3E46E' is dependent on column 'rv'.
>> > Server: Msg 4922, Level 16, State 1, Line 1
>> > ALTER TABLE DROP COLUMN rv failed because one or more objects access this
>> > column.
>> >
>> > The 'object' mentioned above is the default constraint.
>> > How do I just drop the column (without having to know waht constraints it
>> > has)?
>> > Thanks
>> > Michael
>> >
>>|||Tibor
Thanks for the helpful advice :).
Actually I always have explicitly named PK, FK, CHECK etc. constraints, but
not DEFAULTs. I think this particular aspect of SQL Server's design is not
particularly helpful. To my eye, it's much clearer to define:
myColumn smallint default 1
than
myColumn smallint,
constraint MyTab_DF_MyColumn default 1 for MyColumn
Incidently, NOT NULL is treated differently to DEFAULT: SQL Server will
allow me to drop a column that has an impicit not null, but not one with an
implicit DEFAULT.
Ho hum...
Bet this isn't 'fixed' in 2005...
Regards
Michael
"Tibor Karaszi" wrote:
> > This is in a script and I don't know the auto generated name of the
> > constraint, so I can't readily drop it.
> This is why you should name your constraint in the first place... :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Mic" <micspam@.jadegroup.co.uk> wrote in message
> news:D9C9F97B-2660-47D8-BCC7-43076688F5D6@.microsoft.com...
> > Armand,
> > Thanks. Yes, I know I have to drop the constraint but...
> > This is in a script and I don't know the auto generated name of the
> > constraint, so I can't readily drop it.
> > Is there a way to find out the constraint name for the DEFAULT for that
> > column?
> > That way, I could fid the name then frop the constraint programmatically.
> > BTW, this is against SQL 7.0, so I can't use and INFORMATION_SCHEMA
> > Thanks
> > Michael
> >
> > "Armando Prato" wrote:
> >
> >> You first have to drop the default constraint
> >>
> >> alter table myTable
> >> drop constraint DF__ccAstPublica__rv__27C3E46E
> >>
> >> Then drop the cilumn
> >>
> >> "Mic" <micspam@.jadegroup.co.uk> wrote in message
> >> news:7E45448B-E30D-4D47-9A03-75EE568E6928@.microsoft.com...
> >> > Hi,
> >> > I have a table with a column:
> >> > rv smallint default 1 not null
> >> >
> >> > I want to drop it:
> >> > alter table myTable
> >> > drop column rv
> >> >
> >> > But I get an error:
> >> > Server: Msg 5074, Level 16, State 1, Line 1
> >> > The object 'DF__ccAstPublica__rv__27C3E46E' is dependent on column 'rv'.
> >> > Server: Msg 4922, Level 16, State 1, Line 1
> >> > ALTER TABLE DROP COLUMN rv failed because one or more objects access this
> >> > column.
> >> >
> >> > The 'object' mentioned above is the default constraint.
> >> > How do I just drop the column (without having to know waht constraints it
> >> > has)?
> >> > Thanks
> >> > Michael
> >> >
> >>
> >>
> >>
>
>|||Yes, DEFAULTs are handles like constraints in SQL Server. Where in ANSI SQL, they are just column
attributes. Just one of those things to get used to... :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Mic" <micspam@.jadegroup.co.uk> wrote in message
news:0B2F4A00-65E5-4DF1-B2C6-86CA9C21C4D8@.microsoft.com...
> Tibor
> Thanks for the helpful advice :).
> Actually I always have explicitly named PK, FK, CHECK etc. constraints, but
> not DEFAULTs. I think this particular aspect of SQL Server's design is not
> particularly helpful. To my eye, it's much clearer to define:
> myColumn smallint default 1
> than
> myColumn smallint,
> constraint MyTab_DF_MyColumn default 1 for MyColumn
> Incidently, NOT NULL is treated differently to DEFAULT: SQL Server will
> allow me to drop a column that has an impicit not null, but not one with an
> implicit DEFAULT.
> Ho hum...
> Bet this isn't 'fixed' in 2005...
> Regards
> Michael
> "Tibor Karaszi" wrote:
>> > This is in a script and I don't know the auto generated name of the
>> > constraint, so I can't readily drop it.
>> This is why you should name your constraint in the first place... :-)
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> http://www.sqlug.se/
>>
>> "Mic" <micspam@.jadegroup.co.uk> wrote in message
>> news:D9C9F97B-2660-47D8-BCC7-43076688F5D6@.microsoft.com...
>> > Armand,
>> > Thanks. Yes, I know I have to drop the constraint but...
>> > This is in a script and I don't know the auto generated name of the
>> > constraint, so I can't readily drop it.
>> > Is there a way to find out the constraint name for the DEFAULT for that
>> > column?
>> > That way, I could fid the name then frop the constraint programmatically.
>> > BTW, this is against SQL 7.0, so I can't use and INFORMATION_SCHEMA
>> > Thanks
>> > Michael
>> >
>> > "Armando Prato" wrote:
>> >
>> >> You first have to drop the default constraint
>> >>
>> >> alter table myTable
>> >> drop constraint DF__ccAstPublica__rv__27C3E46E
>> >>
>> >> Then drop the cilumn
>> >>
>> >> "Mic" <micspam@.jadegroup.co.uk> wrote in message
>> >> news:7E45448B-E30D-4D47-9A03-75EE568E6928@.microsoft.com...
>> >> > Hi,
>> >> > I have a table with a column:
>> >> > rv smallint default 1 not null
>> >> >
>> >> > I want to drop it:
>> >> > alter table myTable
>> >> > drop column rv
>> >> >
>> >> > But I get an error:
>> >> > Server: Msg 5074, Level 16, State 1, Line 1
>> >> > The object 'DF__ccAstPublica__rv__27C3E46E' is dependent on column 'rv'.
>> >> > Server: Msg 4922, Level 16, State 1, Line 1
>> >> > ALTER TABLE DROP COLUMN rv failed because one or more objects access this
>> >> > column.
>> >> >
>> >> > The 'object' mentioned above is the default constraint.
>> >> > How do I just drop the column (without having to know waht constraints it
>> >> > has)?
>> >> > Thanks
>> >> > Michael
>> >> >
>> >>
>> >>
>> >>
>>

how to do this select query?

I have a table that looks something like this:

CREATE TABLE Oval_Import
(
IdNum INT NOT NULL PRIMARY KEY,
DOB datetime NOT NULL,
.
.
.
.
.
)

I'm trying to select all the records from the table (notice the DOB date field returns only the date part), but I ran into problems displaying the rest of the fields after the DOB.

I tried a select query like this but it returned every column 'again' after the DOB:

select IdNum, convert(varchar,DOB,111), * from Oval_Import;
I don't want to explicitly select each individual column after that either because there are very many after the DOB.

So how can I select the rest of the fields with the * but excluding the IdNum and the DOB columns?

It's a good practice to explicitly define each column in the select statement. Also, when you do special conversion on a column, you are essentially creating a new column. It's not possible to remove the original column from the select if you don't explicitly do so.|||

Ok, how about when I do an insert.

I can perform a query like this in sql server:

insert into Oval_Import (IdNum, DOB) values (226882, '1982/1/9');

but I can't do the following, it gives me a "Incorrect syntax near the keyword 'set'." error:

insert into Oval_Import set IdNum=226882, DOB='1982/1/9';
I know in mysql you can perform the latter query for insertion, but how can you do something similar in sql server? I need an insertion query where I can explicitly see which columns are being assigned to which values because my table have many columns.

|||

The INSERT statement syntax supported by SQL Server is the same as that of the ANSI SQL standards. You can only use the VALUES clause to specify the column values. There are extensions to the insert statement that allow you to do insert...Select or insert..exec for example. Perhaps you can do:

insert into Oval_Import (IdNum, DOB)
select 226882 as IdNum, '1982/1/9' as DOB

But note that the columns between the select and insert statement list is still by position.

How to do this in a report?

I have to write a report in SQL that takes the following data structure:
CREATE TABLE [dbo].[Sales_Customer_List] (
[cust_no] [char] (10) NOT NULL ,
[cust_name] [char] (35) NULL ,
[distr_channel] [char] (2) NOT NULL ,
[sold_to_sales_grp] [char] (3) NULL ,
[ship_to_sales_grp] [char] (3) NULL ,
[sold_to_sales_rep_cd] [char] (10) NULL ,
[ship_to_sales_rep_cd] [char] (10) NULL ,
[sold_to_sales_rep] [varchar] (30) NULL ,
[ship_to_sales_rep] [varchar] (30) NULL ,
[csr] [char] (35) NOT NULL ,
[csr_email] [char] (60) NOT NULL ,
[credit_mgr] [char] (35) NOT NULL ,
[sales_region] [char] (20) NULL ,
[BusArea01] [decimal](18, 2) NULL ,
[BusArea02] [decimal](18, 2) NULL ,
[BusArea03] [decimal](18, 2) NULL
) ON [PRIMARY]
--GO
and gives me a listing by customer number, customer name,
sold_to_sales_grp, etc thru the Sales region field.
The data itself can have multiple distr_channel values but the other
fields (excluding the busarea01, 02 and 03 fields) will not change
between customers.
The data would be like:
custno = 1234
custname= Customer1
distr_channel = DS
etc etc etc and the BusArea fields would be
BusArea01 = 0
BusArea02 = 137
BusArea03 = 984
A second record would be:
custno = 1234
custname= Customer1
distr_channel = GM
etc etc etc and the BusArea fields would be
BusArea01 = 855
BusArea02 = 0
BusArea03 = 211
A Third record would be:
custno = 6543
Custname = Customer2
distr_channel = CH
etc etc etc and the BusArea fields would be
BusArea01 = 1250
BusArea02 = 0
BusArea03 = 335
A fourth record would be
Custno = 8998
Custname = Customer3
distr_channel = DL
etc etc etc and the BusArea fields would be
BusArea01 = 25000
BusArea02 = 0
BusArea03 = 550
A fifth record would be
Custno = 8998
Custname = Customer3
distr_channel = WA
etc etc etc and the BusArea fields would be
BusArea01 = 0
BusArea02 = 15000
BusArea03 = 0
The line I need to have on my report would be:
C No C Name BA01 BA02 BA03
1234 Customer1 etc etc etc GM 855 DS 137 DS & GM 211 + 984
6543 Customer2 etc etc etc CH 1250 CH 0 CH 335
8998 Customer3 etc etc etc DL 25000 WA 15000 DL 550
Some customers would have 1 Distr_channel, some 4 or 5.
How would you do this in SQL?
Thanks,
SCBetter if you do this in the client app / reporting tool and not in sql serv
er.
HOW TO: Rotate a Table in SQL Server
http://support.microsoft.com/defaul...574&Product=sql
Dynamic Crosstab Queries
http://www.windowsitpro.com/SQLServ...5608/15608.html
Dynamic Cross-Tabs/Pivot Tables
http://www.sqlteam.com/item.asp?ItemID=2955
AMB
"Blasting Cap" wrote:

> I have to write a report in SQL that takes the following data structure:
> CREATE TABLE [dbo].[Sales_Customer_List] (
> [cust_no] [char] (10) NOT NULL ,
> [cust_name] [char] (35) NULL ,
> [distr_channel] [char] (2) NOT NULL ,
> [sold_to_sales_grp] [char] (3) NULL ,
> [ship_to_sales_grp] [char] (3) NULL ,
> [sold_to_sales_rep_cd] [char] (10) NULL ,
> [ship_to_sales_rep_cd] [char] (10) NULL ,
> [sold_to_sales_rep] [varchar] (30) NULL ,
> [ship_to_sales_rep] [varchar] (30) NULL ,
> [csr] [char] (35) NOT NULL ,
> [csr_email] [char] (60) NOT NULL ,
> [credit_mgr] [char] (35) NOT NULL ,
> [sales_region] [char] (20) NULL ,
> [BusArea01] [decimal](18, 2) NULL ,
> [BusArea02] [decimal](18, 2) NULL ,
> [BusArea03] [decimal](18, 2) NULL
> ) ON [PRIMARY]
> --GO
> and gives me a listing by customer number, customer name,
> sold_to_sales_grp, etc thru the Sales region field.
> The data itself can have multiple distr_channel values but the other
> fields (excluding the busarea01, 02 and 03 fields) will not change
> between customers.
> The data would be like:
> custno = 1234
> custname= Customer1
> distr_channel = DS
> etc etc etc and the BusArea fields would be
> BusArea01 = 0
> BusArea02 = 137
> BusArea03 = 984
> A second record would be:
> custno = 1234
> custname= Customer1
> distr_channel = GM
> etc etc etc and the BusArea fields would be
> BusArea01 = 855
> BusArea02 = 0
> BusArea03 = 211
> A Third record would be:
> custno = 6543
> Custname = Customer2
> distr_channel = CH
> etc etc etc and the BusArea fields would be
> BusArea01 = 1250
> BusArea02 = 0
> BusArea03 = 335
> A fourth record would be
> Custno = 8998
> Custname = Customer3
> distr_channel = DL
> etc etc etc and the BusArea fields would be
> BusArea01 = 25000
> BusArea02 = 0
> BusArea03 = 550
> A fifth record would be
> Custno = 8998
> Custname = Customer3
> distr_channel = WA
> etc etc etc and the BusArea fields would be
> BusArea01 = 0
> BusArea02 = 15000
> BusArea03 = 0
>
> The line I need to have on my report would be:
> C No C Name BA01 BA02 BA03
> 1234 Customer1 etc etc etc GM 855 DS 137 DS & GM 211 + 984
> 6543 Customer2 etc etc etc CH 1250 CH 0 CH 335
> 8998 Customer3 etc etc etc DL 25000 WA 15000 DL 550
> Some customers would have 1 Distr_channel, some 4 or 5.
> How would you do this in SQL?
>
> Thanks,
> SC
>