Distinct In Common Table Expressions CTE

Feb 20, 2007

I have managed to write my first CTE SQL that handles recursion but I have a problem with distinct - can't get that to work!!

My CTE:
WITH StudentsHierarchy(SystemID1, GroupID, AccessType, AccessGroupID, StudentID, HierarchyLevel) AS
(
--Base Case
SELECT
SystemID,
GroupID,
AccessType,
AccessGroupID,
StudentID,
1 as HierarchyLevel
FROM UserGroup a

UNION ALL

--Recursive step
SELECT
u.SystemID,
u.GroupID,
u.AccessType,
u.AccessGroupID,
u.StudentID,
uh.HierarchyLevel + 1 as HierarchyLevel
FROM UserGroup u
INNER JOIN StudentsHierarchy uh ON
u.GroupID = uh.AccessGroupID

)
Select sh.SystemID1, sh.GroupID, sh.AccessType, sh.AccessGroupID, sh.StudentID, sh.HierarchyLevel, (select StudentName from Student s
where sh.StudentID = s.StudentID) AS StudentName from StudentsHierarchy sh
WHERE AccessType = 'S'

 

and I would like to have a distinct on the StudentID like:
Select DISTINCT sh.StudentID, sh.SystemID1, sh.GroupID, sh.AccessType, sh.AccessGroupID, sh.StudentID, sh.HierarchyLevel, (select StudentName from Student s
where sh.StudentID = s.StudentID) AS StudentName from StudentsHierarchy sh
WHERE AccessType = 'S'

 

 

How should I do?

 

View 1 Replies


ADVERTISEMENT

Common Table Expressions

Aug 31, 2006

Hi guys,

I know its not a good idea to give you a big query...but/...

I have a query (given below) with Common table expressions. It is giving me error : The multi-part identifier "dbo.tmpValidServices_ALL.Service" could not be bound. for all the fields .. Do you have any idea.. I fed up with analysing query.. made me mad.. please help me...

 

----------------------------------------------

DECLARE @sql nvarchar(MAX), @paramlist nvarchar(4000)

SET @sql = 'WITH Q1 (SiteID,Programme,Complete,PID,COC,NotionalSum,TVMinsSUM,Postcode,Suburb,Qty)
AS

(SELECT
 dbo.tmpValidServices_ALL.SiteID,
 dbo.tmpValidServices_ALL.Programme,
 dbo.tmpValidServices_ALL.Complete AS Complete,
 dbo.tmpValidServices_ALL.RID AS PID,
 dbo.tmpValidServices_ALL.COC# AS COC,
 (dbo.lkpService.Notional$) AS NotionalSum,
 (TVMins) AS TVMinsSUM,
 dbo.tmpDemographics_ALL.Postcode,
 dbo.tmpdemographics_ALL.Suburb,
                count(*) as Qty

FROM  lkpService

INNER JOIN dbo.tmpValidServices_ALL vs  ON dbo.lkpservice.code = dbo.tmpValidServices_ALL.Service
INNER JOIN dbo.tmpDemographics_ALL ON dbo.tmpValidServices_ALL.RID = dbo.tmpDemographics_ALL.RID '

IF @COCGroup IS NOT NULL
SELECT     @sql = @sql + 'LEFT OUTER JOIN dbo.lkpCOC c ON dbo.tmpValidServices_ALL.COC = c.pvcode '

SELECT     @sql = @sql + 'LEFT OUTER JOIN dbo.lkpAgency ag ON vs.SiteID = ag.EXACT# '      

SELECT     @sql = @sql + 'WHERE dbo.lkpService.Schedule = @Schedule '

 IF @StartDate IS NOT NULL
     SELECT     @sql = @sql + ' AND (dbo.tmpValidServices_ALL.Complete  >= @StartDate ) '

 IF @EndDate IS NOT NULL
      SELECT     @sql = @sql + ' AND (dbo.tmpValidServices_ALL.Complete <= @EndDate ) '


 IF @SiteID IS NOT NULL
       SELECT     @sql = @sql + 'AND (ag.EXACT# = @SiteID) '

 IF @COCGroup IS NOT NULL
 SELECT     @sql = @sql + ' AND (c.pvcode = @COCGroup OR c.pvcode IN (SELECT COC FROM lkpCOCGroup WHERE COCGroup = @COCGroup)) '

 IF @Provider IS NOT NULL
 SELECT     @sql = @sql + 'AND (dbo.tmpValidServices_ALL.Provider = @Provider) '

 IF @Postcode IS NOT NULL
SELECT     @sql = @sql + 'AND (dbo.tmpdemographics_ALL.Postcode = @Postcode) '

SELECT     @sql = @sql  + ' GROUP BY dbo.tmpValidServices_ALL.SiteID,
                                                              dbo.tmpValidServices_ALL.Programme,
                                                              dbo.tmpValidServices_ALL.Complete ,
                                                              dbo.tmpValidServices_ALL.RID , 
                                                              dbo.tmpValidServices_ALL.COC# ,
                                                              dbo.tmpDemographics_ALL.postcode, 
                                                              dbo.tmpdemographics_ALL.Suburb,
                                                              (dbo.lkpService.Notional$),
                                                              (dbo.lkpService.TVMins)  ) 

