Sql syntax in MS SQL

Question

0.00/5 (No votes)

See more:

Code below based on suggested solution does NOT run:

SQL

Select IIF(`Conversation Time` > 0,1,0) AS [Answered], B.StartDate, B.EndDate, A.`Begin Time`,
A.`End Time`, A.`Charged Duration`,A.`Call Charges`,A.`Call Expense`,A.`Account ID`,
A.`Agent ID`
FROM CDRecords.csv as A
INNER JOIN SearchCriteria.csv AS B
ON (A.`End Time` BETWEEN B.StartDate and B.EndDate)
LEFT JOIN AreaCodes.csv AS C
ON A.`Area Prefix` = C.AreaPrefix

It gives syntax error. Both the joins run ok separately so there is no syntax error in either of them, at least none that is reported when they run separately. I think it is a limitation in the SQL version in Excel(?) I know for sure they don't allow more than two tables in outer joins; perhaps the same is true of inner.

There are a ton of good ways to do it in robust SQL and the proposed solution is a good example of one and probably runs great on a "regular" SQL platform, but the MS Query that is running the SQL has a very limited subset of performance. You can't run joins on multiple tables (i.e. 3 or more tables are involved). Certainly what I am doing is not very complicated and something one would commonly want to do. I'm going to try a few more things and perhaps look as ADO since I'm already in VB maybe I can slice the records that are in the date range first and put it in a file then do the names match. In the meantime if anyone wants to keep hammering away at it in Excel's MS Query I am appreciating all the help.

What I have tried:

I tried the suggested solution it does not work, see above.

Posted 10-Apr-16 10:08am

Member 12405198

Updated 13-Apr-16 13:08pm

v10

Add a Solution

Comments

PIEBALDconsult 10-Apr-16 16:27pm

What error do you get? Use improve question to add context and detail.
And do you really want `End Time` in both expressions in the WHERE clause? Is there no `Start Time` ?

Jörgen Andersson 11-Apr-16 4:51am

You're mixing implicit joins with ANSI joins. While technically possible (unless it's MySQL IIRC), you're ending up in a syntactical quagmire.
Decide for one join syntax. I'd recommend ANSI for clarity.

Richard Deeming 12-Apr-16 10:43am

Your second query is mixing back-ticks (`) and single quotes (') - is that a typo, or is that actually in your query?

Member 12405198 12-Apr-16 10:51am

Typo - not running those join queries I replaced it with newer query at end. I need to put the code for the new query in here where I only use wheres.

Richard Deeming 12-Apr-16 12:13pm

If the query in your question isn't the query you're running, then you need to update your question. We can't help you if we can't see the code/query!

Member 12405198 13-Apr-16 10:15am

Updated... hope it is clearer now.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Answer 1 · 2016-04-13T04:20:00

Solution 2

Your query is using an implicit INNER JOIN, which is why you're not getting the records from A which don't have matching records in B and C.

Change your query back to using LEFT JOINs:

SQL

SELECT
    A.SomeFields, 
    B.SomeDate,
    C.Name
FROM 
    aaa.csv AS A
    INNER JOIN bbb.csv AS B
    ON A.SomeDate BETWEEN B.Date1 And B.Date2
    LEFT JOIN ccc.csv AS C
    ON A.ID = C.ID
;

Posted 13-Apr-16 4:20am

Richard Deeming

Updated 13-Apr-16 4:21am

v2

Comments

Member 12405198 13-Apr-16 11:27am

ok, these joins work separately but I am not able to have them together. Are you using SQL within Excel? (I guess this is MS SQL or a limited subset of it).

Richard Deeming 13-Apr-16 11:30am

According to this[^], you should be able to have several inner-joins and one outer-join in a single query.

If you can't get that to work, you might have to create a view in SQL and query that instead.

Member 12405198 13-Apr-16 12:41pm

I don't think VIEW is supported in this apparently limited subset of SQL. It says "operation not supported for this kind of object" - remember I'm running SQL from MS Query. Too bad because that would be a good solution.

Member 12405198 13-Apr-16 11:53am

They are only using two tables in the example though. I think the limitation is on number of tables (?) using joins... I will look at VIEW...

Member 12405198 13-Apr-16 13:13pm

There might be a way to bracket the JOINS i.e. ...FROM((Join) (Join)) but I haven't got this to work.