Concatenation of field and string data

[treed]

Hello all, yes this has been a busy day and I'm getting more productive with nuBuilder I have so many questions. Can the report builder concatenate a string with field data??? Something like this: myfield +' My String'

[kev1n]

Concat in SQL already with the CONCAT function.

[treed]

Added this to my report sql and then the report would not open (reported as missing page). Removed it and report opens fine.
CONCAT(OrdBillToCity,", ",OrdBillToState," ",OrdBillToZip) AS BillCSZ,
CONCAT(OrdShipToCity, ", ",OrdShipToState," ",OrdShipToZip) AS ShipCSZ

When I run the whole sql statement for the report in PHPMyAdmin it runs fine.
SELECT
Orders.*,
OrdLine.*,
CONCAT(OrdBillToCity,", ",OrdBillToState," ",OrdBillToZip) AS BillCSZ,
CONCAT(OrdShipToCity, ", ",OrdShipToState," ",OrdShipToZip) AS ShipCSZ

FROM
Orders
JOIN OrdLine ON Orders.OrdID = OrdLine.OrdLineOrdID

Any idea what might be going on here?

[Janusz]

maybe try:
SELECT * FROM ( your code ) t


SELECT * FROM
(SELECT
Orders.*,
OrdLine.*,
CONCAT(OrdBillToCity,", ",OrdBillToState," ",OrdBillToZip) AS BillCSZ,
CONCAT(OrdShipToCity, ", ",OrdShipToState," ",OrdShipToZip) AS ShipCSZ

FROM
Orders
JOIN OrdLine ON Orders.OrdID = OrdLine.OrdLineOrdID) t

[treed]

Same results. Tried simplifying it to simply adding a CONCAT(" ") and still have the same results. And ... then just tried it with single quotes and IT WORKS!

[treed]

So I think that implies that "problem" is somewhere in the nuBuilder parser where it turns the sql string into JSON. It's rather frustrating to have SQL that works in one place and not another not be able to tell why.

[apmuthu]

If you must use double quotes, try escaping it with a backslash.

[yvesf]

Hello,

When I save the sql query, the clause CONCAT is removed. Why ?

Code: Select all

SELECT
 patient.*,
    rendezvous.*,
CONCAT(patient.pat_nom,' ',patient.pat_prenom) AS PatientRV

FROM
    patient
        JOIN rendezvous ON patient.patient_id = rendezvous.rv_patient_id

WHERE
    ((rendezvous_id ='#RECORD_ID#'))

The section CONCAT(patient.pat_nom,' ',patient.pat_prenom) AS PatientRV is removed when saving the sql query. .

Capture.PNG

[kev1n]

Pick "Edit Manually" from the SQL dropdown.

[Keith-i]

I'm also having some problems with CONCAT for a report. I'm getting an SQL syntax error message from nuDebug when I include the CONCAT line in the SQL and the generated report is empty. Any ideas as to where I'm going wrong would be appreciated.

Code: Select all

SELECT
 tblRoads.*,
    tblProperties.*,   
    tblInstructions.JobNo,
    tblInstructions.FlatNo,
    tblInstructions.Valuation

CONCAT_WS(' ',tblProperties.BuildingName,tblProperties.UnitNo,tblProperties.UnitName,tblProperties.HouseNo) AS PropAddress

FROM
    tblRoads
        JOIN tblProperties ON tblProperties.id_Roads = tblRoads.idRoads
        JOIN tblInstructions ON tblInstructions.id_Properties = tblProperties.idProperties

WHERE
    ((tblRoads.idRoads = '#idRoads#'))

I'm using CONCAT_WS as many of my fields have null entries.