Link to home
Start Free TrialLog in
Avatar of chandan m
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].[VendorFiles] as a
  join [CUBS].[dbo].[Client] as d
  on d.PK_Client=CONVERT(varchar(250),a.client)
Union all
  Select [Amount],[CDN #] as cdn#,[Card_No] as CC#,[Card_Type] as CCType
  from [PayPal_staging].[dbo].[VirtualFiles] as b
  join [CUBS].[dbo].[Client] as d
  on d.PK_Client=CONVERT(varchar(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].[IVRFiles] as c
  join [CUBS].[dbo].[Client] as d
  on d.PK_Client=CONVERT(varchar(250),c.[SV03_Clientnbr])
  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....
Avatar of Jeff Darling
Jeff Darling
Flag of United States of America image

You might want to do a simple test to see if the clients are in all the tables mentioned.

Example

Create Table #client
(
 PK_Client  varchar(20),
 SetupDate  datetime,
 LogonID varchar(10)
)

Create Table #clientTEST
(
 PK_Client  varchar(20),
)


INSERT INTO #client (PK_Client,SetupDate,LogonID) values ('SWMC600','2017-02-03','acs')
INSERT INTO #client (PK_Client,SetupDate,LogonID) values ('SQMC203','2017-05-01','acs2')
INSERT INTO #client (PK_Client,SetupDate,LogonID) values ('SQMC600','2017-05-01','acs2')
INSERT INTO #client (PK_Client,SetupDate,LogonID) values ('SBMC203','2017-05-01','acs2')
INSERT INTO #client (PK_Client,SetupDate,LogonID) values ('SBMC600','2017-05-01','acs2')
INSERT INTO #client (PK_Client,SetupDate,LogonID) values ('PROV203','2017-05-01','acs2')
INSERT INTO #client (PK_Client,SetupDate,LogonID) values ('PROV600','2017-04-01','acs2')
INSERT INTO #client (PK_Client,SetupDate,LogonID) values ('SWRC203','2017-04-01','acs2')
INSERT INTO #client (PK_Client,SetupDate,LogonID) values ('SWRC600','2017-04-01','acs2')
INSERT INTO #client (PK_Client,SetupDate,LogonID) values ('SMCC203','2017-04-01','acs2')
INSERT INTO #client (PK_Client,SetupDate,LogonID) values ('SWIS203','2017-04-01','acs2')
INSERT INTO #client (PK_Client,SetupDate,LogonID) values ('SWIS600','2017-04-01','acs2')

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 what is missing
select G1.PK_CLIENT,G2.PK_CLIENT FROM #clientTEST G1
left join #client G2 on G1.PK_CLIENT = G2.PK_Client
WHERE isnull(G2.PK_CLIENT,'')='' 

Open in new window

If you run the above code, you will see that one of the clients is missing from the client table.

User generated image
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.

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,'')=''

Open in new window


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') 

Open in new window

Avatar of chandan m
chandan m

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
what is the type of  the VendorFiles client field?
its in Text
ASKER CERTIFIED SOLUTION
Avatar of Jeff Darling
Jeff Darling
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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].[VendorFiles] as a
   join [CUBS].[dbo].[Client] as d
  on d.PK_Client=CONVERT(varchar(250),a.client)
  where  PK_Client in  ('SWMC203','SWMC600','SQMC203','SQMC600','SBMC203','SBMC600',
    'PROV203','PROV600','SWRC203','SWRC600','SMCC203','SMCC600',
    'SWIS203','SWIS600')
Union all
  Select [Amount],[CDN #] as cdn#,[Card_No] as CC#,[Card_Type] as CCType
  from [PayPal_staging].[dbo].[VirtualFiles] as b
   join [CUBS].[dbo].[Client] as d
  on d.PK_Client=CONVERT(varchar(250),b.[Client_Id])
  where  PK_Client in  ('SWMC203','SWMC600','SQMC203','SQMC600','SBMC203','SBMC600',
    'PROV203','PROV600','SWRC203','SWRC600','SMCC203','SMCC600',
    'SWIS203','SWIS600')
  union all
  select [SV10_Amt] as Amount,[Cdr_Id] as cdn#,[SV07_Last4] as CC#,[CardType] as CCType
  from [PayPal_staging].[dbo].[IVRFiles] as c
   join [CUBS].[dbo].[Client] as d
  on d.PK_Client=CONVERT(varchar(250),c.[SV03_Clientnbr])
  where  PK_Client in  ('SWMC203','SWMC600','SQMC203','SQMC600','SBMC203','SBMC600',
    'PROV203','PROV600','SWRC203','SWRC600','SMCC203','SMCC600',
    'SWIS203','SWIS600')