Solved

I can not convert the sql query...

Posted on 2010-11-30
6
286 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

726 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