Changing Access Query to PL/SQL code

[dminich]

I'm fairly new to PL/SQL so I'm hoping some body would be willing to help me convert the following SQL into something the SQL*Plus will be happy with.

Thank you for any assistance you can offer.

SELECT MESSIAHDB_BIB_TEXT.TITLE_BRIEF,
MESSIAHDB_LOCATION.LOCATION_NAME || ' ' ||MESSIAHDB_MFHD_MASTER.DISPLAY_CALL_NO AS DISPLAY,
MESSIAHDB_BIB_MASTER.SUPPRESS_IN_OPAC,
Mid(Messiahdb_Bib_text.bib_format,2,1) AS BIBTEXT, MESSIAHDB_LOCATION.LOCATION_NAME,
MESSIAHDB_MFHD_MASTER.SUPPRESS_IN_OPAC, MESSIAHDB_MFHDBLOB_VW.MARC_RECORD
FROM ((((MESSIAHDB_BIB_MFHD INNER JOIN MESSIAHDB_BIB_TEXT ON MESSIAHDB_BIB_MFHD.BIB_ID = MESSIAHDB_BIB_TEXT.BIB_ID)
INNER JOIN MESSIAHDB_MFHD_MASTER ON MESSIAHDB_BIB_MFHD.MFHD_ID = MESSIAHDB_MFHD_MASTER.MFHD_ID)
INNER JOIN MESSIAHDB_BIB_MASTER ON MESSIAHDB_BIB_MFHD.BIB_ID = MESSIAHDB_BIB_MASTER.BIB_ID)
INNER JOIN MESSIAHDB_LOCATION ON MESSIAHDB_MFHD_MASTER.LOCATION_ID = MESSIAHDB_LOCATION.LOCATION_ID)
INNER JOIN MESSIAHDB_MFHDBLOB_VW ON MESSIAHDB_BIB_MFHD.MFHD_ID = MESSIAHDB_MFHDBLOB_VW.MFHD_ID
WHERE (((MESSIAHDB_BIB_MASTER.SUPPRESS_IN_OPAC)="N")
AND ((Mid(Messiahdb_Bib_text.bib_format,2,1))='s')
AND ((MESSIAHDB_MFHD_MASTER.SUPPRESS_IN_OPAC)<>"Y"))
ORDER BY MESSIAHDB_BIB_TEXT.TITLE_BRIEF;

 

[ejgb]

The Oracle SubStr function is the equilvant to the Access Mid Function. The parameters are in the same order so you should only need to change the function. i,e.

Access = Mid(Messiahdb_Bib_text.bib_format,2,1)
Oracle = Substr(Messiahdb_Bib_text.bib_format,2,1)

 

[dminich]

When I try and run the query in SQL Plus I receive an error message "missing SELECT keyword" on the from statement. Do I have the table joins set up correctly for Oracle?

 

[gunjan14]

Hi,

I am not that familiar with Access but I think this is what you need (this is not tested code)

select a.title_brief, 
       b.location_name || ' ' ||c.display_call_no display, 
       d.suppress_in_opac, 
       substr(a.bib_format,2,1) bibtext, 
       b.location_name, 
       c.suppress_in_opac, 
       e.marc_record
from   messiahdb_bib_text a,
       messiahdb_location b,
       messiahdb_mfhd_master c,
       messiahdb_bib_master d,
       messiahdb_mfhdblob_vw e,
       messiahdb_bib_mfhd f
where  a.bib_id = f.bib_id 
and    c.mfhd_id = f.mfhd_id 
and    d.bib_id = f.bib_id 
and    b.location_id = c.location_id 
and    e.mfhd_id = f.mfhd_id
and    d.suppress_in_opac = 'n'
and    substr(a.bib_format,2,1) = 's'
and    c.suppress_in_opac <> 'y'
order by a.title_brief;

Hope this helps

Regards,
Gunjan