Strange error emssage in query

Hello,

I have creaqted following Query in Layoutdesigner:
textBox5.Value = LD.QueryData("SELECT CASE WHEN T0.U_PG_HYD_HR = 'Y' AND T1.U_PG_HYDL = T0.U_PG_HYD_SuMaLP THEN 'HR' ELSE '' END AS 'HR' FROM OITM T0 INNER JOIN OWOR T1 ON " + PickDetails_PSGGER("OrderEntry") + " = T1.DocEntry WHERE T0.[ItemCode] = '" + PickDetails_PSGGER("ItemCode") + "'")

When I run the layout it throws follwoing error message:
Generate user script failed in textBox5
Ungültige Konvertierung von der Zeichenfolge SELECT CASE WHEN T0.U_PG_HYD_HR in Typ Double.
Hint: Check the FieldType of the used Column

When I run the SQL-Script in SQL-Server there is no issues with.

The underlying queary for PickDetails_PSGGER is as follows:
SELECT
    SUM(T1.RelQtty)        AS 'Freigegeben',
    SUM(T1.PickQtty)    AS 'Kommssioniert',
    T1.ItemCode,
    T4.ItemName,
    T3.BinCode,
    T0.PickDate    AS 'Kommssionierdatum',

    CASE
        WHEN T0.[Status] = 'Y' THEN 'Kommssioniert'
        WHEN T0.[Status] = 'P' THEN 'Teilweise kommssioniert'
        WHEN T0.[Status] = 'D' THEN 'Teilweise geliefert'
        WHEN T0.[Status] = 'C' THEN 'geschlossen'
        WHEN T0.[Status] = 'R' THEN 'Freigegeben'
    End            AS 'Status',
    T5.U_NAME    AS 'Benutzer',
    T0.[Name]    AS 'Kommissionierer',
    T6.DocEntry AS 'OrderEntry'
FROM
    OPKL T0
    INNER JOIN PKL2 T1 ON T1.AbsEntry = T0.AbsEntry
    INNER JOIN PKL1 T10 ON T10.AbsEntry = T0.AbsEntry
    INNER JOIN OBIN T3 ON T3.AbsEntry = T1.BinAbs
    INNER JOIN OITM T4 ON T4.ItemCode = T1.ItemCode
    INNER JOIN OUSR T5 ON T5.USERID = T0.OwnerCode
    INNER JOIN OWOR T6 ON T6.DocEntry = T10.OrderEntry
WHERE
    T0.AbsEntry = [%AbsEntry]
GROUP BY
    T1.ItemCode,
    T4.ItemName,
    T3.BinCode,
    T0.PickDate,
    T0.[Status],
    T5.U_NAME,
    T0.[Name],
    T6.DocEntry
ORDER BY
    T3.BinCode,
    T1.ItemCode

Lothar Hasenkämper

February 27, 2024 06:09

check the TextFormat from textbox5. It seems that you used a numeric value.

regards Lothar

Oezmen, Vural

February 27, 2024 07:10

Hi Lothar,

thank you for your response.

I have checked the format of textBox5; it is set on general. This can not be the issue, since when I set statically an value to textBox5.Value it is working. It is even working when I set it by the query.
The quury has an issue with the inne join. Maybe you can have closer look on the query especially on the inner join?
The issue appear when I let the inn join like this: INNER JOIN OWOR T1 ON " + PickDetails_PSGGER("OrderEntry") + " = T1.DocEntry

But when set it statically e.g. like this: INNER JOIN OWOR T1 ON 663633 = T1.DocEntry
then there is no issue.
Also when I use “'” like follwing INNER JOIN OWOR T1 ON ‘663633’ = T1.DocEntry
also it works

Thanks!
Vural

February 27, 2024 07:22

Ah okay, that could make sense.

The String can't be build perhabs. Try set PickDetails_PSGGER("OrderEntry").ToString() or use CSTR

regards

February 27, 2024 08:05

This is very straninge but you are absolutely right. I don't know why the String could not be build. By using .ToString() it worked!

Thank you very much for your help Lothar!

February 27, 2024 08:54

You are welcome.

The reason is that inside the LD.QueryData you need a complete string I think

Strange error emssage in query

Comments

Didn't find what you were looking for?