UNION QUERY PROBLEM.... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

UNION QUERY PROBLEM....

Author

Topic

krishnet
Starting Member

29 Posts

Posted - 2007-06-06 : 08:26:52

Hi,

I have the following tables

Table :Account

accountid int identity,(p.k.)
accountname varchar(20)not null

Table : MiscPay

miscid int identity,(p.k.)
accountid int not null, (f.k.)
storeid int not null,(f.k.)
paymentdt datetime not null,
payeename varchar(30) not null,
paymode varchar(20) not null,
bankname varchar(50),
chqdt datetime,
chqueno varchar(20),
amt numeric(10, 2) not null,
remarks varchar(50)not null

QUERY :

SELECT '1', '' AS PAYMODE, accountname AS NAME, '' AS payeename, '' AS paymode,'' AS storeid, '' AS amt,'' AS paymentdt
FROM ACCOUNT
WHERE accountid = 2
UNION
SELECT '2', '' AS PAYMODE, '' AS NAME, payeename, paymode,storeid,amt, paymentdt
FROM MiscPay
WHERE accountid = 2 and paymode ='cash' and storeid=1
ORDER BY 1, 2, 3

amt numeric(10, 2) not null,
paymentdt datetime not null,

Error converting data type varchar to numeric.

Column1 PAYMODE NAME
------- ------- ---------------------------------------

paymentdt
1/1/1900

I m getting the above error when i add the field amt which is a numeric field.I also get the wrong value for the payment dt as by default 1/1/1900 for the NAME which i dont want.Can u tell me how to write a numeric field & datetime field for the union query ? What's error in above statement..

Can anyone help me to write this union query...

What i want is the output as :

On basis of paymentmode as cash and cheque
On basis of accountname
Orderby paymentdt and amt.
I also want the summary total of the amt column

Please check where i m wrong and i have already mentioned my error..

Thanxs..

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-06 : 08:32:46

[code]
SELECT '1', '' AS PAYMODE, accountname AS NAME, '' AS payeename, '' AS paymode, NULL AS storeid, NULL AS amt, NULL AS paymentdt
FROM ACCOUNT
WHERE accountid = 2
UNION
SELECT '2', '' AS PAYMODE, '' AS NAME, payeename, paymode, storeid, amt, paymentdt
FROM MiscPay
WHERE accountid = 2 and paymode ='cash' and storeid=1
ORDER BY 1, 2, 3
[/code]

krishnet
Starting Member

29 Posts

Posted - 2007-06-06 : 08:53:10

khtan.....thanxs for ur help

I want the output to be union groupby as paymode where cash and credit are the two modes of payment.

paymode varchar(20) not null,

This are the extra details to be included if payment is thru cheque..
bankname varchar(50),
chqdt datetime,
chqueno varchar(20),

Can u tell me how to do it..in the above query only...
if i don't mention paymode ='cash' and take all the cash and credit data and then group them in cash and credit..

Is it possible..

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-06 : 09:14:06

can you post your table structure, some sample data and the result that you want ?

krishnet
Starting Member

29 Posts

Posted - 2007-06-06 : 09:35:56

The table structure is as above only...
I want to make changes in above union query only using groupby for paymode where paymode is equal to cash and credit transactions

What i want is :
ACCOUNTNAME PAYMODE PAYDATE PAYEENAME AMT

Expenses Cash
11/11/2006 A 300.00
12/12/2006 B 400.00
------
700.00

ACCOUNTNAME PAYMODE PAYDATE CHQNO BANKNAME PAYEENAME AMT
Expense Credit
1/1/2007 56678 SBI A 850.00
--------
850.00
thanxs...

krishnet
Starting Member

29 Posts

Posted - 2007-06-08 : 10:45:28

khtan...or any other member
CAN HELP ME TO WRITE THE ABOVE QUERY AS TO GET THE OUTPUT SHOWN ABOVE..

The groupby with cash and credit of paymode field...with the union query

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources