asked
Select by union with different number of columns
I have two tables as given CashRegister SalesOrder Date PaymentId CustId VenId Debit Credit Date SaleOrderId CustId Debit Credit 2/2/12 Pay_1 Cus_1 444 3/2/2012 So_1 Cus_1 666 4/2/12 Pay_2 Ven_1 555 From These two tables, I have to generate a report like: Joural Report Date TrId AccountId Debit Credit 2/2/12 Pay_1 Cus_1 444 3/3/12 So_1 Cus_1 666 4/2/12 Pay_1 Ven_1 555 Here, I have tried this query select * from( select Date,PaymentId TrId,CustId AccountId,VenId, AccountId,Debit,Credit from CashRegister union select Date,SaleOrderId TrId,CustId AccountId,Debit,Credit) t order by Date But it gives an error that " In Union number of expressions should be equal for target list. How to solve this, please help me i need it badly
Hi this is one of those issues quickly solved by reading the error message. "In Union number of expressions should be equal for target list" This is saying that the 2 queries you're trying to union need to have the same amount of columns. You have in Q 1 1. Date 2. PaymentId TrId 3. CustId AccountId 4. VenId 5. AccountId 6. Debit 7. Credit Q 2: 1. Date 3. SaleOrderId TrId 4. CustId AccountId 5. Debit 6. Credit The number of columns in Q1 Q2.
you can "fake" the columns if you want (just in case you added "real" columns and would like an alternative): select col2, col3, col4 from table1 union select col1, col3, col4, col5 from table2 becomes select convert(col1_datatype, null) as col1, col2, col3, col4, convert(col5_datatype, null) as col5 from table1 union select col1, null as col2, col3, col4, col5 from table2
15 People are following this question.
