Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

SQL for adding flags depending on criteria Expand / Collapse
Author
Message
Posted Monday, February 17, 2014 4:51 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, February 26, 2014 6:59 AM
Points: 11, Visits: 18
Incorrect syntax near keyword 'with'
Post #1542047
Posted Monday, February 17, 2014 8:02 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 03, 2014 10:10 AM
Points: 2,792, Visits: 4,874
Syntax looks right.
Could you please post exact query you are trying to execute.


_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1542140
Posted Monday, February 17, 2014 8:09 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, February 26, 2014 6:59 AM
Points: 11, Visits: 18

; WITH cte (Patient.PatientId,Course.CourseId,Course.StartDateTime,RowNumber) AS (
SELECT Patient.PatientId,Course.CourseId,Course.StartDateTime
,ROW_NUMBER() OVER (PARTITION BY Patient.PatientId,YEAR(Course.StartDateTime),MONTH(Course.StartDateTime ORDER BY YEAR(Course.StartDateTime),MONTH(Course.StartDateTime
))
FROM

Patient,
Course

WHERE

Patient.PatientSer = Course.PatientSer

)
SELECT Patient.PatientId,Course.CourseId,Course.StartDateTime,SIGN(RowNumber)-SIGN(RowNumber-1) AS [CountUnique]
FROM cte ;


I get the error I posted above.
Post #1542143
Posted Monday, February 17, 2014 8:19 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:55 PM
Points: 41,570, Visits: 34,495
What version of SQL Server are you using?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1542148
Posted Monday, February 17, 2014 8:25 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, February 26, 2014 6:59 AM
Points: 11, Visits: 18
Transact SQL? Im using a package that comes with our system but can also use SQL advantage of that makes any difference.

Post #1542156
Posted Monday, February 17, 2014 9:16 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:55 PM
Points: 41,570, Visits: 34,495
Client tool is irrelevant, we need to know what version of the database engine you are using to suggest code that has a hope in hell of doing what you want.

Since WITH throws an error, I doubt it's SQL Server 2012 (which we assumed based on the forum this is posted in)



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1542192
Posted Monday, February 17, 2014 9:39 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, February 26, 2014 6:59 AM
Points: 11, Visits: 18
Sorry, its Adaptive server enterprise version 15

Am I on the wrong forum? Can you point me in the right direction?

Thanks
Post #1542202
Posted Monday, February 17, 2014 11:51 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 7:47 AM
Points: 8,296, Visits: 8,750
Yes, you are on the wrong forum. ASE is a Sybase product. I imagine there is a forum somewhere for ASE T-SQL.

I think your problem is that the T-SQL in ASE doesn't support CTEs (oddly enough, though, SQL Anywhere does support them - so if you can switch to SQL Anwhere you should be OK).

Sybases documentation says
Migrating SQL Anywhere database applications to ASE
SQL Anywhere supports common table expressions, the inlined declaration of views within a query expression using the WITH clause. In ASE, you must instead explicitly create a permanent view. Common table expressions are SQL/2008 language feature T121.


Maybe you could use a subquery (implicit view expresion) instead of an explicit CTE - I don't know Sybase T-SQL well enough to be much help in such a rewrite.


Tom
Post #1542240
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse