Question

Lobby Data Source - Case in 'Condition' not working for all users

  • 4 August 2023
  • 3 replies
  • 50 views
B
Rank
Badge +4

Hi,

I have the following case in a Data Source condition:

SOURCE_REF1 IS NOT NULL

AND
CASE WHEN TO_CHAR(SYSDATE, 'D') = 1 AND 
TRUNC(PLANNED_SHIP_DATE) > TRUNC(SYSDATE)-100
AND TRUNC(PLANNED_SHIP_DATE) < TRUNC(SYSDATE)+2
                      THEN
                      1
WHEN TO_CHAR(SYSDATE, 'D') = 2 AND 
TRUNC(PLANNED_SHIP_DATE) > TRUNC(SYSDATE)-100
AND TRUNC(PLANNED_SHIP_DATE) < TRUNC(SYSDATE)+2
                      THEN
                      1
WHEN TO_CHAR(SYSDATE, 'D') = 3 AND 
TRUNC(PLANNED_SHIP_DATE) > TRUNC(SYSDATE)-100
AND TRUNC(PLANNED_SHIP_DATE) < TRUNC(SYSDATE)+2
                      THEN
                      1
WHEN TO_CHAR(SYSDATE, 'D') = 4 AND 
TRUNC(PLANNED_SHIP_DATE) > TRUNC(SYSDATE)-100
AND TRUNC(PLANNED_SHIP_DATE) < TRUNC(SYSDATE)+2
                      THEN
                      1
WHEN TO_CHAR(SYSDATE, 'D') = 5 AND 
TRUNC(PLANNED_SHIP_DATE) > TRUNC(SYSDATE)-100
AND TRUNC(PLANNED_SHIP_DATE) < TRUNC(SYSDATE)+4
                      THEN
                      1
WHEN TO_CHAR(SYSDATE, 'D') = 6 AND 
TRUNC(PLANNED_SHIP_DATE) > TRUNC(SYSDATE)-100
AND TRUNC(PLANNED_SHIP_DATE) < TRUNC(SYSDATE)+3
                      THEN
                      1
WHEN TO_CHAR(SYSDATE, 'D') = 7 AND 
TRUNC(PLANNED_SHIP_DATE) > TRUNC(SYSDATE)-100
AND TRUNC(PLANNED_SHIP_DATE) < TRUNC(SYSDATE)+2
                      THEN
                      1
                      ELSE 2
               END  = 1

AND ROUTE_ID = 'PRJ'
AND NOT(STATE IN ('Cancelled','Closed'))

 

The issue being it will generate the correct data a user logged in to their profile only on certain pc’s. 

I think there is a problem with the case statement in the ‘Condition’ but need to be sure.

I have deleted the 2.0 folder and cleared AppCache in Command Prompt but still no joy.

Any advise would be gratefully received. 

 

Regards,

Ben

3 replies

A
Rank
Userlevel 7

Which view is this for? The only thing I can think of that the state may be translated, and the other PC would have a different language/region setting? 

NOT(STATE IN ('Cancelled','Closed'))

Can you try to check if there’s a DB value instead e.g. STATE_DB , which wouldn’t be translated.

AND STATE_DB NOT IN ('CANCELLED','CLOSED')

 

B
Rank
Badge +4

Hi Anmise,

Thank you for your reply.

Would the  language/region be specific to the user and not the PC?  i.e. if JSMITH has login as his own user on one PC and then uses his same login credentials to login to another PC, the data viewed in the Element differs.

View = &AO.SHIPMENT

There is no ‘_DB’ in Shipment but there is a OBJSTATE which I am now using. No change.

Kind Regards,

Ben

A
Rank
Userlevel 7

Hi Anmise,

Thank you for your reply.

Would the  language/region be specific to the user and not the PC?  i.e. if JSMITH has login as his own user on one PC and then uses his same login credentials to login to another PC, the data viewed in the Element differs.

View = &AO.SHIPMENT

There is no ‘_DB’ in Shipment but there is a OBJSTATE which I am now using. No change.

Kind Regards,

Ben

It was just a theory that it might pick up language from the locale on the PC, since  you saw a difference between devices, with the same user. 

So if you run this query on the two different devices, what’s the result you are getting?

SELECT shipment_id,
       TO_CHAR(SYSDATE, 'D'),
       TRUNC(planned_ship_date)
FROM   shipment
WHERE  source_ref1 IS NOT NULL
AND    CASE
         WHEN TO_CHAR(SYSDATE, 'D') = 1 AND TRUNC(planned_ship_date) > TRUNC(SYSDATE) - 100 AND TRUNC(planned_ship_date) < TRUNC(SYSDATE) + 2 THEN
          1
         WHEN TO_CHAR(SYSDATE, 'D') = 2 AND TRUNC(planned_ship_date) > TRUNC(SYSDATE) - 100 AND TRUNC(planned_ship_date) < TRUNC(SYSDATE) + 2 THEN
          1
         WHEN TO_CHAR(SYSDATE, 'D') = 3 AND TRUNC(planned_ship_date) > TRUNC(SYSDATE) - 100 AND TRUNC(planned_ship_date) < TRUNC(SYSDATE) + 2 THEN
          1
         WHEN TO_CHAR(SYSDATE, 'D') = 4 AND TRUNC(planned_ship_date) > TRUNC(SYSDATE) - 100 AND TRUNC(planned_ship_date) < TRUNC(SYSDATE) + 2 THEN
          1
         WHEN TO_CHAR(SYSDATE, 'D') = 5 AND TRUNC(planned_ship_date) > TRUNC(SYSDATE) - 100 AND TRUNC(planned_ship_date) < TRUNC(SYSDATE) + 4 THEN
          1
         WHEN TO_CHAR(SYSDATE, 'D') = 6 AND TRUNC(planned_ship_date) > TRUNC(SYSDATE) - 100 AND TRUNC(planned_ship_date) < TRUNC(SYSDATE) + 3 THEN
          1
         WHEN TO_CHAR(SYSDATE, 'D') = 7 AND TRUNC(planned_ship_date) > TRUNC(SYSDATE) - 100 AND TRUNC(planned_ship_date) < TRUNC(SYSDATE) + 2 THEN
          1
         ELSE
          2
      END = 1
AND    route_id = 'PRJ'
AND    objstate NOT IN ('CANCELLED', 'CLOSED')

 

Reply


Terms & ConditionsCookie settings