Post reply

Export query with possible REPLACE clause...

  • January 10, 2011 at 1:59 pm

    #388594

    I have a query i wrote (probably badly...it's been 17 years since i did anything more than a basic SELECT query) that's designed to pull two columns of data (along with our productID column). at the end of each METATAG_Keywords list of data are the words "midwest stereo, midwest pro sound and lighting, chicago" which i'd like to replace during the export with "best pro dj gear".

    i am also pulling from approximately half of our categories and subcategories, which you'll see by the OR phrases. i'm sure there must be a shorter way to do that, but i can't seem to make anything work. any help would be appreciated greatly!

    SELECT

    p.ProductCode AS id,

    REPLACE('pm.METATAG_Keywords', 'Midwest Stereo, Midwest Pro Sound and Lighting, Chicago', 'Best Pro DJ Gear')) AS metakeywords,

    pe.METATAG_Description AS metadescription

    FROM Products p

    INNER JOIN Products_Descriptions pd

    ON p.ProductID = pd.ProductID

    INNER JOIN Products_Extended pe

    ON p.ProductID = pe.ProductID

    INNER JOIN Products_Memos pm

    ON p.ProductID = pm.ProductID

    WHERE p.ProductID IN (SELECT Categories_Products_Link.ProductID FROM Categories_Products_Link WHERE Categories_Products_Link.CategoryID = (21))

    OR p.ProductID IN (SELECT Categories_Products_Link.ProductID FROM Categories_Products_Link WHERE Categories_Products_Link.CategoryID = (219))

    OR p.ProductID IN (SELECT Categories_Products_Link.ProductID FROM Categories_Products_Link WHERE Categories_Products_Link.CategoryID = (61))

    OR p.ProductID IN (SELECT Categories_Products_Link.ProductID FROM Categories_Products_Link WHERE Categories_Products_Link.CategoryID = (59))

    OR p.ProductID IN (SELECT Categories_Products_Link.ProductID FROM Categories_Products_Link WHERE Categories_Products_Link.CategoryID = (187))

    OR p.ProductID IN (SELECT Categories_Products_Link.ProductID FROM Categories_Products_Link WHERE Categories_Products_Link.CategoryID = (63))

    OR p.ProductID IN (SELECT Categories_Products_Link.ProductID FROM Categories_Products_Link WHERE Categories_Products_Link.CategoryID = (57))

    OR p.ProductID IN (SELECT Categories_Products_Link.ProductID FROM Categories_Products_Link WHERE Categories_Products_Link.CategoryID = (60))

    OR p.ProductID IN (SELECT Categories_Products_Link.ProductID FROM Categories_Products_Link WHERE Categories_Products_Link.CategoryID = (23))

    OR p.ProductID IN (SELECT Categories_Products_Link.ProductID FROM Categories_Products_Link WHERE Categories_Products_Link.CategoryID = (56))

    OR p.ProductID IN (SELECT Categories_Products_Link.ProductID FROM Categories_Products_Link WHERE Categories_Products_Link.CategoryID = (62))

    OR p.ProductID IN (SELECT Categories_Products_Link.ProductID FROM Categories_Products_Link WHERE Categories_Products_Link.CategoryID = (54))

    OR p.ProductID IN (SELECT Categories_Products_Link.ProductID FROM Categories_Products_Link WHERE Categories_Products_Link.CategoryID = (65))

    OR p.ProductID IN (SELECT Categories_Products_Link.ProductID FROM Categories_Products_Link WHERE Categories_Products_Link.CategoryID = (64))

    OR p.ProductID IN (SELECT Categories_Products_Link.ProductID FROM Categories_Products_Link WHERE Categories_Products_Link.CategoryID = (58))

    OR p.ProductID IN (SELECT Categories_Products_Link.ProductID FROM Categories_Products_Link WHERE Categories_Products_Link.CategoryID = (95))

    OR p.ProductID IN (SELECT Categories_Products_Link.ProductID FROM Categories_Products_Link WHERE Categories_Products_Link.CategoryID = (88))

    OR p.ProductID IN (SELECT Categories_Products_Link.ProductID FROM Categories_Products_Link WHERE Categories_Products_Link.CategoryID = (108))

    OR p.ProductID IN (SELECT Categories_Products_Link.ProductID FROM Categories_Products_Link WHERE Categories_Products_Link.CategoryID = (110))

    OR p.ProductID IN (SELECT Categories_Products_Link.ProductID FROM Categories_Products_Link WHERE Categories_Products_Link.CategoryID = (107))

    OR p.ProductID IN (SELECT Categories_Products_Link.ProductID FROM Categories_Products_Link WHERE Categories_Products_Link.CategoryID = (109))

    OR p.ProductID IN (SELECT Categories_Products_Link.ProductID FROM Categories_Products_Link WHERE Categories_Products_Link.CategoryID = (98))

    OR p.ProductID IN (SELECT Categories_Products_Link.ProductID FROM Categories_Products_Link WHERE Categories_Products_Link.CategoryID = (90))

    OR p.ProductID IN (SELECT Categories_Products_Link.ProductID FROM Categories_Products_Link WHERE Categories_Products_Link.CategoryID = (103))

    OR p.ProductID IN (SELECT Categories_Products_Link.ProductID FROM Categories_Products_Link WHERE Categories_Products_Link.CategoryID = (106))

    OR p.ProductID IN (SELECT Categories_Products_Link.ProductID FROM Categories_Products_Link WHERE Categories_Products_Link.CategoryID = (105))

    OR p.ProductID IN (SELECT Categories_Products_Link.ProductID FROM Categories_Products_Link WHERE Categories_Products_Link.CategoryID = (104))

    OR p.ProductID IN (SELECT Categories_Products_Link.ProductID FROM Categories_Products_Link WHERE Categories_Products_Link.CategoryID = (89))

    OR p.ProductID IN (SELECT Categories_Products_Link.ProductID FROM Categories_Products_Link WHERE Categories_Products_Link.CategoryID = (198))

    OR p.ProductID IN (SELECT Categories_Products_Link.ProductID FROM Categories_Products_Link WHERE Categories_Products_Link.CategoryID = (99))

    OR p.ProductID IN (SELECT Categories_Products_Link.ProductID FROM Categories_Products_Link WHERE Categories_Products_Link.CategoryID = (100))

    OR p.ProductID IN (SELECT Categories_Products_Link.ProductID FROM Categories_Products_Link WHERE Categories_Products_Link.CategoryID = (102))

    OR p.ProductID IN (SELECT Categories_Products_Link.ProductID FROM Categories_Products_Link WHERE Categories_Products_Link.CategoryID = (101))

    OR p.ProductID IN (SELECT Categories_Products_Link.ProductID FROM Categories_Products_Link WHERE Categories_Products_Link.CategoryID = (96))

    OR p.ProductID IN (SELECT Categories_Products_Link.ProductID FROM Categories_Products_Link WHERE Categories_Products_Link.CategoryID = (92))

    OR p.ProductID IN (SELECT Categories_Products_Link.ProductID FROM Categories_Products_Link WHERE Categories_Products_Link.CategoryID = (181))

    OR p.ProductID IN (SELECT Categories_Products_Link.ProductID FROM Categories_Products_Link WHERE Categories_Products_Link.CategoryID = (97))

    OR p.ProductID IN (SELECT Categories_Products_Link.ProductID FROM Categories_Products_Link WHERE Categories_Products_Link.CategoryID = (93))

    OR p.ProductID IN (SELECT Categories_Products_Link.ProductID FROM Categories_Products_Link WHERE Categories_Products_Link.CategoryID = (94))

    OR p.ProductID IN (SELECT Categories_Products_Link.ProductID FROM Categories_Products_Link WHERE Categories_Products_Link.CategoryID = (91))

    OR p.ProductID IN (SELECT Categories_Products_Link.ProductID FROM Categories_Products_Link WHERE Categories_Products_Link.CategoryID = (87))

    OR p.ProductID IN (SELECT Categories_Products_Link.ProductID FROM Categories_Products_Link WHERE Categories_Products_Link.CategoryID = (86))

    OR p.ProductID IN (SELECT Categories_Products_Link.ProductID FROM Categories_Products_Link WHERE Categories_Products_Link.CategoryID = (292))

    OR p.ProductID IN (SELECT Categories_Products_Link.ProductID FROM Categories_Products_Link WHERE Categories_Products_Link.CategoryID = (295))

    OR p.ProductID IN (SELECT Categories_Products_Link.ProductID FROM Categories_Products_Link WHERE Categories_Products_Link.CategoryID = (121))

    OR p.ProductID IN (SELECT Categories_Products_Link.ProductID FROM Categories_Products_Link WHERE Categories_Products_Link.CategoryID = (171))

    OR p.ProductID IN (SELECT Categories_Products_Link.ProductID FROM Categories_Products_Link WHERE Categories_Products_Link.CategoryID = (175))

    OR p.ProductID IN (SELECT Categories_Products_Link.ProductID FROM Categories_Products_Link WHERE Categories_Products_Link.CategoryID = (177))

    OR p.ProductID IN (SELECT Categories_Products_Link.ProductID FROM Categories_Products_Link WHERE Categories_Products_Link.CategoryID = (169))

    OR p.ProductID IN (SELECT Categories_Products_Link.ProductID FROM Categories_Products_Link WHERE Categories_Products_Link.CategoryID = (173))

    OR p.ProductID IN (SELECT Categories_Products_Link.ProductID FROM Categories_Products_Link WHERE Categories_Products_Link.CategoryID = (225))

    OR p.ProductID IN (SELECT Categories_Products_Link.ProductID FROM Categories_Products_Link WHERE Categories_Products_Link.CategoryID = (226))

    OR p.ProductID IN (SELECT Categories_Products_Link.ProductID FROM Categories_Products_Link WHERE Categories_Products_Link.CategoryID = (163))

    OR p.ProductID IN (SELECT Categories_Products_Link.ProductID FROM Categories_Products_Link WHERE Categories_Products_Link.CategoryID = (164))

    OR p.ProductID IN (SELECT Categories_Products_Link.ProductID FROM Categories_Products_Link WHERE Categories_Products_Link.CategoryID = (165))

    OR p.ProductID IN (SELECT Categories_Products_Link.ProductID FROM Categories_Products_Link WHERE Categories_Products_Link.CategoryID = (172))

    OR p.ProductID IN (SELECT Categories_Products_Link.ProductID FROM Categories_Products_Link WHERE Categories_Products_Link.CategoryID = (174))

    OR p.ProductID IN (SELECT Categories_Products_Link.ProductID FROM Categories_Products_Link WHERE Categories_Products_Link.CategoryID = (67))

    OR p.ProductID IN (SELECT Categories_Products_Link.ProductID FROM Categories_Products_Link WHERE Categories_Products_Link.CategoryID = (76))

    OR p.ProductID IN (SELECT Categories_Products_Link.ProductID FROM Categories_Products_Link WHERE Categories_Products_Link.CategoryID = (77))

    OR p.ProductID IN (SELECT Categories_Products_Link.ProductID FROM Categories_Products_Link WHERE Categories_Products_Link.CategoryID = (78))

    OR p.ProductID IN (SELECT Categories_Products_Link.ProductID FROM Categories_Products_Link WHERE Categories_Products_Link.CategoryID = (75))

    OR p.ProductID IN (SELECT Categories_Products_Link.ProductID FROM Categories_Products_Link WHERE Categories_Products_Link.CategoryID = (220))

    OR p.ProductID IN (SELECT Categories_Products_Link.ProductID FROM Categories_Products_Link WHERE Categories_Products_Link.CategoryID = (69))

    OR p.ProductID IN (SELECT Categories_Products_Link.ProductID FROM Categories_Products_Link WHERE Categories_Products_Link.CategoryID = (126))

    OR p.ProductID IN (SELECT Categories_Products_Link.ProductID FROM Categories_Products_Link WHERE Categories_Products_Link.CategoryID = (70))

    OR p.ProductID IN (SELECT Categories_Products_Link.ProductID FROM Categories_Products_Link WHERE Categories_Products_Link.CategoryID = (119))

    OR p.ProductID IN (SELECT Categories_Products_Link.ProductID FROM Categories_Products_Link WHERE Categories_Products_Link.CategoryID = (127))

    OR p.ProductID IN (SELECT Categories_Products_Link.ProductID FROM Categories_Products_Link WHERE Categories_Products_Link.CategoryID = (124))

    OR p.ProductID IN (SELECT Categories_Products_Link.ProductID FROM Categories_Products_Link WHERE Categories_Products_Link.CategoryID = (123))

    ORDER BY p.ProductCode

  • January 10, 2011 at 3:49 pm

    #1271651

    Change your heavy WHERE clause to

    INNER JOIN Categories_Products_Link

    ON p.ProductID = Categories_Products_Link.ProductID

    WHERE

    Categories_Products_Link.CategoryID IN (21,219,...)

    I'd also recommend to consider using an additional table to hold those numerous numbers if those change frequently.


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply