[SOLVED]sc_concat: No subselect possible

Hi
i want to show in a select field, how many rooms free. For that, i need a subselect:

SELECT `Zimmer`.`ZimmerId`, sc_concat(`Zimmer`.`Gebaeude`,': ',`Zimmer`.`ZimmerNr`,' (',(SELECT COUNT(`ZimmerId`) FROM `TnZimmerLnk` WHERE `TnZimmerLnk`.`ZimmerId`=`Zimmer`.`ZimmerId`),'/',`AnzahlPersonen`,')') FROM `Zimmer` ORDER BY `Zimmer`.`Gebaeude`

This not work. If i do the same statement with a concat instead of sc_concat, everything work?

What is the advantage of sc_concat compared with concat?

Best regards
Steve

[QUOTE=stephanw;35247]Hi
i want to show in a select field, how many rooms free. For that, i need a subselect:

SELECT `Zimmer`.`ZimmerId`, sc_concat(`Zimmer`.`Gebaeude`,': ',`Zimmer`.`ZimmerNr`,' (',(SELECT COUNT(`ZimmerId`) FROM `TnZimmerLnk` WHERE `TnZimmerLnk`.`ZimmerId`=`Zimmer`.`ZimmerId`),'/',`AnzahlPersonen`,')') FROM `Zimmer` ORDER BY `Zimmer`.`Gebaeude`

This not work. If i do the same statement with a concat instead of sc_concat, everything work?

What is the advantage of sc_concat compared with concat?

Best regards
Steve[/QUOTE]

Not sure, but sc_concat is a scriptcase macro and that doesn’t interpret the inner code between (). You’re using MySQL? concat looks as a valid sql statement for me, but it is not supported for every database. The advantage of using sc_concat would be portablility.

But actually, I’m very pleased by your post, as I always overlooked this possibility.

Hello,

I simulated the problem and I am replaying to our development team.

Thank you!

Hello,

The sc_concat macro does not support for subselect. For this you must use the CONCAT function of your database.

Example:

SELECT productid, CONCAT(productname, ’ - ', (SELECT COUNT(*) AS Total FROM orders)) as amount
FROM products
ORDER BY productname