Converting old school SQL to new CJs

This is a conversation specifically about the Form Report Links, but the general idea can be applied elsewhere.

Danielle Buczek (Brandeis)

I assume you're trying to get this as an export? I haven't tried in CJ, but if you look at the SQL for an old query you might be able to figure out where it lives. I'm happy to peek at it if you share the SQL.

Beth Storrs (Tufts Health Sciences)

Unfortunately, this is what it looks like:

select

p.[name] as [Name],

__u.[name] as [Interviewer],

__u3.[name] as [Interviewer 2],

datename(weekday, __f.[date]) as [Day],

try_convert(date, convert(varchar, __f.[date], 101)) as [Date],

(case when (__f.[date] != convert(date, __f.[date])) then format(__f.[date], 'h:mm tt') end) as [Time],

(select [value] from [config] where ([key] = 'https')) + '/register/?id=' + dbo.toGuidString(isnull(__f.[internal], __fp.[internal])) + '&person=' + dbo.toGuidString(__r.[record]) + '&h='+ dbo.toGuidString(dbo.md5(convert(varbinary(max), dbo.salt() + dbo.toGuidString(isnull(__f.[internal], __fp.[internal])) + dbo.toGuidString(__r.[record])))) as [Eval Link]

from [form.response] __rinner join [form] __f on (__f.[id] = __r.[form])

left outer join [form] __fp on (__fp.[id] = __f.[parent])

left outer join [user] __u on (__u.[id] = __f.[user])

left outer join [user] __u2 on (__u2.[id] = __r.[user])

left outer join [user] __u3 on (__u3.[id] = __f.[user2])

left outer join [application] a on (a.[id] = __r.[record])

inner join [person] p on (p.[id] = a.[person])

left outer join [activity] __pd on (__pd.[id] = __r.[payment_due]) and (__r.[payment_due] is not null)

where

(

(__f.[parent] = '81EBD4F8-2A12-4ABB-AFFF-41D81FAC1063') and (__r.[pending] = 0) and (__r.[active] = 1)

and

(

/* Current Application Period */

(a.[round] IN (select _lr.[id] from [lookup.round] _lr inner join [lookup.period] _lp on (_lr.[period] = _lp.[id]) where _lp.[active] = ('0')))

)

order by p.[last], p.[first]

Danielle Buczek (Brandeis)

I don't have any interview forms set up in my instance, but generally in cases like this, I try to rebuild it as a custom SQL export, where you replace the table references as appropriate. So in this piece:

(select [value] from [config] where ([key] = 'https')) + '/register/?id=' + dbo.toGuidString(isnull(__f.[internal], __fp.[internal])) + '&person=' + dbo.toGuidString(__r.[record]) + '&h='+ dbo.toGuidString(dbo.md5(convert(varbinary(max), dbo.salt() + dbo.toGuidString(isnull(__f.[internal], __fp.[internal])) + dbo.toGuidString(__r.[record]))))

You start out by replacing all occurrences of __r with fr__JID_ [only relevant when working from non-CJ code]
If you haven't already, add your join to the form table at the bottom of the query.
Click on Display SQL to find out what table alias was assigned for the form table. (In my sample query, it's f_ec86 and fp_ec86 for the parent). In these queries, it will look something like this:

from [form.response] fr__JID_
left outer join [form] f_a3c3 on (f_a3c3.[id] = fr__JID_.[form])
left outer join [form] fp_a3c3 on (fp_a3c3.[id] = f_a3c3.[parent])

Replace all occurrences of __f with the form table alias from your query (so f_ec86 from my query). Replace all occurrences of __fp with the form parent table alias (so fp_ec86 from my query). Note that for this, these are the same number!

Danielle Buczek (Brandeis)

But each time you build a new query, you would have different table aliases, which is really annoying.

From <https://app.slack.com/client/TFTG51QAW/threads/thread/CFUUKHULW-1677269823.903069>