troubleshooting Question

SQL error

Avatar of jecommera
jecommeraFlag for United Kingdom of Great Britain and Northern Ireland asked on
DatabasesOracle Database
2 Comments1 Solution497 ViewsLast Modified:
Hi,

I want to extend my SQL to bring in a new table and filter by activity_date field is not in the last 31 days

The below SQL works and is which I want to Join the new table and filter

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


The following SQL is my attempt and gives  "SQL command not properly ended" error.

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

Can someone please check the syntax and see what I am doing wrong
thanks for your help
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 2 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 2 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