Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 9 of 9 total |
COMPUTED timestamp field not working |
Mon, Sep 9 2019 9:55 AM | Permanent Link |
Yusuf Zorlu MicrotronX - Speditionssoftware vom Profi | Hi,
ALTER TABLE "mytimes" ADD COLUMN "c_wartezeit" timestamp COMPUTED ALWAYS AS CASE when verladen_start is null and ankunft is null then 0 when ankunft is not null and verladen_start is null then current_timestamp()-ankunft when ankunft is not null and verladen_start is not null then verladen_start-ankunft end is not working: ElevateDB Error #700 An error was found in the c_wartezeit computed column expression at line 3 and column 58 (Expected NULL, SmallInt, Integer, BigInt, Float, Decimal, Interval Year, Interval Day, Interval Hour, Interval Minute, Interval Second, or Interval MSecond expression but instead found CURRENT_TIMESTAMP() - "ankunft") but if i create it as a integer-field it is ok: ALTER TABLE "mytimes" ADD COLUMN "c_wartezeit" integer COMPUTED ALWAYS AS CASE when verladen_start is null and ankunft is null then 0 when ankunft is not null and verladen_start is null then cast(current_timestamp()-ankunft as interval minute) when ankunft is not null and verladen_start is not null then cast(verladen_start-ankunft as interval minute) end So the question is where is the bug, my script or in edb? Thanks for some hits into the right direction. -- Yusuf Zorlu MicrotronX |
Mon, Sep 9 2019 10:18 AM | Permanent Link |
Fernando Dias Team Elevate | Yusuf,
Not knowing what type is "ankunft" I can't tell if the expression is correct but in any case "0" is not a valid TIMESTAMP value for "c_wartezeit". -- Fernando Dias [Team Elevate] |
Mon, Sep 9 2019 10:49 AM | Permanent Link |
Yusuf Zorlu MicrotronX - Speditionssoftware vom Profi | Fernando Dias wrote:
<< Not knowing what type is "ankunft" I can't tell if the expression is correct but in any case "0" is not a valid TIMESTAMP value for "c_wartezeit". Hi Fernando, all fields are timestamp fields. i mean, i can change "0" to NULL but works also not! -- Yusuf Zorlu MicrotronX |
Mon, Sep 9 2019 10:52 AM | Permanent Link |
Yusuf Zorlu MicrotronX - Speditionssoftware vom Profi | If i change it from "0" to NULL i get following error with this:
ALTER TABLE "mytimes" ADD COLUMN "c_wartezeit4" timestamp COMPUTED ALWAYS AS CASE when verladen_start is null and ankunft is null then NULL when ankunft is not null and verladen_start is null then current_timestamp()-ankunft when ankunft is not null and verladen_start is not null then verladen_start-ankunft END ElevateDB Error #700 An error was found in the c_wartezeit4 computed column expression at line 1 and column (Expected NULL, Date, or Timestamp expression but instead found CASE WHEN "ankunft" IS NOT NULL AND "verladen_start" IS NULL THEN CURRENT_TIMESTAMP() - "ankunft" WHEN "ankunft" IS NOT NULL AND "verladen_start" IS NOT NULL THEN "verladen_start" - "ankunft" ELSE NULL END) -- Yusuf Zorlu MicrotronX |
Mon, Sep 9 2019 11:17 AM | Permanent Link |
Fernando Dias Team Elevate | Yusuf,
A difference between 2 timestamp values is always of type INTERVAL DAY TO MSECOND, not INTEGER or TIMESTAMP. Your second query is working only because you are converting the result to INTERVAL MINUTE that can be expressed as an integer value, that is the number of minutes between the timestamps. What do you expect to obtain as the result from the expression current_timestamp()-ankunft ? -- Fernando Dias [Team Elevate] |
Mon, Sep 9 2019 12:36 PM | Permanent Link |
Yusuf Zorlu MicrotronX - Speditionssoftware vom Profi | Fernando Dias wrote:
>>What do you expect to obtain as the result from the expression current_timestamp()-ankunft ? I need a time value in HH:NN as a result for the difference -- Yusuf Zorlu MicrotronX |
Mon, Sep 9 2019 1:18 PM | Permanent Link |
Fernando Dias Team Elevate | Then the result should be of of type INTERVAL HOUR TO MINUTE and not TIMESTAMP. -- Fernando Dias [Team Elevate] |
Mon, Sep 9 2019 1:42 PM | Permanent Link |
Fernando Dias Team Elevate | You might want to revisit an answer from Tim Young to yourself on how to deal with interval type display/edition here: https://www.elevatesoft.com/forums?action=view&category=edb&id=edb_sql&page=1&msg=7760#7760 -- Fernando Dias [Team Elevate] |
Mon, Sep 9 2019 1:50 PM | Permanent Link |
Yusuf Zorlu MicrotronX - Speditionssoftware vom Profi | Fernando Dias wrote:
Then the result should be of of type INTERVAL HOUR TO MINUTE and not TIMESTAMP. -- Thanks Fernando. I'm new to interval type an I have not found specific examples in pure SQL. -- Yusuf Zorlu MicrotronX |
This web page was last updated on Sunday, May 19, 2024 at 08:46 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |