Click here to Skip to main content
14,548,337 members
Rate this:
Please Sign up or sign in to vote.
See more:
SELECT o.ORD_CUS_ID,
min(ODD_RequestDate) as ODD_RequestDate
FROM
Orders o JOIN Orderdetails as c
ON
c.ODD_ORD_ID = o.ORD_ID
GROUP BY o.ORD_CUS_ID

i have used the above code to get the min values of request date.
the request date attribute is in orderdetails table.
i have stored procedure by name usp_orders, which have both orders table and orderdetails table,in which i have to keep the above relation to get the min value for request date.

but iam getting the following error when i execute the above code
Msg 208, Level 16, State 1, Line 1
Invalid object name 'OrdersPhi'.
Posted
Updated 24-May-10 5:05am
v2
Comments
Ankur\m/ 24-May-10 3:04am
   
So is it working now?
Rate this:
Please Sign up or sign in to vote.

Solution 1

sridevikavalakuntla wrote:
Invalid object name 'OrdersPhi'


There is nothing called 'OrderPhi' in the query you have given.
Check your stored procedure if it is typed correctly.
   
Comments
sridevikavalakuntla 24-May-10 2:39am
   
sorry its just orders only, i have just changed the table name to see whether the query i have written itself is wrong. its only orders
sridevikavalakuntla 24-May-10 3:15am
   
yep its working,
but i need to write this in my stored proc by name getorders(in which iam getting error).the code i have used is
CREATE PROCEDURE [dbo].[test_GetOrders]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
(SELECT
ORD_ID,
ORD_NO,
ORD_CUS_ID,
CUS_Name,
CUS_ECCNumber,
CUS_TINCSTNumber,
CUS_OfficeAddress,
CUS_FactoryAddress,
ORD_AdvisedBy,
ORD_DAN_No,
ORD_Qty,
ORD_Status,
ORD_IsDelta,
ORD_CUS_PONo,
ORD_CreatedBy,
dbo.fn_GetDatePart(ORD_CreatedDate) AS 'ORD_CreatedDate',
ORD_LastModifiedBy,
ORD_LastModifiedDate,
isnull(ORD_Date,ORD_CreatedDate) AS 'ORD_Date'
FROM
dbo.Orders as o JOIN Orderdetails as c
ON
c.ODD_ORD_ID = o.ORD_ID
GROUP BY o.ORD_CUS_ID)
i have to give custid as input(manually) so i should get the oreders related to that custid and the min request date.
Rate this:
Please Sign up or sign in to vote.

Solution 2

sridevikavalakuntla wrote:
i have to give custid as input(manually) so i should get the oreders related to that custid and the min request date.


So pass it as a parameter to the stored procedure.

CREATE PROCEDURE [dbo].[test_GetOrders] 
    @CUST_ID int
AS
    ........
    ........
    WHERE o.ORD_CUS_ID = @CUST_ID


I would suggest you to buy a good SQL book or read articles on the web because it's the most basic thing that you should know if you are writing an SQL Query.
   
Comments
sridevikavalakuntla 24-May-10 5:25am
   
iam getting it but while coming to the output i should get only the oders and request date when i give the cust_id/name as input. but iam getting all the column names that are in the table
Rate this:
Please Sign up or sign in to vote.

Solution 3

sridevikavalakuntla wrote:
iam getting it but while coming to the output i should get only the oders and request date when i give the cust_id/name as input. but iam getting all the column names that are in the table


That's because you are selecting all columns in your query.

sridevikavalakuntla wrote:
(SELECT ORD_ID, ORD_NO, ORD_CUS_ID, CUS_Name, CUS_ECCNumber, CUS_TINCSTNumber, CUS_OfficeAddress, CUS_FactoryAddress, ORD_AdvisedBy, ORD_DAN_No, ORD_Qty, ORD_Status, ORD_IsDelta, ORD_CUS_PONo, ORD_CreatedBy, dbo.fn_GetDatePart(ORD_CreatedDate) AS 'ORD_CreatedDate', ORD_LastModifiedBy, ORD_LastModifiedDate, isnull(ORD_Date,ORD_CreatedDate) AS 'ORD_Date'


Select only the column names you want.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100