I am trying to mine some data from tables that were separated from our Content Server instance (due to the size of the tables involved). Another analyst provided me a LiveReport written for Oracle. I copied the SQL out to try and run against our SQL Server DB. I made some changes that cleared some of the initial errors (to_char is not supported apparently). But my SQL knowledge is very limited and I have hit a brick wall. I am hoping someone here can help with the conversion of the following Oracle SQL into something that will work on a SQL Server DB. Also, I think this SQL would be beneficial to a lot of people so if you are on an Oracle DB, you might want to test it as is in a LiveReport (I am told it is resource intensive so test on a DEV instance). And if we can get the SQL Server version then that would help the other half of our friends. I seem to be running into issues with the CASE section right now (on my test I got rid of the to_char commands).
+++
Select da.eventid, da.auditid, da.auditstr,
to_char(da.auditdate, 'yyyy-Mon-dd hh24:mi:ss') "Date", da.performerid, k1.name "Maker",
da.userid "GroupID", k2.name "GroupName",
case
when da.auditid = 1 then 'Created'
when da.auditid = 2 then 'Deleted'
when da.auditid = 10 and da.value1 is not NULL then 'Made: ['||trim(da.valuekey)||']'||(select case k7.type when 0 then '-User-[' when 1 then '-Group-[' else '-' end||k7.name||']' from kuaf k7 where k7.id = to_number(trim(da.value1)))
when da.auditid = 10 and da.value2 is not NULL then 'Removed: ['||trim(da.valuekey)||']'||(select case k8.type when 0 then '-User-[' when 1 then '-Group-[' else '-' end||k8.name||']' from kuaf k8 where k8.id = to_number(trim(da.value2)))
when da.auditid = 30 and da.value1 is not NULL then 'Removed from: ['||to_number(trim(da.value1))||']'||(select case k3.type when 0 then '-User-[' when 1 then '-Group-[' else '-' end||k3.name||']' from kuaf k3 where k3.id = to_number(trim(da.value1)))
when da.auditid = 30 and da.value2 is not NULL then 'Added to: ['||to_number(trim(da.value2))||']'||(select case k4.type when 0 then '-User-[' when 1 then '-Group-[' else '-' end||k4.name||']' from kuaf k4 where k4.id = to_number(trim(da.value2)))
when da.auditid = 33 and da.value1 is not NULL then 'Removed : ['||to_number(trim(da.value1))||']'||(select case k5.type when 0 then '-User-[' when 1 then '-Group-[' else '-' end||k5.name||']' from kuaf k5 where k5.id = to_number(trim(da.value1)))
when da.auditid = 33 and da.value2 is not NULL then 'Added : ['||to_number(trim(da.value2))||']'||(select case k6.type when 0 then '-User-[' when 1 then '-Group-[' else '-' end||k6.name||']' from kuaf k6 where k6.id = to_number(trim(da.value2)))
when da.value1 is NULL then ''
else 'Other ['||to_char(da.value1)||']' end Val1
from dauditnew da
join kuaf k1 on da.performerid = k1.id
join kuaf k2 on da.userid = k2.id and k2.type = 1
where da.auditid in (1, 2, 10, 30, 33) and da.userid = %1
order by da.userid, da.eventid
+++
