problem with sql query

[Shawn Anderson]

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:

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.

[Shawn Anderson]

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!

[Knuth Konrad]

Try ...

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 "*".

[Michael Mattias]

>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)

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(+)
/

[Shawn Anderson]

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..