I am upgrading from version 7.62 to 7.702. In addition SQL has changed from 2005 to 2008

On my old server Webfocus does not even attemt to do the aggregation. On the new serve it does - but in doing so the SQL fails. (See outputs below).

My question Is the difference due to the new version of WebFocus or the new version or SQL?

(I know have to fix the problem)



********
CODE
**********
SET TRACEOFF = ALL
SET TRACEON = STMTRACE//CLIENT
SET TRACEON = SQLAGGR//CLIENT
SET TRACEUSER = CLIENT
SET XRETRIEVAL=ON
SET EMPTYREPORT = ON
SET ASNAMES = ON
SET HOLDLIST = PRINTONLY
SET HOLDLIST = PRINTONLY
-SET &MINYR = '1980';

JOIN
LEFT_OUTER IBNR_SNAPSHOT.IBNR_SNAPSHOT.RESERVE_CELL IN
IBNR_SNAPSHOT TO UNIQUE RESERVE_CELL.RESERVE_CELL.RESERVE_CELL
IN RESERVE_CELL AS J1
END
JOIN
LEFT_OUTER RESERVE_CELL.RESERVE_CELL.RESERVE_CELL_DESC IN
IBNR_SNAPSHOT TO UNIQUE
RESERVE_CELL_GROUP.RESERVE_CELL_GROUP.RESERVE_CELL_DESC
IN RESERVE_CELL_GROUP AS J3
END

DEFINE FILE IBNR_SNAPSHOT
AFFILIATE/A13 = 'Non-Affiliate';
SPEC_CAT/A1= IF NAME EQ '__F_IBNR_SPECCATS_Actuarial' THEN 'Y' ELSE 'N';
UND_YR/I4= IF YEAR GT 1980 THEN YEAR ELSE 1980;
ACC_YR/I4= IF YEAR GT 1980 THEN YEAR ELSE 1980;
END

TABLE FILE IBNR_SNAPSHOT
SUM
IBNR
BY RESERVE_CELL NOPRINT
BY HIGHEST 1 SNAPSHOT_TIMESTAMP NOPRINT
BY RESERVE_CELL_DESC
BY UND_YR
BY ACC_YR
BY AFFILIATE
BY SPEC_CAT
BY ACCOUNT_CODE
BY BRANCH_NAME
WHERE RESERVE_CELL EQ '11557'
ON TABLE HOLD AS HOLD_IBNR
END
-EXIT


******************************
- Result from old server
*************************
12.08.54 BR (FOC2525) FOREIGN KEY IS NOT A SUPERSET OF PRIMARY KEY FOR SEGMENT
12.08.54 BR (FOC2509) RDBMS-MANAGED JOIN SELECTED BUT RESULTS MAY BE INACCURAT
12.08.54 BR (FOC2524) JOIN TO A UNIQUE SEGMENT DOES NOT COVER ITS PRIMARY KEY
12.08.54 BR (FOC2590) AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
12.08.54 BR (FOC2609) CANNOT AGGREGATE BY NON-SQLIZABLE SORT KEY : UND_YR
12.08.54 AE SELECT T1."RESERVE_CELL",T1."NAME",T1."YEAR",
12.08.54 AE T1."SNAPSHOT_TIMESTAMP",T1."IBNR",T2."Reserve_Cell",
12.08.54 AE T2."Reserve_Cell_Desc",T3."Reserve_Cell_Desc",T3."Account_Code",
12.08.54 AE T3."Branch_Name" FROM ( ( dbo.IBNR_SNAPSHOT T1 LEFT OUTER JOIN
12.08.54 AE dbo.RESERVE_CELL T2 ON T2."Reserve_Cell" = T1."RESERVE_CELL" )
12.08.54 AE LEFT OUTER JOIN dbo.RESERVE_CELL_GROUP T3 ON
12.08.54 AE T3."Reserve_Cell_Desc" = T2."Reserve_Cell_Desc" ) WHERE
12.08.54 AE (T1."RESERVE_CELL" = 11557);
0 NUMBER OF RECORDS IN TABLE= 256 LINES= 256
(BEFORE TOTAL TESTS)


*************************
- Result from new server
**************************
11.08.08 BT (FOC2525) FOREIGN KEY IS NOT A SUPERSET OF PRIMARY KEY FOR SEGMENT
11.08.08 BT (FOC2509) RDBMS-MANAGED JOIN SELECTED BUT RESULTS MAY BE INACCURAT
11.08.08 BT (FOC2524) JOIN TO A UNIQUE SEGMENT DOES NOT COVER ITS PRIMARY KEY
AGGREGATION DONE ...
11.08.08 AE SELECT
11.08.08 AE T1."RESERVE_CELL",
11.08.08 AE T1."SNAPSHOT_TIMESTAMP",
11.08.08 AE T2."Reserve_Cell_Desc",
11.08.08 AE (CASE WHEN (T1."YEAR" > 1980) THEN T1."YEAR" ELSE 1980 END),
11.08.08 AE (CASE WHEN (T1."YEAR" > 1980) THEN T1."YEAR" ELSE 1980 END),
11.08.08 AE 'Non-Affiliate',
11.08.08 AE (CASE (T1."NAME") WHEN '__F_IBNR_SPECCATS_Actuarial' THEN 'Y'
11.08.08 AE ELSE 'N' END),
11.08.08 AE T3."Account_Code",
11.08.08 AE T3."Branch_Name",
11.08.08 AE SUM(T1."IBNR")
11.08.08 AE FROM
11.08.08 AE ( ( dbo.IBNR_SNAPSHOT T1
11.08.08 AE LEFT OUTER JOIN dbo.RESERVE_CELL T2
11.08.08 AE ON T2."Reserve_Cell" = T1."RESERVE_CELL" )
11.08.08 AE LEFT OUTER JOIN dbo.RESERVE_CELL_GROUP T3
11.08.08 AE ON T3."Reserve_Cell_Desc" = T2."Reserve_Cell_Desc" )
11.08.08 AE WHERE
11.08.08 AE (T1."RESERVE_CELL" = 11557)
11.08.08 AE GROUP BY
11.08.08 AE T1."RESERVE_CELL",
11.08.08 AE T1."SNAPSHOT_TIMESTAMP",
11.08.08 AE T2."Reserve_Cell_Desc",
11.08.08 AE (CASE WHEN (T1."YEAR" > 1980) THEN T1."YEAR" ELSE 1980 END),
11.08.08 AE (CASE WHEN (T1."YEAR" > 1980) THEN T1."YEAR" ELSE 1980 END),
11.08.08 AE (CASE (T1."NAME") WHEN '__F_IBNR_SPECCATS_Actuarial' THEN 'Y'
11.08.08 AE ELSE 'N' END),
11.08.08 AE T3."Account_Code",
11.08.08 AE T3."Branch_Name"
11.08.08 AE ORDER BY
11.08.08 AE T1."RESERVE_CELL",
11.08.08 AE T1."SNAPSHOT_TIMESTAMP" DESC ,
11.08.08 AE T2."Reserve_Cell_Desc",
11.08.08 AE (CASE WHEN (T1."YEAR" > 1980) THEN T1."YEAR" ELSE 1980 END),
11.08.08 AE (CASE WHEN (T1."YEAR" > 1980) THEN T1."YEAR" ELSE 1980 END),
11.08.08 AE (CASE (T1."NAME") WHEN '__F_IBNR_SPECCATS_Actuarial' THEN 'Y'
11.08.08 AE ELSE 'N' END),
11.08.08 AE T3."Account_Code",
11.08.08 AE T3."Branch_Name";
(FOC1400) SQLCODE IS 169 (HEX: 000000A9) XOPEN: 42000
: Microsoft SQL Server Native Client 10.0: [42000] A column has been speci
: fied more than once in the order by list. Columns in the order by list m
: ust be unique. [42000] Statement(s) could not be prepared. [] Deferred p
: repare could not be completed.
L (FOC1406) SQL OPEN CURSOR ERROR. : IBNR_SNAPSHOT

This message has been edited. Last edited by: jammer,

It's most likely WebFOCUS, did you verify that the WF DB Adapter is the correct one, now that your SQL Server is upgraded?

I can't explain why WebFOCUS generates SQL that is not correct, but I would first fix the problems you originally encountered - I would have fixed the problems in v7.6.2.

FOREIGN KEY IS NOT A SUPERSET OF PRIMARY KEY FOR SEGMENT
RDBMS-MANAGED JOIN SELECTED BUT RESULTS MAY BE INACCURAT
JOIN TO A UNIQUE SEGMENT DOES NOT COVER ITS PRIMARY KEY
AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
CANNOT AGGREGATE BY NON-SQLIZABLE SORT KEY : UND_YR

I will check my adapter.

The sql code the new server generates is correct - it is just that it is now doing an order by using the same column (actually 2 defined columns) which is not allowed in sql.

(Although - different it is good that webfocus would now do this as a sql-passthru but it scary when things work differently!

quote:

My question Is the difference due to the new version of WebFocus or the new version or SQL?

The answer is both MSSQL 2008 has a lot of changes compared to 2005
In the same way the WF adapter has a lot of changes from the 2005 version of the adapter to the 2008 version.

As it looks like the generated SQL is wrong you should raise a case with IBI

JG thanks - I am not sure if the sql generated code is wrong but I will raise a case with IBI

This is why I asked if the SQL Server DB Adapter connected to your 2008 DB is the correct one - I would not expect the adapter to generate incorrect SQL, especially for something this simple...

The SQL translators in 7.7x have been enhanced to pass many (but not all) DEFINE'd fields down to the RDBMS. That is why the 7.7 code is doing the aggregation, because 7.7 is doing a better job of figuring out what SQL you are trying to generate.

The error you are now seeing has nothing to do with your release of SQL Server.

In your example you are summing by two fields: UND_YR and ACC_YR. It's not clear to me why you are doing this since these two fields are defined exactly the same:

 IF YEAR GT 1980 THEN YEAR ELSE 1980; 

Nonetheless, WebFOCUS is obediently following your instructions and selecting, grouping by, and sorting by these two identical columns:

(CASE WHEN (T1."YEAR" > 1980) THEN T1."YEAR" ELSE 1980 END),  

The problem is that for whatever reason, SQL Server does not allow you to do an ORDER BY more than once on the same column. Why SQL server prohibits this is a mystery to me, since this is perfectly legitimate SQL (it may not be the best SQL, but it is legit )

Just to verify, I tried this on Oracle and Teradata and had no problems.

The immediate solution to your problem is to remove one of the redundant BY fields.

EricH,

I think everything you said is correct. Yes I realize the code was odd (which I corrected by removing the by accyr). I just wanted some explaination and this is what I suspected.

Sometimes "Enhancing" things can make things blow up!

(Yes I am using SQL2008 adapter)

I am a bit surprised that WebFOCUS generates SQL that returns an error, I'm used to WebFOCUS weeding out the errors before generating the SQL. It is strange to have WebFOCUS code that would run without problems but would cause the DBMS to return an error.

Francis,

I understand what you are saying about Webfoucs weeding out the error beforehand. I will open a case on this and update this post when/if I get a response.