Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2004
    Posts
    13

    Unanswered: Check the syntax

    I'm getting the following error messages:
    Incorrect syntax near the keyword 'in'.
    Server: Msg 156, Level 15, State 1, Line 38
    Incorrect syntax near the keyword 'group'.


    /* create temp tables */
    select distinct d_vst_id as 'DRW_ID'
    ,d_vst_instid as 'DRW_INSTID'
    into temp_tb1
    from dnr_vst_db_rec
    where d_vst_instid = ''
    and d_vst_dontyp = 'WB'
    and d_vst_status = 'DN'
    and d_vst_date between 20020301 and 20030228
    order by d_vst_id


    Select distinct
    n_per_id as 'ID1'
    ,n_per_gender as 'GENDER'
    ,n_per_birth as 'BIRTH1'
    ,d_bty_abo + d_bty_rhesus as 'ABO1'
    ,n_adr_city as 'CITY1'
    ,n_adr_zip as 'ZIP1'
    into temp_tb3
    from temp_tb1 right outer join nat_per_db_rec
    on DRW_ID = n_per_id
    right outer join dnr_bty_db_rec
    on DRW_ID = d_bty_id
    right outer join nat_adr_db_rec
    on DRW_ID = n_adr_id
    where DRW_INSTID = ''
    order by n_per_id

    select distinct getdate()
    ,d_aaa_insthdg
    , case (d_vst_btcdte - n_per_birth) / 10000
    when in (14,15,16) the '14-16'
    when in (17,18,19,20) then '17-20'
    when in (21, 22,23,24,25) then '21-25'
    when in (26,27,28,29,30) then '26-30'
    when in (31,32,33,34,35) then '31-35'
    when in (36,37,38,39,40) then '36-40'
    when in (41,42,43,44,45) then '41-45'
    when in (46,47,48,49,50) then '46-50'
    when in (51,52,53,54,55) then '51-55'
    when in (56,57,58,59,60) then '56-60'
    when in (61,62,63,64,65) then '61-65'
    when in (66,67,68,69,70) then '66-70'*/
    else
    71+
    end as 'AGE'
    ,sum(case a.d_vst_dontyp when '1' then 1 else 0 end ) as 'DRAW1'
    ,sum(case a.d_vst_dontyp when 'xx' then 1 else 1 end ) as 'TOTAL'
    from dnr_aaa_db_rec, dnr_dud_db_rec, temp_tb3, dnr_vst_db_rec a
    where a.d_vst_instid = ''
    and a.d_vst_instid = d_aaa_instid
    and a.d_vst_id = ID1
    and a.d_vst_instid = n_per_instid
    and a.d_vst_id = n_per_id
    and n_per_gender = 'M'
    and a.d_vst_btcdte between 20020301 and 20030228
    and a.d_vst_btcdte = (Select max(b.d_vst_btcdte)
    from dnr_vst_db_rec b
    where b.d_vst_instid = ''
    and b.d_vst_status = 'DN'
    and b.d_vst_dontyp = 'WB'
    and b.d_vst_id = a.d_vst_id
    and b.d_vst_btcdte between 20020301 and 20030228)

    group by
    d_aaa_insthdg
    ,case (d_vst_btcdte - n_per_birth) / 10000
    when in (14,15,16) the '14-16'
    when in (17,18,19,20) then '17-20'
    when in (21, 22,23,24,25) then '21-25'
    when in (26,27,28,29,30) then '26-30'
    when in (31,32,33,34,35) then '31-35'
    when in (36,37,38,39,40) then '36-40'
    when in (41,42,43,44,45) then '41-45'
    when in (46,47,48,49,50) then '46-50'
    when in (51,52,53,54,55) then '51-55'
    when in (56,57,58,59,60) then '56-60'
    when in (61,62,63,64,65) then '61-65'
    when in (66,67,68,69,70) then '66-70'*/
    else
    71+
    end as

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I think you have 2 problems. First, move (d_vst_btcdte - n_per_birth) / 10000 to be between "when" and "in". Second, in both case statements you have 71+ followed by "*/". You need to surround 71+ with apostrophes and remove */.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

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