Re: Add row if result has no records

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Nikola Milic (hotmnikola_at_hotmail.com)
Date: 07/23/04 

Date: Fri, 23 Jul 2004 12:55:38 +0300

Sorry Steve if I wasn't clear,
I'm seeking for same result as I asked before, I just want to add on top of
recordset "header" rows. Header row is row which will be always shown with
values of CustomerID like 'Header X'.

Please check script below. First and Third query will return what I want
with single header row. Second and Fourth query should return same result
but with three header rows. But they do not work properly.

I noticed that Second and Fourth query work properly if I use DESC in ORDER
BY clausule. It seems to me that there is virtual row with NULL value in
trick column in Second and Fourth query (without DESC).

Thanks for reply
Nikola

create table T (
  OrderID int,
  CustomerID char(5)
)
go
insert into T values (100,'ABCDE')
insert into T values (101,'ABXYZ')
insert into T values (103,'ABMNO')
go
--FIRST
select top 2 with ties
 OrderID, CustomerID
from (
 select -1 as trick, 0 OrderID, 'Header 1' CustomerID
 union all
-- select -1 as trick, 0 OrderID, 'Header 2' CustomerID
-- union all
-- select -1 as trick, 0 OrderID, 'Header 3' CustomerID
-- union all
 select 0 as trick, OrderID, CustomerID
 from T
 where 1 = 1
 union all
 select 1 as trick, 0 OrderID, 'None' CustomerID
) T
order by trick
go
--SECOND
select top 2 with ties
 OrderID, CustomerID
from (
 select -1 as trick, 0 OrderID, 'Header 1' CustomerID
 union all
 select -1 as trick, 0 OrderID, 'Header 2' CustomerID
 union all
 select -1 as trick, 0 OrderID, 'Header 3' CustomerID
 union all
 select 0 as trick, OrderID, CustomerID
 from T
 where 1 = 1
 union all
 select 1 as trick, 0 OrderID, 'None' CustomerID
) T
order by trick
go
--THIRD
select top 2 with ties
 OrderID, CustomerID
from (
 select -1 as trick, 0 OrderID, 'Header 1' CustomerID
 union all
-- select -1 as trick, 0 OrderID, 'Header 2' CustomerID
-- union all
-- select -1 as trick, 0 OrderID, 'Header 3' CustomerID
-- union all
 select 0 as trick, OrderID, CustomerID
 from T
 where 1 > 1
 union all
 select 1 as trick, 0 OrderID, 'None' CustomerID
) T
order by trick
go
--FOURTH
select top 2 with ties
 OrderID, CustomerID
from (
 select -1 as trick, 0 OrderID, 'Header 1' CustomerID
 union all
 select -1 as trick, 0 OrderID, 'Header 2' CustomerID
 union all
 select -1 as trick, 0 OrderID, 'Header 3' CustomerID
 union all
 select 0 as trick, OrderID, CustomerID
 from T
 where 1 > 1
 union all
 select 1 as trick, 0 OrderID, 'None' CustomerID
) T
order by trick
go
drop table T
go

--======================
--RESULTS

--FIRST
OrderID CustomerID
0 Header 1
100 ABCDE
101 ABXYZ
103 ABMNO

--SECOND
--wrong - should be like first with two more rows with Header 2 and Header
3
OrderID CustomerID
0 Header 1
0 Header 2
0 Header 3

--THIRD
OrderID CustomerID
0 Header 1
0 None

--FOURTH
--wrong - should be like third with two more rows with Header 2 and Header
3
OrderID CustomerID
0 Header 1
0 Header 2
0 Header 3

