Invalid Object Name – Tried a few things

I have the following query:

SELECT
    customer_id,
    SUM(extended_amount) AS total_spent,
    COUNT(DISTINCT(client_web_order_number)) AS #_of_orders,
    MAX(oms_order_date) AS last_purchase_date,
    DATEDIFF(day, last_purchase_date, CAST(GETDATE() AS DATE))  AS days_since_last_purchase,
    MIN(oms_order_date) AS registration_date,
    DATEDIFF(day, registration_date, CAST(GETDATE() AS DATE)) AS days_active,
    CASE
        WHEN DATEDIFF(day, last_purchase_date, CAST(GETDATE() AS DATE)) = DATEDIFF(day, registration_date, CAST(GETDATE() AS DATE)) 
        THEN 'One-time purchase'
        ELSE ''
    END AS notes

FROM 
    [us_raw].[l_dmw_order_report]

WHERE 
    quantity_ordered > 0
    AND customer_id IS NOT NULL
    AND customer_id != ('')
    AND customer_id LIKE 'US%'
    AND oms_order_date IS NOT NULL
    AND UPPER(line_status) NOT IN ('','RETURN', 'CANCELLED')
    AND UPPER(item_description_1) NOT IN ('','FREIGHT', 'RETURN LABEL FEE', 'VISIBLE STITCH')
    AND (quantity_ordered * unit_price_amount) > 0
    AND extended_amount < 1000 --NO BULK ORDERS
    AND oms_order_date BETWEEN '2018-01-01' AND GETDATE()
    AND SUBSTRING(upc,1,6) IN (SELECT item_code FROM item_master_zs WHERE new_division BETWEEN '11' AND '39')

GROUP BY
    customer_id
--HAVING
--  SUM(extended_amount) BETWEEN 1 AND 50

It keeps telling me ‘invalid object name’. The table exists. I checked the drop-down to ensure the right database is connected. I refreshed Intellisense cache.

Not sure what else to do…

Answer

I think you are looking for linked server object names, if so then you need to call it :

<linked-server-name>.<database-name>.<schema>.<object-name>

So, after then SELECT Statement would looks :

SELECT <alias>.*
FROM <linked-server-name>.<database-name>.<schema>.<object-name> AS <alias>

Advertisement

Answer