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

[Former Member]

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

[former_member11696]

Still not supported, SQL Expressions should only be used by selecting one field, no SELECT statement. When creating the Expression use the functions available in the Functions windows, that's what your Database Client supports.

You should have see a warning box with more info, try searching also. There is a Kbase article on using SQL Expressions.

Another options is to create a Store Procedure to return the single value.

[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