Solved

I can not convert the sql query...

Posted on 2010-11-30
6
283 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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
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 video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

864 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

21 Experts available now in Live!

Get 1:1 Help Now