sql server new users: Hi

i am running the following query generated using crystal reports 8.5
designet,win98,sql server 2000.

i get the following error message. i am not sure where i could put the alias
for the table

"The column prefix sales_cr_memo_header does not match with a table name or
alias used in the query "

**

Any help is really appreciated
SELECT
National_Oxygen_TEST_Customer_Budget."Year",
National_Oxygen_TEST_Customer_Budget."Jan Amount",
National_Oxygen_TEST_Customer_Budget."Feb Amount",
National_Oxygen_TEST_Customer_Budget."Mar Amount",
National_Oxygen_TEST_Customer_Budget."Apr Amount",
National_Oxygen_TEST_Customer_Budget."May Amount",
National_Oxygen_TEST_Customer_Budget."Jun Amount",
National_Oxygen_TEST_Customer_Budget."Jan Volume",
National_Oxygen_TEST_Customer_Budget."Feb Volume",
National_Oxygen_TEST_Customer_Budget."Mar Volume",
National_Oxygen_TEST_Customer_Budget."Apr Volume",
National_Oxygen_TEST_Customer_Budget."May Volume",
National_Oxygen_TEST_Customer_Budget."Jun Volume",
National_Oxygen_TEST_Item."No_",
National_Oxygen_TEST_Item."Item Category Code",
National_Oxygen_TEST_Item."Is Bulk",
National_Oxygen_TEST_Item."Sales SM3",
National_Oxygen_TEST_Item."Sales NM3",
National_Oxygen_TEST_Item."Production SM3",
National_Oxygen_TEST_Item."Production NM3",
National_Oxygen_TEST_Item_Category."Code",
National_Oxygen_TEST_Item_Category."Description",
National_Oxygen_TEST_Sales_Cr_Memo_Header."No_",
National_Oxygen_TEST_Sales_Cr_Memo_Header."Sell-to Customer No_",
National_Oxygen_TEST_Sales_Cr_Memo_Header."Posting Date",
National_Oxygen_TEST_Sales_Cr_Memo_Line."No_",
National_Oxygen_TEST_Sales_Cr_Memo_Line."Document No_",
National_Oxygen_TEST_Sales_Cr_Memo_Line."Line Amount",
National_Oxygen_TEST_Sales_Cr_Memo_Line."Item Category Code",
National_Oxygen_TEST_Sales_Invoice_Line."Sell-to Customer No_",
National_Oxygen_TEST_Sales_Invoice_Line."No_",
National_Oxygen_TEST_Sales_Invoice_Line."Line Amount",
National_Oxygen_TEST_Sales_Invoice_Header."No_",
National_Oxygen_TEST_Sales_Invoice_Header."Sell-to Customer No_",
National_Oxygen_TEST_Sales_Invoice_Header."Posting Date"
FROM
{oj (((("noxtest"."dbo"."National Oxygen TEST$Sales Invoice Header"
National_Oxygen_TEST_Sales_Invoice_Header INNER JOIN
"noxtest"."dbo"."National Oxygen TEST$Sales Invoice Line"
National_Oxygen_TEST_Sales_Invoice_Line ON
National_Oxygen_TEST_Sales_Invoice_Header."No_" =
National_Oxygen_TEST_Sales_Invoice_Line."Document No_")

INNER JOIN "noxtest"."dbo"."National Oxygen TEST$Sales Cr_Memo Line"
National_Oxygen_TEST_Sales_Cr_Memo_Line ON
National_Oxygen_TEST_Sales_Cr_Memo_Header."No_" =
National_Oxygen_TEST_Sales_Cr_Memo_Line."Document No_")

INNER JOIN "noxtest"."dbo"."National Oxygen TEST$Item"
National_Oxygen_TEST_Item ON National_Oxygen_TEST_Sales_Invoice_Line."No_" =
National_Oxygen_TEST_Item."No_")

LEFT OUTER JOIN "noxtest"."dbo"."National Oxygen TEST$Customer Budget"
National_Oxygen_TEST_Customer_Budget ON
National_Oxygen_TEST_Sales_Invoice_Line."Sell-to Customer No_" =
National_Oxygen_TEST_Customer_Budget."Customer No_" AND

National_Oxygen_TEST_Sales_Invoice_Line."Item Category Code" =
National_Oxygen_TEST_Customer_Budget."Item Code")
INNER JOIN "noxtest"."dbo"."National Oxygen TEST$Item Category"
National_Oxygen_TEST_Item_Category ON National_Oxygen_TEST_Item."Item
Category Code" = National_Oxygen_TEST_Item_Category."Code"}

[quoted text, click to view]
sql references aliases just after the table name in the from part of the
select stmt...
so, when you write
FROM
{oj (((("noxtest"."dbo"."National Oxygen TEST$Sales Invoice Header"
National_Oxygen_TEST_Sales_Invoice_Header INNER JOIN
bla bla bla...
the name just after the last quote will be the start of the alias...

ie... select * form tablename aliasname where field1=2

in that example, the space after tablename means"the next string that I
encounter, I am going to treat as an alias for tablename...

the other issue is that you are doing complicated sql in crystal... it
might be easier using query designer or even Enterprise manager in the
sql tools, create a view of the db, then report off that one table...

Hope that helps!
-ryan schellenberg
ryan@theschellenbergs.com

thanks ryan. i compiled the query using crystal designer and used it in
crystal reports. i split the query with union syntax for clarity purposes

[quoted text, click to view]