0

I need to insert createdate column twice with two different datatypes one with the datatype defined in the table itself and another in char datatype. I can insert it by changing the alias name of createdate column but can't insert with same alias name which i need. so help me out to get correct way of doing it.

My query:

    SELECT DISTINCT TE.id, T.debatchqueuelink, TE.transactionlink, 
                    EC.errorclassification, TE.errorvalue, 
                    EC.errorparameter, TE.classificationlink, TE.description, 
                    TE.createdate AS createdate, TO_CHAR(TE.createdate,  'MM/dd/yyyy') AS createdate, 
                    TE.status, TE.rebutt, TE.rebuttedstatus, BQ.appbatchnumber, 
                    BQ.scanbatchnumber, BQ.clientlink, BQ.locationlink, T.patientid, 
                    (DEUD.firstname|| ' ' ||DEUD.lastname) AS deusername, DEUD.email AS deuseremail, 
                    (QCUD.firstname|| ' ' ||QCUD.lastname) AS qcusername, TE.inactive, 
                    TE.decomment  
                 INTO table373 
                 FROM qctransactionerror TE
                INNER JOIN errorclassification EC ON EC.id = TE.classificationlink
                INNER JOIN qctransaction T ON T.id = TE.transactionlink
                INNER JOIN batchqueue BQ ON T.debatchqueuelink = BQ.id
                INNER JOIN batchqueue QCBQ ON T.qcbatchqueuelink = QCBQ.id
                INNER JOIN userdetail QCUD ON QCBQ.assignedto = QCUD.id
                INNER JOIN userdetail DEUD ON BQ.assignedto = DEUD.id 
                WHERE TE.inactive='t' 
                  AND TE.status IN ('ERROR','QCCORRECTED')
                LIMIT 0

The actual error message I am getting is:

Duplicate column:column "createdate" specified more than once

Ankit Bajpai
  • 13,128
  • 4
  • 25
  • 40
Osama Imam
  • 1
  • 1
  • Simply use another alias as create_date instead of createdate. – Ankit Bajpai Jun 21 '22 at 15:34
  • A table cannot have two columns with the same name. You will need to rename one of the columns. – Adrian Klaver Jun 21 '22 at 15:38
  • You do not need the same data value stored with the different data type and doing so is a very bad idea. What do you do when someone updates one of them so that the columns indicate different dates. You can always do the conversion during `select`, or create a `view` with the string version. As for as *same alias name which i need*, you **cannot** have that. – Belayer Jun 21 '22 at 18:02

0 Answers0