none
WITH clause RRS feed

  • Question

  • I am trying to write a simple query in Access 2016:

    with TEMP as (

    SELECT LINKNUM, CONVERT(date, PHOTODATE) asDATESTAMP

    FROM IMAGE

    ),

    VISIT AS (

    SELECT * FROM TEMP

    GROUP BY LINKNUM, DATESTAMP

    HAVING COUNT(*) >= 16     

    ),

    IMAGE2 AS (

    SELECT [RECNUM],[CREATETIME],[MODTIME],[CREATEUSER],[MODUSER],[LINKNUM],[IMAGE],[TYPE]

          ,[IMAGENUMBER],CONVERT(date, [PHOTODATE]) ASDATESTAMP,[AGE],[PROCDATE],[DIAGCODE]

          ,[DIAGNOSIS],[PROCCODE],[PROCEDURE],[VISITCODE],[RELEASE],[DOCTOR],[GRADE],[NOTES]

          ,[SECTOR],[LESION],[XPOS],[YPOS],[SLINKNUM],[OBSERVE],[SCHEDULE],[EXCISED],[BIOPSY]

          ,[FNOTES],[IMAGE_TYPE],[CAMERA_ID1],[IMAGE_NUM1],[CAMERA_ID2],[IMAGE_NUM2],[CAMERA_ID3]

          ,[IMAGE_NUM3],[COMPOSITE],[REPLACED],[TRANSMIT],[MODIFIED],[CONVERTED],[SOURCE]

          ,[CATEGORY_IMAGE_TYPE],[CATEGORY_IMAGE_STATUS],[CATEGORY_DISPLAY_FLAG]

          ,[CATEGORY_SELECTION_FLAG],[CATEGORY_USAGE_FLAG],[CATEGORY_MODIFICATION_FLAG],[CAPTURE_SET_RECNUM]

          ,[CAPTURE_SET_INDEX],[AppKey],[AppName],[AppVendor],[AppVersion],[HAS_MODIFIED]

          ,[MAIN_VERSION],[THUMB_VERSION],[LAYER_FLAGS],[SOURCE_RECNUM],[GUID]

    FROM [IMAGE]

    )

     

    /* CleanImageTable */

    select * from IMAGE2

    JOIN VISIT ON IMAGE2.LINKNUM = VISIT.LINKNUMAND IMAGE2.DATESTAMP = VISIT.DATESTAMP

    However, I get an error saying SQL does not recognize WITH statement : : "Illegal SQL statement; You need 'Delete', Insert', 'Procedure', 'Select' or ' Update'

    Any suggestion?

    Nir

    Tuesday, April 25, 2017 1:07 PM

All replies

  • Hi Nir_N,

    let me inform you that 'With' keyword is not supported by MS Access.

    so it is giving you the error.

    it is supported in TSQL but it is not supported in MS Access.

    so you need to modify your query and write proper syntax that is supported by MS Access.

    As a reference you can refer thread below will give you some more information regarding this issue.

    How do I use the WITH statement in Access?

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, April 27, 2017 7:31 AM
    Moderator
  • Hi Nir_N,

    I can see that you did not follow the thread after you created it.

    I can see that the suggestion given by me can be the answer for your question.

    if you think that it is an answer then try to mark it as an answer.

    so that we can close this thread.

    the thread is still open and we can't close it until you mark the answer.

    so if you got the answer then help us to close this thread.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, May 8, 2017 6:37 AM
    Moderator