CODEWITHSUNDEEP
sql
JTops
JTops

Reputation: 11

Invalid Column name or

I have the following query

SELECT wo.workorderid "Request ID",
       aau.first_name "Requester",
       dpt.deptname "Department",
       wotodesc.fulldescription "Description",
       sereq.udf_char3 "Owner",
       sereq.udf_char2 "Comments",
       sereq.udf_char10 "KNMgmt Priority",
       std.statusname "Request Status",
       sereq.udf_char4 "Management/Ops Communication",
       sereq.udf_char5 "Charlie Updated"
FROM workorder wo
  LEFT JOIN sduser sdu ON wo.requesterid = sdu.userid
  LEFT JOIN aaauser aau ON sdu.userid = aau.user_id
  LEFT JOIN departmentdefinition dpt ON wo.deptid = dpt.deptid
  LEFT JOIN workordertodescription wotodesc ON wo.workorderid = wotodesc.workorderid
  LEFT JOIN workorderstates wos ON wo.workorderid = wos.workorderid
  LEFT JOIN statusdefinition std ON wos.statusid = std.statusid
  INNER JOIN servicereq_3001 sereq ON wo.workorderid = sereq.workorderid
WHERE (wo.isparent = '1')

I need to sort by the following the "Request Status" does not equal closed and I need to only provide the previous month's records.

I've tried a few different options but receive a invalid column name when trying to specify a value i.e. WHERE dbo.WorkOrderStates.STATUSID <> "3",

other things I tried provide a "The multi-part identifier could not be bound"

I also am unsure how to sort by the last month. I'm fairly new to SQL Coding and any assistance would help.

Upvotes: 0

Views: 161

Answers (3)

Ben
Ben

Reputation: 1291

When joining tables, tables must be aliased, try stick with the aliases, instead of referencing their original names.

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133360

you are using alias so also in your where condition you should use alias again 

  WHERE wos.STATUSID <> "3"

Be sure that your STATUSID is a string otherwise you should use

  WHERE wos.STATUSID <>  3

Upvotes: 1

Hadi
Hadi

Reputation: 37313

Replace 

dbo.workorderstates

with

wos

because when u give an alias you must use it

and replace "3" with 3

Upvotes: 0

Related Questions