Query Woes!

Query Help - Please
I have a table:
CREATE TABLE [dbo].[tblStudentOffers] (
[stud_no] [varchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[crse_offer_code] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[subj_offer_code] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[adms_offer_code] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[adms_elmnt_code] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[crse_code] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[subj_code] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[start_study_date] [datetime] NULL ,
[comp_study_date] [datetime] NULL
) ON [PRIMARY]
GO
With Data:
stud_no,crse_offer_code,subj_offer_code,
adms_offer_code,adms_elmnt_code,crse
_code,subj_code,start_study_date,comp_st
udy_date
1,10,40,40,ELEMENT1,COURSE1,ELEMENT1,200
4-10-01,2004-12-31
1,10,41,41,ELEMENT2,COURSE1,ELEMENT2,200
4-10-01,2004-12-31
1,10,42,42,ELEMENT3,COURSE1,ELEMENT3,200
5-02-01,2005-03-31
1,30,60,60,ELEMENT10,COURSE3,ELEMENT10,2
005-03-01,2005-05-25
1,30,61,61,ELEMENT11,COURSE3,ELEMENT11,2
005-03-01,2005-05-25
1,35,62,62,ELEMENT12,COURSE3,ELEMENT12,2
005-03-01,2005-05-25
2,40,60,60,ELEMENT10,COURSE3,ELEMENT10,2
005-03-01,2005-05-25
2,40,61,61,ELEMENT11,COURSE3,ELEMENT11,2
005-03-01,2005-05-25
2,40,62,62,ELEMENT12,COURSE3,ELEMENT12,2
005-03-01,2005-05-25
3,35,62,62,ELEMENT12,COURSE3,ELEMENT12,2
005-02-01,2005-03-31
I need to provide a list of new course enrolments for the month of March? If
providing new student date range of 2005-03-01 to 2005-03-31 the query would
need to return the following:
stud_no,crse_offer_code,crse_code,start_
study_date
1,30,COURSE3,2005-03-01
2,40,COURSE3,2005-03-01
Have the following query which eliminates student 1 and only return student
2. Am now really stumped how to achieve this. HEEEEEEELPP
select distinct stud_no,crse_offer_code,crse_code,start_
study_date
from tblStudentOffers
where start_study_date BETWEEN CONVERT(DATETIME, '2005-03-01', 102) AND
CONVERT(DATETIME, '2005-03-31', 102)
AND stud_no not in
(SELECT stud_no FROM tblStudentOffers
WHERE start_study_date < CONVERT(DATETIME, '2005-03-01', 102))
ORDER BY crse_code, stud_family_name
Thanks
RosscoMy best guess is this:
select stud_no, crse_offer_code, crse_code, start_study_date
from tblStudentOffers as SO1
where not exists (
select * from tblStudentOffers as SO2
where SO2.stud_no = SO1.stud_no
and SO2.start_study_date <= SO1.start_study_date
and SO2.crse_offer_code = SO1.crse_offer_code
and SO2.adms_elmnt_code < SO1.adms_elmnt_code
)
where start_study_date >= '20050301'
and start_study_date <= '20050401'
The parts of the criteria that get the specific single row you want
for a particular student probably have to be adjusted.
Steve Kass
Drew University
Rossco wrote:

>Query Help - Please
>I have a table:
>CREATE TABLE [dbo].[tblStudentOffers] (
> [stud_no] [varchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [crse_offer_code] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
>NULL ,
> [subj_offer_code] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
>NULL ,
> [adms_offer_code] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [adms_elmnt_code] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [crse_code] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [subj_code] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [start_study_date] [datetime] NULL ,
> [comp_study_date] [datetime] NULL
> ) ON [PRIMARY]
>GO
>
>With Data:
> stud_no,crse_offer_code,subj_offer_code,
adms_offer_code,adms_elmnt_code,crs
e_code,subj_code,start_study_date,comp_s
tudy_date
> 1,10,40,40,ELEMENT1,COURSE1,ELEMENT1,200
4-10-01,2004-12-31
> 1,10,41,41,ELEMENT2,COURSE1,ELEMENT2,200
4-10-01,2004-12-31
> 1,10,42,42,ELEMENT3,COURSE1,ELEMENT3,200
5-02-01,2005-03-31
> 1,30,60,60,ELEMENT10,COURSE3,ELEMENT10,2
005-03-01,2005-05-25
> 1,30,61,61,ELEMENT11,COURSE3,ELEMENT11,2
005-03-01,2005-05-25
> 1,35,62,62,ELEMENT12,COURSE3,ELEMENT12,2
005-03-01,2005-05-25
> 2,40,60,60,ELEMENT10,COURSE3,ELEMENT10,2
005-03-01,2005-05-25
> 2,40,61,61,ELEMENT11,COURSE3,ELEMENT11,2
005-03-01,2005-05-25
> 2,40,62,62,ELEMENT12,COURSE3,ELEMENT12,2
005-03-01,2005-05-25
> 3,35,62,62,ELEMENT12,COURSE3,ELEMENT12,2
005-02-01,2005-03-31
>I need to provide a list of new course enrolments for the month of March? I
f
>providing new student date range of 2005-03-01 to 2005-03-31 the query woul
d
>need to return the following:
> stud_no,crse_offer_code,crse_code,start_
study_date
>1,30,COURSE3,2005-03-01
>2,40,COURSE3,2005-03-01
>Have the following query which eliminates student 1 and only return student
>2. Am now really stumped how to achieve this. HEEEEEEELPP
>select distinct stud_no,crse_offer_code,crse_code,start_
study_date
>from tblStudentOffers
>where start_study_date BETWEEN CONVERT(DATETIME, '2005-03-01', 102) AND
>CONVERT(DATETIME, '2005-03-31', 102)
>AND stud_no not in
>(SELECT stud_no FROM tblStudentOffers
>WHERE start_study_date < CONVERT(DATETIME, '2005-03-01', 102))
>ORDER BY crse_code, stud_family_name
>Thanks
>Rossco
>|||Your blood is worth bottling! Thank you it works perfectly!
Cheers
"Steve Kass" wrote:

> My best guess is this:
> select stud_no, crse_offer_code, crse_code, start_study_date
> from tblStudentOffers as SO1
> where not exists (
> select * from tblStudentOffers as SO2
> where SO2.stud_no = SO1.stud_no
> and SO2.start_study_date <= SO1.start_study_date
> and SO2.crse_offer_code = SO1.crse_offer_code
> and SO2.adms_elmnt_code < SO1.adms_elmnt_code
> )
> where start_study_date >= '20050301'
> and start_study_date <= '20050401'
> The parts of the criteria that get the specific single row you want
> for a particular student probably have to be adjusted.
> Steve Kass
> Drew University
> Rossco wrote:
>
>|||Rossco wrote:
> Your blood is worth bottling! Thank you it works perfectly!
>
I think Steve is realy smart. Maybe even groovy. But I don't want his
blood. Angelina and Billy Bob ruined the whole bottled blood thing for
me.
David Gugick
Imceda Software
www.imceda.com|||Black pudding is quite nice.
http://www.g4cio.demon.co.uk/bpudding/pudding.htm
"David Gugick" wrote:

> Rossco wrote:
> I think Steve is realy smart. Maybe even groovy. But I don't want his
> blood. Angelina and Billy Bob ruined the whole bottled blood thing for
> me.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||In my neck of the woods it's called "boudin noir".
Although I find this sounds better than "blood pouding",
I still wouldn't touch it with... well, with anything.
"Peter 'Not Peter The Spate' Nolan"
<PeterNotPeterTheSpateNolan@.discussions.microsoft.com> wrote in message
news:2CC7355D-0E84-46A5-854E-6A5D34499C9B@.microsoft.com...
> Black pudding is quite nice.
> http://www.g4cio.demon.co.uk/bpudding/pudding.htm
>
> "David Gugick" wrote:
>sql

Query with CASE and NULL values

Hi, please, take a look to this query:

declare @.IDCliente int
declare @.Cliente varchar(50)
declare @.IDUsuario int
declare @.IDUsuarioAlta int

set @.IDcliente = 0
set @.Cliente = ''
set @.IDUsuario = 0
set @.IDUsuarioAlta = 0

select * from cliente
where
(IDUsuario = CASE @.IDUsuario WHEN 0 THEN IDUsuario ELSE @.IDUsuario END or idusuario is null)
AND (IDUsuarioAlta = CASE @.IDUsuarioAlta WHEN 0 THEN IDUsuarioAlta ELSE @.IDUsuarioAlta END or idusuarioalta is null)
AND idCliente = CASE @.idCliente WHEN 0 THEN idCliente ELSE @.idCliente END
AND Cliente LIKE '%' + CASE @.Cliente WHEN '' THEN Cliente ELSE @.Cliente END + '%'

Cliente
IDCliente Cliente IDUsusario IDUsuarioAlta
1 Esteban 1 2
2 Jose 3 1
3 Mario 2 NULL
4 Pedro NULL 2
5 NULL 1 2

Its work fine, except for the NULL values. What can I do to fix it ?

thanks

Here you go:

Code Snippet

select IDCliente, Cliente,

case when IDUsusario is null then '' --or whatever you want in place of null

else IDUsusario

end as IDUsusario,

case when IDUsuarioAlta is null then '' -- same thing here

else IDUsuarioAlta

end as IDUsuarioAlta

from cliente

where

(IDUsuario = CASE @.IDUsuario WHEN 0 THEN IDUsuario ELSE @.IDUsuario END or idusuario is null)

AND (IDUsuarioAlta = CASE @.IDUsuarioAlta WHEN 0 THEN IDUsuarioAlta ELSE @.IDUsuarioAlta END or idusuarioalta is null)

AND idCliente = CASE @.idCliente WHEN 0 THEN idCliente ELSE @.idCliente END

AND Cliente LIKE '%' + CASE @.Cliente WHEN '' THEN Cliente ELSE @.Cliente END + '%'

|||

I'm sorry, I think I didn't explain my self.

The problem is in the WHERE part, No in the SELECT.

When @.IDUsuario has a value, then the query return the record with the NULL value, and that is not correct. If I take off or idusuario is null

then the record with de NULL value is never return.

thanks and sorry my english !.

|||

AH, gotcha.

How about this then:

Code Snippet

select *

from cliente

where

(IDUsuario = CASE @.IDUsuario WHEN 0 THEN IDUsuario

ELSE @.IDUsuario

END

or (idusuario is null and @.IDUsuario = 0) )

AND (IDUsuarioAlta = CASE @.IDUsuarioAlta WHEN 0 THEN IDUsuarioAlta

ELSE @.IDUsuarioAlta

END

or (idusuarioalta is null and @.IDUsuarioAlta = 0) )

AND (idCliente = CASE @.idCliente WHEN 0 THEN idCliente ELSE @.idCliente END

or (idCliente is null and @.idCliente = 0) )

AND Cliente LIKE '%' + CASE @.Cliente WHEN '' THEN Cliente ELSE @.Cliente END + '%'

|||

This might be a little cleaner:

Code Snippet

select *

from cliente

where

((@.IDUsuario <> 0 and idusuario = @.IDUsuario ) or

(@.IDUsuario = 0))

AND ((@.IDUsuarioAlta <> 0 and IDUsuarioAlta = @.IDUsuarioAlta ) or

(@.IDUsuarioAlta = 0))

AND ((@.idCliente <> 0 and idCliente = @.idCliente ) or

(@.idCliente = 0))

AND Cliente LIKE '%' + CASE @.Cliente WHEN '' THEN Cliente ELSE @.Cliente END + '%'

|||*** !, it work perfect, i think you know this !! like we say in Argentina: "Sos Groso !!"..... is like say You are Big !... I think so..sql

Query with "not null"?

Is there a way to do a query and include a field if it is *not* null?
For instance, I know I can:
Select LastName + isnull(FirstName, '') from tblClients
I want to include a field only if it isn't null, for instance, if a client
is inactive, I want to display "(inactive)" in the results:
Smith, Jane (inactive)
Smith, John
Smith, Joe
Smith, Carol (inactive)
My fields are LastName, FirstName, Inactive (bit)Hi dew
I'm not sure what the connection with NULL is - is Inactive nullable,
so that you want to show (inactive) when Inactive is NULL or 0?
To do this, you can use the CASE statement:
SELECT LastName + isnull(FirstName, '') + CASE WHEN Inactive IS NULL
THEN '(inactive)' ELSE CASE WHEN Inactive=0 THEN ('inactive') ELSE ''
END END
(two nested CASE statements - would only need one if Inactive can only
have values 0 or 1 - i.e. is not NULLable).
hope this helps
Seb|||I'm not sure what you want to do.
But, I can tell you that you results will always contain the same number of
columns for all rows. So, you can't return a different number of columns fo
r
different criteria.
You could definitely build a dynamic string based on your query.
Like
SELECT LastName + ', ' + FirstName + CASE WHEN Inactive =1 THEN '
(inactive)' ELSE '' END FROM YourTable
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"dew" wrote:

> Is there a way to do a query and include a field if it is *not* null?
> For instance, I know I can:
> Select LastName + isnull(FirstName, '') from tblClients
> I want to include a field only if it isn't null, for instance, if a client
> is inactive, I want to display "(inactive)" in the results:
> Smith, Jane (inactive)
> Smith, John
> Smith, Joe
> Smith, Carol (inactive)
> My fields are LastName, FirstName, Inactive (bit)
>
>|||The output of the query must be in table-format; all rows returned must have
the same number of columns. You can get something similar in appearance to
your desired output with something like this
SELECT LastName + ', ' + FirstName AS "Name", "Active"=
CASE
WHEN Inactive = 1 THEN '(inactive)'
ELSE ''
END
FROM [Your Table]
-
"dew" wrote:

> Is there a way to do a query and include a field if it is *not* null?
> For instance, I know I can:
> Select LastName + isnull(FirstName, '') from tblClients
> I want to include a field only if it isn't null, for instance, if a client
> is inactive, I want to display "(inactive)" in the results:
> Smith, Jane (inactive)
> Smith, John
> Smith, Joe
> Smith, Carol (inactive)
> My fields are LastName, FirstName, Inactive (bit)
>
>|||Not sure im understanding you properly but isn't this all you need...
Select LastName + isnull(FirstName, '') from tblClients where Inactive is NU
LL
Select LastName + isnull(FirstName, '') from tblClients where Inactive is
NOT NULL
"dew" wrote:

> Is there a way to do a query and include a field if it is *not* null?
> For instance, I know I can:
> Select LastName + isnull(FirstName, '') from tblClients
> I want to include a field only if it isn't null, for instance, if a client
> is inactive, I want to display "(inactive)" in the results:
> Smith, Jane (inactive)
> Smith, John
> Smith, Joe
> Smith, Carol (inactive)
> My fields are LastName, FirstName, Inactive (bit)
>
>|||Thanks so much, the select with Case works great, that is just what I
needed. Currently the Inactive column can be null but I can change that to
always be 0 or 1 so either one works. Thanks!
"dew" <dew@.yahoo.com> wrote in message
news:%23yUecnhEGHA.2072@.TK2MSFTNGP10.phx.gbl...
> Is there a way to do a query and include a field if it is *not* null?
> For instance, I know I can:
> Select LastName + isnull(FirstName, '') from tblClients
> I want to include a field only if it isn't null, for instance, if a client
> is inactive, I want to display "(inactive)" in the results:
> Smith, Jane (inactive)
> Smith, John
> Smith, Joe
> Smith, Carol (inactive)
> My fields are LastName, FirstName, Inactive (bit)
>

Query using two column names in a table (to find rows near each other)

I have a table called Seats in my database...
CREATE TABLE [dbo].[Seats] (
[SeatSerialNo] [int] IDENTITY (1, 1) NOT NULL ,
[VehicleSerialNo] [int] NOT NULL ,
[RowNo] [smallint] NOT NULL ,
[ColumnNo] [smallint] NOT NULL ,
[SeatNo] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
This table defines the seats that are available for a given VehicleSerialNo
(the typical scenario would be a motor coach or possibly an aircraft). A
seat has a RowNo (how far down the vehicle it is), a ColumnNo (it's position
left to right on the vehicle) and a SeatNo (the actual seat number the
customer is given - e.g. please sit in seat number 40 - these can be numeric
or like aircraft seats numbers 23D etc).
I have another table called Passengers which stores the VehicleSerialNo (the
actual motor coach) and the SeatSerialNo (the seat they are sitting in on
that motor coach). Let's now assume this table contains lots of entries
already specifying where the existing passengers will be sitting.
Example: I now want to add 6 people on this vehicle and automatically
allocate each person a seat. I am trying to figure out whether this
automatic seat selection can be achieved in SQL Server or whether this
should be done on the client side using VB. Ideally you would always like
to make sure all 6 people are sitting on the same part of the motor coach
(unless it is getting full). Would it be possible to construct a query that
would select seats that are near each other based on RowNo and ColumnNo
(excluding SeatSerialNo's that exists in the Passengers table - e.g. no
double booking of a seat)? So I want to return a query that returns the 6
seats the system thinks are best. Can such a query be performed comparing
these column names (RowNo and ColumnNo) finding seats that are near to each
other.
Many thanks,
ChrisC-W
Its hard to suggest without seeing sample data+ relationship+ expected
result.
Why you don't have a primary on the table?
"C-W" <nomailplease@.microsoft.nospam> wrote in message
news:O0cKXJanFHA.3312@.tk2msftngp13.phx.gbl...
>I have a table called Seats in my database...
>
> CREATE TABLE [dbo].[Seats] (
> [SeatSerialNo] [int] IDENTITY (1, 1) NOT NULL ,
> [VehicleSerialNo] [int] NOT NULL ,
> [RowNo] [smallint] NOT NULL ,
> [ColumnNo] [smallint] NOT NULL ,
> [SeatNo] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
>
> This table defines the seats that are available for a given
> VehicleSerialNo (the typical scenario would be a motor coach or possibly
> an aircraft). A seat has a RowNo (how far down the vehicle it is), a
> ColumnNo (it's position left to right on the vehicle) and a SeatNo (the
> actual seat number the customer is given - e.g. please sit in seat number
> 40 - these can be numeric or like aircraft seats numbers 23D etc).
>
> I have another table called Passengers which stores the VehicleSerialNo
> (the actual motor coach) and the SeatSerialNo (the seat they are sitting
> in on that motor coach). Let's now assume this table contains lots of
> entries already specifying where the existing passengers will be sitting.
>
> Example: I now want to add 6 people on this vehicle and automatically
> allocate each person a seat. I am trying to figure out whether this
> automatic seat selection can be achieved in SQL Server or whether this
> should be done on the client side using VB. Ideally you would always like
> to make sure all 6 people are sitting on the same part of the motor coach
> (unless it is getting full). Would it be possible to construct a query
> that would select seats that are near each other based on RowNo and
> ColumnNo (excluding SeatSerialNo's that exists in the Passengers table -
> e.g. no double booking of a seat)? So I want to return a query that
> returns the 6 seats the system thinks are best. Can such a query be
> performed comparing these column names (RowNo and ColumnNo) finding seats
> that are near to each other.
>
> Many thanks,
> Chris
>
>|||Sorry, that's just the way I scripted the table. SeatSerialNo is the
primary key.
I will try and work on some sample data.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uwOTGPanFHA.320@.TK2MSFTNGP09.phx.gbl...
> C-W
> Its hard to suggest without seeing sample data+ relationship+ expected
> result.
> Why you don't have a primary on the table?
>|||On Wed, 10 Aug 2005 12:54:59 +0100, C-W wrote:

>I have a table called Seats in my database...
>
>CREATE TABLE [dbo].[Seats] (
> [SeatSerialNo] [int] IDENTITY (1, 1) NOT NULL ,
> [VehicleSerialNo] [int] NOT NULL ,
> [RowNo] [smallint] NOT NULL ,
> [ColumnNo] [smallint] NOT NULL ,
> [SeatNo] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
>GO
Hi Chris,
You have two good candidate keys in the table without the extra identity
column: (VehicleSerialNo, SeatNo) and (VehicleSerialNo, RowNo,
ColumnNo). My advice would be to drop the SeatSerialNo column, declare
one of the composite candidate keys to be the PRIMARY KEY (probably the
one with SeatNo, but it depends on a lot of factors I don't know) and
define a UNIQUE constraint for the other one.

>I have another table called Passengers which stores the VehicleSerialNo (th
e
>actual motor coach) and the SeatSerialNo (the seat they are sitting in on
>that motor coach).
That's redundant. What if a passenger has VehicleSerialNo 1 and
SeatSerialN0 17, but the row in Seats for SeatSerialNo says it's in
VehicleSerialNo 2?
If you keep SeatSerialNo in Seats and use it to refer to a seat in the
Passengers table, then remove VehicleSerialNo from the Passengers table
(the seat will always be in the same vehicle, regardless of who is
sitting on it). Or, if you drop SeatSerialNo from Seats, store the
combination of VehicleSerialNo and SeatNo in the Passengers table.
(snip)
>So I want to return a query that returns the 6
>seats the system thinks are best. Can such a query be performed comparing
>these column names (RowNo and ColumnNo) finding seats that are near to each
>other.
As Uri said. A repro script that others can run to recreate your test
data and the expected output would make it much easier to help you.
See www.aspfaq.com/5006 for more details and hints.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo,
Once i've generate the script to reproduce this I will explain the table
structure further (and hopefully will all make sense). I tried to reproduce
a simple example before but probably caused more confusion. The Seats table
does not actually contain the VehicleSerialNo. Hopefully all will make
sense when I post my script.
Thanks,
Chris
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:bg3kf15cvqe3r4k8hfo1jnr7uhpf52o3el@.
4ax.com...
> On Wed, 10 Aug 2005 12:54:59 +0100, C-W wrote:
>
> Hi Chris,
> You have two good candidate keys in the table without the extra identity
> column: (VehicleSerialNo, SeatNo) and (VehicleSerialNo, RowNo,
> ColumnNo). My advice would be to drop the SeatSerialNo column, declare
> one of the composite candidate keys to be the PRIMARY KEY (probably the
> one with SeatNo, but it depends on a lot of factors I don't know) and
> define a UNIQUE constraint for the other one.
>
> That's redundant. What if a passenger has VehicleSerialNo 1 and
> SeatSerialN0 17, but the row in Seats for SeatSerialNo says it's in
> VehicleSerialNo 2?
> If you keep SeatSerialNo in Seats and use it to refer to a seat in the
> Passengers table, then remove VehicleSerialNo from the Passengers table
> (the seat will always be in the same vehicle, regardless of who is
> sitting on it). Or, if you drop SeatSerialNo from Seats, store the
> combination of VehicleSerialNo and SeatNo in the Passengers table.
>
> (snip)
> As Uri said. A repro script that others can run to recreate your test
> data and the expected output would make it much easier to help you.
> See www.aspfaq.com/5006 for more details and hints.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Query to sequentially number Null fields in a column

I'm trying to write a Query that will Update all the Null fields in Table1
column1 to 'P' and a 6 digit sequential number starting from 000001
including the leading zeros. Can someone help me figure out the correct
syntax? So far, nothing I've come up with is working right.
TIA
MattWell, if you don't want to add an IDENTITY column, and just want to add the
zero-padded char, you could:
1.) Create temp table with IDENTITY column and primary key from source table
1.) Generate identity values for all rows in target table in the temp table
2.) Update target table to include a zero-padded version of the identity
value
Example:
Let's say your table is called Customer and the primary key is CustomerKey
varchar(10)
BEGIN TRANSACTION
CREATE TABLE
#KeyGen
(
CustomerKey varchar(10) NOT NULL,
NewID int NOT NULL IDENTITY (1,1)
)
INSERT INTO KeyGen (CustomerKey) SELECT CustomerKey FROM Customer
WITH(TABLOCKX)
ALTER TABLE Customer ADD NewKey char(10) NOT NULL DEFAULT('')
UPDATE Customer SET NewKey = (SELECT RIGHT('000000' + CAST(NewID AS
varchar(6)), 6) FROM #KeyGen WHERE KeyGen.CustomerKey =
Customer.CustomerKey)
DROP TABLE #KeyGen
COMMIT TRANSACTION
Error handling is an exercise for the reader.
Cheers,
James Hokes
"Matt Williamson" <ih8spam@.spamsux.org> wrote in message
news:%23yx8L0oeGHA.4304@.TK2MSFTNGP05.phx.gbl...
> I'm trying to write a Query that will Update all the Null fields in Table1
> column1 to 'P' and a 6 digit sequential number starting from 000001
> including the leading zeros. Can someone help me figure out the correct
> syntax? So far, nothing I've come up with is working right.
> TIA
> Matt
>|||The problem is the source table doesn't have a primary key. That's what I'm
creating with this query.
I've been working with this code that I found in the archive, but I can't
get it to work
update temp_Reports tr1
set identifier_id = (select count(*) from temp_Reports tr2
where tr2.identifier_id <= tr1.identifier_id) + (select MAX(identifier_id)
FROM temp_Reports)
Where identifier_id is Null
I created this table as a temporary test
CREATE TABLE [temp_Reports] (
[identifier_id] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[somedata] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
And added these values:
1 | Test1
2 | Test2
3 | Test3
Null | Test4
Null | Test5
I get
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'tr1'.
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near '+'.
but I'm not clear why.
Matt
"James Hokes" <noway@.nospamthanksanyway.com> wrote in message
news:eMW1O9oeGHA.3364@.TK2MSFTNGP05.phx.gbl...
> Well, if you don't want to add an IDENTITY column, and just want to add
> the zero-padded char, you could:
> 1.) Create temp table with IDENTITY column and primary key from source
> table
> 1.) Generate identity values for all rows in target table in the temp
> table
> 2.) Update target table to include a zero-padded version of the identity
> value
> Example:
> Let's say your table is called Customer and the primary key is CustomerKey
> varchar(10)
> BEGIN TRANSACTION
> CREATE TABLE
> #KeyGen
> (
> CustomerKey varchar(10) NOT NULL,
> NewID int NOT NULL IDENTITY (1,1)
> )
> INSERT INTO KeyGen (CustomerKey) SELECT CustomerKey FROM Customer
> WITH(TABLOCKX)
> ALTER TABLE Customer ADD NewKey char(10) NOT NULL DEFAULT('')
> UPDATE Customer SET NewKey = (SELECT RIGHT('000000' + CAST(NewID AS
> varchar(6)), 6) FROM #KeyGen WHERE KeyGen.CustomerKey =
> Customer.CustomerKey)
> DROP TABLE #KeyGen
> COMMIT TRANSACTION
>
> Error handling is an exercise for the reader.
> Cheers,
> James Hokes
> "Matt Williamson" <ih8spam@.spamsux.org> wrote in message
> news:%23yx8L0oeGHA.4304@.TK2MSFTNGP05.phx.gbl...
>|||>> The problem is the source table doesn't have a primary key. That's what
Make sure, in the future, to declare a primary key at the time of table
definition itself. Also, unless you have at least one set of columns that
are unique in the table, you have no way out.
The error is due to the alias used in the UPDATE clause. Moreover the logic
does not take into account the rows are already NULL. Assuming the second
column is unique within the table here is a workaround:
UPDATE tbl
SET col1 = ( SELECT COUNT( * )
FROM tbl t
WHERE t.col2 <= tbl.col2
AND t.col1 IS NULL )
+ ( SELECT MAX( col1 )
FROM tbl )
WHERE col1 IS NULL ;
Anith|||Matt,
1 -

> update temp_Reports tr1
Can not use alias in this way. Try:
update temp_Reports
set identifier_id = (select count(*) from temp_Reports tr2
where tr2.identifier_id <= temp_Reports.identifier_id) + (select
MAX(identifier_id)
FROM temp_Reports)
Where identifier_id is Null
go
2 -
The code will not give the result you are expecting, because the update runs
in a transaction, so the rows updated will not be seen by the "select"
statement that is doing the counting.
AMB
"Matt Williamson" wrote:

> The problem is the source table doesn't have a primary key. That's what I'
m
> creating with this query.
> I've been working with this code that I found in the archive, but I can't
> get it to work
> update temp_Reports tr1
> set identifier_id = (select count(*) from temp_Reports tr2
> where tr2.identifier_id <= tr1.identifier_id) + (select MAX(identifier_id)
> FROM temp_Reports)
> Where identifier_id is Null
> I created this table as a temporary test
> CREATE TABLE [temp_Reports] (
> [identifier_id] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [somedata] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> And added these values:
> 1 | Test1
> 2 | Test2
> 3 | Test3
> Null | Test4
> Null | Test5
> I get
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 'tr1'.
> Server: Msg 170, Level 15, State 1, Line 3
> Line 3: Incorrect syntax near '+'.
> but I'm not clear why.
> Matt
> "James Hokes" <noway@.nospamthanksanyway.com> wrote in message
> news:eMW1O9oeGHA.3364@.TK2MSFTNGP05.phx.gbl...
>
>

query to return only non null fields

Is it possible to write a query that returns only non null fields from a specified record? I have a big table with a record for each customer. the record contains a field for each item that can be purchased (only 6 items). I need to write an invoice but not every customer buys every product. I get the feeling Im going about this all wrong. Any help would be great

ThanksOriginally posted by nicky w
Is it possible to write a query that returns only non null fields from a specified record? I have a big table with a record for each customer. the record contains a field for each item that can be purchased (only 6 items). I need to write an invoice but not every customer buys every product. I get the feeling Im going about this all wrong. Any help would be great

Thanks
It would have been better to have the up to 6 items as up to 6 records in a separate table. No SQL query can return a variable number of columns, you would have to write some procedural code to run the query and then present the NOT NULL data.|||are you still in a designing stage? then you should change the design.
What if more items will be offered?

referential integrity will prevent "lost childs"

otherwise andrew is right. write some procedural code

query to retrieve the columns that are null in a table

Hi,
I need help to build a query that shows me how many columns inside a range on columns are null.
Example: quantity1;quantity2;quantity3;quantity4;quantity5; quantity6;quantity7;
Which columns are null?
Thanks in advanceHi Teixeira,
I'm not sure what you are asking. If you could supply a table creation script some test data, and what the "result" should be based on the test data, that would help enormously.

Thanks,
Cat|||USE [myDB]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[books](
[book_id] [int] IDENTITY(1,1) NOT NULL,
[book_description] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
[quantity1] [decimal](18, 2) NOT NULL,
[quantity2] [decimal](18, 2) NULL,
[quantity3] [decimal](18, 2) NULL,
[quantity4] [decimal](18, 2) NULL,
[quantity5] [decimal](18, 2) NULL,
[quantity6] [decimal](18, 2) NULL,
[quantity7] [decimal](18, 2) NULL,
[quantity8] [decimal](18, 2) NULL,
[quantity9] [decimal](18, 2) NULL,
[quantity10] [decimal](18, 2) NULL

this is my struture adapted.
based on this, i want to know which columns are not NULL, for my qyery result do not display for example 10 Quantity columns when i have just 3 that have quantities.|||Do you expect your query to return a single rowset, or is it possible to return multiple rows?|||yes!
It can return several rows.
but its not necessary to return columns that has null or empty values, because it would generated a lot of unnecessary columns in my datagrid display object|||I would change your structure from this:

CREATE TABLE [dbo].[books](
[book_id] [int] IDENTITY(1,1) NOT NULL,
[book_description] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
[quantity1] [decimal](18, 2) NOT NULL,
[quantity2] [decimal](18, 2) NULL,
[quantity3] [decimal](18, 2) NULL,
[quantity4] [decimal](18, 2) NULL,
[quantity5] [decimal](18, 2) NULL,
[quantity6] [decimal](18, 2) NULL,
[quantity7] [decimal](18, 2) NULL,
[quantity8] [decimal](18, 2) NULL,
[quantity9] [decimal](18, 2) NULL,
[quantity10] [decimal](18, 2) NULL)

to this:

CREATE TABLE [dbo].[books](
[book_id] [int] IDENTITY(1,1) NOT NULL,
[book_description] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL)
GO
CREATE TABLE [dbo].[bookquantity](
[book_id] [int] IDENTITY(1,1) NOT NULL,
[quantity] [decimal](18, 2) NOT NULL)
GO
ALTER TABLE [dbo].[bookquantity]
ADD CONSTRAINT FK_book (book_id) REFERENCE [books] (book_id)
GO

This way you are not tied to only 10 quantities and you can don't need to even store the NULL values.

If you can't change the structure of the table, I would suggest either creating a temp table with the above structure and populating it with the data from the master table so that you can weed out the nulls, or creating a single delimited string which the application can parse through. SQL can't really handle returning a result set with a variable number of fields.

The first suggestion would yield a result set like:
book_id quantity
------
1 12.70
1 33.45
1 9.00

The second suggestion would yield a result set like:
book_id quantity_list
--------
1 12.70|33.45|9.00

Hope this helps.
Cat|||I think you're both ideas are a good solution.
As i've some data already in the tables, normalize it more as you suggested would'd take me more time, but the second idea solves the problem perfectly.

Thanks for the help.

Teixeira

Query to get the names of items with different levels

hi

I've a table with coln names

ID
Name
ParentID
Level

I've list with different levels

say

ex.

the Data is:-

ID Name ParentID Level
1 Root null 1
2 Trunk 1 2
3 Branch 2 3
4 Leaf 3 4
5 Stem 3 4

Now I want to show this data as

Root -> Trunk -> Branch -> Leaf

How to write the query for getting the Names for different levels for corresponding ParentID...select case when level is 1 then Name end as Root,
case when level is 2 then Name end as Trunk,
case when level is 3 then Name end as Branch,
case when level is 4 then Name end as Leaf,
case when level is 5 then Name end as Stem

Quote:

Originally Posted by sree078

hi

I've a table with coln names

ID
Name
ParentID
Level

I've list with different levels

say

ex.

the Data is:-

ID Name ParentID Level
1 Root null 1
2 Trunk 1 2
3 Branch 2 3
4 Leaf 3 4
5 Stem 3 4

Now I want to show this data as

Root -> Trunk -> Branch -> Leaf

How to write the query for getting the Names for different levels for corresponding ParentID...

query timeout expired

I am performing a update to 2m+ records, when it reaches 200 records it
times out ?
rsobj = db.execute("select * from Somefile where t2 is null;")
do while
db.commantimeout = 0
******** newexp & newfactor are calculated *****
SQLLine = "UPDATE Australia..InProgress SET t2 = '" & NewExp & "',t3
='" & NewFactor & "' where t1 = '" & business & "';"
DBobj.Execute(SQLLine)
Loop
When I monitor the current processors they are all awaitting commandHi
Define timeout value on an application level. Set it to default
"Tlink" <Tlink@.online.nospam> wrote in message
news:%23kBqCteWGHA.2080@.TK2MSFTNGP05.phx.gbl...
>I am performing a update to 2m+ records, when it reaches 200 records it
>times out ?
> rsobj = db.execute("select * from Somefile where t2 is null;")
> do while
> db.commantimeout = 0
> ******** newexp & newfactor are calculated *****
> SQLLine = "UPDATE Australia..InProgress SET t2 = '" & NewExp & "',t3
> ='" & NewFactor & "' where t1 = '" & business & "';"
> DBobj.Execute(SQLLine)
> Loop
> When I monitor the current processors they are all awaitting command
>|||I am unsure as to what this means and how to do it.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uV6ceweWGHA.3864@.TK2MSFTNGP04.phx.gbl...
> Hi
> Define timeout value on an application level. Set it to default
>
>
> "Tlink" <Tlink@.online.nospam> wrote in message
> news:%23kBqCteWGHA.2080@.TK2MSFTNGP05.phx.gbl...
>|||Tlink
Set cnAdo = New ADODB.Connection
strConnect = "driver={SQL
Server};uid=...;pwd=...;server=..;database=....;Network=dbmssocn"
cnAdo.Provider = "SQLOLEDB"
cnAdo.ConnectionString = strConnect
cnAdo.CommandTimeout = 0--or what do you have here?
cnAdo.CursorLocation = adUseServer
cnAdo.Mode = adModeRead
cnAdo.Open
"Tlink" <Tlink@.online.nospam> wrote in message
news:uUUdQ4eWGHA.3624@.TK2MSFTNGP04.phx.gbl...
>I am unsure as to what this means and how to do it.
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uV6ceweWGHA.3864@.TK2MSFTNGP04.phx.gbl...
>|||try this
SELECT subsnp_id,pop_id,allele_id
FROM AlleleFreqBySsPop as A,
(SELECT Omim_No
FROM av
WHERE Description LIKE '%LIVER%'
ORDER BY Omim_No ASC
UNION ALL
SELECT Omim_No
FROM cs
WHERE CS_Description LIKE '%LIVER%'
OR CS_DATA LIKE '%LIVER%'
ORDER BY Omim_No ASC
UNION ALL
SELECT Omim_No
FROM ti
WHERE Omim_Titles LIKE '%LIVER%'
ORDER BY Omim_No ASC
UNION ALL
SELECT Omim_No
FROM ti_alt_title
WHERE Omim_Alt_Titles LIKE '%LIVER%'
ORDER BY Omim_No ASC
UNION ALL
SELECT Omim_No
FROM tx
WHERE Omim_Text LIKE '%LIVER%' ) as B
WHERE A.source LIKE '%' + cast(B.Omim_no as varchar) + '%'|||Hi Omnibuzz
I think you are about this thread. See ,what does the OP ask for ?
:-))))))))))))))))
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:4AF72CF7-A02E-423A-BF50-A1A95EF10D38@.microsoft.com...
> try this
> SELECT subsnp_id,pop_id,allele_id
> FROM AlleleFreqBySsPop as A,
> (SELECT Omim_No
> FROM av
> WHERE Description LIKE '%LIVER%'
> ORDER BY Omim_No ASC
> UNION ALL
> SELECT Omim_No
> FROM cs
> WHERE CS_Description LIKE '%LIVER%'
> OR CS_DATA LIKE '%LIVER%'
> ORDER BY Omim_No ASC
> UNION ALL
> SELECT Omim_No
> FROM ti
> WHERE Omim_Titles LIKE '%LIVER%'
> ORDER BY Omim_No ASC
> UNION ALL
> SELECT Omim_No
> FROM ti_alt_title
> WHERE Omim_Alt_Titles LIKE '%LIVER%'
> ORDER BY Omim_No ASC
> UNION ALL
> SELECT Omim_No
> FROM tx
> WHERE Omim_Text LIKE '%LIVER%' ) as B
> WHERE A.source LIKE '%' + cast(B.Omim_no as varchar) + '%'
>|||Oops.. sorry wrong number :)
"Uri Dimant" wrote:

> Hi Omnibuzz
> I think you are about this thread. See ,what does the OP ask for
?
> :-))))))))))))))))
>
> "Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
> news:4AF72CF7-A02E-423A-BF50-A1A95EF10D38@.microsoft.com...
>
>|||Wrong query, too. :) Check out the correct post.
ML
http://milambda.blogspot.com/|||Guess I better take a break for sometime :) Thanks for pointing it out.|||You'll feel better after a peaceful wend. :)
ML
http://milambda.blogspot.com/

Query Syntax help

Hello All,

I have the following table:

CREATE TABLE [dbo].[TBL_NAME] (
[NAME] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[STANDARD_NAME] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
) ON [PRIMARY]
GO

With values:

insert into tbl_name
values('DAN', 'DANIEL')
insert into tbl_name
values('DANNY', 'DANIEL')
insert into tbl_name
values('DANYY', 'DANIEL')

Question is:

I need want to construct a query which returns all names for a standard
name plus the standard name itself.
e.g.
if name = 'DAN' then return 'DAN', 'DANNY', 'DANYY', 'DANIEL'
ff name = 'DANIEL', then return 'DAN', 'DANNY', 'DANYY', 'DANIEL'

i have the following sql:

declare @.name varchar(50)
select @.name = 'DANIEL'
select standard_name from tbl_name where name = @.name
union
select name from tbl_name where standard_name = (select standard_name
from tbl_name where name = @.name)
union
select name from tbl_name where standard_name = @.name
union
select standard_name from tbl_name where standard_name = @.name

