CR XI - UNION ALL ERROR

[CRXIuser2005]

I'm receiving the following error when creating a union all command.

FAILED TO RETRIEVE DATA FROM DB. SYNTAX ERROR CONVERTING THE VARCHAR VALUE '[LPO' TO A COLUMN OF DATA TYPE INT.

Any ideas?


Thank you.

MV

"A man is only as happy, as he makes up his mind to be....

 

[synapsevampire]

You are likely using 2 different datatypes in the different selects and the db can't perform the conversion on the fly, you may have to issue a cast(TABLE.FIELD AS int) or whatever your database supports.

The question is about sql, yet you don't share the database nor sql issued, which limits suggestions.

-k

 

[CRXIuser2005]

Here is the logic... after this data element..."BKCYACCT"."Client Type", I added the next data element, but it is only in 2 tables and so I had to "0" fill the others...I get the error....any help is appreciated


SELECT 1 as whichtable,
"B1_WIP_CosDetail"."Matter_ID",
"B1_WIP_CosDetail"."WIP_Cost_Accounting_Display",
"B1_WIP_CosDetail"."WIP_Cost_Accounting_Display",
"B1_WIP_CosDetail"."WIP_Cost_Dollar_Value",
"MatterLookUp"."Client_ID",
"MatterLookUp"."Matter_ID",
"MatterLookUp"."Client_Name",
"MatterLookUp"."Date_Opened",
"BKCYACCT"."Acct File Number",
"BKCYACCT"."Unit",
"BKCYACCT"."Chapter",
"BKCYACCT"."File Open Date",
"BKCYACCT"."FileCopiedDate",
"BKCYACCT"."System Closed Date",
"BKCYACCT"."Earliest OCC Date",
"BKCYACCT"."Client Category",
"BKCYACCT"."Client Type",
"B1_WIP_CosDetail"."Cost_Code___Description",
"B1_WIP_CosDetail"."WIP_Cost_Activity_Code",
"B1_WIP_CosDetail"."Units",
0
FROM ("OMEGA"."dbo"."B1_WIP_CosDetail" "B1_WIP_CosDetail" LEFT OUTER JOIN "OMEGA"."dbo"."MatterLookUp"
"MatterLookUp" ON "B1_WIP_CosDetail"."Matter_ID"="MatterLookUp"."Matter_ID")
LEFT OUTER JOIN "OMEGA"."dbo"."BKCYACCT" "BKCYACCT" ON "B1_WIP_CosDetail"."Matter_ID"="BKCYACCT"."Acct File Number"

Union All
SELECT 2 as whichtable,
"B3_Billed_WIP_CosDet"."Matter_ID",
"B3_Billed_WIP_CosDet"."WIP_Cost_Accounting_Display",
"B3_Billed_WIP_CosDet"."Bill_Date_Display",
"B3_Billed_WIP_CosDet"."WIP_Cost_Dollar_Value",
"MatterLookUp"."Client_ID",
"MatterLookUp"."Matter_ID",
"MatterLookUp"."Client_Name",
"MatterLookUp"."Date_Opened",
"BKCYACCT"."Acct File Number",
"BKCYACCT"."Unit",
"BKCYACCT"."Chapter",
"BKCYACCT"."File Open Date",
"BKCYACCT"."FileCopiedDate",
"BKCYACCT"."System Closed Date",
"BKCYACCT"."Earliest OCC Date",
"BKCYACCT"."Client Category",
"BKCYACCT"."Client Type",
"B3_Billed_WIP_CosDet"."Cost_Code___Description",
"B3_Billed_WIP_CosDet"."WIP_Cost_Task_Code",
0,
0
FROM ("OMEGA"."dbo"."B3_Billed_WIP_CosDet" "B3_Billed_WIP_CosDet" LEFT OUTER JOIN "OMEGA"."dbo"."MatterLookUp"
"MatterLookUp" ON "B3_Billed_WIP_CosDet"."Matter_ID"="MatterLookUp"."Matter_ID")
LEFT OUTER JOIN "OMEGA"."dbo"."BKCYACCT" "BKCYACCT" ON "B3_Billed_WIP_CosDet"."Matter_ID"="BKCYACCT"."Acct File Number"

Union All
SELECT 3 as whichtable,
"B5_WIPEdit_CosEdit"."Matter_ID",
"B5_WIPEdit_CosEdit"."WIP_Cost_Accounting_Display",
"B5_WIPEdit_CosEdit"."WIP_Cost_Accounting_Display",
"B5_WIPEdit_CosEdit"."WIP_Cost_Dollar_Value_Change",
"MatterLookUp"."Client_ID",
"MatterLookUp"."Matter_ID",
"MatterLookUp"."Client_Name",
"MatterLookUp"."Date_Opened",
"BKCYACCT"."Acct File Number",
"BKCYACCT"."Unit",
"BKCYACCT"."Chapter",
"BKCYACCT"."File Open Date",
"BKCYACCT"."FileCopiedDate",
"BKCYACCT"."System Closed Date",
"BKCYACCT"."Earliest OCC Date",
"BKCYACCT"."Client Category",
"BKCYACCT"."Client Type",
0,
"B5_WIPEdit_CosEdit"."WIP_Cost_Activity_Code",
0,
0
FROM ("OMEGA"."dbo"."B5_WIPEdit_CosEdit" "B5_WIPEdit_CosEdit" LEFT OUTER JOIN "OMEGA"."dbo"."MatterLookUp"
"MatterLookUp" ON "B5_WIPEdit_CosEdit"."Matter_ID"="MatterLookUp"."Matter_ID")
LEFT OUTER JOIN "OMEGA"."dbo"."BKCYACCT" "BKCYACCT" ON "B5_WIPEdit_CosEdit"."Matter_ID"="BKCYACCT"."Acct File Number"

Union All
SELECT 4 as whichtable,
"B1_WIP_FeeDetail"."Matter_ID",
"B1_WIP_FeeDetail"."WIP_Fee_Accounting_D_Display",
"B1_WIP_FeeDetail"."WIP_Fee_Accounting_D_Display",
"B1_WIP_FeeDetail"."WIP_Fee_Dollar_Value",
"MatterLookUp"."Client_ID",
"MatterLookUp"."Matter_ID",
"MatterLookUp"."Client_Name",
"MatterLookUp"."Date_Opened",
"BKCYACCT"."Acct File Number",
"BKCYACCT"."Unit",
"BKCYACCT"."Chapter",
"BKCYACCT"."File Open Date",
"BKCYACCT"."FileCopiedDate",
"BKCYACCT"."System Closed Date",
"BKCYACCT"."Earliest OCC Date",
"BKCYACCT"."Client Category",
"BKCYACCT"."Client Type",
0,
"B1_WIP_FeeDetail"."WIP_Fee_Activity_Code",
"B1_WIP_FeeDetail"."Hours_Worked",
0
FROM ("OMEGA"."dbo"."B1_WIP_FeeDetail" "B1_WIP_FeeDetail" LEFT OUTER JOIN "OMEGA"."dbo"."MatterLookUp"
"MatterLookUp" ON "B1_WIP_FeeDetail"."Matter_ID"="MatterLookUp"."Matter_ID")
LEFT OUTER JOIN "OMEGA"."dbo"."BKCYACCT" "BKCYACCT" ON "B1_WIP_FeeDetail"."Matter_ID"="BKCYACCT"."Acct File Number"

