Hello again :), big post coming...
We are updating our windows based environment 8.4.0.3 to new version 9.0.0.1
Everything has went about fine so far in our smaller test environment, but after we made copy of our production environment, which contains in cache drives about 30 million documents came a weard problem with Sql, or with the difference how folders in od retrieve/produce sql statements.
Here is the problem.
In our Sql database we have few tables which contain more rows than 10.000.000 raws ,so od automatically renames the new tables that exceed 10 million with sequence number, for example ondemand.HAA1 and Ondemand.HAA2.
Now in old od (8.4) , when we retrieved documents with our odinterface(not od:s own client) the query looked like this
We used Od:s certain folder where we made above limits
SELECT ODTBL.*, ''HAA1''
FROM ondemand.HAA1 ODTBL
WHERE ARCHIVE_DATE BETWEEN 15341 AND 15706 AND ( custnro = 660365097 )
UNION ALL SELECT ODTBL.*, ''HAA2'' FROM ondemand.HAA2 ODTBL
WHERE ARCHIVE_DATE BETWEEN 15341 AND 15706 AND ( custnro = 660365097 )
ORDER BY INSURANCE, ARCHIVE_DATE DESC
so it retrieved from two tables, but now in version 9.0.0.1 after all the updatesteps the same retrieval looks like
((SELECT ODTBL.*, 'HAA1' AS ODTBL_NAME
FROM ondemand.HAA1 ODTBL WHERE ODTBL.ARCHIVE_DATE BETWEEN 15341 AND 15706
AND ( ODTBL.custnro = 660365097) )
UNION ALL
(SELECT ODTBL.*, 'HAA2' AS ODTBL_NAME
FROM ondemand.HAA3 ODTBL WHERE ODTBL.ARCHIVE_DATE BETWEEN 15341 AND 15706
AND ( ODTBL.custnro = 660365097 ) )) ODTMP
ORDER BY LAJI, ODTBL.ARCHIVE_DATE DESC
and this sql gives
error Msg 4104, Level 16, State 1, Line 23
The multi-part identifier "ODTBL.ARCHIVE_DATE " could not be bound.
First i thought that this was because in version 9 dates are handled differentially( converts old date to new format) but maybe this is just how sql version 2008 r2 operates, in our older environment we had sql server 2005, but actually we are running this new in compatibility mode 2005.
Is there a way to remove correlation names from the query, i mean in sql server, we have to use od:s folder anyway, in the folder configurations there is not nothing much to do.
Any Tips
Is this part a typo?
((SELECT ODTBL.*, 'HAA1' AS ODTBL_NAME
FROM ondemand.HAA1 ODTBL WHERE ODTBL.ARCHIVE_DATE BETWEEN 15341 AND 15706
AND ( ODTBL.custnro = 660365097) )
UNION ALL
(SELECT ODTBL.*, 'HAA2' AS ODTBL_NAME
FROM ondemand.HAA3 ODTBL WHERE ODTBL.ARCHIVE_DATE BETWEEN 15341 AND 15706
AND ( ODTBL.custnro = 660365097 ) )) ODTMP
ORDER BY LAJI, ODTBL.ARCHIVE_DATE DESC
Hi, yes unfortunately only a Typo error only, had to modify the query before pasting it.
Made a PMR to ibm, cant beliave that this is a bug, because the same is in the fresh new environment also.
Retrieval from two tables with UNION (same application group) is not working if order is made by date format.
Tried this several times. Below the same in "fresh" 9.0.0.1,
DECLARE @P1 date
DECLARE @P2 date
DECLARE @P3 int
DECLARE @P4 date
DECLARE @P5 date
DECLARE @P6 int
SET @P1 = '2013-08-02'
SET @P2 = '2013-10-24'
SET @P3 = '220589114'
SET @P4 = '2013-08-02'
SET @P5 = '2013-10-24'
SET @P6 = '220589114'
SELECT TOP 1001 *
FROM ((SELECT ODTBL.*, 'XGA3' AS ODTBL_NAME
FROM ondemand.XGA3 ODTBL
WHERE ODTBL.ARCHIVING_DATE BETWEEN @P1 AND @P2 AND ( ODTBL.CUSNRO = @P3 ) )
UNION ALL (SELECT ODTBL.*, 'XGA2' AS ODTBL_NAME
FROM ondemandadmin.XGA2 ODTBL
WHERE ODTBL.ARCHIVING_DATE BETWEEN @P4 AND @P5 AND ( ODTBL.CUSNRO = @P6 ) )) ODTMP
ORDER BY LAJI, ODTBL.ARCHIVING_DATE DESC
And this is made by FOLDER, would be nice if there is some way to configure the sql that folders produce...
Hello zeus1996,
Well, you cannot change the SQL that folder produce.
For me this is a bug, and must be addressed via a PMR, as you did.
What I would also try, at least in your test environment, is to upgrade to the latest 9.0.0.2 both from the client and server side. There were lots of corrections.
And check if that helps.
Sincerely yours,
Alessandro