spiral2007
asked on
I can not convert the sql query...
Hello experts, I have this query ( I brought it from ms access) and I can't convert it to a valid pl sql query can anyone help me?
SELECT TEMP_DIEUTHINSEIS_CODES.TX TAXC1, substr(TEMP_DIEUTHINSEIS_C ODES.TXTAX C1,0,5) AS ODOS_CODE,
substr(TEMP_DIEUTHINSEIS_C ODES.TXTAX C1,6,3) AS ADDRESSNUM, TEMP_DIAMERISMATA.APARTMEN TNUM AS APARTMENTNUM,
substr(TEMP_DIEUTHINSEIS_C ODES.TXTAX C1,0,2) AS ZONIID, TEMP_DIEU_KAT_KTIRIOU_2.KA T_KTIR,
decode(length(substr(TEMP_ DIEUTHINSE IS_CODES.T XTAXC1,0,2 ))=2,"0"," ") + substr(TEMP_DIEUTHINSEIS_C ODES.TXTAX C1,0,2) AS SECONDARY_CODE,
substr(TEMP_DIEUTHINSEIS_C ODES.TXTAX C1,6,3) AS FIRST_CODE
decode(TEMP_MAIL_ADDRESS_A R_ACC.COAD R3 Like "*7101*",7101,decode(TEMP_ MAIL_ADDRE SS_AR_ACC. COADR3 Like "*7102*",7102,
decode(TEMP_MAIL_ADDRESS_A R_ACC.COAD R3 Like "*7103*",7103,decode(TEMP_ MAIL_ADDRE SS_AR_ACC. COADR3 Like "*7104*",7104,0)))) AS POSTCODE
FROM TEMP_DIEUTHINSEIS_CODES
LEFT JOIN TEMP_DIEU_KAT_KTIRIOU_2 ON TEMP_DIEUTHINSEIS_CODES.TX TAXC1=TEMP _DIEU_KAT_ KTIRIOU_2. TXTAXC
LEFT JOIN TEMP_DIAMERISMATA ON TEMP_DIEUTHINSEIS_CODES.TX TAXC1=TEMP _DIAMERISM ATA.DIEUTH INSI_CODE
INNER JOIN TEMP_ODOSID_TAXNUMBER ON TEMP_ODOSID_TAXNUMBER.txta xc = substr(TEMP_DIEUTHINSEIS_C ODES.TXTAX C1,0,5)
INNER JOIN TEMP_MAIL_ADDRESS_AR_ACC ON TEMP_ODOSID_TAXNUMBER .TXTAXP=TEMP_MAIL_ADDRESS_ AR_ACC.COC ODE;
SELECT TEMP_DIEUTHINSEIS_CODES.TX
substr(TEMP_DIEUTHINSEIS_C
substr(TEMP_DIEUTHINSEIS_C
decode(length(substr(TEMP_
substr(TEMP_DIEUTHINSEIS_C
decode(TEMP_MAIL_ADDRESS_A
decode(TEMP_MAIL_ADDRESS_A
FROM TEMP_DIEUTHINSEIS_CODES
LEFT JOIN TEMP_DIEU_KAT_KTIRIOU_2 ON TEMP_DIEUTHINSEIS_CODES.TX
LEFT JOIN TEMP_DIAMERISMATA ON TEMP_DIEUTHINSEIS_CODES.TX
INNER JOIN TEMP_ODOSID_TAXNUMBER ON TEMP_ODOSID_TAXNUMBER.txta
INNER JOIN TEMP_MAIL_ADDRESS_AR_ACC ON TEMP_ODOSID_TAXNUMBER .TXTAXP=TEMP_MAIL_ADDRESS_
what is the error you are getting when running in pl/sql ?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I have changed the below :
changed * to %
changed " to '
changed to use CASE instead of DECODE accordingly
change + to ||
and so on...
changed * to %
changed " to '
changed to use CASE instead of DECODE accordingly
change + to ||
and so on...
I think you are trying to use DECODE as IIF. Instead
change
decode(length(substr(TEMP_ DIEUTHINSE IS_CODES.T XTAXC1,0,2 ))=2,"0"," ")
to
decode(length(substr(TEMP_ DIEUTHINSE IS_CODES.T XTAXC1,0,2 )),2,"0"," ")
IIF ( true, if-true, else )
DECODE( something, look-for, if-true, else )
Decode has other variants, like having multiple things to look for
DECODE( something, look-for, if-matched, look-for-2, if-matched-2, ... , else )
change
decode(length(substr(TEMP_
to
decode(length(substr(TEMP_
IIF ( true, if-true, else )
DECODE( something, look-for, if-true, else )
Decode has other variants, like having multiple things to look for
DECODE( something, look-for, if-matched, look-for-2, if-matched-2, ... , else )
ASKER
Thanks for the quick reply!!