Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2015
    Posts
    2

    Unanswered: The SELECT statement includes a reserved word or an argument name that is misspelled.

    ... or missing, or the punctuation is incorrect.

    Please help I am using Ace provider to query the ACCDB.

    For some reason when I run the following SQL in MS Access everything is fine however when I call it I get an error:

    Code:
    PARAMETERS seg_type_id Long, div_type_id Long, MBU4_type_id Long, MBU3_type_id Long, MBU2_type_id Long, Team_type_id Long, Project_type_id Long, CFL_type_id Long;
    
    SELECT t.[node_id], t.[parent_id], t.[type_id], t.[attrib_id], t.[node_value], attributes.[default], true AS active, t.[path]
    FROM (
    		select distinct [segment_code] as node_id,0 as parent_id,[segment] as type_id,[segment_name] as attrib_id, [SEGMENT_DESC] as node_value, true as default,"\" & [segment_code] & "\\\\\\\" as path
    		from staging
    		where segment=seg_type_id and SEGMENT_DESC  <>""
    
    	union all
    
    		select distinct [DIVISION_CODE] as node_id, [Division_Parent] as parent_id, [division] as type_id, [division_name] as attrib_id, [DIVN_DESC] as node_value,true as default,"\" & [segment_code] & "\" & [division_code]  & "\\\\\\" as path
    		from staging
    		where division=div_type_id and DIVN_DESC <>""
    
    	union all
    
    		select distinct [MBU4_CODE] as node_id, [MBU4_Parent] as parent_id, [MBU4] as type_id, [MBU4_Name] as attrib_id, [MBU4_DESC] as node_value,true as default,"\" & [segment_code] & "\" & [division_code] & "\" & [mbu4_code]  & "\\\\\" as path
    		from staging
    		where MBU4=mbu4_type_id and mbu4_DESC <>""
    
    	union all
    		
    		select distinct [MBU3_CODE] as node_id, [MBU3_Parent] as parent_id, [MBU3] as type_id, [MBU3_Name] as attrib_id, [MBU3_DESC] as node_value,true as default,"\" & [segment_code] & "\" & [division_code] & "\" & [mbu4_code] & "\" & [mbu3_code] & "\\\\" as path
    		from staging
    		where MBU3=mbu3_type_id and mbu3_DESC <>""
    
    	union all
    		
    		select distinct [MBU2_CODE] as node_id, [MBU2_Parent] as parent_id, [MBU2] as type_id, [MBU2_Name] as attrib_id, [MBU2_DESC] as node_value,true as default,"\" & [segment_code] & "\" & [division_code] & "\" & [mbu4_code] & "\" & [mbu3_code] & "\" & [mbu2_code] & "\\\" as path
    		from staging
    		where MBU2=mbu2_type_id and mbu2_DESC <>""
    
    	union all
    
    		select distinct [TEAM_CODE] as node_id, [Team_Parent] as parent_id, [Team] as type_id, [Team_Name] as attrib_id, [TEAM_DESC] as node_value,true as default,"\" & [segment_code] & "\" & [division_code] & "\" & [mbu4_code] & "\" & [mbu3_code] & "\" & [mbu2_code] & "\" & [team_code] & "\\" as path
    		from staging
    		where Team=team_type_id and TEAM_DESC <>""
    
    	union all
    
    		select distinct [TEAM_CODE] as node_id, [Team_Parent] as parent_id, [Team] as type_id, [cfl] as attrib_id, [CFL_CODE] as node_value,true as default,"\" & [segment_code] & "\" & [division_code] & "\" & [mbu4_code] & "\" & [mbu3_code] & "\" & [mbu2_code] & "\" & [team_code] & "\\" as path
    		from staging
    		where Team=team_type_id and CFL_CODE <>""
    
    	union all
    
    		select distinct [PROJECT_CODE] as node_id, [Project_Parent] as parent_id, [Project] as type_id, [Project_Name] as attrib_id, [project_desc] as node_value,true as default,"\" & [segment_code] & "\" & [division_code] & "\" & [mbu4_code] & "\" & [mbu3_code] & "\" & [mbu2_code] & "\" & [team_code] & "\" & [project_code] & "\" as path
    		from staging
    		where Project=Project_type_id and project_desc <>""
    	
    union all
    
    		select distinct [CFL_CODE] as node_id, [CFL_Parent] as parent_id, [CFL] as type_id, [CFL_Name] as attrib_id, [cfl_code] as node_value,true as default,"\" & [segment_code] & "\" & [division_code] & "\" & [mbu4_code] & "\" & [mbu3_code] & "\" & [mbu2_code] & "\" & [team_code] & "\" & [project_code] & "\" & [cfl_code] as path
    		from staging
    		where cfl=cfl_type_id and cfl_code <>""
    
    	)  AS t LEFT JOIN attributes ON t.attrib_id = attributes.id
    ORDER BY 3, 2, 1, 4;
    Any help would be much appreciated

  2. #2
    Join Date
    Nov 2015
    Posts
    2

    Resolved

    Hi,

    I found out that the default needed to be wrapped with "`" so I replaced all my default's to `default`.

    Hope this helps should anyone need it,
    Jake

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ...or better yet
    don't use a reserved word or symbol in your table or column names
    https://www.google.co.uk/webhp?sourc...0and%20symbols

    if you must ignore SQL syntax then delimit you table and column names using square barckets, eg
    Code:
    [my table name].[my column name]
    Also a suggestion, when you get an error and you are looking for help from this or other forums please make life easier for your audience and only post the relevant code, the code where the error is reported, and as few preccdeding lines n eeded to support the request for help. just posting a whole block of code willy nilly sends out the message that your time is way to important to identify where the problem arises, and therefore by definition others time is of no importance as they must minion like wade through line after line of crap and try and work out where the problem lies. But hey thats OK as its not 'my' time that is being wasted its some other mugs
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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