Northwind - Problem With 'Order Details' Table
Oct 29, 2007
Hi Friends
I faced a problem when using Northwind sample Database. On of the table in the database is 'Order Details'. I am unable to query on the table because of the space in the name ('Order'<space> 'Details') of the table. When I remove the space in the name, the reports associated in Cognos are not working properly. Let me know whether there is any purpose to put the name like that and if I want to see the data in query mode how it is possible.
Thanks in advance
Satish
View 3 Replies
ADVERTISEMENT
Jul 26, 2007
Hi
I have came across a table in SQL server 2000 which named 'Order Details' in the sample 'Northwind' database which is available with the product. Am using the eval version . Generally no table name exist with a 'space' between the words. But the table 'Order Details' exist in the Northwind sample database.
Due to the naming convention i can't run sql queries on that. Is anyone aware of this type of issues.
Pls help
View 1 Replies
View Related
Apr 10, 2014
I have a query that calculate the total amount of order details based on a particular order:
Select a.OrderID,SUM(UnitPrice*Quantity-Discount)
From [Order Details]
Inner Join Orders a
On a.OrderID=[Order Details].OrderID
Group by a.OrderID
My question is what if I wanted to create a formula to something like:
UnitPrice * Quantity - DiscountAmount Where DiscountAmount = UnitPrice Quantity * Discount
Do I need to create a function for that? Also is it possible to have m y query as a table variable?
View 7 Replies
View Related
Sep 15, 2007
Hi,
Check this SQL
SELECT DISTINCT
TblOrder.CustomerUID,
TblOrder.OrderHiddenID,
TblPayment.PaymentAmount,
TblPayment.Result,
TblOrder.OrderID
FROM TblOrder
CROSS JOIN TblPayment
WHERE (TblOrder.CustomerUID = @IsCustomerID)
AND (TblOrder.OrderHiddenID = @IsHiddenID)
AND (TblPayment.Result = 'Pending')
AND (TblOrder.OrderID IN (SELECT MAX(TblOrder2.OrderID) FROM TblOrder TblOrder2))
one customer can have more than one orders.
So i need to select customer last inserted order details from database.So when i use above sql i returns null.what might be the reason for this
View 4 Replies
View Related
Nov 27, 2014
I have a requirement to present computed sales order status value
Parent table : consumer
Child table of consumer : sales order
Child table of sales order: items
Items table has status column with awaiting, accepted , rejected.
Status of the sales order should be set as following
If all items are rejected then sales order status=reject
Or if at least one item is accepted then sales order status = accept
Or if items status is awaiting then sales order status = pend
Query to find all sales order per consumer with computed order status for each sales order.
View 2 Replies
View Related
Aug 5, 2004
Hi all!
I'm trying to get some XML data into SQL Server but i ran into problem when inserting the data (multiple orders with multiple order details) using a single sproc. Is it possible, or do I have to do in some other way? :confused:
I simplified my example to this:
-----------------------------
--CREATE PROCEDURE sp_InsertOrders AS
DECLARE @docHandle INT, @xmlDoc VARCHAR(4000), @orderID INT
--DROP TABLE #Orders
CREATE TABLE #Orders
(
OrderId SMALLINT IDENTITY(1,1),
FkCustomerID SMALLINT NOT NULL,
OrderDate DATETIME NOT NULL
)
--DROP TABLE #OrderDetails
CREATE TABLE #OrderDetails
(
OrderDetailsId SMALLINT IDENTITY(1,1),
FkOrderID SMALLINT NOT NULL,
ProductID SMALLINT NOT NULL,
UnitPrice SMALLINT NOT NULL
)
Set @xmlDoc = '
<Orders>
<Order CustomerID="1" OrderDate="2004-04-01">
<OrderDetails ProductID="6" UnitPrice="19"/>
<OrderDetails ProductID="3" UnitPrice="11"/>
<OrderDetails ProductID="9" UnitPrice="7"/>
</Order>
<Order CustomerID="2" OrderDate="2004-04-12">
<OrderDetails ProductID="2" UnitPrice="24"/>
<OrderDetails ProductID="4" UnitPrice="13"/>
</Order>
</Orders>'
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDoc
INSERT INTO #Orders (FkCustomerID, OrderDate)
SELECT CustomerID, OrderDate
FROM OpenXML(@docHandle, 'Orders/Order', 3)
WITH (
CustomerID INTEGER,
OrderDate DATETIME
)
SET @OrderID = @@IDENTITY;
--INSERT INTO #OrderDetails (@OrderID, ProductID, UnitPrice)
SELECT @OrderID AS OrderID, ProductID, UnitPrice
FROM OpenXML(@docHandle, 'Orders/Order/OrderDetails', 3)
WITH (
ProductID INTEGER,
UnitPrice INTEGER
)
-----------------------------
All orders are inserted first which makes the use of @@IDENTITY incorrect (it works fine if you insert a single order with multiple order details). Since it was quite some time since I last worked with SQL I am not sure if am doing it the right way... :confused: :confused: Anybody out there who knows how to solve the problem?
Cheers,
Christian
View 2 Replies
View Related
Apr 10, 2015
I created am inventory table with few columns say, Servername, version, patching details, etc
I want a tracking of the table.
Let's say people are asked to modify the base table and I want a complete capture of the details modified and the session of the user ( ) who (system_user) is actually modifying the details.
View 1 Replies
View Related
Dec 7, 2007
I have the following data
MASTER
id
name
DETAIL
id
master_id
name
I want a perform a query where i can get all the rows of the master table which have no relationed rows in detail table.
How can I do that???
View 1 Replies
View Related
May 19, 2015
I never paid much attention to this before but I noticed this today in a new table I was creating.
For tables defined in the tabular model the table properties have something like SELECT Blah FROM TableName ORDER BY Blah Then in the tabular model the table's data is in the same order it was ordered by in the data source for the table.
I have a date table I setup and I noticed it is NOT respecting the sort order.
I have it sorted by DateID which sorts with the oldest date first and newest date as last row.However, the table that is imported and stored in the data model is not in that order.
I can of course manually sort the rows in BIDS/DataTools, but I find this discrepancy odd.
Would this have negative impacts on the EARLIER function for example if the data rows are not in the order specified?
View 8 Replies
View Related
Mar 27, 2008
Hi!
I recently run into a senario when a procedure quiered a table without a order by clause. Luckily it retrived data in the prefered order.
The table returns the data in the same order in SQL Manager "Open Table"
So I started to wonder what deterimins the sort order when there is no order by clause ?
I researched this for a bit but found no straight answers. My table has no PK, but an identiy column.
Peace.
/P
View 5 Replies
View Related
Jan 14, 2008
Hi all,
From the http://msdn.microsoft.com/en-us/library/bb384469.aspx (Walkthrough: Creating Stored Procedures for the Northwind Customers Table, I copied the following sql code:
--UpdateSPforNWcustomersTable.sql--
USE NORTHWIND
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'SelectCustomers' AND user_name(uid) = 'dbo')
DROP PROCEDURE dbo.[SelectCustomers]
GO
CREATE PROCEDURE dbo.[SelectCustomers]
AS
SET NOCOUNT ON;
SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax FROM dbo.Customers
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'InsertCustomers' AND user_name(uid) = 'dbo')
DROP PROCEDURE dbo.InsertCustomers
GO
CREATE PROCEDURE dbo.InsertCustomers
(
@CustomerID nchar(5),
@CompanyName nvarchar(40),
@ContactName nvarchar(30),
@ContactTitle nvarchar(30),
@Address nvarchar(60),
@City nvarchar(15),
@Region nvarchar(15),
@PostalCode nvarchar(10),
@Country nvarchar(15),
@Phone nvarchar(24),
@Fax nvarchar(24)
)
AS
SET NOCOUNT OFF;
INSERT INTO [dbo].[Customers] ([CustomerID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax]) VALUES (@CustomerID, @CompanyName, @ContactName, @ContactTitle, @Address, @City, @Region, @PostalCode, @Country, @Phone, @Fax);
SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax FROM Customers WHERE (CustomerID = @CustomerID)
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'UpdateCustomers' AND user_name(uid) = 'dbo')
DROP PROCEDURE dbo.UpdateCustomers
GO
CREATE PROCEDURE dbo.UpdateCustomers
(
@CustomerID nchar(5),
@CompanyName nvarchar(40),
@ContactName nvarchar(30),
@ContactTitle nvarchar(30),
@Address nvarchar(60),
@City nvarchar(15),
@Region nvarchar(15),
@PostalCode nvarchar(10),
@Country nvarchar(15),
@Phone nvarchar(24),
@Fax nvarchar(24),
@Original_CustomerID nchar(5)
)
AS
SET NOCOUNT OFF;
UPDATE [dbo].[Customers] SET [CustomerID] = @CustomerID, [CompanyName] = @CompanyName, [ContactName] = @ContactName, [ContactTitle] = @ContactTitle, [Address] = @Address, [City] = @City, [Region] = @Region, [PostalCode] = @PostalCode, [Country] = @Country, [Phone] = @Phone, [Fax] = @Fax WHERE (([CustomerID] = @Original_CustomerID));
SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax FROM Customers WHERE (CustomerID = @CustomerID)
GO
====================================================================================
I executed the above code in my SQL Server Management Studio Express (SSMSE) and I got the following error messages:
Msg 911, Level 16, State 1, Line 1
Could not locate entry in sysdatabases for database 'NORTHWIND'. No entry found with that name.
Make sure that the name is entered correctly.
===============================================================================================================
I know I recreated the NORTHWIND Database from a different Database before and I did not do anything for the entry in sysdatabases. How can I change the entry in sysdatabases for database 'NORTHWIND' now? Please help and advise.
Thanks in advance,
Scott Chang
View 5 Replies
View Related
Apr 23, 2008
I need to combine the details of two tables and narrow the data included.
in my tutor table i have
tutorLastName, tutorSessions and tutorSkills
in my term table i have
week, date, session_Name, tutorName
i need to combine the tales to show:
(from the term table) week, date,session_Name, tutorName,
(from the tutor table) tutorSkills.
i then need to narrow the table down so it shows only tutors with guitar skills and the term dats when the session_name is empty
i can run the two searches sepereately but cannot combine them i am tryin to use Where date = "whateverdate" AND session_name is null
AND tutorSkills = guitar
please help and tell me where im going wrong
View 2 Replies
View Related
Feb 19, 2007
Hi
Can i able to see the all table structure at once throught the Select
statement.
How?
thansk
asm
View 2 Replies
View Related
Jan 23, 2008
I Use the below QUERY to fetch the index details of a table from SQL Server 2000 database .
EXEC sp_helpindex 'mytable'
Is there any alternative for the above query .
Because the above query is not fetching some indexes in some version of SQL Server database .
I am trying the above query against SQL Server 2000,2005 and SQL Server 7 versions .
View 4 Replies
View Related
Apr 23, 2008
How to query Table and its column details
View 2 Replies
View Related
Mar 9, 2007
Hello all,
I had editted this post to ask a better question.
I had noticed that certain expressions won't work in details. Is this true ?
when I tried using an IIF expression, I always get a blank or an empty field even if it was a text.
could someone clarify this ? I don't know whether it is just me or is it a limitation.
Sincerely,
Bernard Ong
View 5 Replies
View Related
Mar 19, 2007
Hello,
I'm French, so sorry for the faults.
I want to make a report with a table who gives some figures of a family and elements of her subfamily. For example, for the Fruit family I have like subfamily the apple, the pear, the banana.
I tried to make a table with a group but to aggregate some figures of the subfamily dataset, calculations are complicated.
So I make two report:
- one for a dataset for the subfamily with a parameter (the family).
- one for the dataset of the family.
In the second, I want to put the under-report (the first) in the details of the table while passing in parameter the family.
But, I have an error when I want to see the result:"
Error: Impossible to post the under-report."
If you can help me that would be sympathetic!
Thanks
Mickaël
View 1 Replies
View Related
Dec 6, 2006
I want to export the datas of a table into a excel file using an Sql Query.
When I execute that query the Excel file should be created automatically in the given path with all the datas in that table.
Can anybody give me a solution for this problem?
View 4 Replies
View Related
Mar 21, 2008
Hi,
Can anyone tell me is it possible to put static data (a string) inside details section of a table?
I've tried to find some more pieces of information in the Report Definition Language Specification, but to no avail.
When I put some strings as a detail's cells values they are displayed in the preview window in VS.
But thay are invisible when I open my report using ReportViewer in my app.
So does it means that the only accepted value of a cell within the details section of a table is a name of the field from DataSet (something like =Fields!FieldName.Value) ?
Regards,
Stan
View 1 Replies
View Related
Apr 16, 2007
I have created a linked server using my local SQL2005. The linked server can be seen as a linked server, but the database can not been expanded to see the tables, stored procedures, views, and other details regarding the linked server. The only method that I have found that will allow me to see all the details is to use XP's Remote Desktop Connection to access the database. Has anyone else experienced this problem, if so, how did you resolve it?
View 1 Replies
View Related
Jan 8, 2007
I am using table object to present report.
table row1 has all the column names and table row2 has the data.
How can i put a horizontal line between column names and the data rows.
Thank you very much.
View 1 Replies
View Related
Mar 3, 2015
I have a scenario,
We have equipment table which stores Equipment_ID,Code,Parent_Id etc..for each Equipment_ID there is a Parent_Id. The PK is Equipment_ID Now i want to select the Code for the Parent_Id which also sits in the same table. All the Parent_Id's also are Equipment_ID's.
Equipment table looks like :
Equipment_ID Code DescriptionTreeLevelParent_Id
6132 S2611aaa 4 6130
11165 V2546bbb 3 1022
15211 PF_EUccc 5 15192
39539 VP266ddd 4 35200
5696 KA273eee 3 3215
39307 VM2611fff 4 35163
39398 IK264ggg 4 35177
There is another table for Equipment_Tree which has got Equipment_Tree_ID,Parent_Id and Equipment_ID does not has the Code here.
Select query where i need to select the Code for all Parent_Id's.
View 8 Replies
View Related
Apr 4, 2008
Hi.
I building some reports with SSRS, and showing data in a table. The table has one Details row that forms at the end multiple rows with the tree view (+ and -).
Is there a way to open the tree view upwards and not downwards so that the children of level 2, 3 etc come over the parent row?
I've goggled it everywhere, but I don't seem to find the answer. If anyone could help me out, I would appreciated.
Thanks
Novi
View 2 Replies
View Related
Apr 24, 2015
I am trying to create a matrix report to work similar to a pivot table, where when a user clicks on the count, the details are displayed. When I add the action to open a sub-report, how do I pass in the parameter values for the group that was selected? meaning if in row group- I have company name, and column group I have job title, when the count gets clicked, i need to pass in the company name for the row that was clicked along with the column group. How can I proceed?
View 2 Replies
View Related
Nov 18, 2014
I have 2 tables: Order(ID, Quantity) and Product(ID,Name, Price) and I want to add a calculated field in Order table based on the price column in the Product table. How do i do that?
this query returns the values i want in the table.
select a.quantity * b.price
from tblCustomerPurchases as a
join tblProduct as b
on a.ID=b.ID
View 17 Replies
View Related
May 16, 2008
Hey,
I'm looking for a good way to rewrite my T-SQL code with 'bcp' to SSIS package, any help would be greatly appreciated?
I have table1 contain account numbers and output-filename for each account,
I need to join table2 and table3 to get data for each account in table1, and then export as a txt file.
Here is my code using bcp (bulk copy)
DECLARE @RowCnt int,
@TotalRows int,
@AccountNumber char(11),
@sql varchar(8000),
@date char(10),
@ArchPath varchar(500)
SET @RowCnt = 1
SET @date = CONVERT(CHAR(10),GETDATE(),110)
SET @ArchPath = '\D$EDATAWorkFoldersSendSendData'
SELECT @TotalRows = count(*) FROM table1
--select @ArchPath
WHILE (@RowCnt <= @TotalRows)
BEGIN
SELECT @AccountNumber = AccountNumber, @output_filename FROM table1 WHERE Identity_Number = @RowCnt
--PRINT @AccountNumber --test
SELECT @sql = N'bcp "SELECT h.HeaderText, d.RECORD FROM table2 d INNER JOIN table3 h ON d.HeaderID = h.HeaderID WHERE d.ccountNumber = '''
+ @AccountNumber+'''" queryout "'+@ArchPath+ @output_filename + '.txt" -T -c'
--PRINT @sql
EXEC master..xp_cmdshell @sql
SELECT @RowCnt = @RowCnt + 1
END
View 7 Replies
View Related
Jun 8, 1999
I have a database with 200+ tables. How can I get table order (query) from the system tables so that I know which table I should insert data first.
Thanks,
View 2 Replies
View Related
Oct 23, 2007
I noticed that some queries against an mssql db require the tables in the FROM part of the statement to be in a particular order. Does anyone know why?
For example SELECT * FROM table1, table2, table3 WHERE <blah>
May throw an error (Unknown table table3 [I can't remember the exact verbiage of the error]), while simply rearranging the table order to:
SELECT * FROM table3, table1, table2 WHERE <blah> will work.
It seems like the error has something to do with how mssql handles the joins of the tables, but I can't seem to find any documentation about it.
View 2 Replies
View Related
Mar 19, 2007
I have a table
merchant contractbr date
a 2333 1/1/2005
a A34 3/12/2006
a R78 2/1/2005
..
b
b
b
c
c
c
.....
different merchant has different number of contracts.
I want to order the contracts for merchants according to the date.
the result table should look like:
merchant contractbr date order
a 2333 1/1/2005 1
a R78 2/1/2005 2
a A34 3/12/2006 3
..
b 1
b 2
b 3
c 1
c 2
c 3
c 4
c 5
.....
Thanks
View 5 Replies
View Related
Dec 16, 2005
Greetings,I have an application that need to get all the userdefined child tables first before their parents.I wrote a query, given in this newsgroup only, as belowSELECT o.nameFROM sysobjects oWHERE o.type='U'ORDER BY case WHEN exists ( SELECT *FROM sysforeignkeys fWHERE o.id = f.fkeyid )THEN 1ELSE 0end, o.namegoWhen i try to truncate the first table of the list, it still tells methat tha table is being referenced by foreign key in another table. Mymain job is to truncate all the user defined tables before loading datainto them.Is there something wrong in the query? Or if someone can tell me abetter approach.Any help will be appreciated.TIA
View 5 Replies
View Related
Mar 23, 2006
When I say to sort on a datetime field on descending order, the date is sorted. However, the time difference is not reflected in the results.
Any way, we can fix it.
i.e. If I have two records with the same dates but different times, the sorting order is not considering the time.
View 1 Replies
View Related
Jun 13, 2000
Without inserting an additional column with a timestamp or adding an IDENTITY column, can you tell which row was inserted first in a table?
Angel
View 2 Replies
View Related
May 22, 2002
SQL Trasnsactional Replication.
I've set up a transactional replication, but I need to specify in which order the tables are to be replicated (tables with foreign keys last).
How/where do set table order ?
/Carl
View 1 Replies
View Related
