Mysql query union

Ignition
1

I am trying to join a query result data with some property values in a table. The properties are just bound to tag values.
The trouble is that I do not want to see rows returned if the value of my piece is 0 (String)
I have tried If statements, nothing seems to work.

SELECT piece_no AS Piece, wire_type AS Type, end_time AS Time FROM productiondata_enamel
UNION
IF '{LabTests.PieceLine1}' <> '0'
BEGIN
SELECT '{LabTests.PieceLine1}' AS Piece, '{LabTests.TypeLine1}' AS Type, CURRENT_TIMESTAMP as Time
END
UNION
SELECT '{LabTests.PieceLine2}' AS Piece, '{LabTests.TypeLine2}' AS Type, CURRENT_TIMESTAMP as Time
UNION
SELECT '{LabTests.PieceLine3}' AS Piece, '{LabTests.TypeLine3}' AS Type, CURRENT_TIMESTAMP as Time
UNION
SELECT '{LabTests.PieceLine6}' AS Piece, '{LabTests.TypeLine6}' AS Type, CURRENT_TIMESTAMP as Time
UNION
SELECT '{LabTests.PieceLine7}' AS Piece, '{LabTests.TypeLine7}' AS Type, CURRENT_TIMESTAMP as Time
UNION
SELECT '{LabTests.PieceLine8}' AS Piece, '{LabTests.TypeLine8}' AS Type, CURRENT_TIMESTAMP as Time

Order BY Time asc
2

I don’t think you can nest IF within a query. Try putting a WHERE clause on each SELECT.

3

Tried this, LENGTH, CHAR_LENGTH, CHARACTER LENGTH… nothing works.

SELECT piece_no AS Piece, wire_type AS Type, end_time AS Time FROM productiondata_enamel
UNION
SELECT '{LabTests.PieceLine1}' AS Piece, '{LabTests.TypeLine1}' AS Type, CURRENT_TIMESTAMP as Time
WHERE CHAR_LENGTH('{LabTests.PieceLine1}') > 0


Order BY Time asc
4

Does it work without the where clause? Are the data types of the columns you are trying to union together the same?

5

Yes it works without the where clause for all the unions, and all the columns are the same type and name.

6

I think with MySQL you need to wrap the unions in a subquery if you want to have a where clause:

SELECT * FROM
(SELECT '234' as Piece 
UNION
SELECT '1' as Piece 
) as p2
WHERE Char_length(p2.Piece) > 1
7

Thanks, I did get this to work though:

SELECT piece_no AS Piece, wire_type AS Type, end_time AS Time FROM productiondata_enamel
UNION
SELECT IF ('{LabTests.PieceLine1}' AND '{LabTests.TypeLine1}','{LabTests.PieceLine1}', Null) AS Piece, IF ('{LabTests.PieceLine1}' AND '{LabTests.TypeLine1}','{LabTests.TypeLine1}', Null) AS Type,
 IF ('{LabTests.PieceLine1}' AND '{LabTests.TypeLine1}',CURRENT_TIMESTAMP, NULL) as Time
UNION
SELECT IF ('{LabTests.PieceLine2}' AND '{LabTests.TypeLine2}','{LabTests.PieceLine2}', Null) AS Piece, IF ('{LabTests.PieceLine2}' AND '{LabTests.TypeLine2}','{LabTests.TypeLine2}', Null) AS Type,
 IF ('{LabTests.PieceLine2}' AND '{LabTests.TypeLine2}',CURRENT_TIMESTAMP, NULL) as Time