Pl/SQl this works - Toad it doesn't

Toad for Oracle
1

Message from: minalen_828

I’m a new user to Toad and want to use some of the better features that
PL/SQL doesn’t give me.

I have Pl/SQL code that executes in Pl?sql developer -But when I open in Toad it
throws all kind of errors (ORA-00907).
It’s becuase it’s converting || statements to CONCAT satements and
inserting all kinds of parenthesis ( into the code.

I’m on Oracle 10g

Is there a setting in Toad so it doesn’t do any conversion ?

Thanks in advance

Historical Messages

Author: minalen_828
Date: Fri Jun 10 07:53:06 PDT 2011
I’m a new user to Toad and want to use some of the better features that
PL/SQL doesn’t give me.

I have Pl/SQL code that executes in Pl?sql developer -But when I open in Toad it
throws all kind of errors (ORA-00907).
It’s becuase it’s converting || statements to CONCAT satements and
inserting all kinds of parenthesis ( into the code.

I’m on Oracle 10g

Is there a setting in Toad so it doesn’t do any conversion ?

Thanks in advance

__

2

Show us your code.

3

Message from: minalen_828

Select

cdm_number || ’ - ’ || cdm_desc,

substr(to_char(service_date), 1 , 4 ) || ’ Q’ ||
to_char(to_date(to_char(service_date),

‘YYYYMMDD’ ), ‘Q’ ),

substr(to_char(service_date), 1 , 4 ) || ‘-’ ||
substr(to_char(service_date), 5 , 2 ), dept_num ||

’ - ’ || dept_desc,

DECODE(ordering_phys, 0 , attending_phys, ordering_phys) || ’ - ’ ||
DECODE(ordering_phys,

0 , attending_phys_name, phys1.physician_name),

DECODE(pat.referring_phys, 0 , ‘0 - NONE’ , pat.referring_phys || ’

  • ’ || phys2.physician_name), DECODE(pat.admit_source,

NULL , ‘NONE’ ,pat.admit_source || ’ - ’ ||
pat.admit_source_desc),

DECODE(ext.referral_institution, NULL , ‘0 - NONE’
,ext.referral_institution || ’ - ’ || r.ref_inst_desc), hospital_service
||

’ - ’ || hospital_service_desc,

DECODE(prim_ins_code, NULL , ‘NONE’ , prim_ins_code || ’ - ’ ||
prim_ins_desc), substr(pat.zip_code,

1 , 5 ),

DECODE(pat.patient_category, ‘I’ , ‘Inpatient’ ,

‘O’ , ‘Outpatient’ ,

‘E’ , ‘Emergency’ ,

‘N’ , ‘Outpat Zero Charges’ , ‘A’ , ‘Ambulatory’
)

cpt4_code,

cins.cais_fin_class || ’ - ’ || fin.fin_class_desc,

sum (quantity),

sum (amount)

FROM uh_charges chg,

uh_physicians phys1,

uh_physicians phys2,

uh_patient_account pat,

uh_pat_acct_ext ext,

uh_refer_inst r,

uh_cais_ins cins,

uh_financial_classes fin

WHERE service_date between 20100101 and 20101231 AND

pat.patient_account_number = chg.patient_account_number AND

pat.patient_account_number = ext.patient_account_number AND

ext.referral_institution = r.ref_inst_code (+) AND

pat.prim_ins_code = cins.cais_ins_code (+) AND

cins.cais_fin_class = fin.fin_class_code (+) AND

chg.ordering_phys = phys1.physician_number (+) AND

pat.referring_phys = phys2.physician_number (+) AND

cdm_number >= 10000000

GROUP BY cdm_number || ’ - ’ || cdm_desc,

substr(to_char(service_date), 1 , 4 ) || ’ Q’ ||
to_char(to_date(to_char(service_date),

‘YYYYMMDD’ ), ‘Q’ ),

substr(to_char(service_date), 1 , 4 ) || ‘-’ ||
substr(to_char(service_date), 5 , 2 ), dept_num ||

’ - ’ || dept_desc,

DECODE(ordering_phys, 0 , attending_phys, ordering_phys) || ’ - ’ ||
DECODE(ordering_phys,

0 , attending_phys_name, phys1.physician_name),

DECODE(pat.referring_phys, 0 , ‘0 - NONE’ , pat.referring_phys || ’

  • ’ || phys2.physician_name),DECODE(pat.admit_source,

NULL , ‘NONE’ ,pat.admit_source || ’ - ’ ||
pat.admit_source_desc),

DECODE(ext.referral_institution, NULL , ‘0 - NONE’
,ext.referral_institution || ’ - ’ || r.ref_inst_desc), hospital_service
||

’ - ’ || hospital_service_desc,

DECODE(prim_ins_code, NULL , ‘NONE’ , prim_ins_code || ’ - ’ ||
prim_ins_desc),substr(pat.zip_code,

1 , 5 ) ,

DECODE(pat.patient_category, ‘I’ , ‘Inpatient’ ,

‘O’ , ‘Outpatient’ ,

‘E’ , ‘Emergency’ ,

‘N’ , ‘Outpat Zero Charges’ , ‘A’ , ‘Ambulatory’
),

cpt4_code,

cins.cais_fin_class || ’ - ’ || fin.fin_class_desc

ORDER BY cdm_number || ’ - ’ || cdm_desc

Historical Messages

Author: minalen_828
Date: Fri Jun 10 08:06:47 PDT 2011

Select

cdm_number || ’ - ’ || cdm_desc,

substr(to_char(service_date), 1 , 4 ) || ’ Q’ ||
to_char(to_date(to_char(service_date),

‘YYYYMMDD’ ), ‘Q’ ),

substr(to_char(service_date), 1 , 4 ) || ‘-’ ||
substr(to_char(service_date), 5 , 2 ), dept_num ||

’ - ’ || dept_desc,

DECODE(ordering_phys, 0 , attending_phys, ordering_phys) || ’ - ’ ||
DECODE(ordering_phys,

0 , attending_phys_name, phys1.physician_name),

DECODE(pat.referring_phys, 0 , ‘0 - NONE’ , pat.referring_phys || ’

  • ’ || phys2.physician_name), DECODE(pat.admit_source,

NULL , ‘NONE’ ,pat.admit_source || ’ - ’ ||
pat.admit_source_desc),

DECODE(ext.referral_institution, NULL , ‘0 - NONE’
,ext.referral_institution || ’ - ’ || r.ref_inst_desc), hospital_service
||

’ - ’ || hospital_service_desc,

DECODE(prim_ins_code, NULL , ‘NONE’ , prim_ins_code || ’ - ’ ||
prim_ins_desc), substr(pat.zip_code,

1 , 5 ),

DECODE(pat.patient_category, ‘I’ , ‘Inpatient’ ,

‘O’ , ‘Outpatient’ ,

‘E’ , ‘Emergency’ ,

‘N’ , ‘Outpat Zero Charges’ , ‘A’ , ‘Ambulatory’
)

cpt4_code,

cins.cais_fin_class || ’ - ’ || fin.fin_class_desc,

sum (quantity),

sum (amount)

FROM uh_charges chg,

uh_physicians phys1,

uh_physicians phys2,

uh_patient_account pat,

uh_pat_acct_ext ext,

uh_refer_inst r,

uh_cais_ins cins,

uh_financial_classes fin

WHERE service_date between 20100101 and 20101231 AND

pat.patient_account_number = chg.patient_account_number AND

pat.patient_account_number = ext.patient_account_number AND

ext.referral_institution = r.ref_inst_code (+) AND

pat.prim_ins_code = cins.cais_ins_code (+) AND

cins.cais_fin_class = fin.fin_class_code (+) AND

chg.ordering_phys = phys1.physician_number (+) AND

pat.referring_phys = phys2.physician_number (+) AND

cdm_number >= 10000000

GROUP BY cdm_number || ’ - ’ || cdm_desc,

substr(to_char(service_date), 1 , 4 ) || ’ Q’ ||
to_char(to_date(to_char(service_date),

‘YYYYMMDD’ ), ‘Q’ ),

substr(to_char(service_date), 1 , 4 ) || ‘-’ ||
substr(to_char(service_date), 5 , 2 ), dept_num ||

’ - ’ || dept_desc,

DECODE(ordering_phys, 0 , attending_phys, ordering_phys) || ’ - ’ ||
DECODE(ordering_phys,

0 , attending_phys_name, phys1.physician_name),

DECODE(pat.referring_phys, 0 , ‘0 - NONE’ , pat.referring_phys || ’

  • ’ || phys2.physician_name),DECODE(pat.admit_source,

NULL , ‘NONE’ ,pat.admit_source || ’ - ’ ||
pat.admit_source_desc),

DECODE(ext.referral_institution, NULL , ‘0 - NONE’
,ext.referral_institution || ’ - ’ || r.ref_inst_desc), hospital_service
||

’ - ’ || hospital_service_desc,

DECODE(prim_ins_code, NULL , ‘NONE’ , prim_ins_code || ’ - ’ ||
prim_ins_desc),substr(pat.zip_code,

1 , 5 ) ,

DECODE(pat.patient_category, ‘I’ , ‘Inpatient’ ,

‘O’ , ‘Outpatient’ ,

‘E’ , ‘Emergency’ ,

‘N’ , ‘Outpat Zero Charges’ , ‘A’ , ‘Ambulatory’
),

cpt4_code,

cins.cais_fin_class || ’ - ’ || fin.fin_class_desc

ORDER BY cdm_number || ’ - ’ || cdm_desc

__

Author: Jeff Smith
Date: Fri Jun 10 07:53:06 PDT 2011
Show us your code.
__

Author: minalen_828
Date: Fri Jun 10 07:53:06 PDT 2011
Message from: minalen_828 I’m a new user to Toad and want to use some of the
better features that PL/SQL doesn’t give me. I have Pl/SQL code that
executes in Pl?sql developer -But when I open in Toad it throws all kind of
errors (ORA-00907). It’s becuase it’s converting || statements to CONCAT
satements and inserting all kinds of parenthesis ( into the code. I’m on
Oracle 10g Is there a setting in Toad so it doesn’t do any conversion ?
Thanks in advance _______________________________________ Historical Messages
Author: minalen_828 Date: Fri Jun 10 07:53:06 PDT 2011 I’m a new user to
Toad and want to use some of the better features that PL/SQL doesn’t give
me. I have Pl/SQL code that executes in Pl?sql developer -But when I open in
Toad it throws all kind of errors (ORA-00907). It’s becuase it’s
converting || statements to CONCAT satements and inserting all kinds of
parenthesis ( into the code. I’m on Oracle 10g Is there a setting in Toad so
it doesn’t do any conversion ? Thanks in advance __

__

4

That’s not PL/SQL, it’s SQL

Do you have a simple example to demonstrate what is happening?

If it’s valid SQL, Toad will have no problems running it. What if you run
the same code via SQL*Plus?

5

Select

substr(to_char(service_date),1,4) || ’ Q’ || to_char(to_date(to_char(service_date),

substr(to_char(service_date),1,4) || ‘-’ || substr(to_char(service_date),5,2), dept_num ||

DECODE(ordering_phys, 0, attending_phys, ordering_phys) || ’ - ’ || DECODE(ordering_phys,

DECODE(pat.referring_phys,0, ‘0 - NONE’, pat.referring_phys || ’ - ’ || phys2.physician_name), DECODE(pat.admit_source,

DECODE(ext.referral_institution,NULL,‘0 - NONE’,ext.referral_institution || ’ - ’ || r.ref_inst_desc), hospital_service ||

DECODE(prim_ins_code, NULL, ‘NONE’, prim_ins_code || ’ - ’ || prim_ins_desc), substr(pat.zip_code,

DECODE(pat.patient_category,‘I’,‘Inpatient’,

‘E’,‘Emergency’,

cpt4_code,

cins.cais_fin_class || ’ - ’ || fin.fin_class_desc,

sum(quantity),

uh_physicians phys1,

uh_physicians phys2,

uh_patient_account pat,

uh_pat_acct_ext ext,

uh_refer_inst r,

uh_cais_ins cins,

uh_financial_classes fin

WHERE service_date between 20100101 and 20101231 AND

pat.patient_account_number = chg.patient_account_number AND

pat.patient_account_number = ext.patient_account_number AND

ext.referral_institution = r.ref_inst_code (+) AND

pat.prim_ins_code = cins.cais_ins_code (+) AND

cins.cais_fin_class = fin.fin_class_code (+) AND

chg.ordering_phys = phys1.physician_number (+) AND

pat.referring_phys = phys2.physician_number (+) AND

cdm_number >= 10000000

substr(to_char(service_date),1,4) || ’ Q’ || to_char(to_date(to_char(service_date),

substr(to_char(service_date),1,4) || ‘-’ || substr(to_char(service_date),5,2), dept_num ||

DECODE(ordering_phys, 0, attending_phys, ordering_phys) || ’ - ’ || DECODE(ordering_phys,

DECODE(pat.referring_phys,0, ‘0 - NONE’, pat.referring_phys || ’ - ’ || phys2.physician_name),DECODE(pat.admit_source,

DECODE(ext.referral_institution,NULL,‘0 - NONE’,ext.referral_institution || ’ - ’ || r.ref_inst_desc), hospital_service ||

DECODE(prim_ins_code, NULL, ‘NONE’, prim_ins_code || ’ - ’ || prim_ins_desc),substr(pat.zip_code,

DECODE(pat.patient_category,‘I’,‘Inpatient’,

‘E’,‘Emergency’,

cpt4_code,

cins.cais_fin_class || ’ - ’ || fin.fin_class_desc

cdm_number || ’ - ’ || cdm_desc, ‘YYYYMMDD’),‘Q’), ’ - ’ || dept_desc, 0, attending_phys_name, phys1.physician_name), NULL,‘NONE’,pat.admit_source || ’ - ’ || pat.admit_source_desc), ’ - ’ || hospital_service_desc, 1,5), ‘O’,‘Outpatient’,‘N’,‘Outpat Zero Charges’,‘A’,‘Ambulatory’) sum(amount)

FROM uh_charges chg, GROUP BY cdm_number || ’ - ’ || cdm_desc, ‘YYYYMMDD’),‘Q’),’ - ’ || dept_desc, 0, attending_phys_name, phys1.physician_name),NULL,‘NONE’,pat.admit_source || ’ - ’ || pat.admit_source_desc),’ - ’ || hospital_service_desc, 1,5) ,‘O’,‘Outpatient’,‘N’,‘Outpat Zero Charges’,‘A’,‘Ambulatory’),ORDER BY cdm_number || ’ - ’ || cdm_desc

6

[ Attachment(s) from minalen_828 included below]

Message from: minalen_828

that paste came out like garbage.

attached is the sql

Historical Messages

Author: minalen_828
Date: Fri Jun 10 08:08:53 PDT 2011
that paste came out like garbage.

attached is the sql
__

Author: minalen_828
Date: Fri Jun 10 08:06:47 PDT 2011

Select

cdm_number || ’ - ’ || cdm_desc,

substr(to_char(service_date), 1 , 4 ) || ’ Q’ ||
to_char(to_date(to_char(service_date),

‘YYYYMMDD’ ), ‘Q’ ),

substr(to_char(service_date), 1 , 4 ) || ‘-’ ||
substr(to_char(service_date), 5 , 2 ), dept_num ||

’ - ’ || dept_desc,

DECODE(ordering_phys, 0 , attending_phys, ordering_phys) || ’ - ’ ||
DECODE(ordering_phys,

0 , attending_phys_name, phys1.physician_name),

DECODE(pat.referring_phys, 0 , ‘0 - NONE’ , pat.referring_phys || ’

  • ’ || phys2.physician_name), DECODE(pat.admit_source,

NULL , ‘NONE’ ,pat.admit_source || ’ - ’ ||
pat.admit_source_desc),

DECODE(ext.referral_institution, NULL , ‘0 - NONE’
,ext.referral_institution || ’ - ’ || r.ref_inst_desc), hospital_service
||

’ - ’ || hospital_service_desc,

DECODE(prim_ins_code, NULL , ‘NONE’ , prim_ins_code || ’ - ’ ||
prim_ins_desc), substr(pat.zip_code,

1 , 5 ),

DECODE(pat.patient_category, ‘I’ , ‘Inpatient’ ,

‘O’ , ‘Outpatient’ ,

‘E’ , ‘Emergency’ ,

‘N’ , ‘Outpat Zero Charges’ , ‘A’ , ‘Ambulatory’
)

cpt4_code,

cins.cais_fin_class || ’ - ’ || fin.fin_class_desc,

sum (quantity),

sum (amount)

FROM uh_charges chg,

uh_physicians phys1,

uh_physicians phys2,

uh_patient_account pat,

uh_pat_acct_ext ext,

uh_refer_inst r,

uh_cais_ins cins,

uh_financial_classes fin

WHERE service_date between 20100101 and 20101231 AND

pat.patient_account_number = chg.patient_account_number AND

pat.patient_account_number = ext.patient_account_number AND

ext.referral_institution = r.ref_inst_code (+) AND

pat.prim_ins_code = cins.cais_ins_code (+) AND

cins.cais_fin_class = fin.fin_class_code (+) AND

chg.ordering_phys = phys1.physician_number (+) AND

pat.referring_phys = phys2.physician_number (+) AND

cdm_number >= 10000000

GROUP BY cdm_number || ’ - ’ || cdm_desc,

substr(to_char(service_date), 1 , 4 ) || ’ Q’ ||
to_char(to_date(to_char(service_date),

‘YYYYMMDD’ ), ‘Q’ ),

substr(to_char(service_date), 1 , 4 ) || ‘-’ ||
substr(to_char(service_date), 5 , 2 ), dept_num ||

’ - ’ || dept_desc,

DECODE(ordering_phys, 0 , attending_phys, ordering_phys) || ’ - ’ ||
DECODE(ordering_phys,

0 , attending_phys_name, phys1.physician_name),

DECODE(pat.referring_phys, 0 , ‘0 - NONE’ , pat.referring_phys || ’

  • ’ || phys2.physician_name),DECODE(pat.admit_source,

NULL , ‘NONE’ ,pat.admit_source || ’ - ’ ||
pat.admit_source_desc),

DECODE(ext.referral_institution, NULL , ‘0 - NONE’
,ext.referral_institution || ’ - ’ || r.ref_inst_desc), hospital_service
||

’ - ’ || hospital_service_desc,

DECODE(prim_ins_code, NULL , ‘NONE’ , prim_ins_code || ’ - ’ ||
prim_ins_desc),substr(pat.zip_code,

1 , 5 ) ,

DECODE(pat.patient_category, ‘I’ , ‘Inpatient’ ,

‘O’ , ‘Outpatient’ ,

‘E’ , ‘Emergency’ ,

‘N’ , ‘Outpat Zero Charges’ , ‘A’ , ‘Ambulatory’
),

cpt4_code,

cins.cais_fin_class || ’ - ’ || fin.fin_class_desc

ORDER BY cdm_number || ’ - ’ || cdm_desc

__

Author: minalen_828
Date: Fri Jun 10 08:06:47 PDT 2011
Message from: minalen_828 Select cdm_number || ’ - ’ || cdm_desc,
substr(to_char(service_date), 1 , 4 ) || ’ Q’ ||
to_char(to_date(to_char(service_date), ‘YYYYMMDD’ ), ‘Q’ ),
substr(to_char(service_date), 1 , 4 ) || ‘-’ ||
substr(to_char(service_date), 5 , 2 ), dept_num || ’ - ’ || dept_desc,
DECODE(ordering_phys, 0 , attending_phys, ordering_phys) || ’ - ’ ||
DECODE(ordering_phys, 0 , attending_phys_name, phys1.physician_name),
DECODE(pat.referring_phys, 0 , ‘0 - NONE’ , pat.referring_phys || ’

  • ’ || phys2.physician_name), DECODE(pat.admit_source, NULL , ‘NONE’
    ,pat.admit_source || ’ - ’ || pat.admit_source_desc),
    DECODE(ext.referral_institution, NULL , ‘0 - NONE’
    ,ext.referral_institution || ’ - ’ || r.ref_inst_desc), hospital_service
    || ’ - ’ || hospital_service_desc, DECODE(prim_ins_code, NULL ,
    ‘NONE’ , prim_ins_code || ’ - ’ || prim_ins_desc),
    substr(pat.zip_code, 1 , 5 ), DECODE(pat.patient_category, ‘I’ ,
    ‘Inpatient’ , ‘O’ , ‘Outpatient’ , ‘E’ ,
    ‘Emergency’ , ‘N’ , ‘Outpat Zero Charges’ , ‘A’
    , ‘Ambulatory’ ) cpt4_code, cins.cais_fin_class || ’ - ’ ||
    fin.fin_class_desc, sum (quantity), sum (amount) FROM uh_charges chg,
    uh_physicians phys1, uh_physicians phys2, uh_patient_account pat,
    uh_pat_acct_ext ext, uh_refer_inst r, uh_cais_ins cins, uh_financial_classes fin
    WHERE service_date between 20100101 and 20101231 AND pat.patient_account_number
    = chg.patient_account_number AND pat.patient_account_number =
    ext.patient_account_number AND ext.referral_institution = r.ref_inst_code (+)
    AND pat.prim_ins_code = cins.cais_ins_code (+) AND cins.cais_fin_class =
    fin.fin_class_code (+) AND chg.ordering_phys = phys1.physician_number (+) AND
    pat.referring_phys = phys2.physician_number (+) AND cdm_number >= 10000000 GROUP
    BY cdm_number || ’ - ’ || cdm_desc, substr(to_char(service_date), 1 , 4
    ) || ’ Q’ || to_char(to_date(to_char(service_date), ‘YYYYMMDD’
    ), ‘Q’ ), substr(to_char(service_date), 1 , 4 ) || ‘-’ ||
    substr(to_char(service_date), 5 , 2 ), dept_num || ’ - ’ || dept_desc,
    DECODE(ordering_phys, 0 , attending_phys, ordering_phys) || ’ - ’ ||
    DECODE(ordering_phys, 0 , attending_phys_name, phys1.physician_name),
    DECODE(pat.referring_phys, 0 , ‘0 - NONE’ , pat.referring_phys || ’
  • ’ || phys2.physician_name),DECODE(pat.admit_source, NULL , ‘NONE’
    ,pat.admit_source || ’ - ’ || pat.admit_source_desc),
    DECODE(ext.referral_institution, NULL , ‘0 - NONE’
    ,ext.referral_institution || ’ - ’ || r.ref_inst_desc), hospital_service
    || ’ - ’ || hospital_service_desc, DECODE(prim_ins_code, NULL ,
    ‘NONE’ , prim_ins_code || ’ - ’ ||
    prim_ins_desc),substr(pat.zip_code, 1 , 5 ) , DECODE(pat.patient_category,
    ‘I’ , ‘Inpatient’ , ‘O’ , ‘Outpatient’ ,
    ‘E’ , ‘Emergency’ , ‘N’ , ‘Outpat Zero Charges’
    , ‘A’ , ‘Ambulatory’ ), cpt4_code, cins.cais_fin_class || ’
  • ’ || fin.fin_class_desc ORDER BY cdm_number || ’ - ’ || cdm_desc
    _______________________________________ Historical Messages Author: minalen_828
    Date: Fri Jun 10 08:06:47 PDT 2011 Select cdm_number || ’ - ’ ||
    cdm_desc, substr(to_char(service_date), 1 , 4 ) || ’ Q’ ||
    to_char(to_date(to_char(service_date), ‘YYYYMMDD’ ), ‘Q’ ),
    substr(to_char(service_date), 1 , 4 ) || ‘-’ ||
    substr(to_char(service_date), 5 , 2 ), dept_num || ’ - ’ || dept_desc,
    DECODE(ordering_phys, 0 , attending_phys, ordering_phys) || ’ - ’ ||
    DECODE(ordering_phys, 0 , attending_phys_name, phys1.physician_name),
    DECODE(pat.referring_phys, 0 , ‘0 - NONE’ , pat.referring_phys || ’
  • ’ || phys2.physician_name), DECODE(pat.admit_source, NULL , ‘NONE’
    ,pat.admit_source || ’ - ’ || pat.admit_source_desc),
    DECODE(ext.referral_institution, NULL , ‘0 - NONE’
    ,ext.referral_institution || ’ - ’ || r.ref_inst_desc), hospital_service
    || ’ - ’ || hospital_service_desc, DECODE(prim_ins_code, NULL ,
    ‘NONE’ , prim_ins_code || ’ - ’ || prim_ins_desc),
    substr(pat.zip_code, 1 , 5 ), DECODE(pat.patient_category, ‘I’ ,
    ‘Inpatient’ , ‘O’ , ‘Outpatient’ , ‘E’ ,
    ‘Emergency’ , ‘N’ , ‘Outpat Zero Charges’ , ‘A’
    , ‘Ambulatory’ ) cpt4_code, cins.cais_fin_class || ’ - ’ ||
    fin.fin_class_desc, sum (quantity), sum (amount) FROM uh_charges chg,
    uh_physicians phys1, uh_physicians phys2, uh_patient_account pat,
    uh_pat_acct_ext ext, uh_refer_inst r, uh_cais_ins cins, uh_financial_classes fin
    WHERE service_date between 20100101 and 20101231 AND pat.patient_account_number
    = chg.patient_account_number AND pat.patient_account_number =
    ext.patient_account_number AND ext.referral_institution = r.ref_inst_code (+)
    AND pat.prim_ins_code = cins.cais_ins_code (+) AND cins.cais_fin_class =
    fin.fin_class_code (+) AND chg.ordering_phys = phys1.physician_number (+) AND
    pat.referring_phys = phys2.physician_number (+) AND cdm_number >= 10000000 GROUP
    BY cdm_number || ’ - ’ || cdm_desc, substr(to_char(service_date), 1 , 4
    ) || ’ Q’ || to_char(to_date(to_char(service_date), ‘YYYYMMDD’
    ), ‘Q’ ), substr(to_char(service_date), 1 , 4 ) || ‘-’ ||
    substr(to_char(service_date), 5 , 2 ), dept_num || ’ - ’ || dept_desc,
    DECODE(ordering_phys, 0 , attending_phys, ordering_phys) || ’ - ’ ||
    DECODE(ordering_phys, 0 , attending_phys_name, phys1.physician_name),
    DECODE(pat.referring_phys, 0 , ‘0 - NONE’ , pat.referring_phys || ’
  • ’ || phys2.physician_name),DECODE(pat.admit_source, NULL , ‘NONE’
    ,pat.admit_source || ’ - ’ || pat.admit_source_desc),
    DECODE(ext.referral_institution, NULL , ‘0 - NONE’
    ,ext.referral_institution || ’ - ’ || r.ref_inst_desc), hospital_service
    || ’ - ’ || hospital_service_desc, DECODE(prim_ins_code, NULL ,
    ‘NONE’ , prim_ins_code || ’ - ’ ||
    prim_ins_desc),substr(pat.zip_code, 1 , 5 ) , DECODE(pat.patient_category,
    ‘I’ , ‘Inpatient’ , ‘O’ , ‘Outpatient’ ,
    ‘E’ , ‘Emergency’ , ‘N’ , ‘Outpat Zero Charges’
    , ‘A’ , ‘Ambulatory’ ), cpt4_code, cins.cais_fin_class || ’
  • ’ || fin.fin_class_desc ORDER BY cdm_number || ’ - ’ || cdm_desc
    __ Author: Jeff Smith Date: Fri Jun 10 07:53:06 PDT 2011 Show us your code. __
    Author: minalen_828 Date: Fri Jun 10 07:53:06 PDT 2011 Message from: minalen_828
    I’m a new user to Toad and want to use some of the better features that
    PL/SQL doesn’t give me. I have Pl/SQL code that executes in Pl?sql developer
    -But when I open in Toad it throws all kind of errors (ORA-00907). It’s
    becuase it’s converting || statements to CONCAT satements and inserting all
    kinds of parenthesis ( into the code. I’m on Oracle 10g Is there a setting
    in Toad so it doesn’t do any conversion ? Thanks in advance
    _______________________________________ Historical Messages Author: minalen_828
    Date: Fri Jun 10 07:53:06 PDT 2011 I’m a new user to Toad and want to use
    some of the better features that PL/SQL doesn’t give me. I have Pl/SQL code
    that executes in Pl?sql developer -But when I open in Toad it throws all kind of
    errors (ORA-00907). It’s becuase it’s converting || statements to CONCAT
    satements and inserting all kinds of parenthesis ( into the code. I’m on
    Oracle 10g Is there a setting in Toad so it doesn’t do any conversion ?
    Thanks in advance __ _______________________________________ __

__

Author: Jeff Smith
Date: Fri Jun 10 07:53:06 PDT 2011
Show us your code.
__

Author: minalen_828
Date: Fri Jun 10 07:53:06 PDT 2011
Message from: minalen_828 I’m a new user to Toad and want to use some of the
better features that PL/SQL doesn’t give me. I have Pl/SQL code that
executes in Pl?sql developer -But when I open in Toad it throws all kind of
errors (ORA-00907). It’s becuase it’s converting || statements to CONCAT
satements and inserting all kinds of parenthesis ( into the code. I’m on
Oracle 10g Is there a setting in Toad so it doesn’t do any conversion ?
Thanks in advance _______________________________________ Historical Messages
Author: minalen_828 Date: Fri Jun 10 07:53:06 PDT 2011 I’m a new user to
Toad and want to use some of the better features that PL/SQL doesn’t give
me. I have Pl/SQL code that executes in Pl?sql developer -But when I open in
Toad it throws all kind of errors (ORA-00907). It’s becuase it’s
converting || statements to CONCAT satements and inserting all kinds of
parenthesis ( into the code. I’m on Oracle 10g Is there a setting in Toad so
it doesn’t do any conversion ? Thanks in advance __

__

Attachment(s) from minalen_828

1 of 1 File(s)

wtcdm_by_dept.sql

7

that paste came out like garbage.

attached is the sql
wtcdm_by_dept.sql (4 KB)

8

converting || statements to CONCAT satements

Heh, you learn something new every day. There's an actual Oracle function called concat. I've always used || myself.

Ah well... you haven't showed the "after" as well as the before, so I'm going to assume your copy of Toad will convert:

SELECT 'First' || ' Second'
FROM dual;

into:

SELECT CONCAT('First', ' Second')
FROM dual;

Is that correct? Can you drop the first query into Toad and see if it does exactly as identified above?

If not exactly as above, could you produce the "after" that your copy of Toad produces from the first query?

Roger S.

This communication, including any attached documentation, is intended only for the person or entity to which it is addressed, and may contain confidential, personal and/or privileged information. Any unauthorized disclosure, copying, or taking action on the contents is strictly prohibited. If you have received this message in error, please contact us immediately so we may correct our records. Please then delete or destroy the original transmission and any subsequent reply.

9

To clarify, by “first query”, I’m talking about the sample query I provided rather than that rather extensive query you provided.

Roger