--

declare @.name varchar(50)
select @.name = 'DANNY'
select standard_name from tbl_name where name = @.name
union
select name from tbl_name where standard_name = (select standard_name
from tbl_name where name = @.name)
union
select name from tbl_name where standard_name = @.name
union
select standard_name from tbl_name where standard_name = @.name

--

Both appear to work fine..can anyone see a fault or suggest a cleaner
way to achieve the above ?

Suggestions/pointers appreciated
Thanks in advance1) How many people do you know or have ever heard of that have a name
that need to have CHAR(50)? The USPS allows CHAR(35)

2) Why did you violate common sense and ISO-11179 Standards with the
"tbI-" prefix?

3) Why don't you have a key? Why did you prevent having a key with
NULL_able? Why are you smarter than Dr. Codd?

4) If you knew SQL would this look like this:

CREATE TABLE FirstNames
(first_name VARCHAR (35) NOT NULL
CHECK (first_name = RTRIM(LTRIM(first_name))),
alternate_first_name VARCHAR (35) NOT NULL
CHECK (alternate_first_name = RTRIM(LTRIM(alternate_first_name))),
PRIMARY KEY (first_name, alternate_first_name)
);

>> I need want to construct a query which returns all names for a standard name plus the standard name itself. <<

SELECT first_name, alternate_first_name
FROM FirstNames
WHERE first_name = @.my_guy;|||hharry (paulquigley@.nyc.com) writes:
> CREATE TABLE [dbo].[TBL_NAME] (
> [NAME] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [STANDARD_NAME] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL
> ) ON [PRIMARY]
> GO
> With values:
> insert into tbl_name
> values('DAN', 'DANIEL')
> insert into tbl_name
> values('DANNY', 'DANIEL')
> insert into tbl_name
> values('DANYY', 'DANIEL')
> Question is:
> I need want to construct a query which returns all names for a standard
> name plus the standard name itself.
> e.g.
> if name = 'DAN' then return 'DAN', 'DANNY', 'DANYY', 'DANIEL'
> ff name = 'DANIEL', then return 'DAN', 'DANNY', 'DANYY', 'DANIEL'
>...

If you add a row with (DANIEL, DANIEL), you can write a much simpler query:

insert into tbl_name
values('DAN', 'DANIEL')
insert into tbl_name
values('DANNY', 'DANIEL')
insert into tbl_name
values('DANYY', 'DANIEL')
insert into tbl_name
values('DANIEL', 'DANIEL')
go
DECLARE @.name varchar(50)
SELECT @.name = 'DANIEL'
SELECT name
FROM tbl_name t1
WHERE EXISTS (SELECT name, standard_name
FROM tbl_name t2
WHERE t2.standard_name = t1.standard_name
AND t2.name = @.name)
go

If this change is not feasible or possible, you could write:

SELECT t1.name
FROM (SELECT name, standard_name
FROM tbl_name
UNION
SELECT standard_name, standard_name
FROM tbl_name) t1
WHERE EXISTS (SELECT name, standard_name
FROM (SELECT name, standard_name
FROM tbl_name
UNION
SELECT standard_name, standard_name
FROM tbl_name) t2
WHERE t2.standard_name = t1.standard_name
AND t2.name = @.name)

But that's certainly a little more complex, and whether it's cleaner
your current query is a matter of taste.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||--CELKO-- (jcelko212@.earthlink.net) writes:
> 1) How many people do you know or have ever heard of that have a name
> that need to have CHAR(50)? The USPS allows CHAR(35)
> 2) Why did you violate common sense and ISO-11179 Standards with the
> "tbI-" prefix?
> 3) Why don't you have a key? Why did you prevent having a key with
> NULL_able? Why are you smarter than Dr. Codd?
> 4) If you knew SQL would this look like this:
> CREATE TABLE FirstNames
> (first_name VARCHAR (35) NOT NULL
> CHECK (first_name = RTRIM(LTRIM(first_name))),
> alternate_first_name VARCHAR (35) NOT NULL
> CHECK (alternate_first_name = RTRIM(LTRIM(alternate_first_name))),
> PRIMARY KEY (first_name, alternate_first_name)
> );
>>> I need want to construct a query which returns all names for a standard
name plus the standard name itself. <<
> SELECT first_name, alternate_first_name
> FROM FirstNames
> WHERE first_name = @.my_guy;

I don't know don't if "hharry" is smarter than Codd, but he is
obviously smarter than you. After all, he was able to write a query
that solved his problem - you weren't. (Since hharry supplied tables
and insert statements, you could have tested.)

As for your points 1-3, they are completely irrelevant and not the least
helpful. Just impolite and unfriendly. My guess is that hharry's real
business problem is different, and the table he posted he just a
throwaway table to demonstrate the SQL problem.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp