Solved

I can not convert the sql query...

Posted on 2010-11-30
6
287 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
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

688 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