Check my code please? 1

[bdjb]

I have a make table query, where I get the error "the command did not produce any data, and did not create any rows." The Select portion works fine by itself. What am I missing? It is being run against an Advantage DB


Create table Title ("EntityID" CHAR(24), "EntityRole" CHAR(16), "Office" CHAR(50), "Borrower" CHAR(64), "Client_Name" CHAR(64), "Client_Number" CHAR(24), "FieldNum" INTEGER, "EntityRole_1" CHAR(16), "ClosedFlag" INTEGER, "TheStatus" CHAR(16), "AssocNum" INTEGER, "Dates" MEMO);INSERT INTO Title(EntityID, EntityRole, Office, Borrower, Client_Name, Client_Number, FieldNum, EntityRole_1, ClosedFlag, TheStatus, AssocNum, Dates)SELECT "ENTITIES_FC"."EntityID", "ENTITIES_FC"."EntityRole", "OFFICE"."FirstName", "ENTITIES_FC"."RefName", "CLIENT"."RefName", "CLIENT"."EntityID", "EQANSWER_FC"."FieldNum", "EQANSWER_FC"."EntityRole", "ENTITIES_FC"."ClosedFlag", "ENTITIES_FC"."TheStatus", "ERELATED"."AssocNum", "EQANSWER_FC"."AnswerText"FROM ((("ENTITIES" "ENTITIES_FC" INNER JOIN "ERELATED" "ERELATED" ON "ENTITIES_FC"."EntityNum"="ERELATED"."EntityNum") INNER JOIN "ENTITIES" "OFFICE" ON "ENTITIES_FC"."Party0Num"="OFFICE"."EntityNum") INNER JOIN "ENTITIES" "CLIENT" ON "ENTITIES_FC"."Party3Num"="CLIENT"."EntityNum") INNER JOIN "EQANSWER" "EQANSWER_FC" ON "ENTITIES_FC"."EntityNum"="EQANSWER_FC"."EntityNum"WHERE "EQANSWER_FC"."EntityRole"='FORECLOSURE' AND "ENTITIES_FC"."EntityRole" LIKE 'FC_%' AND "ENTITIES_FC"."ClosedFlag"=0 AND "Entities_FC"."TheStatus" Not Like 'dFC_C%' AND "Entities_FC"."TheStatus" Not Like 'bFC_C%' AND "Entities_FC"."TheStatus" Not Like 'dbFC_C%' AND "EQANSWER_FC"."FieldNum" In (1, 4, 5);

 

[Gruuuu]

without seeing the datatypes of columns in the ENTITIES table, there's not much I can suggest in ways of ensuring data integrity.
However, we can fix some syntax

INSERT INTO Title(
EntityID, 
EntityRole, 
Office, 
Borrower, 
Client_Name, 
Client_Number, 
FieldNum, 
EntityRole_1, 
ClosedFlag, 
TheStatus, 
AssocNum, 
Dates)
SELECT "ENTITIES_FC"."EntityID", 
"ENTITIES_FC"."EntityRole", 
"OFFICE"."FirstName", 
"ENTITIES_FC"."RefName", 
"CLIENT"."RefName", 
"CLIENT"."EntityID", 
"EQANSWER_FC"."FieldNum",         
"EQANSWER_FC"."EntityRole", 
"ENTITIES_FC"."ClosedFlag", 
"ENTITIES_FC"."TheStatus", 
"ERELATED"."AssocNum", 
"EQANSWER_FC"."AnswerText" 
FROM   [red][b][s]((([/s][/b][/red]
"ENTITIES" "ENTITIES_FC" 
INNER JOIN "ERELATED" "ERELATED" ON [red][b]([/b][/red]"ENTITIES_FC"."EntityNum"="ERELATED"."EntityNum") 
INNER JOIN "ENTITIES" "OFFICE" ON [red][b]([/b][/red]"ENTITIES_FC"."Party0Num"="OFFICE"."EntityNum")        
INNER JOIN "ENTITIES" "CLIENT" ON [red][b]([/b][/red]"ENTITIES_FC"."Party3Num"="CLIENT"."EntityNum") 
INNER JOIN "EQANSWER" "EQANSWER_FC" ON [red][b]([/b][/red]"ENTITIES_FC"."EntityNum"="EQANSWER_FC"."EntityNum"[red][b])[/b][/red]
WHERE  "EQANSWER_FC"."EntityRole"='FORECLOSURE' 
  AND "ENTITIES_FC"."EntityRole" LIKE 'FC_%' 
  AND "ENTITIES_FC"."ClosedFlag"=0 
  AND "Entities_FC"."TheStatus" Not Like 'dFC_C%' 
  AND "Entities_FC"."TheStatus" Not Like 'bFC_C%' 
  AND "Entities_FC"."TheStatus" Not Like 'dbFC_C%' 
  AND "EQANSWER_FC"."FieldNum" In (1, 4, 5);

 

[bdjb]

Thank you very much, that fixed my problem. I'm just learning SQL, and tried to use the code WinSQL created.

 

[guelphdad]

sql is the language, not the specific database. are you posting in the correct forum? this is for the mysql database. if you are using another application, the solutions may have different syntax and thus you should post in that forum.