Solved

Access/SQL IIF '>' Syntax differences

Posted on 2004-09-30
16
820 Views
Last Modified: 2008-02-01
I have the following SQL query that when i run it in access it works perfectly.
but when i use this same syntax in an oleadapter in my .net app i get the following error.

"Error in list of function arguments: '>' not recognized.
Unable to parse query text."

Is there additional brackets or quotes that i'm missing?


SELECT IIf(A.ASSIGN_DT>A.EXP_DT,DateDiff('d',A.ASSIGN_DT,Now()),DateDiff('d',A.EXP_DT,Now())) AS AGE, A.CASE_ID, A.CASE_TYPE, A.INSURED, A.INS_DBA, A.ASSIGN_DT, A.EXP_DT, A.SENT_DT, A.REP_DUE_DT, A.CASE_READ, A.CON_CITY, A.INS_PHONE, B.STAT_COMMENTS AS Scheduled, B.STATUS_DESC AS Description
FROM [SELECT     dattbl_Case.CASE_ID, dattbl_Case.CASE_TYPE, dattbl_Case.INSURED, dattbl_Case.INS_DBA, dattbl_Case.ASSIGN_DT,
                                              dattbl_Case.REP_DUE_DT, dattbl_Case.EXP_DT, dattbl_Tracker.CASE_READ, dattbl_Tracker.SENT_DT, dattbl_Case.CON_CITY,
                                              dattbl_Case.INS_PHONE
                       FROM          dattbl_Case INNER JOIN
                                              dattbl_Tracker ON dattbl_Case.CASE_ID = dattbl_Tracker.CASE_ID
                       WHERE      (dattbl_Tracker.FOLDER = 'INBOX')]. AS A LEFT JOIN [SELECT     CASE_ID, STAT_CODE, STAT_COMMENTS, STAT_ID, STAT_NEXT_DT, STATUS_DESC
                            FROM          dattbl_Status
                            WHERE      (STAT_CODE = 78 OR
                                                   STAT_CODE = 79 OR
                                                   STAT_CODE = 80 OR
                                                   STAT_CODE = 81)]. AS B ON A.CASE_ID = B.CASE_ID;
0
Comment
Question by:DTRON04
  • 7
  • 4
  • 4
16 Comments
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12193804
Try this:

SELECT CASE WHEN A.ASSIGN_DT > A.EXP_DT THEN DateDiff('d',A.ASSIGN_DT,Now()) ELSE DateDiff('d',A.EXP_DT,Now()) END AS AGE,
 A.CASE_ID, A.CASE_TYPE, A.INSURED, A.INS_DBA, A.ASSIGN_DT, A.EXP_DT, A.SENT_DT, A.REP_DUE_DT, A.CASE_READ,
 A.CON_CITY, A.INS_PHONE, B.STAT_COMMENTS AS Scheduled, B.STATUS_DESC AS [Description]
FROM
(SELECT c.CASE_ID, c.CASE_TYPE, c.INSURED, c.INS_DBA, c.ASSIGN_DT,
 c.REP_DUE_DT, c.EXP_DT, t.CASE_READ, t.SENT_DT, c.CON_CITY,
 c.INS_PHONE
 FROM dattbl_Case c
 INNER JOIN dattbl_Tracker t ON c.CASE_ID = t.CASE_ID
 WHERE t.FOLDER = 'INBOX') AS A
LEFT JOIN (
 SELECT CASE_ID, STAT_CODE, STAT_COMMENTS, STAT_ID, STAT_NEXT_DT, STATUS_DESC
 FROM dattbl_Status
 WHERE STAT_CODE IN (78,79,80,81) AS B ON A.CASE_ID = B.CASE_ID
0
 
LVL 12

Expert Comment

by:monosodiumg
ID: 12193835
Those dots again!!!
Access sometimes lsplis in some extra full stops here and there when you look at the SQL .
E.g. in your last line the first dot should not be there (between $$$):
TAT_CODE = 81)]$$$.$$$ AS B ON A.CASE_ID = B.CASE_ID;

Also in this line:
WHERE      (dattbl_Tracker.FOLDER = 'INBOX')]$$$.$$$

That could be breaking it.
0
 
LVL 12

Expert Comment

by:monosodiumg
ID: 12193850
You also need to reaplce the [] round the subqueries with (). Another Access quirk.
0
 

Author Comment

by:DTRON04
ID: 12194028
yeah that query is what access did to my original syntax when i copy pasted it from the .Net query builder
that syntax actually worked in access as shown in the question.

Sorry here is what i am trying to feed the query builder in .net against the accessdb

SELECT CASE WHEN A.ASSIGN_DT > A.EXP_DT THEN DateDiff('d',A.ASSIGN_DT,Now()) ELSE DateDiff('d',A.EXP_DT,Now()) END AS AGE, A.CASE_ID, A.CASE_TYPE, A.INSURED, A.INS_DBA, A.ASSIGN_DT, A.EXP_DT, A.SENT_DT, A.REP_DUE_DT, A.CASE_READ, A.CON_CITY, A.INS_PHONE, B.STAT_COMMENTS AS Scheduled, B.STATUS_DESC AS Description
FROM         (SELECT     dattbl_Case.CASE_ID, dattbl_Case.CASE_TYPE, dattbl_Case.INSURED, dattbl_Case.INS_DBA, dattbl_Case.ASSIGN_DT,
                                              dattbl_Case.REP_DUE_DT, dattbl_Case.EXP_DT, dattbl_Tracker.CASE_READ, dattbl_Tracker.SENT_DT, dattbl_Case.CON_CITY,
                                              dattbl_Case.INS_PHONE
                       FROM          dattbl_Case INNER JOIN
                                              dattbl_Tracker ON dattbl_Case.CASE_ID = dattbl_Tracker.CASE_ID
                       WHERE      (dattbl_Tracker.FOLDER = 'INBOX')) A LEFT OUTER JOIN
                          (SELECT     CASE_ID, STAT_CODE, STAT_COMMENTS, STAT_ID, STAT_NEXT_DT, STATUS_DESC
                            FROM          dattbl_Status
                            WHERE      (STAT_CODE = 78 OR
                                                   STAT_CODE = 79 OR
                                                   STAT_CODE = 80 OR
                                                   STAT_CODE = 81)) B ON A.CASE_ID = B.CASE_ID

this is the error
Error in SELECT clause: expression near 'A'.
Missing FROM clause.
Error in SELECT clause: expression near ','.
Error in list of function arguments: ')' not recognized.
Error in list of function arguments: ',' not recognized.
Unable to parse query text.

Now i tried to feed it jd's recomendation and got this

Error in SELECT clause: expression near 'A'.
Missing FROM clause.
Error in SELECT clause: expression near ','.
Error in list of function arguments: ')' not recognized.
Error in list of function arguments: ',' not recognized.
Unable to parse query text.

this one is kicking my a**

0
 

Author Comment

by:DTRON04
ID: 12194057
here is the original without access doing what it does

SELECT     IIf(A.ASSIGN_DT > A.EXP_DT, DateDiff('d', A.ASSIGN_DT, Now()), DateDiff('d', A.EXP_DT, Now())), A.CASE_ID, A.CASE_TYPE, A.INSURED, A.INS_DBA,
                      A.ASSIGN_DT, A.EXP_DT, A.SENT_DT, A.REP_DUE_DT, A.CASE_READ, A.CON_CITY, A.INS_PHONE, B.STAT_COMMENTS AS Scheduled,
                      B.STATUS_DESC AS Description FROM  
      (SELECT     dattbl_Case.CASE_ID, dattbl_Case.CASE_TYPE, dattbl_Case.INSURED, dattbl_Case.INS_DBA, dattbl_Case.ASSIGN_DT,
                                              dattbl_Case.REP_DUE_DT, dattbl_Case.EXP_DT, dattbl_Tracker.CASE_READ, dattbl_Tracker.SENT_DT, dattbl_Case.CON_CITY,
                                              dattbl_Case.INS_PHONE
                       FROM          dattbl_Case INNER JOIN
                                              dattbl_Tracker ON dattbl_Case.CASE_ID = dattbl_Tracker.CASE_ID
                       WHERE      (dattbl_Tracker.FOLDER = 'INBOX')) A LEFT OUTER JOIN
                          (SELECT     CASE_ID, STAT_CODE, STAT_COMMENTS, STAT_ID, STAT_NEXT_DT, STATUS_DESC
                            FROM          dattbl_Status
                            WHERE      (STAT_CODE = 78 OR
                                                   STAT_CODE = 79 OR
                                                   STAT_CODE = 80 OR
                                                   STAT_CODE = 81)) B ON A.CASE_ID = B.CASE_ID

i get this error with this

Error in list of function arguments: '>' not recognized.
Unable to parse query text.
0
 

Author Comment

by:DTRON04
ID: 12194067
these guys are talking about what i am i think

http://p2p.wrox.com/topic.asp?TOPIC_ID=5912

0
 
LVL 12

Expert Comment

by:monosodiumg
ID: 12194136
Last version of Access I used was 2K. It didn't have a CASE statement if I remember correctly. Iif was a close as you got.
You need to find out what SQL language is required. Now()  is a VB function, not an SQL server function. YOu would need to use GetDate if this is (as  I suspect) TSQL.

Also the interval constants in sqL server are different:
'd' -> d (no quotes)

Description is a reserverd word I think . Wrap it up in []: [Description].
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 

Author Comment

by:DTRON04
ID: 12194266
i did as you recommended with the [description] and removing the 'd'

the issue has to be in the IIF statement
the query works fine without it and just a straight DATEDIFF

how can i tell if I am using TSQL.

Sorry so clueless i have never developed against access only SQL 7 & 2000

0
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12194331
Access still doesn't have a CASE function. Here's a summary of changes necessary:

 [...] to (...)
 Now() to GetDate()
 IIF to CASE
0
 

Author Comment

by:DTRON04
ID: 12194379
All of that is done except for CASE because Access does not have this function
here is what i have now, am i screwed or am i not seeing what your trying to show me.

SELECT     IIf(A.ASSIGN_DT > A.EXP_DT, DateDiff(d, A.ASSIGN_DT, GetDate()), DateDiff(d, A.EXP_DT, GetDate())), A.CASE_ID, A.CASE_TYPE, A.INSURED,
                      A.INS_DBA, A.ASSIGN_DT, A.EXP_DT, A.SENT_DT, A.REP_DUE_DT, A.CASE_READ, A.CON_CITY, A.INS_PHONE, B.STAT_COMMENTS AS Scheduled,
                      B.STATUS_DESC AS [Description]
FROM         (SELECT     dattbl_Case.CASE_ID, dattbl_Case.CASE_TYPE, dattbl_Case.INSURED, dattbl_Case.INS_DBA, dattbl_Case.ASSIGN_DT,
                                              dattbl_Case.REP_DUE_DT, dattbl_Case.EXP_DT, dattbl_Tracker.CASE_READ, dattbl_Tracker.SENT_DT, dattbl_Case.CON_CITY,
                                              dattbl_Case.INS_PHONE
                       FROM          dattbl_Case INNER JOIN
                                              dattbl_Tracker ON dattbl_Case.CASE_ID = dattbl_Tracker.CASE_ID
                       WHERE      (dattbl_Tracker.FOLDER = 'INBOX')) A LEFT OUTER JOIN
                          (SELECT     CASE_ID, STAT_CODE, STAT_COMMENTS, STAT_ID, STAT_NEXT_DT, STATUS_DESC
                            FROM          dattbl_Status
                            WHERE      (STAT_CODE = 78 OR
                                                   STAT_CODE = 79 OR
                                                   STAT_CODE = 80 OR
                                                   STAT_CODE = 81)) B ON A.CASE_ID = B.CASE_ID
0
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12194419
>All of that is done except for CASE because Access does not have this function

I thought you were trying to pass this to SQL Server...

SQL Server and Access don't use the same flavor of SQL, so there are a lot of things (like in this query) where one command can't work in both.
0
 

Author Comment

by:DTRON04
ID: 12194678
i am aware they cannot work in both that is why i am asking the question.

but this is TSQL against an access db, which is the reason why we cannot use CASE.
this is for a .net app against an access db

should i post this in the access forum?


sorry if i didnt explain that b4.

0
 
LVL 12

Accepted Solution

by:
monosodiumg earned 250 total points
ID: 12194712
If you are hitting Access, then you need Now and Iff and excepting the original issues with [] and "." your code was OK.

I would go back to that.

I have had problems in the past calling function like iif etc because the driver choked on them. At least that was my understanding .
I suggest you try a few very simple queries just to make sure we can properly use iif() and now() and datediff().
Queires such as :

select iif(1=2, "a", "b") from mytable

Let us know how these tests go.
0
 

Author Comment

by:DTRON04
ID: 12194743
thanks to both for the guidance im taking a break for a bit, i will let you know how it goes
0
 
LVL 15

Assisted Solution

by:jdlambert1
jdlambert1 earned 250 total points
ID: 12194781
>should i post this in the access forum?

Yes, if monosodiumg suggestions aren't sufficient. You can post a request in Community Support, but make sure you cite the Question number in the URL so they know which Q you're talking about. They could just move this, but I suggest you ask them to delete this one and post a new one based on what you've learned here so far. Or, if you're a premium member and have unlimited points, just award this to monosodiumg for one of his posts and post the new question.
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now