Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 218
  • Last Modified:

Convert Access query to SQL query

I copied the following query out of Access (I didn't write it) and I need to be able to run it in SQL, but I'm receiving error messages.  Here is what I have so far:

SELECT DISTINCTROW dbo.Balances.CurrentBalance, dbo.Balances.Arrears1Balance,
 dbo.Balances.Arrears2Balance, dbo.Balances.Arrears3Balance,
 dbo.Balances.Arrears4PlusBalance, dbo.Customer.CustomerAcctNumber,
 dbo.Customer.BillToName, dbo.Customer.BillToStreet1, dbo.Customer.BillToStreet2,
 dbo.Customer.BillToCity, dbo.Customer.BillToState, dbo.Customer.BillToZip,
 dbo.Customer.CreditLimit, dbo.Customer.SocialSecurityNumber, dbo.Customer.CustID,
 dbo.Customer.BillingGroupID, dbo.Customer.BillToCountry,
 IIf(Nz([CreditClass])="","*",[CreditClass]) AS CrClass, dbo.SalesPersonnel.SalesGroupID,
 dbo.Customer.InvoiceResponsible, dbo.BillingGroup.CompanyName
FROM ((Customer RIGHT JOIN dbo.Balances ON dbo.Customer.CustID = dbo.Balances.CustID)
LEFT JOIN dbo.BillingGroup ON dbo.Customer.BillingGroupID = dbo.BillingGroup.BillingGroupID)
LEFT JOIN dbo.SalesPersonnel ON dbo.Customer.SalesID = dbo.SalesPersonnel.SalesID
WHERE (((dbo.Customer.CustomerAcctNumber)<>"") AND ((dbo.Customer.InvoiceResponsible)=True));

These are the errors I'm receiving:

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '.'.
Server: Msg 1038, Level 15, State 1, Line 8
Cannot use empty object or column names. Use a single space if necessary.
Server: Msg 170, Level 15, State 1, Line 13
Line 13: Incorrect syntax near 'dbo'.
Server: Msg 1038, Level 15, State 1, Line 13
Cannot use empty object or column names. Use a single space if necessary.
0
lhedgecoth
Asked:
lhedgecoth
  • 5
  • 4
2 Solutions
 
rafranciscoCommented:
Try this:

SELECT DISTINCT dbo.Balances.CurrentBalance, dbo.Balances.Arrears1Balance,
 dbo.Balances.Arrears2Balance, dbo.Balances.Arrears3Balance,
 dbo.Balances.Arrears4PlusBalance, dbo.Customer.CustomerAcctNumber,
 dbo.Customer.BillToName, dbo.Customer.BillToStreet1, dbo.Customer.BillToStreet2,
 dbo.Customer.BillToCity, dbo.Customer.BillToState, dbo.Customer.BillToZip,
 dbo.Customer.CreditLimit, dbo.Customer.SocialSecurityNumber, dbo.Customer.CustID,
 dbo.Customer.BillingGroupID, dbo.Customer.BillToCountry,
 ISNULL([CreditClass], '*') AS CrClass,
 dbo.SalesPersonnel.SalesGroupID,
 dbo.Customer.InvoiceResponsible, dbo.BillingGroup.CompanyName
FROM ((Customer RIGHT JOIN dbo.Balances ON dbo.Customer.CustID = dbo.Balances.CustID)
LEFT JOIN dbo.BillingGroup ON dbo.Customer.BillingGroupID = dbo.BillingGroup.BillingGroupID)
LEFT JOIN dbo.SalesPersonnel ON dbo.Customer.SalesID = dbo.SalesPersonnel.SalesID
WHERE (((dbo.Customer.CustomerAcctNumber)<>'') AND ((dbo.Customer.InvoiceResponsible)=1));
0
 
lhedgecothAuthor Commented:
Almost!  Down to one error:
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value '*' to a column of data type smallint.
0
 
rafranciscoCommented:
What should be the value of CrClass?
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
lhedgecothAuthor Commented:
CrClass can be a value of 1, 2, 3, or 4 or null.  Is that what you needed to know?
0
 
rafranciscoCommented:
Try this:

SELECT DISTINCT dbo.Balances.CurrentBalance, dbo.Balances.Arrears1Balance,
 dbo.Balances.Arrears2Balance, dbo.Balances.Arrears3Balance,
 dbo.Balances.Arrears4PlusBalance, dbo.Customer.CustomerAcctNumber,
 dbo.Customer.BillToName, dbo.Customer.BillToStreet1, dbo.Customer.BillToStreet2,
 dbo.Customer.BillToCity, dbo.Customer.BillToState, dbo.Customer.BillToZip,
 dbo.Customer.CreditLimit, dbo.Customer.SocialSecurityNumber, dbo.Customer.CustID,
 dbo.Customer.BillingGroupID, dbo.Customer.BillToCountry,
[CreditClass] AS CrClass,
 dbo.SalesPersonnel.SalesGroupID,
 dbo.Customer.InvoiceResponsible, dbo.BillingGroup.CompanyName
FROM ((Customer RIGHT JOIN dbo.Balances ON dbo.Customer.CustID = dbo.Balances.CustID)
LEFT JOIN dbo.BillingGroup ON dbo.Customer.BillingGroupID = dbo.BillingGroup.BillingGroupID)
LEFT JOIN dbo.SalesPersonnel ON dbo.Customer.SalesID = dbo.SalesPersonnel.SalesID
WHERE (((dbo.Customer.CustomerAcctNumber)<>'') AND ((dbo.Customer.InvoiceResponsible)=1));
0
 
rafranciscoCommented:
If that works, just tell what this part should be doing?

IIf(Nz([CreditClass])="","*",[CreditClass])
0
 
lhedgecothAuthor Commented:
It works!  All I can tell you is that when I run it in Access, I get another field called CCDescription that goes off the [CreditClass] so if  creditclass = 1 then CCDescription = Good, if  creditclass = 2 then CCDescription = Very Good, if  creditclass = 3 then CCDescription = Bad, and if creditclass = 1 then CCDescription = Risk
0
 
rafranciscoCommented:
In SQL Server, this statement

IIf(Nz([CreditClass])="","*",[CreditClass])

becomes:

CASE WHEN ISNULL([CreditClass], '') = '' THEN '*' ELSE CAST([CreditClass] AS VARCHAR(2)) END
0
 
lhedgecothAuthor Commented:
You're really good!  Thanks for your help!
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now