Solved

I can not convert the sql query...

Posted on 2010-11-30
6
285 Views
Last Modified: 2012-05-10
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.TXTAXC1, substr(TEMP_DIEUTHINSEIS_CODES.TXTAXC1,0,5) AS ODOS_CODE,
substr(TEMP_DIEUTHINSEIS_CODES.TXTAXC1,6,3) AS ADDRESSNUM, TEMP_DIAMERISMATA.APARTMENTNUM AS APARTMENTNUM,
substr(TEMP_DIEUTHINSEIS_CODES.TXTAXC1,0,2) AS ZONIID, TEMP_DIEU_KAT_KTIRIOU_2.KAT_KTIR,
decode(length(substr(TEMP_DIEUTHINSEIS_CODES.TXTAXC1,0,2))=2,"0","") + substr(TEMP_DIEUTHINSEIS_CODES.TXTAXC1,0,2) AS SECONDARY_CODE,
substr(TEMP_DIEUTHINSEIS_CODES.TXTAXC1,6,3) AS FIRST_CODE
decode(TEMP_MAIL_ADDRESS_AR_ACC.COADR3 Like "*7101*",7101,decode(TEMP_MAIL_ADDRESS_AR_ACC.COADR3 Like "*7102*",7102,
decode(TEMP_MAIL_ADDRESS_AR_ACC.COADR3 Like "*7103*",7103,decode(TEMP_MAIL_ADDRESS_AR_ACC.COADR3 Like "*7104*",7104,0)))) AS POSTCODE
FROM TEMP_DIEUTHINSEIS_CODES
LEFT JOIN TEMP_DIEU_KAT_KTIRIOU_2 ON TEMP_DIEUTHINSEIS_CODES.TXTAXC1=TEMP_DIEU_KAT_KTIRIOU_2.TXTAXC
LEFT JOIN TEMP_DIAMERISMATA ON TEMP_DIEUTHINSEIS_CODES.TXTAXC1=TEMP_DIAMERISMATA.DIEUTHINSI_CODE
INNER JOIN TEMP_ODOSID_TAXNUMBER ON TEMP_ODOSID_TAXNUMBER.txtaxc = substr(TEMP_DIEUTHINSEIS_CODES.TXTAXC1,0,5)
INNER JOIN TEMP_MAIL_ADDRESS_AR_ACC ON TEMP_ODOSID_TAXNUMBER .TXTAXP=TEMP_MAIL_ADDRESS_AR_ACC.COCODE;
       
0
Comment
Question by:spiral2007
  • 3
  • 2
6 Comments
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 34236897
what is the error you are getting when running in pl/sql ?
0
 
LVL 58

Assisted Solution

by:cyberkiwi
cyberkiwi earned 100 total points
ID: 34236901
Change all the * in LIKE to %
0
 
LVL 28

Accepted Solution

by:
Naveen Kumar earned 400 total points
ID: 34236903
Can you try this : I have modifed it.

SELECT TEMP_DIEUTHINSEIS_CODES.TXTAXC1,
substr(TEMP_DIEUTHINSEIS_CODES.TXTAXC1,0,5) AS ODOS_CODE,
substr(TEMP_DIEUTHINSEIS_CODES.TXTAXC1,6,3) AS ADDRESSNUM,
TEMP_DIAMERISMATA.APARTMENTNUM AS APARTMENTNUM,
substr(TEMP_DIEUTHINSEIS_CODES.TXTAXC1,0,2) AS ZONIID,
TEMP_DIEU_KAT_KTIRIOU_2.KAT_KTIR,
case when length(substr(TEMP_DIEUTHINSEIS_CODES.TXTAXC1,0,2))=2 then '0' else '' end || substr(TEMP_DIEUTHINSEIS_CODES.TXTAXC1,0,2) AS SECONDARY_CODE,
substr(TEMP_DIEUTHINSEIS_CODES.TXTAXC1,6,3) AS FIRST_CODE,  
case when TEMP_MAIL_ADDRESS_AR_ACC.COADR3 Like '%7101%' then 7101  
when TEMP_MAIL_ADDRESS_AR_ACC.COADR3 Like '%7102%' then 7102
when TEMP_MAIL_ADDRESS_AR_ACC.COADR3 Like '%7103%' then 7103
when TEMP_MAIL_ADDRESS_AR_ACC.COADR3 Like '%7104%' then 7104 else 0 end AS POSTCODE
FROM TEMP_DIEUTHINSEIS_CODES
LEFT JOIN TEMP_DIEU_KAT_KTIRIOU_2 ON TEMP_DIEUTHINSEIS_CODES.TXTAXC1=TEMP_DIEU_KAT_KTIRIOU_2.TXTAXC
LEFT JOIN TEMP_DIAMERISMATA ON TEMP_DIEUTHINSEIS_CODES.TXTAXC1=TEMP_DIAMERISMATA.DIEUTHINSI_CODE
INNER JOIN TEMP_ODOSID_TAXNUMBER ON TEMP_ODOSID_TAXNUMBER.txtaxc = substr(TEMP_DIEUTHINSEIS_CODES.TXTAXC1,0,5)
INNER JOIN TEMP_MAIL_ADDRESS_AR_ACC ON TEMP_ODOSID_TAXNUMBER .TXTAXP=TEMP_MAIL_ADDRESS_AR_ACC.COCODE;
       
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 34236916
I have changed the below :

changed * to %
changed " to '
changed to use CASE instead of DECODE accordingly
change + to ||
and so on...
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34236923
I think you are trying to use DECODE as IIF. Instead
change

decode(length(substr(TEMP_DIEUTHINSEIS_CODES.TXTAXC1,0,2))=2,"0","")

to

decode(length(substr(TEMP_DIEUTHINSEIS_CODES.TXTAXC1,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 )
0
 

Author Comment

by:spiral2007
ID: 34236979

Thanks for the quick reply!!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question