SELECT COUNT(DISTINCT PID + CAST(Complete AS varchar(20))  + CAST(COC AS varchar(20)) + CAST(SiteID AS varchar(20)) + CAST(Programme AS varchar(20))) AS Visits,
 COUNT(DISTINCT PID + CAST(SiteID AS varchar(20)) + CAST(Programme AS varchar(20))) AS Patients, 
 COUNT(DISTINCT  CAST(COC AS varchar(20)) + CAST(SiteID AS varchar(20)) + CAST(Programme AS varchar(20)) + CAST(PID AS varchar(20))) AS COCs,
 SUM(NotionalSum) AS NotionalSum,
 SUM(TVMinsSUM) AS TVMinsSUM,Postcode,Suburb,sum(Qty) as Qty
 
FROM Q1

Group by Postcode,Suburb

Order by Postcode,Suburb  '

SET              @paramlist = '@SiteID as numeric,
@COCGroup as varchar(20),
@Postcode as varchar(20),
@StartDate DateTime,
@EndDate DateTime,
@Schedule varchar(20),
@Provider varchar(20)'
                     
 EXEC sp_executesql @sql, @paramlist, @SiteID, @COCGroup, @Postcode, @StartDate, @EndDate, @Schedule, @Provider

View 2 Replies View Related

Microsoft OLE DB Provider For IBM DB2 Does Not Accept Common Table Expressions

Oct 26, 2007



After reading all the comments about microsoft ole db provider for db2, I've started converting a project from ibm db2 provider to microsoft ole db provider, for the reason that the server would not need a ibm client software installation anylonger.
However in one of the dataflows there is a select statement on the DB2 database (AS400 iseries DB2 V5R4) that uses common table expression. The Oledb data source in the Dataflow returns zero input columns when I use the microsoft provider instead of the ibm provider. When I set it back to use the ibm oledb provider the input columns are available again.
The common table expression is used in the select statement like:
WITH x AS (SELECT ... FROM mytable WHERE ...)
SELECT x.*, (x.a - x.b) As diff
FROM x

We are not allowed to change anything in the DB2 database, so using views or sprocs is not an option.

Did anyone else experience this and can it be avoided without changing the selct logic?

View 3 Replies View Related

Is It Possible To Execute The T-Sql Code Of Common Table Expressions In VB 2005 Express?

Jan 2, 2008

Hi all,
I have the following T-SQL code of Common Table Express (CTE) that works in the SQL Server Management Studio Express (SSMSE):

--CTE.sql--

USE ChemAveRpd

GO

WITH PivotedLabTests AS

(

SELECT LT.AnalyteName, LT.Unit,

Prim = MIN(CASE S.SampleType WHEN 'Primary' THEN LT.Result END),

Dupl = MIN(CASE S.SampleType WHEN 'Duplicate' THEN LT.Result END),

QA = MIN(CASE S.SampleType WHEN 'QA' THEN LT.Result END)

FROM LabTests LT

JOIN Samples S ON LT.SampleID = S.SampleID

GROUP BY LT.AnalyteName, LT.Unit

)

SELECT AnalyteName, Unit, avg1 = (abs(Prim + Dupl)) / 2,

avg2 = (abs(Prim + QA)) / 2,

avg3 = (abs(Dupl + QA)) / 2,

RPD1 = (abs(Prim - Dupl) / abs(Prim + Dupl)) * 2,

RPD2 = (abs(Prim - QA) / abs(Prim + QA)) * 2,

RPD3 = (abs(Dupl - QA) / abs(Dupl + QA)) * 2

FROM PivotedLabTests

GO

===========================================
How can I execute this set of the CTE.sql code in the VB 2005 Express via the Stored Procedure programming?

Please help and respond.

Thanks in advance,
Scott Chang

View 1 Replies View Related

Common Table Expressions With Table Creation

May 20, 2008

I have multiple Common Table Expressions i.e CTE1,CTE2,...CTE5.

I want to Create Temperory Table and inserting data from CTE5. Is this possible to create?

like:


create table #temp (row_no int identity (1,1),time datetime, action varchar(5))

insert into #temp select * from cte5

View 4 Replies View Related

Syntax Of Operators And Common Functions In Expressions

Apr 4, 2008

Hi all, real basic question:
I can't seem to find a basic reference that tells me the syntax and allowable values of parameters in common functions such as Format or CDate when editing a Field Expression in Visual Studio (Report Definition Language?). Where would I start?

For example, I've discovered that the Format function "Returns a string formatted according to instructions contained in a format String expression.", and the string expression can have values like "D" and "d" which produce different results, but where would I find out what the allowable string expressions are and their meaning?

I have also been guessing at the syntax of the CDate command with no luck. I need a reference that tells me what the different function parameters mean.

thanks
Jac

View 1 Replies View Related

Creating A Common Table Expression--temporary Table--using TSQL???

Jul 23, 2005

Using SQL against a DB2 table the 'with' key word is used todynamically create a temporary table with an SQL statement that isretained for the duration of that SQL statement.What is the equivalent to the SQL 'with' using TSQL? If there is notone, what is the TSQL solution to creating a temporary table that isassociated with an SQL statement? Examples would be appreciated.Thank you!!

View 11 Replies View Related

