February 6, 2006 at 3:57 pm
Hello:
I am able to get the view to work without the addition of p21_view_oe_hdr_salesrep and related joins.
I was really after an email address that can only pe picked up by relating the *_hdr_salerep table to a users table but only by using the the p21_view_contacts table again to cross reference salerep id to user id. I've given up on this ultimate goal for the moment and would be very happy if I could get this to generate the view with the salerep ID.
I've tried a variety of things including generating the statement in enterprise manager... where it works as a query. As soon as I move it to SQL Query Analyser and add in the db name, it will not function the error i get is :
Server: Msg 170, Level 15, State 1, Procedure JemWorldshipView, Line 26
Line 26: Incorrect syntax near '='.
I am not a SQL expert so will one of you experts take a look at this and advise where I am going wrong.
Thanks
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER VIEW dbo.JemWorldshipView
AS
SELECT
CommerceCenter.dbo.p21_view_oe_pick_ticket.pick_ticket_no,
CommerceCenter.dbo.p21_view_oe_hdr.order_no,
CommerceCenter.dbo.p21_view_oe_hdr.customer_id,
CommerceCenter.dbo.p21_view_oe_hdr.ship2_name,
CommerceCenter.dbo.p21_view_oe_hdr.ship2_add1,
CommerceCenter.dbo.p21_view_oe_hdr.ship2_add2,
CommerceCenter.dbo.p21_view_oe_hdr.ship2_city,
CommerceCenter.dbo.p21_view_oe_hdr.ship2_state,
CommerceCenter.dbo.p21_view_oe_hdr.ship2_zip,
CommerceCenter.dbo.p21_view_oe_hdr.po_no,
CommerceCenter.dbo.p21_view_oe_pick_ticket.carrier_id AS carrier,
CommerceCenter.dbo.p21_view_oe_pick_ticket.carrier_id AS supplier,
CommerceCenter.dbo.p21_view_oe_pick_ticket.carrier_id,
CommerceCenter.dbo.p21_view_oe_hdr.cod_flag,
CommerceCenter.dbo.p21_view_oe_hdr.terms,
CommerceCenter.dbo.p21_view_oe_hdr.ship2_country,
CommerceCenter.dbo.p21_view_oe_hdr.ship_to_phone,
CommerceCenter.dbo.p21_view_oe_hdr.delivery_instructions,
CommerceCenter.dbo.p21_view_contacts.email_address, (CASE WHEN charindex('@', [email_address])
> 0 THEN [email_address] ELSE 'shiprec@jhwright.com' END) alternate_address,
CommerceCenter.dbo.p21_view_oe_hdr_salesrep.rep_id
QVN='Y',
failure_address = 'shiprec@jhwright.com',
FROM CommerceCenter.dbo.p21_view_oe_pick_ticket INNER JOIN
CommerceCenter.dbo.p21_view_oe_hdr ON CommerceCenter.dbo.p21_view_oe_pick_ticket.order_no = CommerceCenter.dbo.p21_view_oe_hdr.order_no LEFT OUTER JOIN
CommerceCenter.dbo.p21_view_oe_hdr_salesrep ON CommerceCenter.dbo.p21_view_oe_pick_ticket.order_no = CommerceCenter.dbo.p21_view_oe_hdr_salesrep.order_number LEFT OUTER JOIN
CommerceCenter.dbo.p21_view_contacts ON CommerceCenter.dbo.p21_view_oe_hdr.contact_id = CommerceCenter.dbo.p21_view_contacts.id
WHERE (CommerceCenter.dbo.p21_view_oe_pick_ticket.ship_date IS NULL) AND (CommerceCenter.dbo.p21_view_oe_pick_ticket.delete_flag = 'N')
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
February 6, 2006 at 4:08 pm
CommerceCenter.dbo.p21_view_oe_hdr_salesrep.rep_id
QVN='Y',
Missing a comma ? After CommerceCenter.dbo.p21_view_oe_hdr_salesrep.rep_id ?
February 6, 2006 at 11:52 pm
If you look at the last part of your select, you have a comma after the last column you select:
"Select
...
failure_address = 'shiprec@jhwright.com',
FROM CommerceCenter..."
The last column in a select list cannot have a comma. The correct syntax should be as follows:
"
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER VIEW dbo.JemWorldshipView
AS
SELECT
CommerceCenter.dbo.p21_view_oe_pick_ticket.pick_ticket_no,
CommerceCenter.dbo.p21_view_oe_hdr.order_no,
CommerceCenter.dbo.p21_view_oe_hdr.customer_id,
CommerceCenter.dbo.p21_view_oe_hdr.ship2_name,
CommerceCenter.dbo.p21_view_oe_hdr.ship2_add1,
CommerceCenter.dbo.p21_view_oe_hdr.ship2_add2,
CommerceCenter.dbo.p21_view_oe_hdr.ship2_city,
CommerceCenter.dbo.p21_view_oe_hdr.ship2_state,
CommerceCenter.dbo.p21_view_oe_hdr.ship2_zip,
CommerceCenter.dbo.p21_view_oe_hdr.po_no,
CommerceCenter.dbo.p21_view_oe_pick_ticket.carrier_id AS carrier,
CommerceCenter.dbo.p21_view_oe_pick_ticket.carrier_id AS supplier,
CommerceCenter.dbo.p21_view_oe_pick_ticket.carrier_id,
CommerceCenter.dbo.p21_view_oe_hdr.cod_flag,
CommerceCenter.dbo.p21_view_oe_hdr.terms,
CommerceCenter.dbo.p21_view_oe_hdr.ship2_country,
CommerceCenter.dbo.p21_view_oe_hdr.ship_to_phone,
CommerceCenter.dbo.p21_view_oe_hdr.delivery_instructions,
CommerceCenter.dbo.p21_view_contacts.email_address, (CASE WHEN charindex('@', [email_address])
> 0 THEN [email_address] ELSE 'shiprec@jhwright.com' END) alternate_address,
CommerceCenter.dbo.p21_view_oe_hdr_salesrep.rep_id
QVN='Y',
failure_address = 'shiprec@jhwright.com'
FROM CommerceCenter.dbo.p21_view_oe_pick_ticket INNER JOIN
CommerceCenter.dbo.p21_view_oe_hdr ON CommerceCenter.dbo.p21_view_oe_pick_ticket.order_no = CommerceCenter.dbo.p21_view_oe_hdr.order_no LEFT OUTER JOIN
CommerceCenter.dbo.p21_view_oe_hdr_salesrep ON CommerceCenter.dbo.p21_view_oe_pick_ticket.order_no = CommerceCenter.dbo.p21_view_oe_hdr_salesrep.order_number LEFT OUTER JOIN
CommerceCenter.dbo.p21_view_contacts ON CommerceCenter.dbo.p21_view_oe_hdr.contact_id = CommerceCenter.dbo.p21_view_contacts.id
WHERE (CommerceCenter.dbo.p21_view_oe_pick_ticket.ship_date IS NULL) AND (CommerceCenter.dbo.p21_view_oe_pick_ticket.delete_flag = 'N')
"
Thanks,
Eric
February 7, 2006 at 7:02 am
Thanks, but I took the comma out and still have the same issue. I'll play with it some more today and see what I can do. Any additional comments are welcome.
-Ray
February 7, 2006 at 7:31 am
Go it working, amazing how you see things better after taking a break from it. Comma missing, one extra comma plus an invalid column name and it seems to give me what I need now.
here is what worked:
-thanks all
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER VIEW dbo.JemWorldshipView
AS
SELECT
CommerceCenter.dbo.p21_view_oe_pick_ticket.pick_ticket_no,
CommerceCenter.dbo.p21_view_oe_hdr.order_no,
CommerceCenter.dbo.p21_view_oe_hdr.customer_id,
CommerceCenter.dbo.p21_view_oe_hdr.ship2_name,
CommerceCenter.dbo.p21_view_oe_hdr.ship2_add1,
CommerceCenter.dbo.p21_view_oe_hdr.ship2_add2,
CommerceCenter.dbo.p21_view_oe_hdr.ship2_city,
CommerceCenter.dbo.p21_view_oe_hdr.ship2_state,
CommerceCenter.dbo.p21_view_oe_hdr.ship2_zip,
CommerceCenter.dbo.p21_view_oe_hdr.po_no,
CommerceCenter.dbo.p21_view_oe_pick_ticket.carrier_id AS carrier,
CommerceCenter.dbo.p21_view_oe_pick_ticket.carrier_id AS supplier,
CommerceCenter.dbo.p21_view_oe_pick_ticket.carrier_id,
CommerceCenter.dbo.p21_view_oe_hdr.cod_flag,
CommerceCenter.dbo.p21_view_oe_hdr.terms,
CommerceCenter.dbo.p21_view_oe_hdr.ship2_country,
CommerceCenter.dbo.p21_view_oe_hdr.ship_to_phone,
CommerceCenter.dbo.p21_view_oe_hdr.delivery_instructions,
CommerceCenter.dbo.p21_view_contacts.email_address, (CASE WHEN charindex('@', [email_address])
> 0 THEN [email_address] ELSE 'shiprec@jhwright.com' END) alternate_address,
CommerceCenter.dbo.p21_view_oe_hdr_salesrep.salesrep_id,
QVN='Y',
failure_address = 'shiprec@jhwright.com'
FROM CommerceCenter.dbo.p21_view_oe_pick_ticket INNER JOIN
CommerceCenter.dbo.p21_view_oe_hdr ON CommerceCenter.dbo.p21_view_oe_pick_ticket.order_no = CommerceCenter.dbo.p21_view_oe_hdr.order_no LEFT OUTER JOIN
CommerceCenter.dbo.p21_view_oe_hdr_salesrep ON CommerceCenter.dbo.p21_view_oe_pick_ticket.order_no = CommerceCenter.dbo.p21_view_oe_hdr_salesrep.order_number LEFT OUTER JOIN
CommerceCenter.dbo.p21_view_contacts ON CommerceCenter.dbo.p21_view_oe_hdr.contact_id = CommerceCenter.dbo.p21_view_contacts.id
WHERE (CommerceCenter.dbo.p21_view_oe_pick_ticket.ship_date IS NULL) AND (CommerceCenter.dbo.p21_view_oe_pick_ticket.delete_flag = 'N')
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply