I need to create one table from 3, for now, and I followed the instructions on a couple of websites and in my books but as I thought I had finished, an error appeared at the beginning (. Msg 102, Level 15, State 1, Incorrect syntax near '('. I tried everything I can think of but I don't see the problem. It's a little long...but here's the code: I had to remove PHI so the Where clause has no values as criteria here.
Create Table OPVisits
AS (Select OrganizationMRN, LastNM, FirstNM, VisitID
FROM Patient.PatientInternalOrganizationBASE, Person.PersonBASE, Encounter.VisitBASE
WHERE OrganizationMRN in THERE ARE ABOUT 25 SEPARATE VALUES HERE, ALL IN SINGLE QUOTES...and Encounter.VisitBASE.VisitID is not null);
It would be better that you posted the exact code, otherwise we are just guessing. Substitute "xxx" for anything confidential, but be careful not to alter the syntax at all
Just in case this is the problem your IN clause should look like this
WHERE OrganizationMRN IN ('value1', 'value2', ... 'value25')
and Encounter.VisitBASE.VisitID is not null);
Better that you reply in the forum - other folk can join in then, if they have something to add, or see something that I have done wrong / could be done better.
Is this for Azure Data Warehouse? Otherwise I don't believe "CREATE TABLE ... AS" is valid in SQL Server. In CTAS, there are no open and close parens, just the SELECT statement.
Finally, you should do explicit JOINs rather than just list tables in the FROM clause [you may need OUTER joins rather than INNER joins]. You are going to get a potentially very large CROSS JOIN from the query you posted.
Putting it all together, something like this then:
CREATE TABLE OPVisits
AS
/* no open paren needed here */
SELECT OrganizationMRN, LastNM, FirstNM, VisitID
FROM Patient.PatientInternalOrganizationBASE POB
INNER JOIN Person.PersonBASE PB ON
PB.whatever = POB.whatever /*AND ...*/
INNER JOIN Encounter.VisitBASE VB ON
VB.whatever = POB.whatever /*AND ...*/
WHERE POB.OrganizationMRN IN ('value', ...)
AND VB.VisitID IS NOT NULL;
Ermm ... also perhaps worth pointing out that if this is MS SQL, rather than Azure, then the alternative SELECT ... INTO ... syntax might help
SELECT OrganizationMRN, LastNM, FirstNM, VisitID
INTO OPVisits
FROM ...
WHERE ...
Dunno how that compares with CTAS - presumably CTAS can't "steal" PKey, Index and other DDL stuff from the original tables / columns, and more than SELECT ... INTO does?