Combining subquery results into one field

Hello there

I have an application that allows users to book rooms in a building. I have a booking request table and a rooms booked table since there is a booking that can be made that includes multiple rooms. I have an instance where i need to select the booking requests for a particular date and need to display the rooms for each booking. Since the rooms booked table has the booking request id i'm wondering if there is a way to combine all the subquery results into one record to get around the error of having multiple records being returned in a sub query. The table structures are as follow:

bookingrequestion - bookingrequestid, startdate, enddate

roomsbooked-id, bookingrequestid, roomname

i'm basically trying to use the following query

select br.bookingrequestid, (select roomname from roomsbooked where id = br.bookingrequest) as rooms

i'd like the results of the subquery to return the room names as A,B,C. I'm trying to avoid having to obtain the recordset for the booking requests and then loop through them and for each one obtain a recordset for the rooms, seems like too many database hits to me.

thanks

I would look at returning two results to a dataset then creating a relationship between them. Displaying the results is pretty easy using nested repeaters:http://gridviewguy.com/ArticleDetails.aspx?articleID=185

Column does not allow nulls

Hi,

This should be straight forward, but I've searched high and low on the net.

I have a FORM which allows me to INSERT data, SQL Server 2005 backend. I populate the all the mandatory fields.

But when I click on the Insert button, it won't let me save and says:

Column 'PERSONAL_ID' does not allow nulls

I'musing tableadapters, business logic layers etc. and pausing clearlyshows that the values from the form are being passed to the procedurethat I have created "AddNewRecord". And PERSONAL_ID is definitely notNULL!

It fails on

Line 971: Me.Rows.Add(row)

PERSONAL_IDis a primary key which I generate. The pause also shows that forPERSONAL_ID it says" {"Conversion from type 'DBNull' to type 'String'is not valid."}

Function AddNewRecord looks like this:

<System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Insert, True)> _
Public FunctionAddNewRecord(ByVal PERSONAL_ID As String, ByVal SURNAMEAs String, ByVal CHRISTIAN_NAME As String, ByVal SEX As String, _
ByVal FAMILY_POSITION As String, ByVal FAMILY_ID As String, ByValADDRESS_1 As String, ByVal ADDRESS_2 As String, _
ByVal ADDRESS_3As String, ByVal ADDRESS_4 As String, ByVal ADDRESS_5 As String, ByValADDRESS_6 As String, ByVal POSTCODE As String, ByVal COUNTRY As String,ByVal ORG_ID As String) As Boolean
' create a new details row instance
Dim details As New smDetails.smTbl_DetailsIDDataTable
Dim detail As smDetails.smTbl_DetailsIDRow = details.NewsmTbl_DetailsIDRow

details.AddsmTbl_DetailsIDRow(detail)
.
.
.
my formview code is this:

<InsertItemTemplate>
PERSONAL_ID:
<asp:TextBox ID="PERSONAL_IDTextBox" runat="server"
Text='<%# Bind("PERSONAL_ID") %>' AutoPostBack="True" />
<br />
SURNAME:
<asp:TextBox ID="SURNAMETextBox" runat="server" Text='<%# Bind("SURNAME") %>'
AutoPostBack="True" />
<br />
CHRISTIAN_NAME:
<asp:TextBox ID="CHRISTIAN_NAMETextBox" runat="server"
Text='<%# Bind("CHRISTIAN_NAME") %>' />
<br />
SEX:
<asp:TextBox ID="SEXTextBox" runat="server" Text='<%# Bind("SEX") %>' />
<br />
FAMILY_POSITION:
<asp:TextBox ID="FAMILY_POSITIONTextBox" runat="server"
Text='<%# Bind("FAMILY_POSITION") %>' />
<br />
FAMILY_ID:
<asp:TextBox ID="FAMILY_IDTextBox" runat="server"
Text='<%# Bind("FAMILY_ID") %>' />
<br />
ADDRESS_1:
<asp:TextBox ID="ADDRESS_1TextBox" runat="server"
Text='<%# Bind("ADDRESS_1") %>' />
<br />
ADDRESS_2:
<asp:TextBox ID="ADDRESS_2TextBox" runat="server"
Text='<%# Bind("ADDRESS_2") %>' />
<br />
ADDRESS_3:
<asp:TextBox ID="ADDRESS_3TextBox" runat="server"
Text='<%# Bind("ADDRESS_3") %>' />
<br />
ADDRESS_4:
<asp:TextBox ID="ADDRESS_4TextBox" runat="server"
Text='<%# Bind("ADDRESS_4") %>' />
<br />
ADDRESS_5:
<asp:TextBox ID="ADDRESS_5TextBox" runat="server"
Text='<%# Bind("ADDRESS_5") %>' />
<br />
ADDRESS_6:
<asp:TextBox ID="ADDRESS_6TextBox" runat="server"
Text='<%# Bind("ADDRESS_6") %>' />
<br />
POSTCODE:
<asp:TextBox ID="POSTCODETextBox" runat="server"
Text='<%# Bind("POSTCODE") %>' />
<br />
COUNTRY:
<asp:TextBox ID="COUNTRYTextBox" runat="server" Text='<%# Bind("COUNTRY") %>' />
<br />
ORG_ID:
<asp:TextBox ID="ORG_IDTextBox" runat="server" Text='<%# Bind("ORG_ID") %>' />
<br />
<asp:LinkButton ID="InsertButton" runat="server" CausesValidation="True"
CommandName="Insert" Text="Insert" />
<asp:LinkButton ID="InsertCancelButton" runat="server"
CausesValidation="False" CommandName="Cancel" Text="Cancel" />
</InsertItemTemplate

