Announcement

Collapse
No announcement yet.

problem with sql query

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • problem with sql query

    I have an access database that tracks hits on specific web pages on a web server.

    I want to query out the information, and want to omit hits from people on the office LAN. The first three octets of the LAN is 10.0.0.

    My sql statement looks like this:
    Code:
    select ipAddress,hTime,pageName,loginID from mainTable where ((hTime>=#01/01/1900# and hTime<=#01/01/2199#) AND (ipAddress not like '10.0.0.*')) order by hTime,pageName,loginID
    If I cut and paste this sql query into an Access query, it works, but when I run it from my CGI, in includes IP's starting with 10.0.0.

    Why?

    PS: I'm using PBCC and SQLToolsPro.

  • #2
    OK, I figured it out.
    I had to replace '10.0.0.*' with '10.0.0.%'

    It works exactly the opposite in the Access IDE as opposed to an external SQL call.

    d'oh!

    Comment


    • #3
      Try ...
      Code:
      AND (LEFT(ipAddress, 7) <> '10.0.0.')
      Getting rid off the LIKE operator and the wildcards should speed your query up a bit.

      As far as different wildcards goes, Access somewhere has a setting to use ANSI SQL. If I'm not mistaken, it than uses "%" instead of "*".

      Comment


      • #4
        >Getting rid off the LIKE operator and the wildcards should speed your query up a bit

        Even faster, create a column for the first three nodes; store. Create a VIEW which excludes nodes with "10.0.0" in that column.

        For that matter, if Access supports creating views using SUBSTR() just create the VIEW against the existing data.

        In either case above the VIEW will be updated as new records are stored and retrieval won't have to do ANY filtering.

        By doing this filtering a few microseconds at a time on each INSERT, you never need to do it on the SELECT, and Re-do it on each and every SELECT forever and ever.

        Faster still... don't even store those rows in the first place.

        [added]

        You can do a lot of stuff with VIEWs. Here is one I am working with now (no, I did not write this)... it may seem a bit extreme, but in context it offers amazingly quick applications access to summary invoice information. (This is Oracle)
        Code:
        CREATE OR REPLACE VIEW bpi.invoice_header_v 
        /* Title: invoice_header_v
            Modification History:
            04/11/2001 - 1.0   [name edited]  Original
            10/21/2003 - 1.1   [name edited   Added Insurance and Additional taxes to total, insurance to freight total, 
                                               additional tax to sales tax total
        */
        (
           seg_no,
           user_invc_no,
           invc_no,
           acct_dt,
           acct_per,
           acct_yr,
           bill_to_cust_no,
           user_cust_no,
           cust_name,
           billto_addr_no,
           soldto_cust_no,
           soldto_addr_no,
           close_dt,
           curr_cd,
           curr_type_desc,
           cust_type_cd,
           invc_dt,
           invc_due_dt,
           invc_src_cd,
           invc_status_cd,
           invc_type_cd,
           pmt_terms_cd,
           pmt_terms_desc,
           print_invc_status,
           shipto_addr_no,
           remit_to_addr_no,
           cust_purch_ord_no,
           shipmt_no,
           bill_of_lading,
           carr_cd,
           fob_cd,
           no_of_crtns,
           pick_list_no,
           shipmt_dt,
           ship_via,
           terr_no,
           dock_no,
           net_wgt,
           invc_cost,
           part_ext_cost,
           part_misc_cost,
           misc_cost,
           invc_frt_amt,
           part_frt_amt,
           invc_ins_amt,
           part_ins_amt,
           srvc_chrg_cost,
           part_sales_tax_cost,
           srvc_sales_tax_cost,
           misc_sales_tax_cost,
           invc_addl_tax_cost,
           part_addl_tax_cost,
           misc_addl_tax_cost,
           misc_vat_tax_cost,
           part_misc_vat_cost,
           part_vat_tax_cost,
           total_invc,
           invc_disc_cost,
           total_part,
           total_misc,
           total_disc,
           total_frt,
           total_ins,
           total_sales_tax,
           net_part,
           part_disc_cost,
           net_misc,
           misc_disc_cost )
        AS
        SELECT a.seg_no, a.user_invc_no, a.invc_no, a.acct_dt, a.acct_per, a.acct_yr,
               a.bill_to_cust_no, d.user_cust_no, d.cust_name,
               e.addr_no AS billto_addr_no,
               DECODE(a.invc_src_cd,
                      'SHIPMENTS', f.cust_no,
                      a.bill_to_cust_no
                     ) AS soldto_cust_no,
               NVL(h.addr_no, 0) AS soldto_addr_no, a.close_dt, a.curr_cd,
               c.curr_type_desc, a.cust_type_cd, a.invc_dt, a.invc_due_dt,
               a.invc_src_cd, a.invc_status_cd, a.invc_type_cd, a.pmt_terms_cd,
               i.pmt_terms_desc, a.print_invc_status,
               NVL(DECODE(a.invc_src_cd,
                          'SHIPMENTS', f.ship_to_addr_no,
                          a.ship_to_addr_no
                         ),
                   0) AS shipto_addr_no,
               a.remit_to_addr_no, f.cust_purch_ord_no, f.shipmt_no, g.bill_of_lading,
               g.carr_cd, g.fob_cd, g.no_of_crtns, g.pick_list_no, g.shipmt_dt,
               g.ship_via, g.terr_no, g.dock_no, g.net_wgt,
               ROUND(b.invc_cost, 2) AS invc_cost,
               ROUND(b.part_ext_cost, 2) AS part_ext_cost,
               ROUND(b.part_misc_cost, 2) AS part_misc_cost,
               ROUND(b.misc_cost, 2) AS misc_cost,
               ROUND(b.invc_frt_amt, 2) AS invc_frt_amt,
               ROUND(b.part_frt_amt, 2) AS part_frt_amt,
               ROUND(b.invc_ins_amt, 2) AS invc_ins_amt,
               ROUND(b.part_ins_amt, 2) AS part_ins_amt,
               ROUND(b.srvc_chrg_cost, 2) AS srvc_chrg_cost,
               ROUND(b.part_sales_tax_cost, 2) AS part_sales_tax_cost,
               ROUND(b.srvc_sales_tax_cost, 2) AS srvc_sales_tax_cost,
               ROUND(b.misc_sales_tax_cost, 2) AS misc_sales_tax_cost,
               ROUND(b.invc_addl_tax_cost, 2) AS invc_addl_tax_cost,
               ROUND(b.part_addl_tax_cost, 2) AS part_addl_tax_cost,
               ROUND(b.misc_addl_tax_cost, 2) AS misc_addl_tax_cost,
               ROUND(b.misc_vat_tax_cost, 2) AS misc_vat_tax_cost,
               ROUND(b.part_misc_vat_cost, 2) AS part_misc_vat_cost,
               ROUND(b.part_vat_tax_cost, 2) AS part_vat_tax_cost,
               ROUND(b.part_ext_cost, 2)
               +(ROUND(b.part_misc_cost, 2) + ROUND(b.misc_cost, 2))
               +(ROUND(b.invc_frt_amt, 2) + ROUND(b.part_frt_amt, 2))
               +(ROUND(b.invc_ins_amt, 2) + ROUND(b.part_ins_amt, 2))
               -(ROUND(b.part_disc_cost, 2) + ROUND(b.misc_disc_cost, 2)
                 + ROUND(b.invc_disc_cost, 2)
                )
               +(ROUND(b.part_sales_tax_cost, 2) + ROUND(b.srvc_sales_tax_cost, 2)
                 + ROUND(b.misc_sales_tax_cost, 2)
                )
               +(ROUND(b.invc_addl_tax_cost, 2) + ROUND(b.part_addl_tax_cost, 2)
                 + ROUND(b.misc_addl_tax_cost, 2)
                ) AS total_invc,
               ROUND(b.invc_disc_cost, 2) AS invc_disc_cost,
               ROUND(b.part_ext_cost, 2) AS total_part,
               (ROUND(b.part_misc_cost, 2) + ROUND(b.misc_cost, 2)) AS total_misc,
               (ROUND(b.part_disc_cost, 2) + ROUND(b.misc_disc_cost, 2)
                + ROUND(b.invc_disc_cost, 2)
               ) AS total_disc,
               (ROUND(b.invc_ins_amt, 2) + ROUND(b.part_ins_amt, 2))
               +(ROUND(b.invc_frt_amt, 2) + ROUND(b.part_frt_amt, 2)) AS total_frt,
               (ROUND(b.invc_ins_amt, 2) + ROUND(b.part_ins_amt, 2)) AS total_ins,
               (ROUND(b.part_sales_tax_cost, 2) + ROUND(b.srvc_sales_tax_cost, 2)
                + ROUND(b.misc_sales_tax_cost, 2)
               )
               +(ROUND(b.invc_addl_tax_cost, 2) + ROUND(b.part_addl_tax_cost, 2)
                 + ROUND(b.misc_addl_tax_cost, 2)
                ) AS total_sales_tax,
               (ROUND(b.part_ext_cost, 2) - ROUND(b.part_disc_cost, 2)) AS net_part,
               ROUND(b.part_disc_cost, 2) AS part_disc_cost,
               ((ROUND(b.part_misc_cost, 2) + ROUND(b.misc_cost, 2))
                - ROUND(b.misc_disc_cost, 2)
               ) AS net_misc,
               ROUND(b.misc_disc_cost, 2) AS misc_disc_cost
          FROM prod.invc a, prod.invc_totals b, prod.curr_type c, prod.cust d,
               (SELECT addr_no, cust_no
                  FROM prod.cust_addr_func_rel
                 WHERE cust_addr_func_cd = 'BILLTO') e, prod.cust_shipmt f,
               prod.shipmt g, (SELECT addr_no, cust_no
                                 FROM prod.cust_addr_func_rel
                                WHERE cust_addr_func_cd = 'SOLDTO') h,
               prod.pmt_terms i
         WHERE (a.invc_no = b.invc_no) AND(a.curr_cd = c.curr_type_cd(+))
               AND(a.bill_to_cust_no = d.cust_no) AND(a.bill_to_cust_no = e.cust_no)
               AND(a.invc_no = f.invc_no(+)) AND(f.shipmt_no = g.shipmt_no(+))
               AND(DECODE(a.invc_src_cd, 'SHIPMENTS', f.cust_no, a.bill_to_cust_no) =
                                                                             h.cust_no
                  )
               AND a.pmt_terms_cd = i.pmt_terms_cd(+)
        /
        Last edited by Michael Mattias; 5 Aug 2008, 07:49 AM.
        Michael Mattias
        Tal Systems (retired)
        Port Washington WI USA
        [email protected]
        http://www.talsystems.com

        Comment


        • #5
          very good ideas, thanks.

          I didn't even realize that access had a LEFT function.
          I never use Access to do anything, but often will use the database from a pb application when somebody else has created something in access.

          Though theoretically both your suggestions will speed the application up, it is nearly instantaneous now....

          (I know, "said like a true vb programmer". )

          I will take a look at VIEWs, the have piqued my interest on another project I'm working on..

          Comment

          Working...
          X