troubleshooting Question

Please verify my sql is correct

Avatar of jecommera
jecommeraFlag for United Kingdom of Great Britain and Northern Ireland asked on
DatabasesOracle DatabaseSQL
4 Comments1 Solution585 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros