Query Error 3802 Database does not exist, UPDATE TABLE From...

Database

Query Error 3802 Database does not exist, UPDATE TABLE From...

I need help with the below query. Things to note are:

  1. The "FROM" query, runs perfectly on it's own
  2. All of the parenthesis are positioned correctly
  3. I'm using Teradata 14

 

UPDATE snd_bqa.open_order_all_test
FROM (
SELECT 
CASE WHEN m.order_status_desc = 'Completed' AND NVL(c.supptype, 'NULL') <> 'CAN'
		THEN 'YES'
		ELSE 'NO'
		END AS "Order Complete",
CASE WHEN m.order_status_desc = 'Completed' AND c.supptype = 'CAN'
		THEN 'YES'
		ELSE 'NO'
		END AS "Order Cancelled",
CASE WHEN pih.effective_start_date IS NOT NULL AND pih.product_instance_status_code = 3
		THEN 'YES'
		ELSE 'NO'
		END AS "Service Active in PB",
c.ordernumber,
c1.order_requested_due_date,
c.creationdate AS "Order Creation Date",
c.ordersubmitdate AS "Order Submit Date",
c.lastupdateddate,
c.supptype,
m.order_status_desc,
m.phase_stage,
cus.sub_nasp_id,
cus.gch_id,
b.work_order_no,
b.itemcode,
b.instance_id,
COALESCE(b.specific_date, b.standard_date) AS "Service Req Due Date",
m.milestone,
m.milestone_desc,
cus.custlegalname,
a.account_number,
vle.vle_id,
vle.currency_cd,
cus.duns_number,
a.cle_vle_id,
CURRENT_TIMESTAMP(2) AS "Originally Loaded",
CURRENT_TIMESTAMP(2) AS "Last Upd",
CAST((CASE WHEN m.order_status_desc = 'Completed' AND NVL(c.supptype, 'NULL') <> 'CAN'
		THEN c.lastupdateddate
		ELSE NULL
		END) AS TIMESTAMP(2)) "Order Completed Date",
CAST((CASE WHEN m.order_status_desc = 'Completed' AND c.supptype = 'CAN'
		THEN c.lastupdateddate
		ELSE NULL
		end) AS TIMESTAMP(2)) "Order Cancelled Date",
pih.last_modified AS "Service Last Modified Date",
pih.effective_start_date AS "Service Effective Date"


FROM (
	SELECT *
	FROM edw_stg_ord_cw_vw.cwpc_basketitem
	WHERE itemcode LIKE 'PR%'
	AND (instance_id, lastupdateddate) IN (
			SELECT instance_id, MAX(lastupdateddate)
			FROM edw_stg_ord_cw_vw.cwpc_basketitem
			GROUP BY instance_id
			)) AS b
INNER JOIN edw_stg_ord_cw_vw.cworderinstance AS c
	ON c.basket_id = b.basketid
	AND c.isactive = 1
	AND c.request_type = 'ORD'
LEFT JOIN (
		SELECT c.ordernumber, 
		MAX(COALESCE(b.specific_date, b.standard_date)) order_requested_due_date
		FROM edw_stg_ord_cw_vw.cworderinstance AS c
		INNER JOIN edw_stg_ord_cw_vw.cwpc_basketitem AS b
			ON b.basketid = c.basket_id

			AND c.request_type = 'ORD'
			AND c.isactive = 1
		GROUP BY c.ordernumber
		) AS c1
	ON c1.ordernumber = c.ordernumber
LEFT JOIN edw_stg_ord_cw_vw.uno_customer AS cus
	ON cus.cworderid = c.cwdocid
	AND cus.oi_customer_id = c.ordering_customer_id
LEFT JOIN edw_stg_ord_cw_vw.uno_milestone AS m
	ON m.milestone_id = c.milestone_id
LEFT JOIN edw_stg_ord_cw_vw.uno_account AS a
	ON a.cworderid = c.cwdocid
	AND b.account_id = a.oi_account_id
LEFT JOIN edw_stg_ord_cw_vw.uno_cle_vle AS vle
	ON vle.cle_vle_id = a.cle_vle_id
	AND c.cwdocid = vle.cworderid
INNER JOIN snd_bqa.open_order_all_test tst
	ON tst."Service Instance ID" = b.instance_id
	AND tst."Milestone" <> m.milestone
LEFT JOIN edw_pb_stg_vw.product_instance_history AS pih
	ON pih.general_5 = tst."Service Instance ID"
WHERE cus.sub_nasp_id NOT IN ('19HNYO', '23MAXA', '19HNYR', '10INTD')
AND COALESCE(b.specific_date, b.standard_date, c1.order_requested_due_date) BETWEEN CURRENT_DATE - 60 AND CURRENT_DATE - 30
)  oo

SET "Order Complete" = oo."Order Complete",
"Order Cancelled" = oo."Order Cancelled",
"Service Active in PB" = oo."Service Active in PB",
"Service Order Number" = oo.ordernumber,
"Order Requested Due Date" = oo.order_requested_due_date,
"Order Last Updated" = oo.lastupdateddate,
"Order Creation Date" = oo."Order Creation Date",
"Order Submit Date" = oo."Order Submit Date",
"Order Supp Type" = oo.c.supptype,
"Order Status" = oo.order_status_desc,
"Phase Stage" = oo.phase_stage,
"Milestone" = oo.milestone,
"Milestone Description" = oo.milestone_desc,
"NASP ID" = oo.sub_nasp_id,
"GCH ID" = oo.gch_id,
"Work Order Numbers" = oo.work_order_no,
"Product Code" = oo.itemcode,
"Service Requested Due Date" = oo."Service Req Due Date",
"Customer Name" = oo.custlegalname,
"Account Number" = oo.account_number,
"VLE ID" = oo.vle_id,
"Currency Code" = oo.currency_cd,
"DUNs Number" = oo.duns_number,
"CLE VLE ID" = oo.cle_vle_id,
"Last Record Update" = oo."Last Upd",
"Order Completed Date" = oo."Order Completed Date",
"Order Cancelled Date" = oo."Order Cancelled Date",
"Service Last Modified Date" = oo."Service Last Modified Date",
"Service Effective Date" = oo."Service Effective Date"


WHERE open_order_all_test."Service Instance ID" = oo.instance_id
Tags (2)
1 REPLY

Re: Query Error 3802 Database does not exist, UPDATE TABLE From...

I found the issue, I accidentally had oo.c.supptype when it should have been oo.supptype, which caused it to think I was referring to oo as a database. Case closed