Crystal SQL Expression Error: multi-part identifier could not be bound

[#1]

Hi, I am trying to create a Crystal SQL Expression to be used in the 'Record Selection'. After creating the code I get an error stating that the Tables I am referencing can not be bound (there is more than one) crystal-sql-exp-error-01.jpg . However my tables are clearly in the "Field Tree". My attached example states "OR_LOG_ALL_SURG"."LOG_ID".

How can i resolve this issue?

My SQL code is below:

(Select Top 1 OR_Shifts.SHIFT_TYPE_C
			From OR_LOC_SHIFT_TIMES AS OR_Shifts
				Left Join ZC_SHIFT_TYPE AS ZC_Shifts ON OR_Shifts.SHIFT_TYPE_C = ZC_Shifts.SHIFT_TYPE_C
			Where ZC_Shifts.ABBR Like '%' + "CLARITY_LOC"."LOC_NAME" + '%'
			Order BY
				(Case When (ZC_Shifts.ABBR Like '%' + "CLARITY_LOC"."LOC_NAME" + '%' AND ZC_Shifts.ABBR Like '%' + Left("DATE_DIMENSION"."DAY_OF_WEEK",3) + '%') Then 1 Else 0 End) Desc
				,(Case When ZC_Shifts.ABBR = "CLARITY_LOC"."LOC_NAME" Then 1 Else 0 End) Desc
			)    


End

[DellSC]

Crystal expects SQL Expression to return a single value, not multiple values or rows of data. The "multi-part identifier could not be bound" error is saying that your SQL expression is returning multiple values either in a single row or multiple rows.

In order to filter your data like it seems you're trying to do, you'll probably have to write a Command instead of using a SQL Expression. A Command is a SQL Select statement that should provide ALL of the data for your report - generally it's not good to join multiple commands or a command and one or more tables. For more info about Commands and best practices for how to use them see this blog: https://blogs.sap.com/2015/04/01/best-practices-when-using-commands-with-crystal-reports/

-Dell

[Former Member]

Hi, Thanks for the response. The SQL code I posted does only return 1 row and 1 column. That's what the "TOP 1" returns. Also, the report I am creating requires bringing the tables in to the report, and not through the "Command" use.

Any other suggestions?

Thanks.

[DellSC]

Assuming that you're using the "order by" in the query to sort a specific record to the top, I might change the SQL just a bit. Also, the "desc" with the case statements on the order by might be causing the issue. Here's what you might try with the SWL:

Select Top 1 OR_Shifts.SHIFT_TYPE_C
From(
  Select
    OR_Shifts.SHIFT_TYPE_C,
      Case 
        When (ZC_Shifts.ABBR Like '%' + "CLARITY_LOC"."LOC_NAME" + '%' AND ZC_Shifts.ABBR Like '%' 
          + Left("DATE_DIMENSION"."DAY_OF_WEEK",3) + '%') Then 1 
        Else 0 
      End locate_day,
      Case 
        When ZC_Shifts.ABBR = "CLARITY_LOC"."LOC_NAME" Then 1 
        Else 0 
      End locate
  From OR_LOC_SHIFT_TIMES AS OR_Shifts
    Left Join ZC_SHIFT_TYPE AS ZC_Shifts ON OR_Shifts.SHIFT_TYPE_C = ZC_Shifts.SHIFT_TYPE_C
  Where ZC_Shifts.ABBR Like '%' + "CLARITY_LOC"."LOC_NAME" + '%'

  )
order by locate_day desc, locate desc

-Dell