Hi Guys,

Is there anybody out there who can help with this little problem, please?

I would appreciate any help .

Cheers,

Neil.

Are those custom fields that you have added or what fields are those? 

Hi @anmise ,

Thanks for your reply .

Further investigation has revealed that they are indeed custom fields. The SQL statement is returning a string value directly from another table which is in that format (i.e. yyyymmdd hh:mi:ss)

SELECT COALESCE(MIN(old_Value), MIN(New_Value))
FROM history_log_tab a
JOIN history_log_attribute_tab b
     ON a.log_id = b.log_id 
WHERE REPLACE(REPLACE(a.Keys, '^',''), 'WO_NO=','') = :Wo_No
AND a.history_type = 2 
AND b.column_name = 'PLAN_F_DATE'
 

I suppose that I need use a to_date function to convert this string to the correct date format (i.e. ‘dd/mm/yyyy hh:mi:ss’) but I’m not sure where to try to put it.

I have tried to convert the old_Value/New_Value directly in the SELECT statement.

TO_DATE(COALESCE(MIN(old_Value), MIN(New_Value)),'dd/mm/yyyy hh:mi:ss')

FROM history_log_tab a
JOIN history_log_attribute_tab b
     ON a.log_id = b.log_id 
WHERE REPLACE(REPLACE(a.Keys, '^',''), 'WO_NO=','') = :Wo_No
AND a.history_type = 2 
AND b.column_name = 'PLAN_F_DATE'
 

but the select statement text is blue (instead of black).

Can you help any further, please?

Cheers,

Neil.

Hi @anmise ,

I have seen that the text turning blue is simply an indication that the SQL statement has been modified. I tried clicking on the Published checkbox and the text turned black. So I am happy with that, however the SQL statement (with the to_date functionality in my previous post) which works in PL/SQL developer causes the message ‘Not all bind variables have a corresonding argument’ to be displayed when clicking on Finish in the Edit Custom Objects dialogue. Very frustrating.

Do you know why IFS is complaining about bind variables in the statement which includes the to_date functionality?

Cheers,

Neil.

Hi @anmise and anybody else out there who might be interested .

It seems that IFS SQL Statement cannot ignore the : character and always interprets it as a bind variable indicator. So, I had to manipulate the SQL statement to reformat the date that was held on the database as yyyymmdd hh:mi:ss. Here is the SELECT statement showing this manipulation:

SELECT 
SUBSTR(COALESCE(MIN(SUBSTR(old_Value,1,8)), MIN(SUBSTR(New_Value,1,8))),7,2) ||
'/' ||
SUBSTR(COALESCE(MIN(SUBSTR(old_Value,1,8)), MIN(SUBSTR(New_Value,1,8))),5,2) ||
'/' ||
SUBSTR(COALESCE(MIN(SUBSTR(old_Value,1,8)), MIN(SUBSTR(New_Value,1,8))),1,4) ||
' ' ||
SUBSTR(COALESCE(MIN(old_Value), MIN(New_Value)),10,8)
 

FROM TABLE_NAME.

old_Value and New_Value are the database columns with the date in yyyymmdd hh:mi:ss format.

And so this is solved, thanks. .

Cheers,

Neil.