Valid Expressions Are Constants, Constant Expressions, And (in Some Contexts) Variables. Column Names Are Not Permitted.

Dec 11, 2007

I want to have this query insert a bunch of XML but i get this error...


Msg 128, Level 15, State 1, Procedure InsertTimeCard, Line 117

The name "ExpenseRptID" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

Msg 128, Level 15, State 1, Procedure InsertTimeCard, Line 151

The name "DateWorked" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

What am i doing wrong...Can anyone help me out!! Thanks!!

p.s I know this query looks crazy...


Code Block

IF EXISTS (SELECT NAME FROM sysobjects WHERE NAME = 'InsertTimeCard' AND type = 'P' AND uid=(Select uid from sysusers where name=current_user))
BEGIN
DROP PROCEDURE InsertTimeCard
END
go
/*********************************************************************************************************
** PROC NAME : InsertTimeCardHoursWorked
**
** AUTHOR : Demetrius Powers
**
** TODO/ISSUES
** ------------------------------------------------------------------------------------
**
**
** MODIFICATIONS
** ------------------------------------------------------------------------------------
** Name Date Comment
** ------------------------------------------------------------------------------------
** Powers 12/11/2007 -Initial Creation
*********************************************************************************************************/
CREATE PROCEDURE InsertTimeCard
@DateCreated DateTime,
@EmployeeID int,
@DateEntered DateTime,
@SerializedXML text,
@Result int output
as
declare @NewTimeCardID int
select @NewTimeCardID = max(TimeCardID) from OPS_TimeCards
-- proc settings
SET NOCOUNT ON

-- local variables
DECLARE @intDoc int
DECLARE @bolOpen bit
SET @bolOpen = 0
--Prepare the XML document to be loaded
EXEC sp_xml_preparedocument @intDoc OUTPUT, @SerializedXML
-- check for error
IF @@ERROR <> 0
GOTO ErrorHandler
--The document was prepared so set the boolean indicator so we know to close it if an error occurs.
SET @bolOpen = 1


--Create temp variable to store values inthe XML document
DECLARE @tempXMLTimeCardExpense TABLE
(
TimeCardExpenseID int not null identity(1,1),
TimeCardID int,
ExpenseRptID int,
ExpenseDate datetime,
ProjectID int,
ExpenseDescription nvarchar(510),
ExpenseAmount money,
ExpenseCodeID int,
AttachedRct bit,
SubmittoExpRep bit
)
DECLARE @tempXMLTimeCardWorked TABLE
(
TimeCardDetailID int not null identity(1,1),
TimeCardID int,
DateWorked DateTime,
ProjectID int,
WorkDescription nvarchar(510),
BillableHours float,
BillingRate money,
WorkCodeID int,
Location nvarchar(50)
)
-- begin trans
BEGIN TRANSACTION
insert OPS_TimeCards(NewTimeCardID, DateCreated, EmployeeID, DateEntered, Paid)
values (@NewTimeCardID, @DateCreated, @EmployeeID, @DateEntered, 0)
-- check for error
IF @@ERROR <> 0
GOTO ErrorHandler


--Now use @intDoc with XPATH style queries on the XML
INSERT @tempXMLTimeCardExpense (TimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep)
SELECT @NewTimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep
FROM OPENXML(@intDoc, '/ArrayOfTimeCardExpense/TimeCardExpense', 2)
WITH ( ExpenseRptID int 'ExpenseRptID',
ExpenseDate datetime 'ExpenseDate',
ProjectID int 'ProjectID',
ExpenseDescription nvarchar(510) 'ExpenseDescription',
ExpenseAmount money 'ExpenseAmount',
ExpenseCodeID int 'ExpenseCodeID',
AttachedRct bit 'AttachedRct',
SubmittoExpRep bit 'SubmittoExpRep')
-- check for error
IF @@ERROR <> 0
GOTO ErrorHandler

-- remove XML doc from memory
EXEC sp_xml_removedocument @intDoc
SET @bolOpen = 0


INSERT OPS_TimeCardExpenses(TimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep)
Values(@NewTimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep)
select @NewTimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep
from @tempXMLTimeCardExpense
-- check for error
IF @@ERROR <> 0
GOTO ErrorHandler

-- For time worked...
INSERT @tempXMLTimeCardWorked(TimeCardID, DateWorked, ProjectID, WorkDescription, BillableHours, BillingRate, WorkCodeID, Location)
SELECT @NewTimeCardID, DateWorked, ProjectID, WorkDescription, BilliableHours, BillingRate, WorkCodeID, Location
FROM OPENXML(@intDoc, '/ArrayOfTimeCardWorked/TimeCardWorked', 2)
WITH ( DateWorked DateTime 'DateWorked',
ProjectID datetime 'ProjectID',
WorkDescription nvarchar(max) 'WorkDescription',
BilliableHours float 'BilliableHours',
BillingRate money 'BillingRate',
WorkCodeID int 'WorkCodeID',
Location nvarchar(50)'Location')
-- check for error
IF @@ERROR <> 0
GOTO ErrorHandler

-- remove XML doc from memory
EXEC sp_xml_removedocument @intDoc
SET @bolOpen = 0


