SQL Expression Changes - Unix Server to Windows

Name	Points
Greg Moeller	4184
David Williams	3349
JonA	3291
Kat V	2984
Woozy	1973
Jimmy Chiu	1883
Kwane McNeal	1437
Ragu Raghavan	1372
Roger French	1315
mark.cook	1244

FireGeek21

Veteran Member

Posts: 84

2/5/2013 8:52 PM

We are undergoing a change in environments that our Lawson system resides on - moving from a Unix/Oracle environment to a Windows/SQL environment. During testing, I noticed my Crystal Reports that have SQL Expressions in them are no longer working. A sample SQL Expression I have:

(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")

I have received a variety of messages I am trying to work through. I was wondering if anyone can lend a hand in creating a SQL Expression in a Windows/SQL environment. At this point, I found quotes are not needed. LAWSON is also not needed. My table joins also have to be explicit (INNER JOIN) and not implicit. Still having a problem.

THANKS!!!

Matthew Nye

Veteran Member

Posts: 514

2/5/2013 9:08 PM

The SQL script as you noted:

(SELECT HREMPUSF.A_FIELD 
FROM HREMPUSF
WHERE HREMPUSF.FIELD_KEY = 78 
and HREMPUSF.COMPANY = EMPLOYEE.COMPANY
and HREMPUSF.EMPLOYEE = EMPLOYEE.EMPLOYEE)

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?

FireGeek21

Veteran Member

Posts: 84

2/5/2013 9:12 PM

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.

FireGeek21

Veteran Member

Posts: 84

2/5/2013 9:17 PM

Here is my latest draft:


(
 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
)

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!!!

John Henley

Posts: 3353

2/5/2013 9:29 PM

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'

FireGeek21

Veteran Member

Posts: 84

2/5/2013 9:35 PM

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."

Matthew Nye

Veteran Member

Posts: 514

2/5/2013 9:41 PM

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:

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"

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

Matthew Nye

Veteran Member

Posts: 514

2/5/2013 9:48 PM

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?

Matthew Nye

Veteran Member

Posts: 514

2/5/2013 9:52 PM

as an aside, can you try the following:

 (SELECT HREMPUSF.A_FIELD 
FROM HREMPUSF
WHERE HREMPUSF.FIELD_KEY = '78'
and HREMPUSF.COMPANY = EMPLOYEE.COMPANY
and HREMPUSF.EMPLOYEE = EMPLOYEE.EMPLOYEE)

' should be single quotes. '78'

FireGeek21

Veteran Member

Posts: 84

2/6/2013 1:07 PM

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!!!

Matthew Nye

Veteran Member

Posts: 514

2/6/2013 1:24 PM

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):


 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

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.

Robby

Veteran Member

Posts: 87

2/6/2013 1:27 PM

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