SQL Query CONCAT

Hello!
I have a problem that I can’t solve, mainly because I don’t see the problem!

This TripleMap works fine:

    <#TriplesMapGSDRB_SCHEDA_RB_TITLE> a rr:TriplesMap;
    rr:logicalTable [ rr:sqlQuery """SELECT rb.ID_CATALOGO, rb.IDK, rb.CUTT, rb.CUTC,
                                            cup.REF_CATALOGO,
                                            CASE
                                                WHEN CUTC IS NOT NULL AND CUPN IS NOT NULL AND CUPM IS NOT NULL THEN CONCAT(rb.CUTT, ' : ', rb.CUTC, '. ', CUPN, ', ', CUPM)
                                                WHEN CUTC IS NOT NULL AND CUPN IS NOT NULL AND CUPM IS NULL THEN CONCAT(rb.CUTT, ' : ', rb.CUTC, '. ', CUPN)
                                                WHEN CUTC IS NOT NULL AND CUPN IS NULL AND CUPM IS NULL THEN CONCAT(rb.CUTT, ' : ', rb.CUTC)
                                                WHEN CUTC IS NOT NULL AND CUPN IS NULL AND CUPM IS NOT NULL THEN CONCAT(rb.CUTT, ' : ', rb.CUTC, '. ', CUPM)
                                                WHEN CUTC IS NULL AND CUPN IS NOT NULL AND CUPM IS NOT NULL THEN CONCAT(rb.CUTT, '. ', CUPN)
                                                WHEN CUTC IS NULL AND CUPN IS NULL AND CUPM IS NOT NULL THEN CONCAT(rb.CUTT, '. ', CUPM)
                                                WHEN CUTC IS NULL AND CUPN IS NOT NULL AND CUPM IS NOT NULL THEN CONCAT(rb.CUTT, '. ', CUPN, ', ', CUPM)
                                                ELSE rb.CUTT
                                            END AS TITLE
                                      FROM DB.RB.GSDRB_SCHEDA_RB rb
                                      LEFT JOIN DB.RB.GSDRB_SUB_CUP cup ON rb.ID_CATALOGO = cup.REF_CATALOGO
                                      WHERE rb.VSV = 'S' AND (rb."CAST"='L' OR rb."CAST"='B'); """ ] ;
    rr:subjectMap [ rr:termType rr:IRI  ; rr:template "http://localhost:8890/DB/instance/title/{ID_CATALOGO}#this"; rr:class bf:Title; rr:graph <http://localhost:8890/DB#> ];
    rr:predicateObjectMap [ rr:predicateMap [ rr:constant DB:id_catalogo ] ; rr:objectMap [ rr:column "ID_CATALOGO" ]; ] ;
    rr:predicateObjectMap [ rr:predicateMap [ rr:constant DB:idk ] ; rr:objectMap [ rr:column "IDK" ]; ] ;
    rr:predicateObjectMap [ rr:predicateMap [ rr:constant bf:mainTitle ] ; rr:objectMap [ rr:column "CUTT" ]; ] ;
    rr:predicateObjectMap [ rr:predicateMap [ rr:constant bf:subTitle ] ; rr:objectMap [ rr:column "CUTC" ]; ] ;
    rr:predicateObjectMap [ rr:predicateMap [ rr:constant bf:title ] ; rr:objectMap [ rr:column "TITLE" ]; ] ;
    rr:predicateObjectMap [ rr:predicateMap [ rr:constant bf:title ] ; rr:objectMap [ rr:column "TITLE" ]; ] .

(the last two rows are identical because of this not solved issue: https://community.openlinksw.com/t/last-triplemap-row-is-not-visible-in-linked-data-views/3732 )

This TripleMap, instead, not works because of TITLE. (If I delete the rr:column “TITLE” works fine)

    <#TriplesMapGSDRB_SUB_CUA_VARIANT_TITLE> a rr:TriplesMap;
    rr:logicalTable [ rr:sqlQuery """SELECT rb.ID_CATALOGO, rb.IDK,
                                     cua.SUBCOUNTER, cua.REF_CATALOGO, cua.CUAP, cua.CUAT, cua.CUAL, cua.CUAR, cua.CUAC,
                                     CASE
                                        WHEN CUAT IS NOT NULL AND CUAC IS NOT NULL THEN CONCAT(cua.CUAT, ' : ', cua.CUAC)
                                        WHEN CUAT IS NULL THEN cua.CUAC
                                        WHEN CUAC IS NULL THEN cua.CUAT
                                        ELSE NULL
                                      END AS TITLE,
                                      li.ISO2
                                  FROM DB.RB.GSDRB_SCHEDA_RB rb
                                  LEFT JOIN DB.RB.GSDRB_SUB_CUA cua ON rb.ID_CATALOGO=cua.REF_CATALOGO
                                  LEFT JOIN DB.VOC.VOC_LINGUE li ON li.ISO3=cua.CUAL
                                  WHERE REF_CATALOGO IS NOT NULL AND rb.VSV = 'S' AND (rb."CAST"='L' OR rb."CAST"='B');""" ] ;
    rr:subjectMap [ rr:termType rr:IRI  ; rr:template "http://localhost:8890/DB/instance/variantTitle/{SUBCOUNTER}#this"; rr:class bf:VariantTitle; rr:graph <http://localhost:8890/DB#> ];
    rr:predicateObjectMap [ rr:predicateMap [ rr:constant DB:id_catalogo ] ; rr:objectMap [ rr:column "ID_CATALOGO" ]; ] ;
    rr:predicateObjectMap [ rr:predicateMap [ rr:constant DB:idk ] ; rr:objectMap [ rr:column "IDK" ]; ] ;
    rr:predicateObjectMap [ rr:predicateMap [ rr:constant DB:subcounter ] ; rr:objectMap [ rr:column "SUBCOUNTER" ]; ] ;
    rr:predicateObjectMap [ rr:predicateMap [ rr:constant DB:ref_catalogo ] ; rr:objectMap [ rr:column "REF_CATALOGO" ]; ] ;
    rr:predicateObjectMap [ rr:predicateMap [ rr:constant DB:cuap ] ; rr:objectMap [ rr:column "CUAP" ]; ] ;
    rr:predicateObjectMap [ rr:predicateMap [ rr:constant bf:mainTitle ] ; rr:objectMap [ rr:column "CUAT" ]; ] ;
    rr:predicateObjectMap [ rr:predicateMap [ rr:constant DB:cual ] ; rr:objectMap [ rr:column "CUAL" ]; ] ;
    rr:predicateObjectMap [ rr:predicateMap [ rr:constant DB:cuar ] ; rr:objectMap [ rr:column "CUAR" ]; ] ;
    rr:predicateObjectMap [ rr:predicateMap [ rr:constant bf:subTitle ] ; rr:objectMap [ rr:column "CUAC" ]; ] ;
    rr:predicateObjectMap [ rr:predicateMap [ rr:constant DB:iso2 ] ; rr:objectMap [ rr:column "ISO2" ]; ] ;
    rr:predicateObjectMap [ rr:predicateMap [ rr:constant bf:title ] ; rr:objectMap [ rr:column "TITLE" ]; ] ; #se aggiungo questa colonna non fa vedere più nulla.
    rr:predicateObjectMap
      [ rr:predicateMap
          [ rr:constant dcterms:relation ] ;
        rr:objectMap
          [ rr:parentTriplesMap <#TriplesMapVOC_RB_CUAP_CONCEPT>;
            rr:joinCondition
              [ rr:child "CUAP" ;
                rr:parent "CODE"
              ] ;
          ];
      ] .

I think there are no substantial differences between the two blocks of code. How come one works and the other doesn’t?

What is the version of Virtuoso being used ?

Are the SQL tables being queried local or remote ?

Do you have a step by step test case for recreating ?

Not sure what you mean by “If I delete the rr:column “TITLE” works fine” , how exactly are you doing this ?

This is the version of my Virtuoso:

name version build_date thread opsys
OpenLink Virtuoso Server 07.20.3236 Feb 27 2023 -pthreads Linux

The SQL tables are inside Openlink Relational DB, so local.

This is the link with a Test case

Not sure what you mean by “If I delete the rr:column “TITLE” works fine ” , how exactly are you doing this ?

If i delete the predicateObjectMap in r2rml with the column TITLE, all works fine… so maybe the problem is on it.

What happens to the Title column when you run the following independently via iSQL?

SELECT rb.ID_CATALOGO, rb.IDK, rb.CUTT, rb.CUTC, cup.REF_CATALOGO,
    CASE
        WHEN CUTC IS NOT NULL AND CUPN IS NOT NULL AND CUPM IS NOT NULL THEN CONCAT(rb.CUTT, ' : ', rb.CUTC, '. ', CUPN, ', ', CUPM)
        WHEN CUTC IS NOT NULL AND CUPN IS NOT NULL AND CUPM IS NULL THEN CONCAT(rb.CUTT, ' : ', rb.CUTC, '. ', CUPN)
        WHEN CUTC IS NOT NULL AND CUPN IS NULL AND CUPM IS NULL THEN CONCAT(rb.CUTT, ' : ', rb.CUTC)
        WHEN CUTC IS NOT NULL AND CUPN IS NULL AND CUPM IS NOT NULL THEN CONCAT(rb.CUTT, ' : ', rb.CUTC, '. ', CUPM)
        WHEN CUTC IS NULL AND CUPN IS NOT NULL AND CUPM IS NOT NULL THEN CONCAT(rb.CUTT, '. ', CUPN)
        WHEN CUTC IS NULL AND CUPN IS NULL AND CUPM IS NOT NULL THEN CONCAT(rb.CUTT, '. ', CUPM)
        WHEN CUTC IS NULL AND CUPN IS NOT NULL AND CUPM IS NOT NULL THEN CONCAT(rb.CUTT, '. ', CUPN, ', ', CUPM)
        ELSE rb.CUTT
    END AS TITLE
FROM DB.RB.GSDRB_SCHEDA_RB rb
LEFT JOIN DB.RB.GSDRB_SUB_CUP cup ON rb.ID_CATALOGO = cup.REF_CATALOGO
WHERE rb.VSV = 'S' AND (rb."CAST"='L' OR rb."CAST"='B'); 
SELECT rb.ID_CATALOGO, rb.IDK, cua.SUBCOUNTER, cua.REF_CATALOGO, cua.CUAP, cua.CUAT, cua.CUAL, cua.CUAR, cua.CUAC,
        CASE
            WHEN CUAT IS NOT NULL AND CUAC IS NOT NULL THEN CONCAT(cua.CUAT, ' : ', cua.CUAC)
            WHEN CUAT IS NULL THEN cua.CUAC
            WHEN CUAC IS NULL THEN cua.CUAT
            ELSE NULL
        END AS TITLE,
        li.ISO2
FROM DB.RB.GSDRB_SCHEDA_RB rb
LEFT JOIN DB.RB.GSDRB_SUB_CUA cua ON rb.ID_CATALOGO=cua.REF_CATALOGO
LEFT JOIN DB.VOC.VOC_LINGUE li ON li.ISO3=cua.CUAL
WHERE REF_CATALOGO IS NOT NULL AND rb.VSV = 'S' AND (rb."CAST"='L' OR rb."CAST"='B');

Hello kidehen.
I tested the queries on iSQL and they work well.

Are you able to provide a SPARQL Query snippet that showcases;

  1. Working properly
  2. Not working property

/cc @hwilliams

How do you load the SQL data into Virtuoso as when I attempt to load with the isql command line tool it is failing with a number of errors:

SQL> load load.sql;

Done. -- 17 msec.

*** Error 37000: VD [Virtuoso Server]SQ074: Line 2 (line 47 of "load.sql"): syntax error
in lines 46-48 of load load.sql:
#line 46 "load.sql"
INSERT INTO GECA.RB.GSDRB_SCHEDA_RB (ID_CATALOGO,TSK,IDK,VSV,NINN,NINR,NINL,ESC,GAF,ERP,CASI,CASM,"CAST",CASB,CASU,CAB,CAM,CAF,CADS,CADD,CADA,CADQ,CADM,CADC,CADR,CADP,CADL,CADI,CADY,CTP,CUTT,CUTR,CUTL,CUTC,CMZD,CMZN,AGRD,AGRN,OSS) VALUES
	 (435,'RB','IT/ItRC/00000435','S','Geca_rdc','435','http://geca.area.ge.cnr.it/scheda-bibliografica/index.php?id=435&sheet=etichette','CNR','N',' | Discovery','N','C','B','3','Mazzoleni-77','S','M','1','M_TXT','1964',NULL,'a','n','N','S','QUAMSTR','Inglese','0','Paesi Bassi','Vol. 82, n. 1 (Jan. 1964)-','Biochimica et biophysica acta','biochimica et biophysica acta','B','international journal of biochemistry and biophysics','20140101','GecaSup.00','20170309','GecaSup.00',NULL),
 	 (437,'RB','IT/ItRC/00000437','S','Geca_rdc','437','http://geca.area.ge.cnr.it/scheda-bibliografica/index.php?id=437&sheet=etichette','CNR','N',' | Discovery','N','C','B','3','Mazzoleni-77','S','M','1','M_TXT','1947','1963','b','n','N','S','SETT','Inglese, Francese, Tedesco','0','Paesi Bassi','Vol. 1 (Jan. 1947)-vol. 78, n. 4 (Dec. 1963)','Biochimica et biophysica acta','biochimica biophysica acta','B','international weekly of biochemistry and biophysics','20140101','GecaSup.00','20170224','GecaSup.00','Aggiornata da Alice D''Albis')

Done. -- 5 msec.

*** Error 37000: VD [Virtuoso Server]SQ074: Line 2 (line 62 of "load.sql"): syntax error
in lines 61-63 of load load.sql:
#line 61 "load.sql"
INSERT INTO GECA.RB.GSDRB_SUB_CUA (SUBCOUNTER,REF_CATALOGO,CUAP,CUAT,CUAL,CUAR,CUAC) VALUES
	 (1423,437,'P',NULL,'fre',NULL,'revue internationale hebdomadaire de biochimie et biophysique'),
	 (1424,437,'P',NULL,'deu',NULL,'internationale Wochenschrift für Biochemie und Biophysik')

Done. -- 4 msec.

*** Error 37000: VD [Virtuoso Server]SQ074: Line 2 (line 74 of "load.sql"): syntax error
in lines 73-83 of load load.sql:
#line 73 "load.sql"
INSERT INTO GECA.VOC.VOC_LINGUE (id,lingua,ISO3,ISO2) VALUES
	 (1,'Multilingue','mul',NULL),
	 (2,'Italiano','ita','it'),
	 (3,'Inglese','eng','en'),
	 (4,'Francese','fre','fr'),
	 (5,'Tedesco','deu','de'),
	 (6,'Spagnolo','spa','es'),
	 (7,'Afrihili','afh',NULL),
	 (8,'Afrikaans','afr','af'),
	 (9,'Ainu','ain',NULL),
	 (10,'Akan','aka','ak')

Done. -- 5 msec.
/opt/virtuoso/bin/isql: get_next_token: Unknown macro word "d" at line 95 of load load.sql
/opt/virtuoso/bin/isql: get_next_token: Unknown macro word "a" at line 95 of load load.sql
/opt/virtuoso/bin/isql: get_next_token: Unknown macro word "b" at line 95 of load load.sql
/opt/virtuoso/bin/isql: get_next_token: Unknown macro word "d" at line 95 of load load.sql
/opt/virtuoso/bin/isql: get_next_token: Unknown macro word "a" at line 95 of load load.sql
/opt/virtuoso/bin/isql: get_next_token: Unknown macro word "b" at line 95 of load load.sql
/opt/virtuoso/bin/isql: get_next_token: Unknown macro word "c" at line 95 of load load.sql
/opt/virtuoso/bin/isql: get_next_token: Unknown macro word "a" at line 95 of load load.sql

*** Error 37000: VD [Virtuoso Server]SQ074: Line 2 (line 96 of "load.sql"): syntax error
in lines 95-98 of load load.sql:
#line 95 "load.sql"
INSERT INTO GECA.VOC.VOC_RB_CUAP (ID,CODE,UNIMARC,MARC21,VALUE_I,VALUE_E) VALUES
	 (2,'P','200 NULL - 510 NULL','245 NULL - 246 | 1 | 1','Titolo Parallelo','Parallel title'),
	 (3,'PU','200 NULL - 510 NULL','245 NULL - 246 | 1 | 1','Parallelo dell''ulteriore Titolo','Parallel of the further title'),
	 (1,'U','200 NULL','245 NULL','Ulteriore Titolo','Further title')

Done. -- 4 msec.

*** Error 37000: VD [Virtuoso Server]SQ074: Line 2 (line 108 of "load.sql"): syntax error
in lines 107-110 of load load.sql:
#line 107 "load.sql"
INSERT INTO GECA.RB.GSDRB_SUB_CQ (SUBCOUNTER,REF_CATALOGO,CQA) VALUES
	 (1,435,'1964-'),
	 (2,436,'1964-1964'),
	 (3,437,'1947-1963')

Done. -- 7 msec.

*** Error 37000: VD [Virtuoso Server]SQ074: Line 2 (line 128 of "load.sql"): syntax error
in lines 127-130 of load load.sql:
#line 127 "load.sql"
INSERT INTO GECA.RB.GSDRB_SUB_CQP (SUBCOUNTER,REF_CATALOGO,REF_CQ,CQPP,CQPL,CQPE,CQPG,CQPS,CQPA,CQPN,CQPR) VALUES
	 (1,435,1,'PR','Amsterdam','Elsevier',NULL,NULL,NULL,'id catalogo_pubblicazione: 109',NULL),
	 (2,436,2,'PR','Amsterdam','Elsevier',NULL,NULL,NULL,'id catalogo_pubblicazione: 110',NULL),
	 (3,437,3,'PR','Amsterdam','Elsevier',NULL,NULL,NULL,'id catalogo_pubblicazione: 111',NULL)

Done. -- 6 msec.
/opt/virtuoso/bin/isql: get_next_token: Unknown macro word "a" at line 143 of load load.sql
/opt/virtuoso/bin/isql: get_next_token: Unknown macro word "c" at line 143 of load load.sql
/opt/virtuoso/bin/isql: get_next_token: Unknown macro word "a" at line 143 of load load.sql
/opt/virtuoso/bin/isql: get_next_token: Unknown macro word "b" at line 143 of load load.sql

*** Error 37000: VD [Virtuoso Server]SQ074: Line 2 (line 144 of "load.sql"): syntax error
in lines 143-146 of load load.sql:
#line 143 "load.sql"
INSERT INTO GECA.VOC.VOC_RB_CQPP (ID,CODE,UNIMARC,MARC21,VALUE_I,VALUE_E) VALUES
	 (2,'PA','210 NULL= e NULL=','260 NULL e NULL','Pubblicazione Parallela','Parallel publication'),
	 (4,'PR','210','260','Pubblicazione Principale','Principal publication'),
	 (1,'U','210','260','Ulteriore Pubblicazione','Further publication')
SQL>

Hello, I loaded the data in my real project via ETL.

In this test case I auto generate SQL script via DBEver.
I fixed the problems on sql insert.

Now in the shared folder you can find 2 files SQL:
01.1 SQL QUERY CREATE.sql <---- Table Creations
01.2 SQL QUERY INSERT.sql <---- Table Insert

I hope that now I’ll be more clear.

FIRST ISSUE (works only if I duplicate predicateObjectMap bf:title, column “TITLE” in
TriplesMapGSDRB_SCHEDA_RB_TITLE):

Case OK:

  <#TriplesMapGSDRB_SCHEDA_RB_TITLE> a rr:TriplesMap;
    .....
    rr:predicateObjectMap [ rr:predicateMap [ rr:constant bf:title ] ; rr:objectMap [ rr:column "TITLE" ]; ] ;
    rr:predicateObjectMap [ rr:predicateMap [ rr:constant bf:title ] ; rr:objectMap [ rr:column "TITLE" ]; ] .

CASE KO:

  <#TriplesMapGSDRB_SCHEDA_RB_TITLE> a rr:TriplesMap;
     .....
    rr:predicateObjectMap [ rr:predicateMap [ rr:constant bf:title ] ; rr:objectMap [ rr:column "TITLE" ]; ] ;

So, why I have to write two times the same predicaObjectMap?

SECOND ISSUE (Works fine only if THERE ISN’T predicateObjectMap bf:title, column “TITLE” inTriplesMapGSDRB_SCHEDA_RB_VARIANT_TITLE):

Case OK, but in R2RML there isn’t bf:title predicate:

  <#TriplesMapGSDRB_SCHEDA_RB_VARIANT_TITLE> a rr:TriplesMap;
     .....
#excluded: rr:predicateObjectMap [ rr:predicateMap [ rr:constant bf:title ] ; rr:objectMap [ rr:column "TITLE" ]; ] ; 

Case KO, in R2RML there is:

  <#TriplesMapGSDRB_SCHEDA_RB_VARIANT_TITLE> a rr:TriplesMap;
     .....
   rr:predicateObjectMap [ rr:predicateMap [ rr:constant bf:title ] ; rr:objectMap [ rr:column "TITLE" ]; ] ; 

So, why when I have in R2RML the predicate bf:title, all the others predicate disappear and when there isn’t all works fine?

We are going to recreate this in-house and assess what’s going on.

/cc @hwilliams

Thank you a lot!

As you can understand, these are my first steps with R2RML and Open Link Virtuoso, so if you have any suggestions for improvement, I’m happy to receive them.

Have you actually loaded the SQL scripts and R2RML script into your Virtuoso instance ? As there are a number inconsistencies in the scripts for example , the tables are loaded as GECA.PROVA.* , where the R2RML reference them with a combination of GECA.RB.* and GECA.VOC.* ; also there are no grant to SPARQL_SELECT on any of the tables ?

Anyway the R2RML script is now loading, so what queries are to be run to demonstrate your problem(s) ?

Also, what are those SPARQL | HTML Microdata document screenshots on previous post ie how are you generating them and what are there significance ?

Have you actually loaded the SQL scripts and R2RML script into your Virtuoso instance ?

Obviusly, impossible to generate previous screen without SQL scripts, grant and R2RML into my instance.

Anyway the R2RML script is now loading, so what queries are to be run to demonstrate your problem(s) ?

You can find all the problems that I have just reading the R2RML comment lines in the file

For example you can try on SPARQL endpoint:

DESCRIBE <http://localhost:8890/GECA/instance/title/437#this>
and
DESCRIBE <http://localhost:8890/GECA/instance/437#this>

Do you see the bf:title predicate?

Than try:

DESCRIBE <http://localhost:8890/GECA/voc_rb_cqpp/1#this>
DESCRIBE <http://localhost:8890/GECA/voc_rb_cqpp/2#this>

Do you see the rr:language?

I have setup the R2RML script on a public instance on domain hfw.openlinksw.com:8890 and the first DESCRIBE query returns results:

Description of entity denoted by http://hfw.openlinksw.com:8890/GECA/instance/437#this

But the second does not as no mappings where created for it:

Description of entity denoted by http://hfw.openlinksw.com:8890/GECA/voc_rb_cqpp/1#this

You can access the SPARQL endpoint yourself to check …