chandan m
asked on
Query Task
Hi All,
Can anybody correct me with the below query where the query is going wrong ?
For the below Query i need to add where clause and get based on client names. When i execute the query i am able to view only first client data. And also i want the result to store in separate database.
select amount,[Account #]as cdn#,[CC ACCT]as CC#,[CardType] as CCType
from [PayPal_staging].[dbo].[Ve ndorFiles] as a
join [CUBS].[dbo].[Client] as d
on d.PK_Client=CONVERT(varcha r(250),a.c lient)
Union all
Select [Amount],[CDN #] as cdn#,[Card_No] as CC#,[Card_Type] as CCType
from [PayPal_staging].[dbo].[Vi rtualFiles ] as b
join [CUBS].[dbo].[Client] as d
on d.PK_Client=CONVERT(varcha r(250),b.[ Client_Id] )
union all
select [SV10_Amt] as Amount,[Cdr_Id] as cdn#,[SV07_Last4] as CC#,[CardType] as CCType
from [PayPal_staging].[dbo].[IV RFiles] as c
join [CUBS].[dbo].[Client] as d
on d.PK_Client=CONVERT(varcha r(250),c.[ SV03_Clien tnbr])
where PK_Client in ('SWMC600',' SQMC203',' SQMC600',' SBMC203',' SBMC600',
' PROV203',' PROV600',' SWRC203',' SWRC600',' SMCC203',' SMCC600',' SWIS203',' SWIS600')
Can anybody help me with the above query where the query is going wrong and how to get the result for all clients in Where clause and how to store the result in separate database ?
Please write me a complete query for the above task....
Can anybody correct me with the below query where the query is going wrong ?
For the below Query i need to add where clause and get based on client names. When i execute the query i am able to view only first client data. And also i want the result to store in separate database.
select amount,[Account #]as cdn#,[CC ACCT]as CC#,[CardType] as CCType
from [PayPal_staging].[dbo].[Ve
join [CUBS].[dbo].[Client] as d
on d.PK_Client=CONVERT(varcha
Union all
Select [Amount],[CDN #] as cdn#,[Card_No] as CC#,[Card_Type] as CCType
from [PayPal_staging].[dbo].[Vi
join [CUBS].[dbo].[Client] as d
on d.PK_Client=CONVERT(varcha
union all
select [SV10_Amt] as Amount,[Cdr_Id] as cdn#,[SV07_Last4] as CC#,[CardType] as CCType
from [PayPal_staging].[dbo].[IV
join [CUBS].[dbo].[Client] as d
on d.PK_Client=CONVERT(varcha
where PK_Client in ('SWMC600',' SQMC203',' SQMC600',' SBMC203',' SBMC600',
' PROV203',' PROV600',' SWRC203',' SWRC600',' SMCC203',' SMCC600',' SWIS203',' SWIS600')
Can anybody help me with the above query where the query is going wrong and how to get the result for all clients in Where clause and how to store the result in separate database ?
Please write me a complete query for the above task....
Start by testing each table to see if all clients are there.
See if this gives you back any rows. I'm assuming that the client table contains all the clients you listed in the where clause, but to be safe, test that first.
If the above is ok, then see if the cubs client table has all the clients.
See if this gives you back any rows. I'm assuming that the client table contains all the clients you listed in the where clause, but to be safe, test that first.
Create Table #clientTEST
(
PK_Client varchar(20),
)
INSERT INTO #clientTEST (PK_Client) values ('SWMC600')
INSERT INTO #clientTEST (PK_Client) values ('SQMC203')
INSERT INTO #clientTEST (PK_Client) values ('SQMC600')
INSERT INTO #clientTEST (PK_Client) values ('SBMC203')
INSERT INTO #clientTEST (PK_Client) values ('SBMC600')
INSERT INTO #clientTEST (PK_Client) values ('PROV203')
INSERT INTO #clientTEST (PK_Client) values ('PROV600')
INSERT INTO #clientTEST (PK_Client) values ('SWRC203')
INSERT INTO #clientTEST (PK_Client) values ('SWRC600')
INSERT INTO #clientTEST (PK_Client) values ('SMCC203')
INSERT INTO #clientTEST (PK_Client) values ('SMCC600')
INSERT INTO #clientTEST (PK_Client) values ('SWIS203')
INSERT INTO #clientTEST (PK_Client) values ('SWIS600')
-- Show me if any are missing
select G1.PK_CLIENT,d.PK_CLIENT FROM #clientTEST G1
left join [CUBS].[dbo].[Client] d on G1.PK_CLIENT = d.PK_Client
WHERE isnull(d.PK_CLIENT,'')=''
If the above is ok, then see if the cubs client table has all the clients.
select d.PK_Client,a.client from [CUBS].[dbo].[Client] as d
left join [PayPal_staging].[dbo].[VendorFiles] as a
on d.PK_Client=CONVERT(varchar(250),a.client)
Where isnull(a.client,'')=''
and
d.PK_Client in ('SWMC600',' SQMC203',' SQMC600',' SBMC203',' SBMC600',
' PROV203',' PROV600',' SWRC203',' SWRC600',' SMCC203',' SMCC600',' SWIS203',' SWIS600')
ASKER
Wheni run the above query i am getting below error for the line
Where isnull(a.client,'')=''
The data types text and varchar are incompatible in the equal to operator.
I converted the data type to VARCHAR but still the same
Where isnull(a.client,'')=''
The data types text and varchar are incompatible in the equal to operator.
I converted the data type to VARCHAR but still the same
what is the type of the VendorFiles client field?
ASKER
its in Text
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank You so much, Your discussion helped me in modifying the Query . Below is the actual Query.
After Testing single table came to know where the mistake was.
select amount,[Account #]as cdn#,[CC ACCT]as CC#,[CardType] as CCType
from [PayPal_staging].[dbo].[Ve ndorFiles] as a
join [CUBS].[dbo].[Client] as d
on d.PK_Client=CONVERT(varcha r(250),a.c lient)
where PK_Client in ('SWMC203','SWMC600','SQMC 203','SQMC 600','SBMC 203','SBMC 600',
'PROV203','PROV600','SWRC2 03','SWRC6 00','SMCC2 03','SMCC6 00',
'SWIS203','SWIS600')
Union all
Select [Amount],[CDN #] as cdn#,[Card_No] as CC#,[Card_Type] as CCType
from [PayPal_staging].[dbo].[Vi rtualFiles ] as b
join [CUBS].[dbo].[Client] as d
on d.PK_Client=CONVERT(varcha r(250),b.[ Client_Id] )
where PK_Client in ('SWMC203','SWMC600','SQMC 203','SQMC 600','SBMC 203','SBMC 600',
'PROV203','PROV600','SWRC2 03','SWRC6 00','SMCC2 03','SMCC6 00',
'SWIS203','SWIS600')
union all
select [SV10_Amt] as Amount,[Cdr_Id] as cdn#,[SV07_Last4] as CC#,[CardType] as CCType
from [PayPal_staging].[dbo].[IV RFiles] as c
join [CUBS].[dbo].[Client] as d
on d.PK_Client=CONVERT(varcha r(250),c.[ SV03_Clien tnbr])
where PK_Client in ('SWMC203','SWMC600','SQMC 203','SQMC 600','SBMC 203','SBMC 600',
'PROV203','PROV600','SWRC2 03','SWRC6 00','SMCC2 03','SMCC6 00',
'SWIS203','SWIS600')
After Testing single table came to know where the mistake was.
select amount,[Account #]as cdn#,[CC ACCT]as CC#,[CardType] as CCType
from [PayPal_staging].[dbo].[Ve
join [CUBS].[dbo].[Client] as d
on d.PK_Client=CONVERT(varcha
where PK_Client in ('SWMC203','SWMC600','SQMC
'PROV203','PROV600','SWRC2
'SWIS203','SWIS600')
Union all
Select [Amount],[CDN #] as cdn#,[Card_No] as CC#,[Card_Type] as CCType
from [PayPal_staging].[dbo].[Vi
join [CUBS].[dbo].[Client] as d
on d.PK_Client=CONVERT(varcha
where PK_Client in ('SWMC203','SWMC600','SQMC
'PROV203','PROV600','SWRC2
'SWIS203','SWIS600')
union all
select [SV10_Amt] as Amount,[Cdr_Id] as cdn#,[SV07_Last4] as CC#,[CardType] as CCType
from [PayPal_staging].[dbo].[IV
join [CUBS].[dbo].[Client] as d
on d.PK_Client=CONVERT(varcha
where PK_Client in ('SWMC203','SWMC600','SQMC
'PROV203','PROV600','SWRC2
'SWIS203','SWIS600')
Example
Open in new window