query

damn touchpads......
I have a query:
SELECT id, name, rank, serial_number from status
this will return many rows...if all values are null for a single row, i need
to be able to assign a unique "id" for that row (PK constraint on another
table) is there a way to do this so that for each row that has NULL values
across all columns. This way I can populate with an incrementing or otherwis
e
unique value (using d1, d2, d3... here)
id name rank serial_number
--
d1 null null null
d2 null null null
34r bill 0-6 4420
d3 null null null
d4 null null null
d5....
TIA!You can try using the following:
NOTE: This recreates the status table (truncate + re-populate)
-- BEGIN SCRIPT
create table status
(id varchar(10),
[name] varchar(10),
rank varchar(10),
serial_number varchar(10))
insert into status
values (null,null,null,null)
insert into status
values ('34r','bill','0-6','4420')
insert into status
values (null,null,null,null)
insert into status
values (null,null,null,null)
insert into status
values (null,null,null,null)
create table #status
(RowID int identity (1,1),
id varchar(10),
[name] varchar(10),
rank varchar(10),
serial_number varchar(10))
create table #status2
(RowID int identity (1,1),
id varchar(10),
[name] varchar(10),
rank varchar(10),
serial_number varchar(10))
insert into #status
select * from status
insert into #status2
select * from status
update #status
set id = 'd'+convert(varchar(10), s.RowID)
from #status s, #status2 s2
where s.RowID = s2.RowID
and s.id is null
truncate table status
insert into status
SELECT id, name, rank, serial_number from #status
select * from status
drop table #status
drop table #status2
Hope it helps
"JMNUSS" wrote:

> damn touchpads......
> I have a query:
> SELECT id, name, rank, serial_number from status
> this will return many rows...if all values are null for a single row, i ne
ed
> to be able to assign a unique "id" for that row (PK constraint on another
> table) is there a way to do this so that for each row that has NULL values
> across all columns. This way I can populate with an incrementing or otherw
ise
> unique value (using d1, d2, d3... here)
> id name rank serial_number
> --
> d1 null null null
> d2 null null null
> 34r bill 0-6 4420
> d3 null null null
> d4 null null null
> d5....
> TIA!
>

Query

The look folwoing query:
select RequisicaoPassagem.rpdata as Data,
RequisicaoPassagem.rpcodigo as Cdigo,
RequisicaoPassagem.rpcpf as CPF,
RequisicaoPassagem.rpnomefavorecido,
(((RequisicaoPassagem.rpTarifa+RequisicaoPassagem. rpTaxaEmbarque)/10) +
(select distinct ((viagemdiariatipo.vdtValor*viagemdados.vdQte)/10 )
from viagemdiariatipo, viagemdados, RequisicaoPassagem
where viagemdiariatipo.vdtcodigo = viagemdadoS.vdtcodigo and
RequisicaoPassagem.rpcodigo=viagemdados.rpcodigo and
RequisicaoPassagem.stsCodigo=1 )) AS Total
from RequisicaoPassagem,
viagemdados,
viagemdiariatipo
where viagemdados.vdtCodigo = viagemdiariatipo.vdtCodigo and
RequisicaoPassagem.stsCodigo=1
order by rpdata desc
this return error:
Server: Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery
follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
What is this error?
Mozart,
it looks like the correlated part of your subquery is returning more than
one row (the select distinct bit). You could restructure the query to prove
that is the case. If it is reasonable that the subquery returns > 1 row
you'll need to convert it to an aggregate to avoid the problem.
HTH,
Paul Ibison

Query

The look folwoing query:
select RequisicaoPassagem.rpdata as Data,
RequisicaoPassagem.rpcodigo as Cdigo,
RequisicaoPassagem.rpcpf as CPF,
RequisicaoPassagem.rpnomefavorecido,
(((RequisicaoPassagem.rpTarifa+RequisicaoPassagem.rpTaxaEmbarque)/10) +
(select distinct ((viagemdiariatipo.vdtValor*viagemdados.vdQte)/10 )
from viagemdiariatipo, viagemdados, RequisicaoPassagem
where viagemdiariatipo.vdtcodigo = viagemdadoS.vdtcodigo and
RequisicaoPassagem.rpcodigo=viagemdados.rpcodigo and
RequisicaoPassagem.stsCodigo=1 )) AS Total
from RequisicaoPassagem,
viagemdados,
viagemdiariatipo
where viagemdados.vdtCodigo = viagemdiariatipo.vdtCodigo and
RequisicaoPassagem.stsCodigo=1
order by rpdata desc
this return error:
Server: Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery
follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
What is this error?Mozart,
it looks like the correlated part of your subquery is returning more than
one row (the select distinct bit). You could restructure the query to prove
that is the case. If it is reasonable that the subquery returns > 1 row
you'll need to convert it to an aggregate to avoid the problem.
HTH,
Paul Ibison

QUERY

QUERY:
select enddate EDT,
NEW_TIME(ENDDATE, 'EDT','PDT')
FROM PROJECT
ERROR:
Server: Msg 195, Level 15, State 10, Line 2
'NEW_TIME' is not a recognized function name.RMSANDS (whoever you are)
You are posting many functions that are not defined in SQL-Server. You
are probably converting code from a different RDBMS.
You should really explain what each function is supposed to do. In
addition to that, it wouldn't hurt to take a look at the SQL-Server
Books Online (a download link has been posted by Ray Higdon) to see what
functions SQL-Server does support.
Gert-Jan
RMSANDS wrote:
> QUERY:
> select enddate EDT,
> NEW_TIME(ENDDATE, 'EDT','PDT')
> FROM PROJECT
> ERROR:
> Server: Msg 195, Level 15, State 10, Line 2
> 'NEW_TIME' is not a recognized function name.
(Please reply only to the newsgroup)