phantom records

[TB]

Hi all:

I realize that this is strictly speaking not an ASP.NET question, but
since most of you people out there SQL gurus as well, I hope you will
bear with me on this occasion:

I would like to know how to create a "phantom" record when running a
SELECT query.

For example:

Running "Select ID, Firstname, Lastname from Customers order by
Lastname" would produce a list all the records in the Customers table
such as:

1 John Brown
2 Anne Johnsen
3 Tom Smith


But I would like the first the result of such a query to contain a
"virtual record", i.e information that I have manually inserted into
the query and does not correspond to any record.

So, by way of an example how do I ensure a return like this:

0 PETER JENSEN
1 John Brown
2 Anne Johnsen
3 Tom Smith

when there is no record containing the information "0 Peter Jensen"?

Thanks.

TB

 

[sloan]

I answered it in

microsoft . public . sqlserver . programming

newsgroup.

Search by subject for "
Re: phantom records"

 

[sloan]

http://groups.google.com/group/micr...d83cc80de3e/877ddf6f31318af4#877ddf6f31318af4

 

[Flinky Wisty Pomm]

SELECT 0 as ID,
'Peter' AS FirstName,
'Jensen' AS Surname
UNION ALL
SELECT Id,
FirstName,
Surname
FROM...

But that will insert the record and then sort. What are you doing with
the result of the query?

I suppose you could do

SELECT 0 AS ID, 'Peter' AS FirstName, 'Jensen' AS Surname, 0 AS DUMMY
UNION ALL
SELECT ID, FirstName, Surname, 1
FROM ...
ORDER BY Dummy, Surname

But that's getting uglier and uglier, why on earth would you want to do
this?

 

[TB]

Thanks a lot for your reply. Your last proposal works.

Now why would I want to do this? Well You see, I have this drop-down
list which is populated from a datebase. However, in addition to the
list items suppled by a table, I need some kind of default "all of the
above" (or below in case of af DDL) choice.

I have discovered that neither of the below statements:
ddlChoices.items.add(new listitem("Default",0)) 'This one adds at the
end of the list

OR

ddlchoice.items.insert(0,New listitem("Default",9)) 'This one adds at
the beginning of the list

seem to be compatible with databinding from a data source, such as

ddlchoice.DataSource = mydatareader
ddlchoice.DataValueField = "ID"
ddlchoice.DataTextField = "Lastname"
ddlchoice.DataBind()

If I databind after adding either of the two statements above, then
only the results from the database appear. If I add either of the two
statements after databinding then ddl will only contain one list item,
namely the manual insert.

So I came up with the idea of this phantom record.

What do you think?

TB

 

[TB]

One more thing:

I discovered that the columns get truncated according the length of the
dummy column. For example, the firstname column width is 5, because the
dummy record contains the 5 letter word Peter. Why is that?

Another thing is that the statement after the UNION ALL should contain
a CONCAT function.

However, if I do this:

SELECT 0 AS IDuser, '(All employees)' AS Lastname, 0 AS dummy
UNION ALL
SELECT Iduser, concat( FirstName, ' ', Lastname) AS name, 1
FROM tblUsers
ORDER BY dummy, lastname

then the secondary sort (after dummy) is done on the result of the
CONCAT operation. In other words, the (undesired) result will be

0 (All Employees) 0
2 Anne Johnsen 1
1 John Brown 1
3 Tom Smith 1


If I shorten to
SELECT 0 AS IDuser, '(All)' AS Lastname, 0 AS dummy
UNION ALL
SELECT Iduser, concat( FirstName, ' ', Lastname) AS name, 1
FROM tblUsers
ORDER BY dummy, lastname

then the result is

0 (All) 0
2 Anne 1
1 John 1
3 Tom 1

Cheers

TB

 

[Flinky Wisty Pomm]

Your DDL only contains one item if you insert after databinding? That's
.... odd; I've got manifold examples right here in front of me where I
do something like

locations.DataSource = Data.GetCarLocations(CurrentDealership);
locations.DataTextField = "LocationId";
locations.DataValueField = "LocationName";
locations.DataBind();

ListItem item = new ListItem("Select a location", "");
locations.Items.Insert(0, item);

Without a problem.

What is mydatareader exactly?

 

[Flinky Wisty Pomm]

Oh, and the column length - This is off-topic, so I'll be brief. SQL
Server is guessing the data type of your columns from the first set in
the union operation. 'Peter' fits nicely as a VARCHAR(5) and the second
set is cast to that type. If you wanted to keep the columns separate
without truncating data, try CAST('PETER' as VARCHAR(xxx)) AS FirstName
where xxx is (1 + FirstName.Length + Surname.Length)

 

[TB]

I discovered the reason, and I am the one to blame: My dataloading
routine in the page_load sub was not surrounded by "If Not
Page.IsPostBack Then [...] End if" condition. So, the whole shop was of
course reset every time I hit something.

Mydatareader is the data I pick from a MySQL database:

Imports MySql.Data.MySqlClient
Public Class searchpanel
[.....]

Dim myConnection As MySqlConnection
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
myConnection = New MySqlConnection(connstring)
If Not Page.IsPostBack Then
[...]
inisearch()
End If
End sub
sub inisearch()
Dim objCommand As MySqlCommand
Dim mydatareader As MySqlDataReader
strSQL = "Select ID, Concat(firstname,' ',lastname) as name from
tblUsers order by lastname"
objCommand = New MySqlCommand(strSQL, myConnection)
myConnection.Open()
mydatareader =
objCommand.ExecuteReader(CommandBehavior.CloseConnection)
ddlIDuser.DataSource = mydatareader
ddlIDuser.DataValueField = "ID"
ddlIDuser.DataTextField = "name"
ddlIDuser.DataBind()
mydatareader.Close()
end sub


As you can see, I use CONCAT to join firstname and lastname into one
column, but if I do that with the UNION ALLl statement, then the
sorting will be from left to right in the combined field, and not on
the lastname.

Anyway, I will now try to go back to my original idea of inserting a
listitem after databinding as the error in the page_load sub has been
corrected.

 

[TB]

Interesting. Thanks for the info.
I have replied separately to your other posting.

TB