Any pointers in the right direction would be appreciated.

Thanks in advance

Tushar

Can you post the stored procedure/sql statement that performs the insert?

|||

INSERT INTO smTbl_DetailsID (PERSONAL_ID, SURNAME, CHRISTIAN_NAME, SEX, FAMILY_POSITION, FAMILY_ID, ADDRESS_1, ADDRESS_2, ADDRESS_3, ADDRESS_4, ADDRESS_5, ADDRESS_6, POSTCODE, COUNTRY, ORG_ID)VALUES (@.PERSONAL_ID,@.SURNAME,@.CHRISTIAN_NAME,@.SEX,@.FAMILY_POSITION,@.FAMILY_ID,@.ADDRESS_1,@.ADDRESS_2,@.ADDRESS_3,@.ADDRESS_4,@.ADDRESS_5,@.ADDRESS_6,@.POSTCODE,@.COUNTRY,@.ORG_ID)

Execute mode is Scalar

thanks

Tushar

|||

I've found the solution is to replace

details.AddsmTbl_DetailsIDRow(detail)

inFunction AddNewRecord in the BLL

with

Adapter1.InsertQuery_TAM(PERSONAL_ID, SURNAME, CHRISTIAN_NAME, SEX, _
FAMILY_POSITION, FAMILY_ID, ADDRESS_1, ADDRESS_2, _
ADDRESS_3, ADDRESS_4, ADDRESS_5, ADDRESS_6, POSTCODE, COUNTRY, ORG_ID)

i.e. my user defined insert query.

For some reason adding a new row directly causes the SELECT routine to run, which expects a value for the PERSONAL_ID (primary key). I'm sure I'll discover why in due course.

Also the above is not really very clear in the tutorials.

HTH

Tushar

Column allows nulls i want to change not to allow

When i do a select on my emplee table for rows with null idCompany i dont ge
t
any records
I then try to modify the table to not allow a null idCompany and i get this
error message:
'Employee (aMgmt)' table
- Unable to modify table.
Cannot insert the value NULL into column 'idCompany', table
'D2.aMgmt.Tmp_Employee'; column does not allow nulls. INSERT fails.
The statement has been terminated.
This suxis you select like this?
SELECT * FROM D2.aMgmt.Tmp_Employee
WHERE idCompany IS NOT NULL
or do you have
WHERE idCompany <> NULL
because that will return no rows unless ANSI_NULLS is set to OFF
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Try specifying a default value for idCompany when issuing the ALTER TABLE
statement.
ALTER TABLE employee ALTER COLUMN idCompany datatype NOT NULL DEFAULT ''
"thom" wrote:

