Link to home
Create AccountLog in
Avatar of Jiggens

asked on

SQL Syntax

Alright i trying to just simply add a field to my sql syntax here but it seems not to be working it works fine minus the line  "   dbo.p21_view_invoice_line.other_charge_item"  The tables are already added and they are working but this one line wont let me alter the code and i get an error . See Attached !!!

another error i get is Msg 4104, Level 16, State 1, Procedure p21_view_salesrep_commissions_report, Line 9
The multi-part identifier "invoice_line.other_charge_item" could not be bound.

SELECT     invoice_hdr.company_no,, invoice_hdr.invoice_date, dbo.invoice_hdr_salesrep.invoice_number, 
                      dbo.invoice_hdr_salesrep.salesrep_id, dbo.invoice_hdr_salesrep.commission_amount, dbo.invoice_hdr_salesrep.commission_paid, 
                      dbo.invoice_hdr_salesrep.commission_percentage, dbo.invoice_hdr_salesrep.commission_cost, dbo.invoice_hdr_salesrep.extended_price, 
                      CASE oe_hdr.order_type WHEN 1877 THEN dealer_commission.dealer_commission_amt_due ELSE (invoice_hdr_salesrep.extended_price - invoice_hdr_salesrep.commission_cost)
                       END AS profit, 
                      CASE WHEN invoice_hdr_salesrep.extended_price = 0 THEN 0 ELSE CASE WHEN oe_hdr.order_type = 1877 THEN ((dealer_commission.dealer_commission_amt_due
                       / invoice_hdr_salesrep.extended_price) * 100) ELSE ((invoice_hdr_salesrep.extended_price - invoice_hdr_salesrep.commission_cost) 
                      / invoice_hdr_salesrep.extended_price) * 100 END END AS profit_percent, invoice_hdr.period_fully_paid, invoice_hdr.year_fully_paid, 
                      CASE WHEN invoice_hdr.invoice_adjustment_type = 'M' THEN dealer_commission.paid_in_full_flag ELSE invoice_hdr.paid_in_full_flag END AS paid_in_full_flag,
                       invoice_hdr.year_for_period, invoice_hdr.period, invoice_hdr.net_due_date, invoice_hdr.branch_id, invoice_hdr.terms_taken_home AS terms_taken, 
                      invoice_hdr.allowed_home AS allowed, invoice_hdr.bad_debt_amount_home AS bad_debt_amount, 
                      invoice_hdr.memo_amount_home AS memo_amount, invoice_hdr.date_paid, invoice_hdr.other_charge_amount_home AS other_charge_amount, 
                      invoice_hdr.tax_amount_home AS tax_amount, invoice_hdr.consolidated, invoice_hdr.order_no, invoice_hdr.customer_id, 
                      dbo.customer.customer_name, dbo.contacts.last_name, dbo.contacts.first_name, dbo.contacts.mi, COALESCE (invoice_line_freight_out.freight_home, 
                      0) AS freight_out, COALESCE (invoice_line_freight_in.freight_home, 0) AS freight_in, 
                      CASE WHEN invoice_hdr.invoice_adjustment_type <> 'M' THEN 'I' ELSE invoice_hdr.invoice_adjustment_type END AS cc_order_type, 
                      CASE salesrep_commission.commission_paid_on WHEN 'C' THEN 'Cash Receipts' WHEN 'I' THEN 'Invoice Generation' END AS based_on, 
                      DRV_ConsInv.date_paid AS cons_inv_date_paid, invoice_hdr.date_created, invoice_hdr.date_last_modified, invoice_hdr.last_maintained_by, 
                      dbo.commission_defaults.commission_paid_on AS comm_def_paid_on, dbo.branch.branch_description, dbo.oe_hdr.order_type, 
FROM         dbo.p21_view_invoice_hdr AS invoice_hdr INNER JOIN
             WITH (NOLOCK) ON = invoice_hdr.company_no INNER JOIN
                      dbo.invoice_hdr_salesrep WITH (NOLOCK) ON dbo.invoice_hdr_salesrep.invoice_number = invoice_hdr.invoice_no INNER JOIN
                      dbo.contacts WITH (NOLOCK) ON = dbo.invoice_hdr_salesrep.salesrep_id INNER JOIN
                      dbo.customer WITH (NOLOCK) ON dbo.customer.customer_id = invoice_hdr.customer_id_number AND 
                      dbo.customer.company_id = invoice_hdr.company_no INNER JOIN
                      dbo.branch WITH (NOLOCK) ON dbo.branch.company_id = invoice_hdr.company_no AND 
                      dbo.branch.branch_id = invoice_hdr.branch_id LEFT OUTER JOIN
                      dbo.oe_hdr WITH (NOLOCK) ON dbo.oe_hdr.order_no = invoice_hdr.order_no LEFT OUTER JOIN
                      dbo.salesrep_commission WITH (NOLOCK) ON dbo.salesrep_commission.company_id = invoice_hdr.company_no AND 
                      dbo.salesrep_commission.salesrep_id = dbo.invoice_hdr_salesrep.salesrep_id LEFT OUTER JOIN
                          (SELECT     SUM(extended_price_home) AS freight_home, invoice_no
                            FROM          dbo.p21_view_invoice_line(NOLOCK) AS p21_view_invoice_line_1
                            WHERE      (dbo.p21_view_invoice_line.invoice_line_type = 929)
                            GROUP BY invoice_no) AS invoice_line_freight_out ON invoice_line_freight_out.invoice_no = invoice_hdr.invoice_no LEFT OUTER JOIN
                          (SELECT     SUM(extended_price_home) AS freight_home, invoice_no
                            FROM          dbo.p21_view_invoice_line(NOLOCK) AS p21_view_invoice_line_1
                            WHERE      (dbo.p21_view_invoice_line.invoice_line_type = 928)
                            GROUP BY invoice_no) AS invoice_line_freight_in ON invoice_line_freight_in.invoice_no = invoice_hdr.invoice_no LEFT OUTER JOIN
                      dbo.p21_view_invoice_line WITH (NOLOCK) ON dbo.p21_view_invoice_line.invoice_no = invoice_hdr.invoice_no AND 
                      dbo.p21_view_invoice_line.item_id = 'DOWNPAYMENT' AND dbo.p21_view_invoice_line.item_desc = 'Downpayment' AND 
                      dbo.p21_view_invoice_line.order_no IS NOT NULL AND dbo.p21_view_invoice_line.oe_line_number = 0 LEFT OUTER JOIN
                          (SELECT     IH_ConsInv.date_paid, dbo.consolidated_invoices_xref.component_invoice_no
                            FROM          dbo.consolidated_invoices_xref WITH (NOLOCK) INNER JOIN
                                                   dbo.invoice_hdr AS IH_ConsInv WITH (NOLOCK) ON IH_ConsInv.invoice_no = dbo.consolidated_invoices_xref.consolidated_invoice_no) 
                      AS DRV_ConsInv ON DRV_ConsInv.component_invoice_no = invoice_hdr.invoice_no LEFT OUTER JOIN
                      dbo.dealer_commission WITH (NOLOCK) ON dbo.dealer_commission.invoice_no = invoice_hdr.invoice_no LEFT OUTER JOIN
                      dbo.commission_defaults WITH (NOLOCK) ON dbo.commission_defaults.company_id = invoice_hdr.company_no
WHERE     (invoice_hdr.approved = 'Y') AND (invoice_hdr.invoice_adjustment_type NOT IN ('B', 'X', 'P')) AND (dbo.invoice_hdr_salesrep.delete_flag = 'N')

Open in new window

Avatar of David Todd
David Todd
Flag of New Zealand image


The above is a fair amount of text.

Can you outline what its trying to do, and what version of what database?

What I'm thinking is that the query parser might be on the limit, and adding another field ends up confusing it. That is, it might work better if you divided the above into chunks.

Avatar of Jiggens


Its on SQL 2005 server, and all im trying to do is add one field  invoice_line.other_charge_item to so that it will be part of my view.

So that i may have it in my crystal report.  Could the problem be this part of the code, since its saying it cant be bound .

LEFT  JOIN (SELECT SUM(extended_price_home) freight_home, invoice_no 
            FROM p21_view_invoice_line (NOLOCK)
            WHERE p21_view_invoice_line.invoice_line_type = 928 GROUP BY invoice_no) 
            AS invoice_line_freight_in ON invoice_line_freight_in.invoice_no = invoice_hdr.invoice_no
	LEFT JOIN p21_view_invoice_line (NOLOCK) ON p21_view_invoice_line.invoice_no = invoice_hdr.invoice_no
		  and p21_view_invoice_line.item_id = 'DOWNPAYMENT'
		  and p21_view_invoice_line.item_desc = 'Downpayment'
		  and p21_view_invoice_line.order_no is not null
		  and p21_view_invoice_line.oe_line_number = 0 

Open in new window


Are you creating a view in SQL 2005, or is this a query from Crystal Reports?

Avatar of ee_rlee
Flag of Philippines image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Jiggens


I tried the code below and get the same results, i know i am missing something else?

Msg 156, Level 15, State 1, Line 34
Incorrect syntax near the keyword 'AS'.
Msg 156, Level 15, State 1, Line 38
Incorrect syntax near the keyword 'AS'.
Msg 156, Level 15, State 1, Line 47
Incorrect syntax near the keyword 'AS'.