Help with select query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help with select query

Author

Topic

CoachBarker
Posting Yak Master

170 Posts

Posted - 2009-03-15 : 11:57:18

I've been trying all morning to get this query to work:


ALTER PROCEDURE [dbo].[BQBO11MP_Summary] 
	@customer_bill_to varchar(10) ,
    @customer_delv_to varchar(10),
	@start_date datetime,
	@end_date	datetime
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     detail_record.ticket_number AS TicketNumber, cmDelvTo.customer_number AS DeliverToNumber, cmDelvTo.customer_name AS DeliverToName, 
                      cmDelvTo.city AS DeliverToCity, cmBillTo.customer_number AS BillToNumber, cmBillTo.customer_name AS BillToName, cmBillTo.city AS BillToCIty, 
                      CAST(detail_record.pickup_dt AS datetime) AS PickupDate, CAST(detail_record.deliver_dt AS datetime) AS DeliverDate, 
                      SUM(detail_record.pickup_weight) AS PickupPounds, detail_record.hauler_number AS HaulerNumber, detail_record.division AS Division,
                      load_pounds_detail.detail_ticket_number, load_pounds_detail.detail_pickup_date, load_pounds_detail.load_pounds_detail

FROM         detail_record INNER JOIN 
                      customer_master AS cmBillTo ON detail_record.customer_bill_to = cmBillTo.customer_number INNER JOIN
                      customer_master AS cmDelvTo ON detail_record.customer_delv_to = cmDelvTo.customer_number INNER JOIN
                      load_pound_detail ON detail_record.ticket_number = load_pound_detail.detail_ticket_number
 
WHERE     (detail_record.pickup_date >= @start_date) AND (@customer_bill_to IS NULL) AND (@customer_delv_to IS NULL) AND 
                      (detail_record.pickup_date < DATEADD(day, 1, @end_date)) OR
                      (detail_record.pickup_date >= @start_date) AND (detail_record.customer_bill_to = @customer_bill_to) AND (@customer_delv_to IS NULL) AND 
                      (detail_record.pickup_date < DATEADD(day, 1, @end_date)) OR
                      (detail_record.pickup_date >= @start_date) AND (detail_record.customer_delv_to = @customer_delv_to) AND (@customer_bill_to IS NULL) AND 
                      (detail_record.pickup_date < DATEADD(day, 1, @end_date)) OR
                      (detail_record.pickup_date >= @start_date) AND (detail_record.customer_delv_to = @customer_delv_to) AND 
                      (detail_record.customer_bill_to = @customer_bill_to) AND (detail_record.pickup_date < DATEADD(day, 1, @end_date))
GROUP BY cmBillTo.customer_number, cmBillTo.customer_name, cmBillTo.city, cmDelvTo.customer_number, cmDelvTo.customer_name, cmDelvTo.city, 
                      detail_record.pickup_dt, detail_record.ticket_number, detail_record.deliver_dt, detail_record.hauler_number, 
                      detail_record.pickup_date, detail_record.division
ORDER BY detail_record.pickup_dt

and I get the following error message no matter what I do:


Msg 4104, Level 16, State 1, Procedure BQBO11MP_Summary, Line 12
The multi-part identifier "load_pounds_detail.detail_ticket_number" could not be bound.
Msg 4104, Level 16, State 1, Procedure BQBO11MP_Summary, Line 12
The multi-part identifier "load_pounds_detail.detail_pickup_date" could not be bound.
Msg 4104, Level 16, State 1, Procedure BQBO11MP_Summary, Line 12
The multi-part identifier "load_pounds_detail.load_pounds_detail" could not be bound.

The parts in red belong together.

Thanks for the help
CoachBarker

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-03-15 : 12:06:11

quote:

Originally posted by CoachBarker

I've been trying all morning to get this query to work:


