January 23, 2006 at 2:27 am
Hello,
I am a student working on a project in an entreprise, and I was assigned the task to migrate a database from an Hyperfile format (database format used by Windev, a french IDE) to SQL Server 2000.
There are, of course, problems that appeared with the conversion (I'd never use Windev if I had the choice), and one I have difficulties with is this query, that works correctly on Hyperfile "databases" but not in SQL Server 2000 :
SELECT INITIAL_INVENTORY.ID AS ID,
SYSTEM_CODE.HSS_Code AS HSS_Code,
INITIAL_INVENTORY.HasQualityImpact AS HasQualityImpact,
INITIAL_INVENTORY.SEQ AS SEQ,
INITIAL_INVENTORY.System AS System,
INITIAL_INVENTORY.Description AS Description,
INITIAL_INVENTORY.Platform AS Platform,
INITIAL_INVENTORY.Soft AS Soft,
INITIAL_INVENTORY.InHouseDevelopment AS InHouseDevelopment,
INITIAL_INVENTORY.Location AS Location,
CONTROLLING_FUNCTION.Designation AS ControllingFunction,
INITIAL_INVENTORY.QSR AS QSR,
INITIAL_INVENTORY.LinkedToSystem AS LinkedToSystem,
INITIAL_INVENTORY.LinkedToHardwareComponent AS LinkedToHardwareComponent,
INITIAL_INVENTORY.LinkedToSoftwareComponent AS LinkedToSoftwareComponent,
INITIAL_INVENTORY.LinkedToDatabaseComponent AS LinkedToDatabaseComponent,
INITIAL_INVENTORY.NotApplicableForDRP AS NotApplicableForDRP,
PROVIDER.ProviderName AS ProviderName,
INITIAL_INVENTORY.HasERESImpact AS HasERESImpact
FROM SYSTEM_CODE INNER JOIN INITIAL_INVENTORY ON SYSTEM_CODE.ID = INITIAL_INVENTORY.SystemCodeID,
CONTROLLING_FUNCTION INNER JOIN INITIAL_INVENTORY ON CONTROLLING_FUNCTION.ID = INITIAL_INVENTORY.ControllingFunctionID,
PROVIDER RIGHT OUTER JOIN INITIAL_INVENTORY ON PROVIDER.ID = INITIAL_INVENTORY.ProviderID
ORDER BY HSS_Code ASC, HasQualityImpact DESC, SEQ ASC
SQL Server tells me INITIAL_INVENTORY and INITIAL_INVENTORY share the same name, and I have to use aliases, which I tried :
SELECT I.ID AS ID,
S.HSS_Code AS HSS_Code,
I.HasQualityImpact AS HasQualityImpact,
I.SEQ AS SEQ,
I.System AS System,
I.Description AS Description,
I.Platform AS Platform,
I.Soft AS Soft,
I.InHouseDevelopment AS InHouseDevelopment,
I.Location AS Location,
C.Designation AS ControllingFunction,
I.QSR AS QSR,
I.LinkedToSystem AS LinkedToSystem,
I.LinkedToHardwareComponent AS LinkedToHardwareComponent,
I.LinkedToSoftwareComponent AS LinkedToSoftwareComponent,
I.LinkedToDatabaseComponent AS LinkedToDatabaseComponent,
I.NotApplicableForDRP AS NotApplicableForDRP,
P.ProviderName AS ProviderName,
I.HasERESImpact AS HasERESImpact
FROM SYSTEM_CODE S INNER JOIN INITIAL_INVENTORY I ON S.ID = I.SystemCodeID,
CONTROLLING_FUNCTION C INNER JOIN INITIAL_INVENTORY I2 ON C.ID = I2.ControllingFunctionID,
PROVIDER P RIGHT OUTER JOIN INITIAL_INVENTORY I3 ON P.ID = I3.ProviderID
ORDER BY S.HSS_Code ASC, I.HasQualityImpact DESC, I.SEQ ASC
... but to no avail.
I tried modifying the query (cross joins, left outer joins, WHERE ... AND statements to constitute joins, ...), but I never get the same results I do with the old query on the old database.
Anyone knows a way to make that query work under SQL Server 2000 ?
January 23, 2006 at 4:10 am
A follow-up from myself (still trying everything I can think of) : the following query, under the SQL Server Query Analyzer (on the SQL database), produces the same results as the results the old query used to produce on the old database :
SELECT INITIAL_INVENTORY.ID AS ID, SYSTEM_CODE.HSS_Code AS HSS_Code, INITIAL_INVENTORY.HasQualityImpact AS HasQualityImpact,
INITIAL_INVENTORY.SEQ AS SEQ, INITIAL_INVENTORY.System AS System, INITIAL_INVENTORY.Description AS Description,
INITIAL_INVENTORY.Platform AS Platform, INITIAL_INVENTORY.Soft AS Soft, INITIAL_INVENTORY.InHouseDevelopment AS InHouseDevelopment,
INITIAL_INVENTORY.Location AS Location, CONTROLLING_FUNCTION.Designation AS ControllingFunction, INITIAL_INVENTORY.QSR AS QSR,
INITIAL_INVENTORY.LinkedToSystem AS LinkedToSystem, INITIAL_INVENTORY.LinkedToHardwareComponent AS LinkedToHardwareComponent,
INITIAL_INVENTORY.LinkedToSoftwareComponent AS LinkedToSoftwareComponent,
INITIAL_INVENTORY.LinkedToDatabaseComponent AS LinkedToDatabaseComponent,
INITIAL_INVENTORY.NotApplicableForDRP AS NotApplicableForDRP, PROVIDER.ProviderName AS ProviderName,
INITIAL_INVENTORY.HasERESImpact AS HasERESImpact
FROM SYSTEM_CODE INNER JOIN
INITIAL_INVENTORY ON SYSTEM_CODE.ID = INITIAL_INVENTORY.SystemCodeID INNER JOIN
CONTROLLING_FUNCTION ON INITIAL_INVENTORY.ControllingFunctionID = CONTROLLING_FUNCTION.ID LEFT OUTER JOIN
PROVIDER ON INITIAL_INVENTORY.ProviderID = PROVIDER.ID
ORDER BY SYSTEM_CODE.HSS_Code, INITIAL_INVENTORY.HasQualityImpact DESC, INITIAL_INVENTORY.SEQ
Yet, under the Windev application, on the SQL database, it gets some excess results ... I don't understand what's going on ...
January 23, 2006 at 10:39 am
there is a setting ANSI_NULLS ON/OFF
ANSI_NULLS ON -> left join (NULL=NULL) =false
ANSI_NULLS OFF -> left join (NULL=NULL) =TRUE
January 25, 2006 at 3:14 am
Without having Windev it is very difficult to say for sure but the problem almost certainly lies with how the JOINs are evaluated.
In some DBs the order of the joins is sequential, for instance in your second post a row set would be produced for SYSTEM_CODE inner joined to INITIAL_INVENTORY, and then this row set would then be inner joined to CONTROLLING_FUNCTION to produce another row set ...etc
In other DBs the precedence will be determined by the order of the items in the ON clauses (Note: try typing 'evaluation order of joins' into Google, there are lots of little articles on this).
As such I would recommend trying to re-order the joins in your SQL and see what resultsets you get.
(Marvin)
January 25, 2006 at 4:41 am
Thank you for your replies.
Apparently, the problem lies in the fact that, as you said, Giles, Windev & SQL Server have different ways to evaluate JOINs :
In Windev, the word "JOIN" must be between the names of 2 tables, even if that means mentionning the same table several times.
In SQL Server, mentionning the same table several times causes the system to JOIN the table multiple times.
That's why this :
FROM SYSTEM_CODE INNER JOIN INITIAL_INVENTORY ON SYSTEM_CODE.ID = INITIAL_INVENTORY.SystemCodeID,
CONTROLLING_FUNCTION INNER JOIN INITIAL_INVENTORY ON CONTROLLING_FUNCTION.ID = INITIAL_INVENTORY.ControllingFunctionID,
PROVIDER RIGHT OUTER JOIN INITIAL_INVENTORY ON PROVIDER.ID = INITIAL_INVENTORY.ProviderID
works in Windev but produces errors in SQL Server and this :
SYSTEM_CODE INNER JOIN
INITIAL_INVENTORY ON SYSTEM_CODE.ID = INITIAL_INVENTORY.SystemCodeID INNER JOIN
CONTROLLING_FUNCTION ON INITIAL_INVENTORY.ControllingFunctionID = CONTROLLING_FUNCTION.ID LEFT OUTER JOIN
PROVIDER ON INITIAL_INVENTORY.ProviderID = PROVIDER.ID
works in SQL Server but fails to compile in Windev.
That much, I know. What I don't know is how to make the query work through Windev on the SQL Server tables. It would be nice to have Windev not proofread the query (afterall, all Windev needs to do is transfer the query and receive the results), but given the context, I think it's impossible.
I tried re-ordering the joins in SQL Server and found some queries that produced the same results, but alas, Windev doesn't accept any of them (due to the aforementionned problem).
January 25, 2006 at 5:19 am
If re-ordering the tables in the joins does not work you could try changing the order of the items in the ON clauses.
And that just about does it for any other suggestions I may have....
(Marvin)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply