Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

CR XI - UNION ALL ERROR

Status
Not open for further replies.

CRXIuser2005

Programmer
Sep 23, 2005
135
US
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....
 
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
 

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....
 
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
 
Gotcha thanks

Thank you.

MV

"A man is only as happy, as he makes up his mind to be....
 
Sorry, let me correct that. They have to be single quotes: '0'

-LB
 
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....
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top