ALTER PROCEDURE [dbo].[BQBO11MP_Summary] 
	@customer_bill_to varchar(10) ,
    @customer_delv_to varchar(10),
	@start_date datetime,
	@end_date	datetime
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     detail_record.ticket_number AS TicketNumber, cmDelvTo.customer_number AS DeliverToNumber, cmDelvTo.customer_name AS DeliverToName, 
                      cmDelvTo.city AS DeliverToCity, cmBillTo.customer_number AS BillToNumber, cmBillTo.customer_name AS BillToName, cmBillTo.city AS BillToCIty, 
                      CAST(detail_record.pickup_dt AS datetime) AS PickupDate, CAST(detail_record.deliver_dt AS datetime) AS DeliverDate, 
                      SUM(detail_record.pickup_weight) AS PickupPounds, detail_record.hauler_number AS HaulerNumber, detail_record.division AS Division,
                      load_pounds_detail.detail_ticket_number, load_pounds_detail.detail_pickup_date, load_pounds_detail.load_pounds_detail

FROM         detail_record INNER JOIN 
                      customer_master AS cmBillTo ON detail_record.customer_bill_to = cmBillTo.customer_number INNER JOIN
                      customer_master AS cmDelvTo ON detail_record.customer_delv_to = cmDelvTo.customer_number INNER JOIN
                      load_pound_detail ON detail_record.ticket_number = load_pound_detail.detail_ticket_number
 
WHERE     (detail_record.pickup_date >= @start_date) AND (@customer_bill_to IS NULL) AND (@customer_delv_to IS NULL) AND 
                      (detail_record.pickup_date < DATEADD(day, 1, @end_date)) OR
                      (detail_record.pickup_date >= @start_date) AND (detail_record.customer_bill_to = @customer_bill_to) AND (@customer_delv_to IS NULL) AND 
                      (detail_record.pickup_date < DATEADD(day, 1, @end_date)) OR
                      (detail_record.pickup_date >= @start_date) AND (detail_record.customer_delv_to = @customer_delv_to) AND (@customer_bill_to IS NULL) AND 
                      (detail_record.pickup_date < DATEADD(day, 1, @end_date)) OR
                      (detail_record.pickup_date >= @start_date) AND (detail_record.customer_delv_to = @customer_delv_to) AND 
                      (detail_record.customer_bill_to = @customer_bill_to) AND (detail_record.pickup_date < DATEADD(day, 1, @end_date))
GROUP BY cmBillTo.customer_number, cmBillTo.customer_name, cmBillTo.city, cmDelvTo.customer_number, cmDelvTo.customer_name, cmDelvTo.city, 
                      detail_record.pickup_dt, detail_record.ticket_number, detail_record.deliver_dt, detail_record.hauler_number, 
                      detail_record.pickup_date, detail_record.division,load_pounds_detail.detail_ticket_number, load_pounds_detail.detail_pickup_date, load_pounds_detail.load_pounds_detail
ORDER BY detail_record.pickup_dt

and I get the following error message no matter what I do:


Msg 4104, Level 16, State 1, Procedure BQBO11MP_Summary, Line 12
The multi-part identifier "load_pounds_detail.detail_ticket_number" could not be bound.
Msg 4104, Level 16, State 1, Procedure BQBO11MP_Summary, Line 12
The multi-part identifier "load_pounds_detail.detail_pickup_date" could not be bound.
Msg 4104, Level 16, State 1, Procedure BQBO11MP_Summary, Line 12
The multi-part identifier "load_pounds_detail.load_pounds_detail" could not be bound.

The parts in red belong together.

Thanks for the help
CoachBarker

CoachBarker
Posting Yak Master

170 Posts

Posted - 2009-03-15 : 12:13:31

I grouped them in the query in SQL MS after I posted and got the same error just doubled.

Thanks for the help
CoachBarker

matty
Posting Yak Master

161 Posts

Posted - 2009-03-16 : 01:04:34

Your table name is load_pound_detail but in column load_pounds_detail.detail_pickup_date there is a 's'

quote:

Originally posted by CoachBarker

I've been trying all morning to get this query to work:


ALTER PROCEDURE [dbo].[BQBO11MP_Summary] 
	@customer_bill_to varchar(10) ,
    @customer_delv_to varchar(10),
	@start_date datetime,
	@end_date	datetime
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     detail_record.ticket_number AS TicketNumber, cmDelvTo.customer_number AS DeliverToNumber, cmDelvTo.customer_name AS DeliverToName, 
                      cmDelvTo.city AS DeliverToCity, cmBillTo.customer_number AS BillToNumber, cmBillTo.customer_name AS BillToName, cmBillTo.city AS BillToCIty, 
                      CAST(detail_record.pickup_dt AS datetime) AS PickupDate, CAST(detail_record.deliver_dt AS datetime) AS DeliverDate, 
                      SUM(detail_record.pickup_weight) AS PickupPounds, detail_record.hauler_number AS HaulerNumber, detail_record.division AS Division,
                      load_pounds_detail.detail_ticket_number, load_pounds_detail.detail_pickup_date, load_pounds_detail.load_pounds_detail

FROM         detail_record INNER JOIN 
                      customer_master AS cmBillTo ON detail_record.customer_bill_to = cmBillTo.customer_number INNER JOIN
                      customer_master AS cmDelvTo ON detail_record.customer_delv_to = cmDelvTo.customer_number INNER JOIN
                      load_pound_detail ON detail_record.ticket_number = load_pound_detail.detail_ticket_number
 
WHERE     (detail_record.pickup_date >= @start_date) AND (@customer_bill_to IS NULL) AND (@customer_delv_to IS NULL) AND 
                      (detail_record.pickup_date < DATEADD(day, 1, @end_date)) OR
                      (detail_record.pickup_date >= @start_date) AND (detail_record.customer_bill_to = @customer_bill_to) AND (@customer_delv_to IS NULL) AND 
                      (detail_record.pickup_date < DATEADD(day, 1, @end_date)) OR
                      (detail_record.pickup_date >= @start_date) AND (detail_record.customer_delv_to = @customer_delv_to) AND (@customer_bill_to IS NULL) AND 
                      (detail_record.pickup_date < DATEADD(day, 1, @end_date)) OR
                      (detail_record.pickup_date >= @start_date) AND (detail_record.customer_delv_to = @customer_delv_to) AND 
                      (detail_record.customer_bill_to = @customer_bill_to) AND (detail_record.pickup_date < DATEADD(day, 1, @end_date))
GROUP BY cmBillTo.customer_number, cmBillTo.customer_name, cmBillTo.city, cmDelvTo.customer_number, cmDelvTo.customer_name, cmDelvTo.city, 
                      detail_record.pickup_dt, detail_record.ticket_number, detail_record.deliver_dt, detail_record.hauler_number, 
                      detail_record.pickup_date, detail_record.division
ORDER BY detail_record.pickup_dt

and I get the following error message no matter what I do:


Msg 4104, Level 16, State 1, Procedure BQBO11MP_Summary, Line 12
The multi-part identifier "load_pounds_detail.detail_ticket_number" could not be bound.
Msg 4104, Level 16, State 1, Procedure BQBO11MP_Summary, Line 12
The multi-part identifier "load_pounds_detail.detail_pickup_date" could not be bound.
Msg 4104, Level 16, State 1, Procedure BQBO11MP_Summary, Line 12
The multi-part identifier "load_pounds_detail.load_pounds_detail" could not be bound.

The parts in red belong together.

Thanks for the help
CoachBarker

CoachBarker
Posting Yak Master

170 Posts

Posted - 2009-03-16 : 05:58:13

Yes I saw that after staring at it for anout a 1/2 hour yesterday, it is working now as it should.

Thanks for the help
CoachBarker

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources