on 12-26-2014 8:24 AM
Hi Experts,
I am trying to achieve the current logged in user detail , below query i tried on SAP B1 on SQL and it is working fine
SELECT T0.U_NAME FROM OUSR T0 WHERE T0.INTERNAL_K = $[USER]
but it is not working in B1 on HANA , I tried the below query
SELECT T0."U_NAME" FROM OUSR T0 WHERE T0."INTERNAL_K" = (SELECT CURRENT_USER FROM DUMMY)
any help will be appreciate
Regards,
Manish
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Balaji,
Thanks for the response , I tried the below query as per Prasanna query
SELECT r."UserCode",
(SELECT
CASE
WHEN "Action" = 'I' THEN 'OPEN'
WHEN "Action" = 'O' THEN 'CLOSED'
END
FROM USR5 r1
WHERE r1."UserCode" = r."UserCode" AND r1."Date" = r."Date" AND r1."Time" = MAX(r."Time")) AS "Login Status",
r."Date", "ClientName", MAX(r."Time") AS "Login Time"
FROM USR5 r
WHERE r."ProcName" = 'SAP Business One.exe' AND CAST(r."Date" AS varchar(50)) = CAST(NOW() AS varchar(50))
GROUP BY r."UserCode",r."Date", "ClientName" HAVING MAX("Time") = MAX(r."Time")
but am not getting any result,query is running but with no result.
Regards,
Manish
Hi
Try the below it is worked on my system
select UserCode ,
(select case when Action = 'I' then 'OPEN' when Action = 'O' then 'CLOSED' end from USR5 r1
where r1.UserCode = r.UserCode and r1.Date = r.Date and r1.Time = Max(r.Time)) [Login Status]
, DATE, ClientName, Max(r.Time) [Login Time]
from USR5 r where ProcName = 'SAP Business One.exe' and CONVERT(VARCHAR(50), r.DATE ,103) = CONVERT(VARCHAR(50), getdate() ,103)
group by
UserCode , DATE, ClientName
having MAX(time) = Max(r.Time)
With Regards
Balaji Sampath
User | Count |
---|---|
94 | |
11 | |
10 | |
6 | |
5 | |
5 | |
4 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.