question

ikramonly avatar image
ikramonly 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
sql serverselectunionunion-all
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
@SQLShark avatar image
@SQLShark answered
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.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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
1 Like 1 ·
yeah man, I know where the error is. But i didn't wanted to change the number of columns, Well Its done now
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.