INSERT OPS_TimeCardHours(TimeCardID, DateWorked, ProjectID, WorkDescription, BillableHours, BillingRate, WorkCodeID, Location)
Values(@NewTimeCardID,DateWorked, ProjectID, WorkDescription, BillableHours, BillingRate, WorkCodeID, Location)
select @NewTimeCardID ,DateWorked, ProjectID, WorkDescription, BillableHours, BillingRate, WorkCodeID, Location
from @tempXMLTimeCardWorked


-- commit transaction, and exit
COMMIT TRANSACTION
set @Result = @NewTimeCardID
RETURN 0

-- Error Handler
ErrorHandler:
-- see if transaction is open
IF @@TRANCOUNT > 0
BEGIN
-- rollback tran
ROLLBACK TRANSACTION
END
-- set failure values
SET @Result = -1
RETURN -1

go

View 1 Replies View Related

Common Table Expression

May 28, 2008

Hi
 
I was studying Common Table expression in Sql server 2005.
I have written the code
Declare @PictureArray as varchar(200)
Set @PictureArray = '';
with UserProfile_CTE(UserPicture)
As(
select @PictureArray = @PictureArray + '~' + PictureName from UserPicture where UserProfileID = 1102
select @PictureArray
)
select * from UserProfile_CTE
 
But I am getting the error
Incorrect syntax near '='
I am getting the error in the lineselect @PictureArray = @PictureArray + '~' + PictureName from UserPicture where UserProfileID = 1102
But I don't know the reason for this,
Kindly advice
Regards
Karan 
 

View 3 Replies View Related

Delete The Common Row Of A Table

Jan 7, 2006

hi i am using MS SQL Server 2000, i want to delete the common row from a table

table name EMP
rows
Eno Name Sal
1 Jim 1000
2 Mark 1200
1 Jim 1000
2 Mark 1250
1 Jim 1300

no primary key defile in to the table ....

i want to delete the row of
1 Jim 1000

please anybody can can help me ...

samarjit

View 1 Replies View Related

Common Table Expression?

Jul 20, 2005

What is the SQL Server equivalent of DB2 common table expressions? Forexample,with gry(year,count) as(select floor(sem/10),count(distinct ssn)from gradesgroup by floor(sem/10))select year,sum(count) Head_Count from grygroup by yearhaving year >= 1980;N. ShamsundarUniversity of Houston

View 2 Replies View Related

Common Need: History Table

Jan 22, 2008

Hi there!

I'm working on an application designed like this:
There's a table "DailyTransations" (DT) containing daily transactions...
Then there's an archive table "TransationsArchive" (TA) with the exact same structure.

When a record is inserted in DT, it is also in TA (via a trigger) and the reporting is done against TA.
Now for performance issues, we delete from DT the records older than 2 days since they are not needed for processing.

First, what do you think of that implementation?

We thought about using partitions based on the transaction date and completely eliminate TA, but it seems once a record is assigned to a partition, it is not moved automatically...

What is the common solution for this need?

Thanks
Frantz

View 4 Replies View Related

Index On Used Table Common Fields

Jan 6, 2012

I would like to know if SQL Server creates automatic index on the used tables' common fields.

For example:

Code:
CREATE VIEW [dbo].[ORMAN]
AS
SELECT dbo.PARSEL.OBJECTID, dbo.PARSEL.ADAPARSEL, dbo.PARSEL.ORMANADI, dbo.PARSEL.MULKIYET, dbo.PARSEL.ALAN, dbo.PARSEL.YERKOD,
dbo.PARSEL.UYG_KAN, dbo.PARSEL.KomNo, dbo.PARSEL.KadKan, dbo.PARSEL.Mescere, dbo.PARSEL.ACIKLAMA, dbo.PARSEL.URETIM_YONTEMI,

[Code] .....

On the above view example, if table PARSEL has 1 million records do I have to create an index for PARSELDURUM or when I create a view does SQL server create automatic virtual index?

View 6 Replies View Related

When Connect Two Table Should They Always Have Column In Common With Each Other?

Jun 27, 2014

When I connect two table should they always have a column in common with each other?For example the 'patients' and 'doctors' tables in that ER are connected to say each doctor can have many patients and each patient can have many doctors, but they have no columns in common. Is it right?

View 8 Replies View Related

Order By Common Lookup Table Matches

Mar 11, 2013

Basically I have a table of users (id, name), a lookup table preferences (id, title) and the table of user-pref (idUser, idPref).Given a user id X I wanted to select a list of users, ordered by the highest amount of coincidence in the preference table...

something like

SELECT * FROM users WHERE id <> X ORDER BY -number of common preferences-

View 7 Replies View Related

Join A Table With Different Tables But With A Field In Common?

Jan 27, 2014

I have a table activity which show the activities of the site. An activity could be related with no other entity or it could be related with an account, or with a contact or with an opportunity. An opportunity and a contact could be also related with an account.

My problem is that I need to show the activity and the related account (if there is any) but because the relationship with an account could be done by different ways, I dont know how to do it without join the account table several times.

This is my current query:

SELECT AC.IdActividad, CU.idcuenta
FROM Actividades AC LEFT JOIN Tareas TA ON TA.IdActividad = C.IdActividad
LEFT JOIN ActividadesXCuenta AXC ON AC.IdActividad = AXC.IdActividad LEFT JOIN dbo.Cuentas CU ON axc.IdCuenta = CU.IdCuenta
LEFT JOIN dbo.ActividadesXOportunidad axo ON AC.IdActividad = axo.IdActividad LEFT JOIN dbo.Oportunidades o ON axo.IdOportunidad = o.IdOportunidad LEFT JOIN dbo.Cuentas CU1 ON o.IdCuenta = CU.IdCuenta
LEFT JOIN dbo.ActividadesXContacto axco ON AC.IdActividad = axco.IdActividad LEFT JOIN dbo.Contactos con ON axco.IdContacto = con.IdContacto LEFT JOIN dbo.Cuentas CU2 ON o.IdCuenta = CU.IdCuenta

As you see, I have the table Cuenta joined 3 times. That means that I need to put the ActivityID field 3 times but based on how the report was developed, I could not do that.

View 3 Replies View Related

SQL 2012 :: Common Table (CTE) In Store Procedure?

Jul 31, 2014

While writing store procedure in db most of Time i will Use common Table to write select queries for selecting more than seven table whether it reduce speed performance or it won't

;with cte
{
}

View 2 Replies View Related

Recursive Common Table Expression Data?

Jan 10, 2014

With my new data, need result for below scenario ( use CTE )

´`````````````
CREATE TABLE
´´´´´´´´´´´´´´´´
CREATE TABLE [dbo].[Travel_Master](
[Load_Id] [int] NULL,
[Mode_Id] [nchar](2) NULL,
[Mode_Info] [nchar](10) NULL,

[Code] ....

===============================
Insert DATA TABLE 1
===============================

INSERT INTO [dbo].[Travel_Master] ([Load_Id] ,[Mode_Id] ,[Mode_Info] ,[Has_Nodes]) VALUES ( 1,'AP' ,'AIR' ,'Yes')
INSERT INTO [dbo].[Travel_Master] ([Load_Id] ,[Mode_Id] ,[Mode_Info] ,[Has_Nodes]) VALUES ( 1,'FL' ,'FLIGHT' ,'Yes')
INSERT INTO [dbo].[Travel_Master] ([Load_Id] ,[Mode_Id] ,[Mode_Info] ,[Has_Nodes]) VALUES ( 1,'FP' ,'FLIGHT-Pas' ,'No')
INSERT INTO [dbo].[Travel_Master] ([Load_Id] ,[Mode_Id] ,[Mode_Info] ,[Has_Nodes]) VALUES ( 1,'SE' ,'SEA' ,'Yes')
INSERT INTO [dbo].[Travel_Master] ([Load_Id] ,[Mode_Id] ,[Mode_Info] ,[Has_Nodes]) VALUES ( 1,'SP' ,'SHIP' ,'No')
INSERT INTO [dbo].[Travel_Master] ([Load_Id] ,[Mode_Id] ,[Mode_Info] ,[Has_Nodes]) VALUES ( 1,'RD' ,'ROAD' ,'No')
INSERT INTO [dbo].[Travel_Master] ([Load_Id] ,[Mode_Id] ,[Mode_Info] ,[Has_Nodes]) VALUES ( 1,'BU' ,'BUS' ,'No')

===============================
Insert DATA TABLE 2
===============================
INSERT INTO [Travel_Quantity]([Load_Id],[Mode_Sno],[Mode_Id],[Mode_Parent_Sno],[QA]) VALUES ( 1,'1' ,'AP' ,'-1','4' )
INSERT INTO [Travel_Quantity]([Load_Id],[Mode_Sno],[Mode_Id],[Mode_Parent_Sno],[QA]) VALUES ( 1,'2' ,'FL' ,'1','2' )
INSERT INTO [Travel_Quantity]([Load_Id],[Mode_Sno],[Mode_Id],[Mode_Parent_Sno],[QA]) VALUES ( 1,'3' ,'FP' ,'2','1' )
INSERT INTO [Travel_Quantity]([Load_Id],[Mode_Sno],[Mode_Id],[Mode_Parent_Sno],[QA]) VALUES ( 1,'4' ,'SE' ,'-1','0' )
INSERT INTO [Travel_Quantity]([Load_Id],[Mode_Sno],[Mode_Id],[Mode_Parent_Sno],[QA]) VALUES ( 1,'5' ,'SP' ,'4','1' )
INSERT INTO [Travel_Quantity]([Load_Id],[Mode_Sno],[Mode_Id],[Mode_Parent_Sno],[QA]) VALUES ( 1,'6' ,'RD' ,'-1','6' )
INSERT INTO [Travel_Quantity]([Load_Id],[Mode_Sno],[Mode_Id],[Mode_Parent_Sno],[QA]) VALUES ( 1,'7' ,'BU' ,'6','3' )

========
RULE
========

1.QA of Parent Row value Greater than 0- Consider Parent and 1st Node.From the available result,

2.HasNodes = Yes, Display Parent and 1st Node

3.HasNodes = No, Display Parent Only.

================
Expected Result
================
Mode_Info|Mode_Detail|QA
AIR||4
AIR|FLIGHT|2
ROAD||6

===============
My Query
=============

learning CTE To apply for HasNodes Rules.

;with MyCTE1 as (
select
CONVERT(nvarchar(MAX),RTRIM(T2.Mode_Info)) Mode_Info, T1.Mode_Parent_Sno, T1.Mode_Sno, T1.QA ,T2.Has_Nodes
from [Travel_Quantity] T1
left join [Travel_Master] T2 on T1.Mode_Id = T2.Mode_Id
) ,
MyCTE2 as (

[code]....

View 4 Replies View Related

Writing ETL Error Records To A Common Table

Jun 13, 2007

I am working on building a template/design pattern for a DTS to SSIS upgrade project.



During our ETL processing, if we encounter a record that cannot be inserted into a destination table, we'd like to be able to write the entire record out to a common error/reject table. The obvious problem is that every SSIS package that is using this template will of course be dealing with varying table schemas.



I was thinking that if there were a way that I could transform the error record/buffer row into XML, I could then achieve my goal of having a common table to receive errors/rejects.



Has anyone done something like this, or have suggestions on how we might accomplish?



View 7 Replies View Related

Multiple Operations On A Common Table Expression

Jan 28, 2008

Hi,
I'd like to perform a number of different operations on my Common Table expression but I seem to be limited to only one operation. For example I cannot both delete duplicate rows and then perform a select statement. I can only execute one of the statements referencing the common table expression.

What is wrong with my syntax?


;With OrderedTable

AS

(

select Row_number() OVER (partition BY SSNumber order by Department_Id desc ) AS ROWID,* from Employee

)

delete from OrderedTable where RowId != 1

SELECT COUNT(*),SSNumber FROM OrderedTable group by Department_Id order by count(*) desc

View 1 Replies View Related

Conditional Processing From A Common Table Expression (CTE)

Sep 5, 2007

I want to do conditional processing depending on values in the rows of a CTE. For example, is the following kind of thing possible with a CTE?:


WITH Orders_CTE (TerritoryId, ContactId)
AS
(
SELECT TerritoryId, ContactId
FROM Sales.SalesOrderHeader
WHERE (ContactId < 200)
)
IF Orders_CTE.TerritoryId > 3
BEGIN
/* Do some processing here */
END
ELSE
BEGIN
/* Do something else here */
END

When I try this, I get a syntax error near the keyword 'IF'

Any ideas? I know this kind of thing can be done with a cursor but wanted to keep with the times and avoid using one!

View 3 Replies View Related

Is WITH / Common Table Expression A Syntactic Sugar?

Oct 4, 2006

I tried to use WITH to factor out the shared star join portion from a sql statement as it's usually the most expensive part.

However, examing the execution plan shows that the WITH clause is merely a syntactic suger that will internally be plugged back as derived tables where the same star join is executed repeatedly.

Is the intermediate rowset produced by a WITH caluse ever shared during the query execution?

View 5 Replies View Related

Encountered Problems With Multiple Common Table Expression

Feb 20, 2006

Hi guys,

I'm trying to have two common table expression in my stored procedure, but I'm receiving errors when executing it, I found that they can't exist side by side,once I removed 1 of them, the stored procedure executed successfully.

The following are the errors

Code:


Msg 156, Level 15, State 1, Procedure GetProductsByCategory, Line 27
Incorrect syntax near the keyword 'With'.
Msg 319, Level 15, State 1, Procedure GetProductsByCategory, Line 27
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
Msg 319, Level 15, State 1, Procedure GetProductsByCategory, Line 33
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.



I'm using SQL Server Express

View 4 Replies View Related

SQL Server 2005 Common Table Expression(CTE) Implement Reg.

Aug 30, 2006

Hi,

We are developing the web application using ASP.NET 2.0 using C# with Backend of SQL Server 2005.

Now we have one requirement with my client, Already our application live in ASP using MS Access Database. In Access Database our client already wrote the query, those query call the another query from same database. So I want to over take this functionality in SQL Server 2005.

In ASP Application, We call this query €œ081Stats€? from Record set.

This €˜081Stats€™ query call below sub query itself

Master Query: 081Stats

SELECT DISTINCTROW [08Applicants].school, [08Applicants].Applicants, [08Interviewed].Interviewed, [Interviewed]/[Applicants] AS [interviewed%], [08Accepted].Accepted, [Accepted]/[Applicants] AS [Accepted%], [08Dinged].Dinged, [Dinged]/[Applicants] AS [Dinged%], [08Waitlisted].Waitlisted, [Applicants]-[Accepted]-[Dinged] AS Alive, [08Matriculating].Matriculating, [Matriculating]/[Accepted] AS [Yield%]
FROM ((((08Applicants LEFT JOIN 08Interviewed ON [08Applicants].school = [08Interviewed].school) LEFT JOIN 08Accepted ON [08Applicants].school = [08Accepted].school) LEFT JOIN 08Dinged ON [08Applicants].school = [08Dinged].school) LEFT JOIN 08Waitlisted ON [08Applicants].school = [08Waitlisted].school) LEFT JOIN 08Matriculating ON [08Applicants].school = [08Matriculating].school;

Sub Query 1: 08Accepted

SELECT statusTbl.school, Count(1) AS Accepted
FROM statusTbl
WHERE (((statusTbl.decision)=1) AND ((statusTbl.yearapp)="2008"))
GROUP BY statusTbl.school
ORDER BY Count(1) DESC;

Sub Query 2: 08Applicants

SELECT statusTbl.school, Count(1) AS Accepted
FROM statusTbl
WHERE (((statusTbl.decision)=1) AND ((statusTbl.yearapp)="2008"))
GROUP BY statusTbl.school
ORDER BY Count(1) DESC;

Sub Query 3: 08Dinged

SELECT statusTbl.school, Count(1) AS Dinged
FROM statusTbl
WHERE (((statusTbl.decision)=0) AND ((statusTbl.yearapp)="2008"))
GROUP BY statusTbl.school
ORDER BY Count(1) DESC;

Sub Query 4: 08Interviewed

SELECT statusTbl.school, Count(1) AS Interviewed
FROM statusTbl
WHERE (((statusTbl.interview)=True) AND ((statusTbl.yearapp)="2008"))
GROUP BY statusTbl.school
ORDER BY Count(1) DESC;

Sub Query 5: 08Matriculating

SELECT statusTbl.school, Count(1) AS Matriculating
FROM statusTbl
WHERE (((statusTbl.userdec)=True) AND ((statusTbl.yearapp)="2008"))
GROUP BY statusTbl.school
ORDER BY Count(1) DESC;

So now I got the solution from SQL Server 2005. I.e. Common Table Expressions, So I got the syntax and other functionality, Now my doubts is how do implement the CTE in SQL Server 2005, where can I store the CTE in SQL Server 2005.

How can I call that CTE from ASP.NET 2.0 using C#?

CTE is replacing the Stored Procedure and Views, because it is memory based object in SQL Server 2005.

How can I implement the CTE, where can I write the CTE and where can I store the CTE.

And how can I call the CTE from ASP.NET 2.0 using C#.

It€™s Very Urgent Requirements.

We need your timely help.

With Thanks & Regards,
Sundar

View 1 Replies View Related

Common Table Expression Calling Stored Procedure

Nov 19, 2007

Hi,

I have a stored procedure that return 0 or 1 row and 10 columns. In my subsequent queries I only need 1 column from those 10 columns. Is there any better way other than creating or declaring temp table and than making a select from that table.

So I am looking int CTE to execute stored procedure and than make a selection from CTE, but CTE does not allow me to execute stored procedure. Is there any other better way of acheiving this.

This is what I want to change:

Declare @Col Varchar(10)
Decalre @TempTable(Col1 Int, Col2 Varchar(10), Col3 Varchar(10), Col4 Varchar(10))

Insert into @TempTable
Exec Procedure @Paramter

select @Col = col2 from @TempTable

INTO

With CTE(Col2, Col3, Col4) AS
(

Exec Procedure @Paramter)

select @Col = col2 from @TempTable

Thanks
Punu

View 6 Replies View Related

Grouping By Month In Common Table Expression Counts Wrong

Sep 26, 2012

I'm using CTEs to try and get the totals of two different criteria queries that I want to group by Month depending on the associated Inquiry Date. Here is what I have right now;

Code:
;With CTE(total, InitDate) as
(
SELECT count(Inquiry.ID), Inquiry.Date from Inquiry
Inner Join Inquirer on Inquirer.ID = Inquiry.InquirerID_fk
Left Join Transfer on Transfer.TransferInquiryID_fk = Inquiry.ID
WHERE (Inquiry.Date >= '3/1/2012' AND Inquiry.Date <= '9/26/2012' AND Inquiry.Date IS NOT NULL)
AND (Inquirer.Program = 'Res. Referral Coord.')AND TransferInquiryID_fk IS NULL
Group By Inquiry.Date

[code]...

I get 170 for InitCount, but for TransCount I only get 19, not 26. I assume it is with my left outer join statement grouping but I am not sure how I would change this to get the proper counts. All I want to do is group the values together depending on the month they were done in.

View 3 Replies View Related

SQL Server 2008 :: Aggregating Data From Common Table Expression

Apr 2, 2015

I am trying to create a small data set for use in a charting program. However, I am not getting the results that I expected.

********QUERY BEGIN*****************
DECLARE @BeginDate datetime
DECLARE @EndDate datetime
SET @BeginDate = '2014-01-01'
SET @EndDate = '2014-12-31';

WITH CTE AS (
SELECT bkg_nbr

[Code] ....

The raw data from the CTE is:

Bkg_nbr STATE_NBR Ranking
20140000943200060000 1
20140000131500140000 1
20140000682200140000 2
20140000504100210000 1

The result of the query above was:

Category RecCount
Non-Recidivists3
Recidivists 1

The outcome I was looking for was:

Category RecCount
Non-Recidivists2
Recidivists 1

What I am trying to do is count persons in buckets "non-recidivists" and "recidevists" based on how many bkg_nbr they have per STATE_NBR. If they have more than 1 bkg_nbr per STATE_NBR then put them in the "recdivists" bucket. If they only have a 1 to 1 then put them in the "non-recidivists" bucket.

View 2 Replies View Related

SQL Server 2012 :: Filtering Common Table Expression Within View

Sep 1, 2015

I have a multi-tenant database where each row and each table has a 'TenantId' column. I have created a view which has joins on a CTE. The issue I'm having is that entity framework will do a SELECT * FROM MyView WHERE TenantId = 50 to limit the result set to the correct tenant. However it does not limit the CTE to the same TenantId so that result set is massive and makes my view extremely slow. In the included example you can see with the commented line what I need to filter on in the CTE but I am not sure how to get the sql plan executor to understand this or weather it's even possible.I have included a simplified view definition to demonstrate the issue...

ALTER VIEW MyView
AS
WITH ContactCTE AS(
SELECT Col1,
Col2,
TenantId

[code]....

View 4 Replies View Related

Preferred Method To Incorporate Common Table Expression (CTE) Functionality

Feb 26, 2007

What is the best approach to utilize a recursive CTE (Common Table Expression) to filter a resultset?  The CTE function is used in all application queries to limit recursively the final resultset based on a provided hierarchical organization identifier. i.e. join from some point in the organization chart on down based on an organization id. I would prefer that the query could be run real-time. i.e. not having to persist the prediction portion of the results to a sql relational table and  then limiting the persisted results based on the CTE function. 

It appears that I can use a linked server to access the prediction queries directly from SQL Server (link below). I believe that I might also be able to deploy a CTE recursive function within a .net assembly to the Analysis Server but I doubt that recursive functionality is availalble without a linked SQL Server.
Executing prediction queries from the relational server
http://www.sqlserverdatamining.com/DMCommunity/TipsNTricks/3914.aspx
 
 

 

View 1 Replies View Related

How To Join Two Tables Having No Common Coloumns And Having No Primary Key Fields In Any Of The Table

Apr 14, 2008

hi friends i am reading a csv file and storing the data in table i have created the tables like this:

coloumn names datatype constraints

start_date_time varchar(50) allownull = true
ani varchar(50) allownull=true
dialed_digit varchar(50) allownull=true
actual_dur varchar(50) allownull=true
rounded_dur varchar(50) allownull=true
cost varchar(50) allownull=true

and the second table as

coloumn names datatype constraints

country varchar(50) allownull = true
code varchar(50) allownull=true
rate/min varchar(50) allownull=true

now i want to relate and join the table so that i can create a view with the following coloumns such as

start_date_time, dialed_digits, rounded_digits, cost, country, code,rate/min so can any body help me please i have tried outerjoin but it gave me rudundant data so please help me plz its urgent...

View 11 Replies View Related

Transact SQL :: How To Apply Foreign Keys On Top Of A Common Lookup Table

Apr 29, 2015

I have an issue where i am mandated to enforce RI on an applications database (a good thing). but I have several common lookup tables where many of the "codes" reside for many different code types. I also have the mandate that i cannot change the underlying DDL to make composite keys to match the codes table PK. I am currently looking at creating indexed views on top of the Codes table to seperate the logical tables it contains. This is several hundred views.

I do know that I do not want to write several hundred triggers to enforce RI. Table schema below, the CdValue column is the column that is used throughout the hundreds of tables that use this codes table, and their corresponding column is not named the same.

CREATE TABLE dbo.CodesTable (
PartyGrpId INT  NOT NULL
  , CdTyp  VARCHAR ( 8 ) NOT NULL
  , CompId INT  NOT NULL
  , CdValue VARCHAR ( 8 ) NOT NULL
  , CdValueDesc VARCHAR ( 255 ) NULL
  , AltValueDesc VARCHAR ( 100 ) NULL

[Code] ....

I did though run into one forum where a person brought up a great idea. Filtered Foreign Keys, what a novel concept, if it could work it would make so much less code to fix an issue like this.

ALTER TABLE dbo.BusinessStatus WITH NOCHECK
ADD CONSTRAINT FK_dbo_BusinessStatus_CodesTable FOREIGN KEY (LoanStsDtCd) REFERENCES dbo.CodesTable (CdValue) WHERE CdTyp = 'Status'

View 5 Replies View Related

Multiple Select And Sum Expressions On The Same Table SQL…??

Oct 25, 2005

Little complex but I'll try to explain the best I canI have the following table…
 
UserID___Year___Month_____A1____A2____B1____B2
__1_______4______5______45_____5____43____0
__1_______4______6______10_____12___1_____3
__1_______5______5______12_____22___8_____7
__1_______5______6______11_____10___5_____0
 
__2_______4______5______9______65___33____66
__2_______4______6______3______3____10____11
__2_______5______5______44_____2___77_____1
 
 
I want to pull out the data to show the sum of the totals for each year the user is in. The idea is to then be able to see a comparison for year on year on a month by month basis
Eg.
 
UserID____TotalAYear4____TotalBYear4___TotalAYear5____TotalBYear5___Month
__1___________50_____________43___________34____________15__________5
__1___________22_____________4____________21____________5___________6
__2___________74_____________99___________21____________5___________5
 
 
There would be no data returned for UserID2 for month 6 as there is no data for them for year 5 month 6 therefore no comparison can be made.
 
I’ve having real difficulty getting the correct SQL syntax to do this. Any ideas or pointers would be great thanks

View 1 Replies View Related

Speed/efficiency Of View Vs. Common/nested Table Expression In A Join

Mar 2, 2008



i have been trying to determine which is the most efficient, with regards to speed and efficiency, between a view and a common/nested table expression when used in a join.

i have a query which could be represented as index view or a common table expression, which will then be used to join against another table.

the indexed view will use indexes when performing the join. is there a way to make the common table expression faster than an indexed view?

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved