jecommera
asked on
Please verify my sql is correct
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
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
can you post the real table names? makes it much easier than the $A$ which is illegal
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi,
$A$ - Abandoned_list
$B$ - Contacts
$C$ - Inventory
$D$ - Catalogue
$E$ - Recommendations
$F$ - Resulting_table
$A$ - Abandoned_list
$B$ - Contacts
$C$ - Inventory
$D$ - Catalogue
$E$ - Recommendations
$F$ - Resulting_table
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.