Results 1 to 9 of 9

Thread: Trim SQL (A2k)

  1. #1
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Trim SQL (A2k)

    Hi

    The SQL as below needs to trim each field to remove spaces from CLI and create the table from the result:
    Table CLI is Dataflex and each field has spaces as well as data. I can't remember how to do this, can anyone help?

    Thanks


    SELECT CLI.CLI_TYP, CLI.NME, CLI.ADD_1, CLI.SALUTE, CLI.ADD_2, CLI.ADD_3, CLI.ADD_4, CLI.PCD, CLI.CNT, CLI.TEL_H, CLI.RECORD_NUMBER, CLI.NME_CDE, CLI.TEL_W, CLI.VAT_IND, CLI.LAB_RTE, CLI.INS_IND, CLI.LAB_TIM, CLI.PRT_NO_IND, CLI.INS_NME_CDE, CLI.POL_NO, CLI.EXS, CLI.BET, CLI.LAB_DSC_PCT, CLI.PRT_DSC_PCT, CLI.PNT_DSC_PCT, CLI.PNT_MRK_PCT, CLI.OTH_DSC_PCT, CLI.ACC_NO, CLI.CAT, CLI.VAT_SPLIT, CLI.VAT_RTE, CLI.FAX, CLI.TERMS INTO tmpCLI
    FROM CLI
    WHERE (((CLI.CLI_TYP)="I") AND ((CLI.NME) Not In ("OBSOLETE","DO NOT USE","Obsolete","obsolete")));

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Trim SQL (A2k)

    Hi Dave,

    Does this do what you want?
    <code>
    SELECT TRIM(CLI.CLI_TYP) AS CLI_TYP, TRIM(CLI.NME) AS NME, TRIM(CLI.ADD_1) AS ADD_1, TRIM(CLI.SALUTE) AS SALUTE, TRIM(CLI.ADD_2) AS ADD_2, TRIM(CLI.ADD_3) AS ADD_3, TRIM(CLI.ADD_4) AS ADD_4, TRIM(CLI.PCD) AS PCD, TRIM(CLI.CNT) AS CNT, TRIM(CLI.TEL_H) AS TEL_H, TRIM(CLI.RECORD_NUMBER) AS RECORD_NUMBER, TRIM(CLI.NME_CDE) AS NME_CDE, TRIM(CLI.TEL_W) AS TEL_W, TRIM(CLI.VAT_IND) AS VAT_IND, TRIM(CLI.LAB_RTE) AS LAB_RTE, TRIM(CLI.INS_IND) AS INS_IND, TRIM(CLI.LAB_TIM) AS LAB_TIM, TRIM(CLI.PRT_NO_IND) AS PRT_NO_IND, TRIM(CLI.INS_NME_CDE) AS INS_NME_CDE, TRIM(CLI.POL_NO) AS POL_NO, TRIM(CLI.EXS) AS EXS, TRIM(CLI.BET) AS BET, TRIM(CLI.LAB_DSC_PCT) AS LAB_DSC_PCT, TRIM(CLI.PRT_DSC_PCT) AS PRT_DSC_PCT, TRIM(CLI.PNT_DSC_PCT) AS PNT_DSC_PCT, TRIM(CLI.PNT_MRK_PCT) AS PNT_MRK_PCT, TRIM(CLI.OTH_DSC_PCT) AS OTH_DSC_PCT, TRIM(CLI.ACC_NO) AS ACC_NO, TRIM(CLI.CAT) AS CAT, TRIM(CLI.VAT_SPLIT) AS VAT_SPLIT, TRIM(CLI.VAT_RTE) AS VAT_RTE, TRIM(CLI.FAX) AS FAX, TRIM(CLI.TERMS) AS TERMS INTO tmpCLI
    FROM CLI
    WHERE CLI.CLI_TYP="I" AND UCase(CLI.NME) Not In ("OBSOLETE","DO NOT USE")</code>

  3. #3
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Trim SQL (A2k)

    Hans

    Thanks for the update but the following query has stopped working ?
    Any idea's as to why 0 fields are populated?


    INSERT INTO DBA_CUSTOMER_DETAIL ( SiteCD, CustomerType, CusCD, Name, Address1, Address2, Address3, Address4, PostCode, Telephone1, Telephone2, StandardLabourRate, SalesLedgerCD )
    SELECT DBA_CUSTOMER_DETAIL.SiteCD, DBA_CUSTOMER_DETAIL.CustomerType, tmpCLI.NME_CDE, tmpCLI.NME, tmpCLI.ADD_1, tmpCLI.ADD_2, tmpCLI.ADD_3, tmpCLI.ADD_4, tmpCLI.PCD, tmpCLI.TEL_H, tmpCLI.TEL_W, tmpCLI.LAB_RTE, tmpCLI.ACC_NO
    FROM DBA_CUSTOMER_DETAIL, tmpCLI
    WHERE (((DBA_CUSTOMER_DETAIL.SiteCD)="MAM001") AND ((DBA_CUSTOMER_DETAIL.CustomerType)="Insurer") AND ((tmpCLI.CLI_TYP)="I"));

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Trim SQL (A2k)

    Does tmpCLI contain any records?

  5. #5
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Trim SQL (A2k)

    Yes, the table is fully populated 424 records !

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Trim SQL (A2k)

    If you temporarily change the append query to a select query, does it return any records?

    If not, does the following query return records?

    SELECT DBA_CUSTOMER_DETAIL.*
    FROM DBA_CUSTOMER_DETAIL
    WHERE (((DBA_CUSTOMER_DETAIL.SiteCD)="MAM001") AND ((DBA_CUSTOMER_DETAIL.CustomerType)="Insurer"));

    And this one?

    SELECT tmpCLI.*
    FROM tmpCLI
    WHERE (((tmpCLI.CLI_TYP)="I"));

  7. #7
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Trim SQL (A2k)

    The first one edited to this:
    SELECT DBA_CUSTOMER_DETAIL.*
    FROM DBA_CUSTOMER_DETAIL;

    works because there are three records in the destination table
    Th esecond query is good as it is.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Trim SQL (A2k)

    Was the condition

    WHERE (((DBA_CUSTOMER_DETAIL.SiteCD)="MAM001") AND ((DBA_CUSTOMER_DETAIL.CustomerType)="Insurer")

    essential for your purposes?

  9. #9
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Trim SQL (A2k)

    Good morning Hans

    Thank you for the help on this and we sorted it in the end.
    Your solution wasn't too far removed from what I had tried to do in the first place.
    We were trying to export data into a Sybase database but the new queries kept falling over and couldn't understand why!
    The fault was a connection error which Access wasn't telling us what the problem was.

    So thank you once again for your expert help.

Posting Permissions

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