Multi-part identifier issue

Question

Multi-part identifier issue

in_beta

SSC Enthusiast

Points: 118
More actions
May 1, 2007 at 1:41 am

#173882

Hi all, new to the forums.
Having a little issue here where I am getting multiple multi-part identifier errors in my query.
SELECT PosTerminalTable.Location_Description as __entity_group_by__
, EJTable.Receipt_Number as receipt
, convert(datetime, EJTable.Receipt_Date_Time,103) as dtime
, EJTable.Clerk_Number as clerk
, EJTable.Loc_Reg_Number as __entity_order_by__
, EJTable.Receipt_Date_Time as __order_by__
FROM TASKPOS...EJTable
INNER JOIN PosTerminalTable USING (Loc_Reg_number)
INNER JOIN ClerkTable ON (EJTable.Clerk_number = ClerkTable.Clerk_Number)
LEFT JOIN EJItemsTable ON (EJTable.Transaction_Number = EJItemsTable.Transaction_Number)
WHERE EJItemsTable.Transaction_Number IS NULL
AND EJTable.Receipt_Date_Time >= $dtime_from
AND EJTable.Receipt_Date_Time <= $dtime_to
$selected_tills
$selected_clerks
ORDER BY __entity_order_by__ , __order_by__

If I run a query without the tablename in single statements, they run fine, but obviously does not solve the issue. I might add, this was originally created for MySQL but I am porting to MSSQL.
Thanks,
Dovinshka.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398913 More actions · Answer 1

One likely issue is the USING word you've got in the very first join. Also, you can't use variables $x. Instead you should use @x. What are those other two lines just prior to the ORDER BY doing?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

in_beta SSC Enthusiast Points: 118 More actions · Answer 2

Hi Grant,

The variables, I should have mentioned, are actually coming via a php script. Before even trying to use the variables I can't even get the first few lines to work

They are being called from an earlier function in the script.

David A. Long SSCrazy Eights Points: 8421 More actions · Answer 3

I think that this is the culprit:

FROM TASKPOS...EJTable

I do not believe that you can just use the Server and Table portions of the 4 part naming, you have to include the Database, as in:

FROM TASKPOS.MyDatabase..EJTable

Andy

Grant Fritchey SSC Guru Points: 398913 More actions · Answer 4

Good catch. Once I spotted that USING word I couldn't see much else.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

in_beta SSC Enthusiast Points: 118 More actions · Answer 5

Hey guys,

Actually it seems that you can't use any multi-part for the identifier. I changed the query to:

SELECT PosTerminalTable.Location_Description as __entity_group_by__

, EJTable.Receipt_Number as receipt

, convert(datetime, EJTable.Receipt_Date_Time,103) as dtime

, EJTable.Clerk_Number as clerk

, EJTable.Loc_Reg_Number as __entity_order_by__

, EJTable.Receipt_Date_Time as __order_by__

FROM TASKPOS...EJTable a

INNER JOIN PosTerminalTable b

ON (Loc_Reg_number)

INNER JOIN ClerkTable c

ON (a.Clerk_number = c.Clerk_Number)

Etc, etc.

Thanks for the help though