Solved

I can not convert the sql query...

Posted on 2010-11-30
6
282 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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to take different types of Oracle backups using RMAN.

706 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now