SQL Expression Changes - Unix Server to Windows

The SQL script as you noted:

[code](SELECT HREMPUSF.A_FIELD FROM HREMPUSF WHERE HREMPUSF.FIELD_KEY = 78 and HREMPUSF.COMPANY = EMPLOYEE.COMPANY and HREMPUSF.EMPLOYEE = EMPLOYEE.EMPLOYEE) [/code]

The issue youre probably having is there is a reference to the EMPLOYEE table (EMPLOYEE.COMPANY and EMPLOYEE.EMPLOYEE).

Is this command linked to another command? Are you certain this is the full script?

The HREMPUSF.COMPANY = EMPLOYEE.COMPANY and HREMPUSF.EMPLOYEE = EMPLOYEE.EMPLOYEE are actually working as table joins ~ joining this SQL Expression (HREMPUSF) to the EMPLOYEE table in the report.

This SQL Expression worked fine in our UNIX/Oracle environment.

Here is my latest draft:
[code] ( SELECT u.A_FIELD FROM HREMPUSF u INNER JOIN EMPLOYEE ec ON u.COMPANY = ec.COMPANY INNER JOIN EMPLOYEE ee ON u.EMPLOYEE= ee.EMPLOYEE WHERE u.FIELD_KEY = 67 ) [/code]

This is triggering the message: "Database Connector Error: '21000:[Microsoft][ODBC SQL Server Driver][SQL Server]Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <=, >, >= or when the subquery is used as an expression."

I did a dump of the table HREMPUSF using Add-Ins and checked the data with FIELD_KEY = 67 and found there are no duplicates or any EMPLOYEE with more than one record.

PUZZLED!!!

Your INNER JOIN is wrong. you only inner join to the EMPLOYEE table once--with both index fields.
Also, FIELD_KEY is not a numeric -- try:
SELECT u.A_FIELD
FROM HREMPUSF u
INNER JOIN EMPLOYEE ee
ON u.COMPANY = ee.COMPANY
AND u.EMPLOYEE= ee.EMPLOYEE
WHERE u.FIELD_KEY = '67'

John, I made the changes you suggested and I am still getting the message: "Database Connector Error: '21000:[Microsoft][ODBC SQL Server Driver][SQL Server]Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <=, >, >= or when the subquery is used as an expression."

Are you sure you posted the full SQL query? Sorry to be a broken record. The only reason I ask is your original query shouldnt have run in Oracle by it self, but it would run as a subquery for a derived field where you are joining to the EMPLOYEE table in the main query. Something like this:

[code]SELECT "EMPLOYEE".*, (SELECT "LAWSON"."HREMPUSF"."A_FIELD" FROM "LAWSON"."HREMPUSF" WHERE "LAWSON"."HREMPUSF"."FIELD_KEY = 78 and "LAWSON"."HREMPUSF"."COMPANY" = "EMPLOYEE"."COMPANY" and "LAWSON"."HREMPUSF"."EMPLOYEE" = "EMPLOYEE"."EMPLOYEE") A_FIELD FROM "LAWSON"."EMPLOYEE" "EMPLOYEE" [/code]

But by it self, implicit or explicit, the original query you posted is invalid SQL.

Ok, I think I know whats going on here. I missed it, you said this is a SQL Expression not a SQL Command. In that case your query is valid and the EMPLOYEE table is in your SQL Command. Be sure to update youre SQL Command first before attempting to fix this expression.

If youre still getting the error that means you dont have the join to your EMPLOYEE table correct. can you post the main SQL Command script here as well?

as an aside, can you try the following:

[code] (SELECT HREMPUSF.A_FIELD FROM HREMPUSF WHERE HREMPUSF.FIELD_KEY = '78' and HREMPUSF.COMPANY = EMPLOYEE.COMPANY and HREMPUSF.EMPLOYEE = EMPLOYEE.EMPLOYEE) [/code]

' should be single quotes. '78'

Matthew thank you for your replies! I did try '78' and get the same message.

Essentially what is going on here is I have a report - for simplicity's sake let's say the Crystal report only has one table... the EMPLOYEE table. Now, I am creating a SQL Expression to grab the A_FIELD from HREMPUSF. To link this SQL Expression to the table in the report you set the links in the SQL Expression. This has worked perfectly when we were in the UNIX/Oracle environment.

UGH!!!

I would test this outside of Crystal. The error messages you are receiving are delivered to Crystal via the SQL ODBC drivers. These are not always as verbose as the when run inside SSMS. You can replicate what Crystal is doing as follows (i believe, though I havent seen the SQL Command so this is just a guess):

[code] SELECT EMPLOYEE.*, (SELECT HREMPUSF.A_FIELD FROM LAWSON.HREMPUSF WHERE HREMPUSF.FIELD_KEY = 78 and HREMPUSF.COMPANY = EMPLOYEE.COMPANY and HREMPUSF.EMPLOYEE = EMPLOYEE.EMPLOYEE) A_FIELD FROM EMPLOYEE [/code]

Assuming this returns the same error message in SSMS as in Crystal, follow up with this:


WITH CTE as(
SELECT *, ROW_NUMBER() OVER (PARTITION BY EMP.COMPANY, EMP.EMPLOYEE, HEU.A_FIELD ORDER BY (SELECT 0)) AS Dups
FROM EMPLOYEE EMP
INNER JOIN HREMPUSF HEU ON HEU.COMPANY = EMP.COMPANY AND HEU.EMPLOYEE = EMP.EMPLOYE
WHERE HEU.FIELD_KEY = 78
)
SELECT * FROM CTE WHERE Dups >1


I dont have a SQL instance to check this syntax on so no guarantees on the accuracy of the SQL but this should show you where youre duplicate records are.

Firegeek...
Here's what I do when I'm trying to grab data like you are explaining here...
Make your "expression" a table, give it an alias, and join it (for this instance, I'd use LEFT OUTER JOIN, as some employees will invariably not have data in that field, unless of course you only want ee's with data there, then use INNER JOIN)

You will have to include COMPANY & EMPLOYEE in the SELECT part of the expression, in order to have those fields to use to join to the main query EMPLOYEE table.

[main query above]
LEFT OUTER JOIN
(SELECT
u.COMPANY
,u.EMPLOYEE
,u.A_FIELD
FROM
HREMPUSF u
WHERE u.FIELD_KEY = 67)Q1--alias name, also FIELD_KEY is indeed numeric.
ON Q1.COMPANY = ee.COMPANY
AND Q1.EMPLOYEE= ee.EMPLOYEE

~~then, when you put the data in the main select clause, do it like this....Q1.A_FIELD


hth
-Robby