> When i do a select on my emplee table for rows with null idCompany i dont
get
> any records
> I then try to modify the table to not allow a null idCompany and i get thi
s
> error message:
> 'Employee (aMgmt)' table
> - Unable to modify table.
> Cannot insert the value NULL into column 'idCompany', table
> 'D2.aMgmt.Tmp_Employee'; column does not allow nulls. INSERT fails.
> The statement has been terminated.
> This sux|||I hope you wrote the query as
select * from tbl1
where dCompany is null -- and not "dCompany = null"|||How did you write the query to select all records having null idcompany.
This should list all records with null idcompany
Select * from Employee where idcompany IS NULL
thom wrote:
> When i do a select on my emplee table for rows with null idCompany i dont
get
> any records
> I then try to modify the table to not allow a null idCompany and i get thi
s
> error message:
> 'Employee (aMgmt)' table
> - Unable to modify table.
> Cannot insert the value NULL into column 'idCompany', table
> 'D2.aMgmt.Tmp_Employee'; column does not allow nulls. INSERT fails.
> The statement has been terminated.
> This sux|||Of course the IS NOT NULL should be IS NULL and the <> NULL should be
= NULL, sorry for that
so
SELECT * FROM D2.aMgmt.Tmp_Employee
WHERE idCompany IS NULL
or do you have
WHERE idCompany = NULL
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Sorry wrong post :P|||I Dont know how but i fixed it, i was not using <> null and not using = nul
l
This issue seemed to be with the Check existing data on creation, i ended up
getting that message on another table and when i changed check existing data
on create to no there was no problem.
BUT either way when i SELECTED * FROM myTable WHERE idColumn IS NULL
i got no records returned
"thom" wrote:

> When i do a select on my emplee table for rows with null idCompany i dont
get
> any records
> I then try to modify the table to not allow a null idCompany and i get thi
s
> error message:
> 'Employee (aMgmt)' table
> - Unable to modify table.
> Cannot insert the value NULL into column 'idCompany', table
> 'D2.aMgmt.Tmp_Employee'; column does not allow nulls. INSERT fails.
> The statement has been terminated.
> This sux|||Was it really?
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:840FB234-5802-4A53-BEE6-D36963CE17F7@.microsoft.com...
> Sorry wrong post :P|||No it wasn't. This was posted in the wrong thread...
and thanks for finding this.. I was searching for this post :)

Column allows nulls I want to change no nulls allowed

When i do a select on my emplee table for rows with null idCompany i dont get any records

I then try to modify the table to not allow a null idCompany and i get this error message:

'Employee (aMgmt)' table
- Unable to modify table.
Cannot insert the value NULL into column 'idCompany', table 'D2.aMgmt.Tmp_Employee'; column does not allow nulls. INSERT fails.
The statement has been terminated.

This suxWhat does a SELECT statement not returning any records have to do with allowing NULLs? And why are you doing an INSERT statment while modifying the table?

Your post does not make much sense.|||It would help if you poseted the 'select' statement.

Are you using columnname = NULL or columnname IS NULL in the 'where' clause?

I don't think you can alter a table to prohibit NULLs if NULLs already exist in the table. You'll have to change those values or provide a default when you do the alter.|||This issue seemed to be with the Check existing data on creation, i ended up getting that message on another table and when i changed check existing data on create to no there was no problem.

BUT either way when i SELECTED * FROM myTable WHERE idColumn IS NULL
i got no records returned

HERE IS THE SQL On the 2nd table problem

SELECT idMaster, idVendor, idExpType, idCompany, DateEntered, EnteredBy, CheckNumber, CheckAmount, VendorName, ExpenseType, CheckDate, voided,
ckSuffix, MasterTs, tempCol
FROM aDataEntry.master
WHERE (idCompany IS NULL)|||Post the script (DDL) for the table.

Column Alias in views

Hi All,
I am currently transferring my Access application to SQL Server. Access allows you to declare and use aliases in the query at the same time.

e.g.
Select field1 as Alias1, field2 as Alias2, Alias1 & " " & Alias2 as Alias3 from table1;

In Access the above query will execute perfectly, no problem. However in SQL Server, if you try to run the same query it will give an error "Invalid column name Alias1" meaning that SQL Server is searching for Alias1 as a field in the table, not as an alias from the query.

My question is does SQL Server have a facility to declare and use alias directly as in Access and if no, is there a workaround?

Thanks for your time.

Regards:
Prathmeshhi

try this

Select field1 as Alias1, field2 as Alias2, field1 + ' ' + field2 as Alias3 from table1;

hope this will solve ur problem|||Hi,
Ok, I think I need to explain a bit more detail. I have got a database table that stores data about different equipments. Each equipment is identified by 3 distinct fields Area, Type, No. So a particular equipment tag would be of type:
Area+Type+No.

Now at the same time the table also holds the description of the equipment which comes from 2 fields desc1 and desc2. So the whole equimment desc would be desc1+desc2

Now on the reports the equiptag and equipment desc need to be concatenated to form one equipment number i.e. Area+type+No+Desc1+desc2

So what I wanted to do was
Select Area+type+No as Equiptag, Desc1+Desc2 As EquipDesc, EquipTag+EquipDesc As EquipNo from equipment;

but obviously SQL Server will give an error of invalid column for "EquipTag" and "EquipDesc"

So, Is there any way to do this?

Regards:
Prathmesh|||Hi,

So what I wanted to do was

Select
Area+type+No as Equiptag,
Desc1+Desc2 As EquipDesc,
EquipTag+EquipDesc As EquipNo
from
equipment;

but obviously SQL Server will give an error of invalid column for "EquipTag" and "EquipDesc"

So, Is there any way to do this?

To the best of my knowledge, you can't use an alias as part of a formula within the same SQL. You would either have to do this:

Select
Area+type+No as Equiptag,
Desc1+Desc2 As EquipDesc,
Area+type+No+Desc1+Desc2 As EquipNo
from
equipment;

or you could try creating a subquery like this:

SELECT
t.EquipTag,
t.EquipDesc,
t.EquipTag+t.EquipDesc As EquipNo
FROM
(SELECT
Area+type+No as Equiptag,
Desc1+Desc2 As EquipDesc
FROM
equipment) t

Regards,

hmscott|||Thanks hmscott,
The subquery idea is a good one. I'll give it a try. I was just curious if this could be done similar to Access or not. I must say, being an Access programmer, there are certain things in SQL Server which really annoy you. Most of my queries use this type of aliasing, so I now have to go and rewrite them to replace Aliasing.

Another thing is the "concat null yields null" thing. When you concat 2 strings and one is null, the returned string is Null. Huh!!! Why? I think this is totally stupid. In Access, this is not at all a problem. It just discards the nulls, and returns the concatenated string without nulls. Well I guess this is typical Microsoft behaviour. I tried executing the stored procedure to set the concat null yeidls null to false, but it does not work. I cannot figure out why. A similar question was posted in this forum asking why it does not work, but nobody was able to answer. If anybody has got any suggestions, please do let me know.

Thanks.

Regards:
Prathmesh|||All databases are different. All databases have things that are worse than other databases or extra things that are better than other databases. There is no reason. What is included in the SQL Standard should be the same accross databases but for anything else ...|||hi Prathmesh,

try this

SELECT ISNULL(columnwithnull,'') + nonnullcolumns from yourtable|||Hi baburaj,
Yep, that is what I am using now. However, I have decided on something else. I am planning to use SQL Server backend to Access frontend, because all my forms , reports, etc. are in Access.I am going to do all the complex join queries on SQL Server side as views and link the tables via odbc to Access using the Access "link tables" facility and the required formatting I will still do on Access side. This way I can have best of both worlds. I can make use of SQL server's performance and Access' formatting features.

Thanks to all for your help and suggestion guys.|||Another thing is the "concat null yields null" thing. When you concat 2 strings and one is null, the returned string is Null. Huh!!! Why? I think this is totally stupid. In Access, this is not at all a problem. It just discards the nulls, and returns the concatenated string without nulls. Well I guess this is typical Microsoft behaviour. I tried executing the stored procedure to set the concat null yeidls null to false, but it does not work. I cannot figure out why. A similar question was posted in this forum asking why it does not work, but nobody was able to answer. If anybody has got any suggestions, please do let me know.
Not entirely true - Access also provides the "+" concatenation operator where Null + "Something" = Null.
Rather than thinking of it as a bind you need to think through the implications. The + operator is great, for example, when putting together a csv address string for presentation - you don't need to use a load of conditional statements to exclude the comma if, for example, the address has no House Name.