0

SQLSTATE[42703]: Undefined column: 7 ERROR: column v_patient.employeeid does not exist LINE 16: ... LEFT JOIN v_emp v_patient ON v_patie... ^ HINT: Perhaps you meant to reference the column "v_patient.employeeID" or the column "v_patient.employeeCC". (SQL: SELECT sp .*,…

I'm getting error where few query is not capitalize when the actual column name should be capitalize, so I try to follow where someone said it should be putting in identifier double quote just like this "**" or backticks ``. Both doesn't work and still producing error, is that alternative for identifier double quote in laravel in order to put column that have capital letter on it?

$sql = "SELECT 
            sp.*,
            patient.v_emp_id as badge_number, v_patient.positionTitle as jabatan, v_patient.dpName as tempat_kerja, 
            `v_spv.employeeName` as name_spv, v_spv.phones as telp_spv, `v_patient.todayShiftDesc` as today_shift, `v_patient.tomorrowShiftDesc` as tom_shift,
            (CASE WHEN rp.ifirm_id IS NOT NULL THEN ifirm.ifirm_name ELSE 'UMUM' END) AS penanggung,
            loc.locid
        FROM surat sp 
        LEFT JOIN reg rp ON rp.regpid = sp.regpid
        LEFT JOIN person patient ON patient.pid = sp.pid
        LEFT JOIN v_emp v_patient ON `v_patient.employeeID` = patient.v_emp_id
        LEFT JOIN v_emp v_spv ON `v_patient.sprEmployeeID` = `v_spv.employeeID`
        LEFT JOIN person doctor ON doctor.pid = sp.doctor_id
        LEFT JOIN insurance_firm ifirm ON rp.ifirm_id = ifirm.ifirm_id
        LEFT JOIN laboratory_order_chem loc ON loc.regpid = rp.regpid
        WHERE
            sp.spid = {$spid}";

return DB::selectOne($sql);
RiggsFolly
  • 93,638
  • 21
  • 103
  • 149
1988
  • 309
  • 2
  • 15
  • 2
    Backticks are not a part of PostgreSQL syntax. MySQL? – Stefanov.sm Jun 30 '23 at 07:20
  • Hello, I'm already try to follow the answer on that question but it still producing error, in my case the capital letter like `employeeID` is processing as `employeeid` when I try to input it as query – 1988 Jun 30 '23 at 07:22
  • @Stefanov.sm I'm actually have try to not using any quote whether it single quote or double quote and it's not working for both, so I'm just trying to try mysql solution by putting backticks just to know whether it works or not but ended up it's not working – 1988 Jun 30 '23 at 07:24
  • You need to escape table name and column name separately e.g. `"v_patient"."employeeID"` (sidenote double quotes seem to work in most modern DBMS to escape column and table names). However this does not explain why the case changes during transmission to the database. This should not happen regardless of escaping as far as I know. Does this work in another database tool e.g. in a command line tool ? – apokryfos Jun 30 '23 at 07:44
  • hello @apokryfos thank you for your solution, actually it can't be that way because the double quote is already using in making SELECT query itself, so there can't be double quote inside double quote in `"SELECT sp.*, patient.v_emp_id. . . "` I'm already try to use double quote as well before it – 1988 Jun 30 '23 at 07:49
  • Use single quote outside or escape the double quote e.g. `"SELECT \"sp\".*` (note to not escape the stars because they are not literal names but indicate "everything"). OR `'SELECT "sp".*...'` . take a look at https://stackoverflow.com/questions/7999148/escaping-quotation-marks-in-php for more info. Or you can do the sensible thing and convert your query to use the Laravel query builder and not have to worry about escaping things manually – apokryfos Jun 30 '23 at 07:53

0 Answers0