1

How does one avoid creating a null field when doing a concat with a field that is null in Firebird.

with data as ( select article_name || ' STK:' || cast(quantity as integer) || NOTES as quantity ,PREORDER_ID as PID  from preorder_item ) 

SELECT PREORDER.NAME ,PREORDER.DELIVERY_DATE ,LIST(DATA.QUANTITY, ' | ')
FROM PREORDER 
INNER JOIN DATA ON PREORDER.ID = 
DATA.PID GROUP by PREORDER.NAME ,PREORDER.DELIVERY_DATE

In all the fields were my NOTES are NULL the whole content is null how can I work around this and only add the notes information when it is available.

I already tried something like

with comments as (select NOTES as NOTES,PREORDER_ID as PID FROM PREORDER_ITEM where NOTES is not NULL)
with data as ( select article_name || ' STK:' || cast(quantity as integer) as quantity ,PREORDER_ID as PID  from preorder_item ) 

SELECT PREORDER.NAME ,PREORDER.DELIVERY_DATE ,LIST(DATA.QUANTITY, ' | ')
FROM PREORDER 
INNER JOIN DATA ON PREORDER.ID = DATA.PID 
INNER JOIN COMMENTS ON PREORDER.ID = COMMENTS.PID

GROUP by PREORDER.NAME ,PREORDER.DELIVERY_DATE
Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Michael Pietzsch
  • 115
  • 1
  • 5
  • Maybe just do NOT do concat then? You seem to mix data generation and data presentation functions/layers. Firebird should give you each column as its own value, binary, with bit to bit precision. It does not have to be human-readable, less so beauty-printed. The latter is "presentation", it is for "report generators" and similar CLIENT software to do. It is same idea as splitting web pages into XML with data and CSS with presentation information, like MVC pattern, etc. You try to abuse RDBMS engine into what it was not supposed to do. The further you go the harder it would be. Sisyphus' rock. – Arioch 'The Jul 07 '21 at 09:07
  • This is standard behaviour specified by the SQL standard: expressions involving NULL result in NULL, so this is not Firebird specific. See the duplicate for details. – Mark Rotteveel Jul 07 '21 at 11:14

1 Answers1

2

You can take a look to SQL function COALESCE or NULLIF which allow you to transform null value

COALESCE( Expr1, Expr2, Expr3, ... )

NULLIF (<exp1>, <exp2>)
Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Hugues Van Landeghem
  • 6,755
  • 3
  • 34
  • 59
  • 1
    `NULLIF` does the opposite of what is needed here. `NULLIF` returns the value of ``, unless that value is equal to ``, in which case it returns `NULL`. – Mark Rotteveel Jul 07 '21 at 11:12