SQL statement

  
Hello all,

I have a SQL statement that the platform hasn't been able to process, it reminds me that only SQL1 statements are supported.

Here is the SQL statement:
SELECT C.Cube.Name, C.UC.Code, C.GR.Code, C.BSC.Code, Cubie.Statement
FROM (SELECT Cube.Id, Cube.Name, UC.Id, UC.Code, GR.Id, GR.Code, BSC.Id, BSC.Code
             FROM Cube, UC, GR, CubeGR, BSC, CubeBSC
             Where Cube.Id = 1 and UC.Id = 1 and Cube.Id = UC.CubeId and Cube.Id = CubeGR.CubeId and Cube.Id = CubeBSC.CubeId and CubeGR.GRId = GR.Id and CubeBSC.BSCId = BSC.Id
             ORDER BY UC.Code)  AS C LEFT JOIN Cubie ON (Cubie.BSCId = C.BSC.Id) AND (Cubie.GRId = C.GR.Id) AND (Cubie.UCId = C.UC.Id) AND (C.Cube.Id = Cubie.CubeId);

 Does anyone know how to turnaround this situation with a equivalent SQL1 compatible statement?

Thanks in advance.

Hello Carlos,

I'm not getting why wouldn't the Platform run that query. What's the error that you're getting?

Pedro

'Invalid SQL: Output structure must be set in SQL1.'

Hi,

"SQL1" is the name of your sql node.
The error means that you need to set an entity or a structure as output structure of your query.



In your case it needs to be a structure with 5 attributes to match your "Select" fields.

Note: also I'm assuming that all those table names exist in your database. If they are names of entities defined in your module then you also need to put { } around the names. Like {Cube}.

Regards,
João Rosado

Thanks, that's solved!
The need for a specifically designed structure for that output made all the sense, just got caught-up on that SQL1 expression.

I made some small changes to the SQL statement as the C alias was not well understood:

SELECT Cubie.Id, UC.Code, GR.Code, BSC.Code, Cubie.Statement
FROM (SELECT UC.Code, GR.Code, BSC.Code
             FROM Cube, UC, GR, CubeGR, BSC, CubeBSC
             Where Cube.Id = 1 and UC.Id = 1 and Cube.Id = UC.CubeId and Cube.Id = CubeGR.CubeId and Cube.Id = CubeBSC.CubeId and CubeGR.GRId = GR.Id and CubeBSC.BSCId = BSC.Id
             ORDER BY UC.Code)  AS C LEFT JOIN Cubie ON (Cubie.BSCId = BSC.Id) AND (Cubie.GRId = GR.Id) AND (Cubie.UCId = UC.Id) AND (Cube.Id = Cubie.CubeId);

Nevertheless, there is a new error:
 

I think the order by shold be on the column from the most outer select statemant , it should be some column from Cubie.Id, UC.Code, GR.Code, BSC.Code, Cubie.Statement these columns.
Cannot see what you are giving here in order by clause.

No, the problem is that there's an ORDER BY in the subclause, which is not allowed.

Well, you were both right, the ORDER BY should really be on the outer SELECT statement, as it is not allowed in the SELECT subclause.

After dealing with all the platform notation, here is the final (and working) SQL statement:

SELECT {Cubie}.[Id], UCCode, GRCode, BSCCode, {Cubie}.[Statement]
FROM (SELECT {Cube}.[Id] as CubeId, {UC}.[Id] as UCId, {UC}.[Code] as UCCode, {GR}.[Id] as GRId, {GR}.[Code] as GRCode, {BSC}.[Id] as BSCId, {BSC}.[Code] as BSCCode
             FROM {Cube}, {UC}, {GR}, {CubeGR}, {BSC}, {CubeBSC}
             Where {Cube}.[Id] = 1 and {UC}.[Id] = 1 and {Cube}.[Id] = {UC}.[CubeId] and {CubeGR}.[CubeId] = {Cube}.[Id] and {CubeBSC}.[CubeId] = {Cube}.[Id] and {CubeGR}.[GRId] = {GR}.[Id] and {CubeBSC}.[BSCId] = {BSC}.[Id]) as C
LEFT JOIN {Cubie} ON ({Cubie}.[BSCId] = C.BSCId) AND ({Cubie}.[GRId] = C.GRId) AND ({Cubie}.[UCId] = C.UCId) AND ({Cubie}.[CubeId] = C.CubeId)
ORDER BY UCCode

Thank you all for your help and feedback.