Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2003
    Posts
    2

    ORA 00936 Missing Expression

    I am getting ora-00936 Missing expression message while running report .
    Below are details.

    SQL query
    -------------


    select distinct bd.family_id ,bd.member_id Member_id,
    date_drawn,
    blood_id,date_proc, wbc_yield,v.code_meaning, Freezer_no, Box_no, Slot_no, dna_microliters,
    concentration, decode(ship,'D','Sent',null) ship
    from blood_sample bs,
    blood_draw bd,
    family_member fm,
    study_proband sp,
    cancer_verification cv,
    valid_codes v
    &p_where
    order by 1,2,3,4,5,6,7

    ----------------
    P_where is given in after parameter form trigger as like


    :p_where := 'where ' ||
    ' bd.family_id = fm.family_idb and ' ||
    ' bd.member_id = fm.member_id and ' ||
    ' sp.family_id (+)=fm.family_id and ' ||
    ' cv.family_id =fm.family_id and ' ||
    ' cv.member_id=fm.member_id and ' ||
    ' bs.draw_id = bd.draw_id and ' ||
    ' bs.sample_type = v.code_value and ' ||
    ' v.col_name= ' || '''SAMPLE_TYPE ''' ||' ' || 'and' ||
    ' ('||'SP.family_id >= nvl'||'(' ||:p_family_id_low ||' '|| ',SP.family_id'||')'||') '|| 'and' ||
    ' ('||'SP.family_id <=nvl'||'(' || :p_family_id_high||' ' || ',SP.family_id'||')'||') '|| 'and' ||
    ' ('||'sp.study_id = NVL'||'(' || :P_study_id ||' '|| ',sp.study_id'||')'||') '|| 'and' ||
    ' ('||'sp.study_arm = NVL'||'(' || :P_study_arm ||' '|| ',sp.study_arm'||')'||') '|| 'and' ||
    ' ('||'substr(cv.datedx,5,4'||')'||'>= NVL'||'(' || :P_datedx_low||' ' ||',substr'||'('||'cv.datedx,5,4'||')'||')'||') ' || 'and' ||
    ' ('||'substr(cv.datedx,5,4'||')'||'<= NVL'||'(' || :P_datedx_high||' ' || ',substr'||'('||'cv.datedx,5,4'||')'||')'||') ' || 'and' ||
    ' cv.site between NVL'||'('||:P_site_from ||' '|| ',cv.site'||')'||' and NVL'||'(' || :P_site_to ||' ' || ',cv.site'||')' ;



    Please help ASAP.
    Thanks in advance.

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    you got a lot of useless crap in there.
    like:
    PHP Code:
    ||' '|| ',sp.study_id'||')'||') '|| 'and' || 
    why have that at all??
    you can do the same with:
    PHP Code:
    ||' ,sp.study_id)) and '|| 

    I converted your trigger into what oracle is trying to execute and
    then formatted it into a readable format.
    This is basically how it comes out:
    PHP Code:
    WHERE 
      bd
    .family_id fm.family_idb AND
      
    bd.member_id fm.member_id AND
      
    sp.family_id (+)=fm.family_id AND
      
    cv.family_id =fm.family_id AND
      
    cv.member_id=fm.member_id AND
      
    bs.draw_id bd.draw_id AND
      
    bs.sample_type v.code_value AND
      
    v.col_name'SAMPLE_TYPE ' AND
      (
    SP.family_id >= NVL( ,SP.family_id)) AND
      (
    SP.family_id <=NVL( ,SP.family_id)) AND
      (
    sp.study_id NVL( ,sp.study_id)) AND
      (
    sp.study_arm NVL( ,sp.study_arm)) AND
      (
    SUBSTR(cv.datedx,5,4)>= NVL( ,SUBSTR(cv.datedx,5,4))) AND
      (
    SUBSTR(cv.datedx,5,4)<= NVL( ,SUBSTR(cv.datedx,5,4))) AND
      
    cv.site BETWEEN NVL( ,cv.site) AND
      
    NVL( ,cv.site
    as you can see there are some errors in there

    start with eliminating needless concatenation of basically nothing.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Dec 2003
    Posts
    2

    ora-00936 Missing Expression

    Hi Duck,
    I modified the where clause as like given below, still I am getting the same error message.

    :p_where := 'where ' ||
    ' bd.family_id = fm.family_idb and ' ||
    ' bd.member_id = fm.member_id and ' ||
    ' sp.family_id (+)=fm.family_id and ' ||
    ' cv.family_id =fm.family_id and ' ||
    ' cv.member_id=fm.member_id and ' ||
    ' bs.draw_id = bd.draw_id and ' ||
    ' bs.sample_type = v.code_value and ' ||
    ' v.col_name= ''SAMPLE_TYPE '' and' ||
    ' (SP.family_id >= nvl('||:p_family_id_low ||',SP.family_id)) and' ||
    ' (SP.family_id <=nvl('|| :p_family_id_high||',SP.family_id)) and' ||
    ' (sp.study_id = NVL('|| :P_study_id ||',sp.study_id)) and' ||
    ' (sp.study_arm = NVL('|| :P_study_arm ||',sp.study_arm)) and' ||
    ' (substr(cv.datedx,5,4)>= NVL('|| :P_datedx_low ||',substr(cv.datedx,5,4))) and' ||
    ' (substr(cv.datedx,5,4)<= NVL(' || :P_datedx_high|| ',substr(cv.datedx,5,4)))and' ||
    ' cv.site between NVL('||:P_site_from ||',cv.site) and NVL('||:P_site_to ||',cv.site)' ;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •