Page 1 of 2
Concatenation of field and string data
Posted: Fri Oct 16, 2020 1:45 am
by 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'
Re: Concatenation of field and string data
Posted: Fri Oct 16, 2020 4:16 am
by kev1n
Concat in SQL already with the CONCAT function.
Re: Concatenation of field and string data
Posted: Fri Dec 18, 2020 11:53 pm
by 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?
Re: Concatenation of field and string data
Posted: Sat Dec 19, 2020 1:49 pm
by 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
Re: Concatenation of field and string data
Posted: Mon Dec 21, 2020 6:25 pm
by 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!
Re: Concatenation of field and string data
Posted: Mon Dec 21, 2020 6:50 pm
by 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.
Re: Concatenation of field and string data
Posted: Thu Dec 24, 2020 8:19 am
by apmuthu
If you must use double quotes, try escaping it with a backslash.
Re: Concatenation of field and string data
Posted: Mon Jan 02, 2023 6:23 pm
by 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
Re: Concatenation of field and string data
Posted: Mon Jan 02, 2023 6:27 pm
by kev1n
Pick "Edit Manually" from the SQL dropdown.
Re: Concatenation of field and string data
Posted: Mon Mar 13, 2023 4:57 pm
by 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.