quote:

---

Originally posted by Fifah

I've filtered my data using CTE but I was told that CTE data can only be retrieved once after the query. I need to manipulate the data further and was told to create a temp table.
Here is the full scenarios of what to be achieved.

Inner join from 2 tables:
select a.ref_caseid, b.court, a.annotation, a.src_caseid, c.shepardsflag
from cba_annot a inner join cbm_case b on a.ref_caseid = b.caseid
inner join cbm_case c on a.src_caseid = c.caseid
where a.src_caseid = '17338'

Output:
ref_caseid src_caseid annotation court
17334 17338 Refd high court
17600 17338 Foll federal court
18271 17338 Foll federal court
43220 17338 Not Foll supreme court

The aim is to get data on 'federal court' ONLY if they exist. If 'federal court' NOT exist, then it should output other court (from example above: High Court & Supreme court). Below is the CTE to get the desired data.

;with cte as (SELECTa.ref_caseid, b.court, a.annotation, a.src_caseid, RANK() OVER
(PARTITION BY a.src_CaseID ORDER BY CASE when b.court = 'MYFC' THEN 1 ELSE 2 END) AS Row
FROM cba_annot a INNER JOIN cbm_case b ON a.ref_caseid = b.caseid
WHERE src_caseid = 17338)
SELECT * FROM cte WHERE Row = 1 -- this will select rows for Federal Court and if there is no Federal Court row, it will select other courts.

Because I want to manipulate the data further based on the courts, I created a temp table; but it's prompting "Invalid syntax near ')'". Below is my script.

CREATE TABLE #temptable(
ref_caseid int,
src_caseid int,
court nvarchar(30),
annotation nvarchar(30))

INSERT INTO #temptable (ref_caseid, src_caseid, court, annotation)
SELECT ref_caseid, src_caseid, court, annotation
FROM (select a.ref_caseid, b.court, a.annotation, a.src_caseid, rank() over
(partition by a.src_CaseID order by case when b.court = 'MYFC' then 1 else 2 end) as Row
from cba_annot a inner join cbm_case b on a.ref_caseid = b.caseid
where src_caseid = 17338)as dt
where row = 1

Is this the right way of creating temp table? I also get the error saying the insert column must match the select column.
I've tried several other ways but nothing seem to work. Appreciate your feedback on this. Thanks.

---