"Steve Kass" <skass@drew.edu> wrote in message
news:e5D4FGBcEHA.2660@tk2msftngp13.phx.gbl...
> Nikola,
>
> What results are you seeking? The original question asked how to
> return a "no results" row if a query returned no results, and I'm not
> sure what you now want with "header rows"
>
> SK
>
> Nikola Milic wrote:
>
> >Hi Steve,
> >Please check problem in script below. I modified your query to have
header
> >rows and get wrong results if header has more than one row.
> >
> >Thanks in advance
> >Nikola
> >
> >
> >create table T (
> > OrderID int,
> > CustomerID char(5)
> >)
> >go
> >insert into T values (100,'ABCDE')
> >insert into T values (101,'ABXYZ')
> >insert into T values (103,'ABMNO')
> >go
> >select top 2 with ties
> > OrderID, CustomerID
> >from (
> > select -1 as trick, 0 OrderID, 'Header 1' CustomerID
> > union all
> >
> >--if you comment lines with Header 2 & 3 rows, query returns result
properly
> > select -1 as trick, 0 OrderID, 'Header 2' CustomerID
> > union all
> > select -1 as trick, 0 OrderID, 'Header 3' CustomerID
> > union all
> >--end comment lines
> >
> > select 0 as trick, OrderID, CustomerID
> > from T
> > where 1 = 1
> > union all
> > select 1 as trick, 0 OrderID, 'None' CustomerID
> >) T
> >order by trick
> >go
> >drop table T
> >go
> >
> >
> >
> >
> >
> >"Steve Kass" <skass@drew.edu> wrote in message
> >news:%23gvYkKybEHA.3148@TK2MSFTNGP10.phx.gbl...
> >
> >
> >>Nikola,
> >>
> >> You can create a record set that always contains the "no rows
> >>returned" row and use the following TOP 1 WITH TIES trick to select it
> >>only when there are no other rows. As others have said, this is
> >>probably a bad idea for many reasons.
> >>
> >>create table T (
> >> OrderID int,
> >> CustomerID char(5),
> >> OrderDate datetime
> >>)
> >>go
> >>
> >>create view V as
> >>select top 1 with ties
> >> OrderID, CustomerID, OrderDate
> >>from (
> >> select 0 as trick, OrderID, CustomerID, OrderDate
> >> from T
> >> where OrderDate >= getdate()-1
> >> and OrderDate < getdate()
> >> union all
> >> select 1, 0, 'None', null
> >>) T
> >>order by trick
> >>go
> >>
> >>select * from V
> >>insert into T values (100,'ABCDE', getdate()-'01:00')
> >>insert into T values (101,'ABXYZ', getdate()+'01:00')
> >>insert into T values (103,'ABMNO', getdate()-'03:00')
> >>select * from V
> >>delete from T where OrderID <> 101
> >>select * from V
> >>
> >>go
> >>drop view V
> >>drop table T
> >>
> >>
> >>Steve Kass
> >>Drew University
> >>
> >>Nikola Milic wrote:
> >>
> >>
> >>
> >>>Hi,
> >>>Is it possible to make view (I need it to be view because of client
> >>>application) which will add row to empty recordset with text "No
records
> >>>returned" when query returns no records?
> >>>
> >>>I'm using SS2000 Enterprise edition SP3 on Win2000 Advanced Server SP4
> >>>
> >>>Thanks in advance
> >>>Nikola Milic
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >
> >
> >
> >
> >
> >

Relevant Pages

  • Re: Add row if result has no records
    ... If you want to add header rows to what I previously suggested, ... select CustomerID, showthis, OrderID from ( ... CustomerID as CustomerID, ... >trick column in Second and Fourth query. ...
    (microsoft.public.sqlserver.programming)
  • Re: Principle of Orthogonal Design
    ... requires the two tables to have identical column names." ... usual simplification where there is only one type / domain. ... the union of two relations I am not. ... relation with header? ...
    (comp.databases.theory)
  • Re: Date and McGoveran comments on view updating problem
    ... D has the same header as ... inserting projections of D into base relations S and SP. ... It looks very similar to distributivity of union over ... But R00 joined with anything ...
    (comp.databases.theory)
  • Re: Distributivity in Tropashkos Lattice Algebra
    ... > There is no controversy. ... Let 'join' be the natural join from your aticle, 'union' the ... It is a mapper of a relation into a header ...
    (comp.databases.theory)
  • Re: Distributivity in Tropashkos Lattice Algebra
    ... >> with the same header as A. Then, ... these formal identities are literally the same as the definitions. ... > The same can be said about the union where the attribute set is defined ... identified by rowids. ...
    (comp.databases.theory)