|
|
Have you heard of Google? There only about 1mil returns on the subject...
______________________
stuff + cats = awesome
|
|
|
|
|
I am having issues with the following code ever since I added a subquery to it. The error tells me that more than one value was returned from the subquery. The query should in fact retrieve more than one value in most instances so this is fine. Thing is it stops the query from working and I don't know why. My code is as follows in it's entirety. Thanks for any help
SELECT<br />
IH.InvoiceDate AS HistoryDate,<br />
'Part of Invoice' AS HistoryType,<br />
SI.SiteName AS HistoryBranch,<br />
('Invoice Number - ' + IH.InvoiceNumber + ' - ' + C.CustomerForeName + ' ' + C.CustomerSurname) AS HistoryReference,<br />
(<br />
SELECT TIP.InvoicePhoneIMEI <br />
FROM tblInvoicePhones TIP <br />
INNER JOIN tblInvoiceItems TII <br />
ON TIP.InvoiceItemID = TII.InvoiceItemID <br />
WHERE TII.InvoiceID = IH.InvoiceID<br />
) As HistoryIMEIs,<br />
(ST.StaffForeName + ' ' + ST.StaffSurname) AS HistoryUser<br />
FROM tblInvoiceHeader IH<br />
INNER JOIN tblInvoiceItems II<br />
ON II.InvoiceID = IH.InvoiceID<br />
INNER JOIN tblSites SI<br />
ON IH.SiteID = SI.SiteID<br />
INNER JOIN tblStaff ST<br />
ON IH.StaffID = ST.StaffID<br />
LEFT JOIN tblCustomers C<br />
ON IH.CustomerID = C.CustomerID<br />
WHERE II.StockID = '1750'<br />
AND IH.InvoiceDate >= 'January 1, 2007'<br />
AND IH.InvoiceDate <= 'July 2, 2007'<br />
AND IH.SiteID = '10'<br />
AND IH.InvoiceProForma = 0
|
|
|
|
|
The message relates to the subquery. In this example the subquery can only return one row. I would run the subquery only and see how many rows it is returning. If it returns more than one then you have a problem
Jon
|
|
|
|
|
As i mentioned in my original post the subquery should and does return more than one rows. I want for example the following to be returned.
12/10/2007
Sale
967676689768879, 23987298739872, 39278387298372, 3927328739872973
24/12/2007
Sale
8768676876876876, 87687678876876786, 9876987865765678
Where the large numbers are collected from the subquery. Is this not possible? How else can I go about getting information in this way?
|
|
|
|
|
No, You cant do it that way. The way your SQL is structured the subquery is in effect a single field. you cant just put several values into one field.
I see what you are trying to do but i dont think there is a way to do it with a single SQL statement (although i may be wrong about that).
|
|
|
|
|
Ok so you can't merge the fields in anyway. Does anyone else know?
|
|
|
|
|
Hi
You can write your sub-query in a separate function. (table-valued function). Using this function, you can join with the main query as given below.
Select Col1, Col2, dbo.Fn_SubQuery(param1,param2)
from Table1
Hope this helps.
See the example below
<br />
use northwind<br />
go<br />
create function dbo.Fn_OrdersProductID(@OrderID int)<br />
returns nvarchar(4000)<br />
as <br />
begin<br />
DECLARE @ProductID nvarchar(4000)<br />
set @ProductID = null <br />
SELECT @ProductID = COALESCE(@ProductID + ',','') + cast(ProductID as nvarchar)<br />
FROM [Order Details]<br />
WHERE OrderID = @OrderID<br />
<br />
RETURN @ProductID<br />
<br />
end<br />
<br />
-- run the foll. stmt after executing the above function<br />
-- SELECT OrderID,dbo.Fn_OrdersProductID(OrderID) from Orders
Apply the same for your query
-- modified at 7:08 Tuesday 3rd July, 2007
-- modified at 7:11 Tuesday 3rd July, 2007
Harini
|
|
|
|
|
what is the need of COALESCE
|
|
|
|
|
Harini is right I believe.
Something like:
SELECT DISTINCT
IH.InvoiceDate AS HistoryDate
, 'Part of Invoice' AS HistoryType
, SI.SiteName AS HistoryBranch
, ('Invoice Number - ' + IH.InvoiceNumber + ' - ' + C.CustomerForeName + ' ' + C.CustomerSurname) AS HistoryReference
, getInvoicePhoneIMEIs(IH.InvoiceID) AS HistoryIMEIs
FROM tblInvoiceHeader IH
INNER JOIN tblInvoiceItems II ON II.InvoiceID = IH.InvoiceID
INNER JOIN tblSites SI ON IH.SiteID = SI.SiteID
INNER JOIN tblStaff ST ON IH.StaffID = ST.StaffID
LEFT JOIN tblCustomers C ON IH.CustomerID= C.CustomerID
WHERE II.StockID = '1750'
AND IH.InvoiceDate >= 'January 1, 2007'
AND IH.InvoiceDate <= 'July 2, 2007'
AND IH.SiteID = '10'
AND IH.InvoiceProForma = 0
where getInvoicePhoneIMEIs(IH.InvoiceID) uses a cursor based on your subselect to build a string.
http://www.sqlteam.com/article/using-coalesce-to-build-comma-delimited-string[^]
Be sure to include the DISTINCT keyword or you'll get a result row for every InvoicePhoneIMEI value.
David
|
|
|
|
|
I want to disable the dbo schema in my Database.
I want to disable the windows authontication users
merwa
|
|
|
|
|
Great. Now where is your code for us to review?
______________________
stuff + cats = awesome
|
|
|
|
|
You really cannot disable or rename the dbo schema. You cannot disable Windows Authentication entirely - the options are either Windows Authentication, or Mixed Mode which is both Windows Authentication and SQL Server Authentication.
You can delete the BUILTIN\Administrators login (I think, but I'm not willing to try it - back up your master database first and make sure you have another login in the sysadmin role, or have SQL Server authentication enabled and know your sa password). You can certainly change that login from a GRANT to a DENY, but I think that will have the effect of denying access to anyone in the Administrators group even if they would be GRANTed access through another login.
However, Windows Authentication is generally considered Best Practice as the account details are not stored in the SQL Server database server itself (SQL Server login passwords are stored in master), and therefore cannot be compromised through SQL Server itself. Also, before SQL Server 2005, SQL Server passwords were passed in the clear - anyone with a network sniffer can see the passwords. SQL Server 2005 Native Client and .NET Framework 2.0 support encrypted connections, IIRC.
If you're not talking about allowing Administrators to have access you will find any other logins under Security\Logins in Enterprise Manager or Management Studio.
|
|
|
|
|
I have a table Tab1 with fields Name and Dt( data type Date/Time).
the col Dt have values
2/2/2007
3/3/2007
5/5/2008
i want to display names with date before 1/1/2008
i wrote the folowing query
"SELECT Name from tab1 where dt < 1/1/2008"
but this returns nothing..
iam using msaccess 2003.
thanks
|
|
|
|
|
I think in Access you have to surround date/time literals with the # sign. Try:
"SELECT Name from tab1 where dt < #1/1/2008#"
|
|
|
|
|
|
Hello there,
the correct query is here
SELECT Name from tab1 where dt < #1/1/2008#
Rahul Goel
Microsoft Cerfied Solution Developer .Net
Contact me at : rahul.g2510@gmail.com
|
|
|
|
|
Our application is running successfully on production server from last three month. Yesterday we received the error - Timeout Error: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. Our application is built in asp.net 2.0 and Sql server 2000. We are importing a large set of data in database using transaction object of ado.net. When I ran the same application on test database with same set of files I didn’t received any error. The problem is with sql server on production. We don’t want to set the time out property of command object. Can any one suggest me some solution?
Saurav
|
|
|
|
|
My first guess would be you need look at both your production database and your test database. I would guess that your test database perhaps doesn't have as many records as your prod database. If you prod database has a lot of records it can take longer to insert records. Especially if you have lots of keys or indexs in prod that you don't have in test.
I have found with large import systems, sometimes the best thing to do is to let the web site queue it up and then have something like a windows service process the files. This will keep you from having to worry about any web site timeouts.
In the end you may have to increase your transaction object timeout.
One last thing you can check, when an import is running check the prod server to see if some other process is blocking it. You can do this by running sp_who or sp_who2
Hope that helps.
Ben
|
|
|
|
|
hi,
i am tired searching the net for this solution,
i may b using the wrong query to find my solution..
i finally came here hope anyone can help me,and that i have posted my question in the proper frame
my question is ,
i would like to write an sql query to display data from two table found in my database,in a single table in the asp page..
plz help
thanks
|
|
|
|
|
data from 2 tables in a database and display it as single table in asp page.
You have to joins! Use inner join and join the two tables accordingly!
Gautham
|
|
|
|
|
I want to add a column in a table using dynamic query.
e.g
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[SP_InsertColumnToAudience]
@ColumnName varchar(50)
as
set nocount on
declare @qry varchar(250)
begin
set @qry='alter table tblAudience add aaa1aa varchar(100) NULL'
exec @qry
end
but it gives error........
any sugession........
Do good and have good.
|
|
|
|
|
|
Hi,
I need to read/write the flat files from oracle, as i have read some article saying that we can do it thru utl_file. I have created a directory in oracle as
Create directory oraload as 'c:\oraload'
As i have got a sample which is compiling by when executing its giving a error saying invalid file operation.
create or replace
PROCEDURE create_cmd_file IS
DirLoc VARCHAR2(30) := 'ORALOAD';
DayFile utl_file.file_type;
LogFile utl_file.file_type;
vErrMsg VARCHAR2(250):='WRITE CHECKUP';
vMessage VARCHAR2(250):='BY RAVINDRA';
BEGIN
DayFile := utl_file.fopen(DirLoc, 'execsqlldr.ksh','W');
LogFile := utl_file.fopen(DirLoc, 'log_list.dat','W');
utl_file.putf(dayfile, '%s\n',vErrMsg);
utl_file.putf(Logfile,'%s\n',vMessage);
utl_file.fclose(DayFile);
utl_file.fclose(LogFile);
EXCEPTION
WHEN OTHERS THEN
vErrMsg := SQLERRM;
vMessage := 'DailyLoad CREATE_CMD_FILE Failed With ERROR ' || vErrMsg;
DBMS_OUTPUT.PUT_LINE ('dba@psoug.org' || 'SQL Loader Failure Notification' || vMessage);
END create_cmd_file;
Ravi 
|
|
|
|
|
I haven't used UTL_FILE for several years - but I do remember that you have to add a "UTL_FILE_DIR" configuration value to the .ORA file to specify the list of folders that UTL_FILE operations can access.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
