We help IT Professionals succeed at work.

Please verify my sql is correct

581 Views
Last Modified: 2012-02-11
I have SQL as follows:

SELECT e.activity_date,        e.email_address_,        e.item,  h.gbp_list_prices,        d.product_name,        d.image_original_1,        f.recommendation1,        g.product_name AS rec_prod_name1,        g.gbp_list_prices AS rec_prod_price1,        g.image_original_1 AS rec_prod_image1,        f.recommendation2,        h.product_name AS rec_prod_name2,        h.gbp_list_prices AS rec_prod_price2,        h.image_original_1 AS rec_prod_image2,        f.recommendation3,        i.product_name AS rec_prod_name3,        i.gbp_list_prices AS rec_prod_price3,        i.image_original_1 AS rec_prod_image3,        f.recommendation4,        j.product_name AS rec_prod_name4,        j.gbp_list_prices AS rec_prod_price4,        j.image_original_1 AS rec_prod_image4    
FROM(SELECT a.activity_date,               a.email_address_,               a.item,               a.item_price AS highest_price,               ROW_NUMBER() OVER (PARTITION BY a.email_address_ ORDER BY a.item_price DESC) rn          
 FROM $A$ a              JOIN $B$ b                  ON LOWER(a.email_address_) = LOWER(b.email_address_)              
JOIN $C$ c                  ON LOWER(a.item) = LOWER(c.sku)        
 WHERE a.activity_date > TRUNC(SYSDATE - 7) AND c.current_free_stock > 10) e      
 JOIN $D$ d           ON lower(d.sku) = lower(e.item)      
 JOIN $E$ f           ON lower(e.item) = lower(f.sku)      
 JOIN $D$ g           ON LOWER(g.sku) = lower(f.recommendation1)      
 LEFT JOIN $D$ h           ON LOWER(h.sku) = LOWER(f.recommendation2)    
  LEFT JOIN $D$ i           ON LOWER(i.sku) = lower(f.recommendation3)      
 LEFT JOIN $D$ j           ON LOWER(j.sku) = LOWER(f.recommendation4)  
WHERE rn = 1

Now I want to join to the $F$ table (using e-mail address_) and only select anyone where the ACTIVITY_DATE field is NOT in the last 31 days

My attempt at the SQL is as follows:

SELECT e.activity_date,        e.email_address_,        e.item,  h.gbp_list_prices,        d.product_name,        d.image_original_1,        f.recommendation1,        g.product_name AS rec_prod_name1,        g.gbp_list_prices AS rec_prod_price1,        g.image_original_1 AS rec_prod_image1,        f.recommendation2,        h.product_name AS rec_prod_name2,        h.gbp_list_prices AS rec_prod_price2,        h.image_original_1 AS rec_prod_image2,        f.recommendation3,        i.product_name AS rec_prod_name3,        i.gbp_list_prices AS rec_prod_price3,        i.image_original_1 AS rec_prod_image3,        f.recommendation4,        j.product_name AS rec_prod_name4,        j.gbp_list_prices AS rec_prod_price4,        j.image_original_1 AS rec_prod_image4      FROM(SELECT a.activity_date,               a.email_address_,               a.item,               a.item_price AS highest_price,               ROW_NUMBER() OVER (PARTITION BY a.email_address_ ORDER BY a.item_price DESC) rn             FROM $A$ a               JOIN $B$ b                   ON LOWER(a.email_address_) = LOWER(b.email_address_)   JOIN $F$ k ON LOWER (a.email_address_) = LOWER (k.email_address_)              JOIN $C$ c                   ON LOWER(a.item) = LOWER(c.sku)   WHERE a.activity_date > TRUNC(SYSDATE - 7) AND NOT (k.activity_date > TRUNC(SYSDATE - 30)) AND c.current_free_stock > 10) e        JOIN $D$ d           ON lower(d.sku) = lower(e.item)        JOIN $E$ f           ON lower(e.item) = lower(f.sku)         JOIN $D$ g           ON LOWER(g.sku) = lower(f.recommendation1)         LEFT JOIN $D$ h           ON LOWER(h.sku) = LOWER(f.recommendation2)        LEFT JOIN $D$ i           ON LOWER(i.sku) = lower(f.recommendation3)        LEFT JOIN $D$ j           ON LOWER(j.sku) = LOWER(f.recommendation4)   WHERE rn = 1

When I run it I get 0 records
Comment
Watch Question

Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
can you post the real table names?  makes it much easier than the $A$ which is illegal
Database Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Hi,

$A$ - Abandoned_list
$B$ - Contacts
$C$ - Inventory
$D$ - Catalogue
$E$ - Recommendations
$F$ - Resulting_table
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
I've already faked in legal names so I could check syntax.  For future posts though, don't mask the names unless you need to hide them.  Since you posted the names, I have to assume you don't need to.  If you do mask then I suggest masking with legal names; just makes everything easier to examine.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.