Display the field in a table as markdown link

[castlekaybee]

From the examples:

select 'table' as component, 'action' as markdown;
select *, format('[Edit](edit.sql?id=%s)', id) as action from users;

How to display instead of the static text "Edit" the value from an SQL (SQLite) query?
I tried:

(
  SELECT
    '[' || Agg01 || '](/UI/MaschinenKomponenten/QueryDelete.sql?m_id_from_url=)'  
    from MaschineAggregate 
    WHERE M_ID = 4
) AS action

[YoannCalamai]

Hi,

Here is an example of how I do that :

SELECT 
    'table' as component,
    'action' as markdown,
    1 as sort,
    1 as search;
SELECT
    s.Name as Saison,
   co.Name as Nom,
   to_char(co.StartDate,'DD/MM/YYYY') as "Date de début",
   to_char(co.EndDate,'DD/MM/YYYY') as "Date de fin",
   to_char(co.DeliveryDate,'DD/MM/YYYY') as "Date de livraison",
   '[Mettre à jour](./Campaign.sql?id=' || CampagnOrderId ||')' ||
   ' - [Voir les commandes](/order/Orders.sql?coid=' || CampagnOrderId ||')'
   as Action
FROM CampagnOrder co
JOIN Season s on co.SeasonId=s.SeasonId
WHERE s.StartDate<=CURRENT_TIMESTAMP AND CURRENT_TIMESTAMP<=s.EndDate
ORDER BY co.StartDate DESC, co.Name;

[lovasoa]

Hello !
@castlekaybee , can you tell us a bit more about what you are building, what your tables look like, the full sql for your page, and what errors you are seeing if any?

What you say you tried seems syntactically correct, bit logically a bit usual. If you need to include data from another table, then what you probably want is a join.

[castlekaybee]

In order to keep it general, let's stick with the sqlpage example:

select 'table' as component, 'action' as markdown;
select *, format('[Edit](edit.sql?id=%s)', id) as action from users;

What I want is instead of the Text "Edit" I want to include a select statement

select 'table' as component, 'action' as markdown;
select *, format('[' || (SELECT someTextColumn FROM someTable WHERE textID = 2) || '](edit.sql?id=%s)', id) as action from users;

[lovasoa]

This should work. Do you have a problem getting it to run ?

As a side note, it is probably more readable written like this (using the format function for the link text too):

select 'table' as component, 'action' as markdown;
select
  *,
  format(
    '[%s](edit.sql?id=%s)', 
    (select x from y where z), -- this must return a single row
    id
  ) as action
from users;

[castlekaybee]

Thanks I'll try