Originally posted by **lozdown** July 27, 2024
I have a simple Postgresql database application for managing attendance records of club members at events, with table for members, table for events and a many-to-many join table linking events and members.
This query works correctly in pgadmin4.
> ```sql
> with
> attendances as (
> select distinct
> em."members_Contact ID" contactid,
> (bool_or((CASE WHEN e.eventdate ='2024-01-17' THEN true END))) "2024-01-17"
> from events_members em inner join events e on em.events_id=e.id
> group by em."members_Contact ID"
> )
> select m."First Name",m."Last Name",
> case when "2024-01-17" then U&'\2713' else 'x' end as "2024-01-17"
>
> from members m left join attendances a on m."Contact ID"=a.contactid ;
> ```
Giving example result table
"F" "A" "✓"
"A" "A" "✓"
"C" "A" "x"
"J" "F" "x"
(nb "✓" indicates attended, "x" indicates absent)
However the same query used in SQLPage throws an error associated with the escaped unicode string U&'\2173'
> Error in query number 1:
>
> Failed to execute SQL statement:
> WITH attendances AS (SELECT DISTINCT em."members_Contact ID" AS contactid, (bool_or((CASE WHEN e.eventdate = '2024-01-17' THEN true END))) AS "2024-01-17" FROM events_members AS em JOIN events AS e ON em.events_id = e.id GROUP BY em."members_Contact ID") SELECT m."First Name", m."Last Name", CASE WHEN "2024-01-17" THEN U & '\2713' ELSE 'x' END AS "2024-01-17" FROM members AS m LEFT JOIN attendances AS a ON m."Contact ID" = a.contactid;
> ⬆️
> line 1, character 322
>
> Backtrace
>
> error returned from database: column "u" does not exist
>
> column "u" does not exist
The SQLPage definition is
> select
> 'table' as 'component', true as 'small',
> TRUE as search
> ;
>
> with
> attendances as (
> select distinct
> em."members_Contact ID" contactid,
> (bool_or((CASE WHEN e.eventdate ='2024-01-17' THEN true END))) "2024-01-17"
> from events_members em inner join events e on em.events_id=e.id
> group by em."members_Contact ID"
> )
> select m."First Name",m."Last Name",
> case when "2024-01-17" then &'\2713' else 'x' end as "2024-01-17"
>
> from members m left join attendances a on m."Contact ID"=a.contactid
> ;
If I change the U&\'2713' to '2713' the query executes (although clearly does not display tick mark that I want)
Discussed in https://github.com/lovasoa/SQLpage/discussions/511