Union All
SELECT 5 as whichtable,
"B3_Billed_WIP_FeeDet"."Matter_ID",
"B3_Billed_WIP_FeeDet"."WIP_Fee_Accounting_D_Display",
"B3_Billed_WIP_FeeDet"."WIP_Fee_Accounting_D_Display",
"B3_Billed_WIP_FeeDet"."WIP_Fee_Dollar_Value",
"MatterLookUp"."Client_ID",
"MatterLookUp"."Matter_ID",
"MatterLookUp"."Client_Name",
"MatterLookUp"."Date_Opened",
"BKCYACCT"."Acct File Number",
"BKCYACCT"."Unit",
"BKCYACCT"."Chapter",
"BKCYACCT"."File Open Date",
"BKCYACCT"."FileCopiedDate",
"BKCYACCT"."System Closed Date",
"BKCYACCT"."Earliest OCC Date",
"BKCYACCT"."Client Category",
"BKCYACCT"."Client Type",
0,
"B3_Billed_WIP_FeeDet"."WIP_Fee_Task_Code",
"B3_Billed_WIP_FeeDet"."Hours_Worked",
"B3_Billed_WIP_FeeDet"."Hourly_Rate"
FROM ("OMEGA"."dbo"."B3_Billed_WIP_FeeDet" "B3_Billed_WIP_FeeDet" LEFT OUTER JOIN "OMEGA"."dbo"."MatterLookUp"
"MatterLookUp" ON "B3_Billed_WIP_FeeDet"."Matter_ID"="MatterLookUp"."Matter_ID")
LEFT OUTER JOIN "OMEGA"."dbo"."BKCYACCT" "BKCYACCT" ON "B3_Billed_WIP_FeeDet"."Matter_ID"="BKCYACCT"."Acct File Number"

Union All
SELECT 6 as whichtable,
"B5_WIPEdit_FeeEdit"."Matter_ID",
"B5_WIPEdit_FeeEdit"."WIP_Fee_Accounting_D_Display",
"B5_WIPEdit_FeeEdit"."WIP_Fee_Accounting_D_Display",
"B5_WIPEdit_FeeEdit"."WIP_Fee_Dollar_Value_Change",
"MatterLookUp"."Client_ID",
"MatterLookUp"."Matter_ID",
"MatterLookUp"."Client_Name",
"MatterLookUp"."Date_Opened",
"BKCYACCT"."Acct File Number",
"BKCYACCT"."Unit",
"BKCYACCT"."Chapter",
"BKCYACCT"."File Open Date",
"BKCYACCT"."FileCopiedDate",
"BKCYACCT"."System Closed Date",
"BKCYACCT"."Earliest OCC Date",
"BKCYACCT"."Client Category",
"BKCYACCT"."Client Type",
0,
"B5_WIPEdit_FeeEdit"."WIP_Fee_Activity_Code",
"B5_WIPEdit_FeeEdit"."Hours_Worked_Change",
"B5_WIPEdit_FeeEdit"."Hourly_Rate"
FROM ("OMEGA"."dbo"."B5_WIPEdit_FeeEdit" "B5_WIPEdit_FeeEdit" LEFT OUTER JOIN "OMEGA"."dbo"."MatterLookUp"
"MatterLookUp" ON "B5_WIPEdit_FeeEdit"."Matter_ID"="MatterLookUp"."Matter_ID")
LEFT OUTER JOIN "OMEGA"."dbo"."BKCYACCT" "BKCYACCT" ON "B5_WIPEdit_FeeEdit"."Matter_ID"="BKCYACCT"."Acct File Number"














Thank you.

MV

"A man is only as happy, as he makes up his mind to be....

 

[lbass]

You can't use a zero whenever there is a missing field--the datatype must match the datatype of the field that is in the same sequential position in the first select. If it is a string, you could use "0". If it is a number, then 0 is okay.

-LB

 

[CRXIuser2005]

Gotcha thanks

Thank you.

MV

"A man is only as happy, as he makes up his mind to be....

 

[lbass]

Sorry, let me correct that. They have to be single quotes: '0'

-LB

 

[CRXIuser2005]

Thx LB... It DID error out, but your second reply helped..


thanks as always for your expertise


Thank you.

MV

"A man is only as happy, as he makes up his mind to be....

 

[IdoMillet]

Some DBMSs allow you to specify Null, like this:
..."BKCYACCT"."Client Type", Null, "B3_Billed_WIP_FeeDet"."WIP_Fee_Task_Code",...

hth,
- Ido


Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com