May 8, 2012 at 5:56 am
i wrote this request but I got an error and i can not solve it
thankks for your help
-- Insert statements for procedure here
SELECT dbo.CSR_DOSSIER.DOS_NOM +' '+ ISNULL ( dbo .CSR_DOSSIER.DOS_PRENOM, ' ') as Nom
FROM dbo.CSR_DOSSIER ,dbo.CSR_STATUT
WHERE CSR_DOSSIER.DOS_ID = CSR_STATUT.STA_ID
AND dbo .CSR_DOSSIER.DOS_ID =(SELECT dbo.CSR_DOSSIER.DOS_ID
FROM dbo.CSR_DOSSIER WHERE DOS_NODE =@node )
AND CSR_STATUT.STA_ID=6 OR CSR_STATUT.STA_ID=7
May 8, 2012 at 6:02 am
can you detail the error
if your trying to put a uniqueidentifier into an int column it will not work as this conversion is not allowed
May 8, 2012 at 6:04 am
the error is locate just here
AND dbo .CSR_DOSSIER.DOS_ID =(SELECT dbo.CSR_DOSSIER.DOS_ID
FROM dbo.CSR_DOSSIER WHERE DOS_NODE =@node )
i can t understand
May 8, 2012 at 6:06 am
what is the error?
can you also detail the full query, including tables and sample data as per the second link in my signature block to help us create a mock environment to help you out the best we can.
May 8, 2012 at 6:09 am
there is the full code
se [RECLACSR-DB]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:<Author,,Name>
-- Create date: <Create Date,,>
-- Description:<Description,,>
-- =============================================
CREATE PROCEDURE dbo.DossierStatut
-- Add the parameters for the stored procedure here
@Node nvarchar (10)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT dbo.CSR_DOSSIER.DOS_NOM +' '+ ISNULL ( dbo .CSR_DOSSIER.DOS_PRENOM, ' ') as Nom
FROM dbo.CSR_DOSSIER ,dbo.CSR_STATUT
WHERE CSR_DOSSIER.DOS_ID = CSR_STATUT.STA_ID
AND dbo .CSR_DOSSIER.DOS_ID =(SELECT dbo.CSR_DOSSIER.DOS_ID
FROM dbo.CSR_DOSSIER WHERE DOS_NODE =@node )
AND CSR_STATUT.STA_ID=6 OR CSR_STATUT.STA_ID=7
END
GO
May 8, 2012 at 6:11 am
thanks, can you also provide the create table command as well for the table and a few insert statements for some data within the table (granted data maybe sensitive, but some dummy values will do)
also please provide the error text you are getting.
May 8, 2012 at 6:20 am
there is the full error statement
Msg 206, Level 16, State 2, Procedure DossierStatut, Line 16
Conflit de types d'opérandes : uniqueidentifier est incompatible avec int
sorry is in french
May 8, 2012 at 6:21 am
please provide the create table definition and some sample data.
May 8, 2012 at 6:23 am
Hi,
I found the error,
I have miswrite an instruction so i have corrected it
so instead of WHERE CSR_DOSSIER.DOS_ID = CSR_STATUT.sta_id
i have corrected to WHERE CSR_DOSSIER.DOS_ID = CSR_STATUT.DOS_ID
thanks..
May 8, 2012 at 6:30 am
Not to worry, glad you spotted the mistake.
Just out of noticing your joining syntax, what type of join are you after here as the syntax from table1, table2 denotes a cross join
May 8, 2012 at 7:18 am
anthony.green (5/8/2012)
Not to worry, glad you spotted the mistake.Just out of noticing your joining syntax, what type of join are you after here as the syntax from table1, table2 denotes a cross join
He isnt doing a cross join, he is using the old Ansi 86 syntax for joins. The "ON" clause is in the where clause.
To the OP: You should change to the newer Ansi 92 syntax, similar to:
FROM table A
INNER JOIN table B ON A.PKID=B.FKID
May 8, 2012 at 7:21 am
DiverKas (5/8/2012)
anthony.green (5/8/2012)
Not to worry, glad you spotted the mistake.Just out of noticing your joining syntax, what type of join are you after here as the syntax from table1, table2 denotes a cross join
He isnt doing a cross join, he is using the old Ansi 86 syntax for joins. The "ON" clause is in the where clause.
To the OP: You should change to the newer Ansi 92 syntax, similar to:
FROM table A
INNER JOIN table B ON A.PKID=B.FKID
Thats what I thought but then I sumbled across this
The "implicit join notation" simply lists the tables for joining, in the FROM clause of the SELECT statement, using commas to separate them. Thus it specifies a cross join, and the WHERE clause may apply additional filter-predicates (which function comparably to the join-predicates in the explicit notation).
SELECT *
FROM employee, department
WHERE employee.DepartmentID = department.DepartmentID;
May 8, 2012 at 7:21 am
Another thing would be the mixing of AND & OR in the WHERE clause - is that being evaluated correctly for desired criteria?
May 8, 2012 at 7:25 am
anthony.green (5/8/2012)
Not to worry, glad you spotted the mistake.Just out of noticing your joining syntax, what type of join are you after here as the syntax from table1, table2 denotes a cross join
Technically, yes, but the comparison of the two tables in the WHERE clause means it's functionally an INNER JOIN.
May 8, 2012 at 8:13 am
And be careful of the subselect in your join condition. This will not return all rows if there are more than 1, it will return a single random row.
Here is the original for reference.
SELECT dbo.CSR_DOSSIER.DOS_NOM +' '+ ISNULL ( dbo .CSR_DOSSIER.DOS_PRENOM, ' ') as Nom
FROM dbo.CSR_DOSSIER ,dbo.CSR_STATUT
WHERE CSR_DOSSIER.DOS_ID = CSR_STATUT.STA_ID
AND dbo .CSR_DOSSIER.DOS_ID =(SELECT dbo.CSR_DOSSIER.DOS_ID
FROM dbo.CSR_DOSSIER WHERE DOS_NODE =@node )
AND CSR_STATUT.STA_ID=6 OR CSR_STATUT.STA_ID=7
I think you could change your query to the following which contains the current standard join syntax instead of the old style cross join with filters. It also replaces the subquery in the join on what I think you are trying to retrieve. I also changed up the where clause a little bit. The way you have it coded it will return rows if the STA_ID = 7 regardless of the rest of the join conditions.
SELECT cd.DOS_NOM + ' ' + ISNULL(cd.DOS_PRENOM, ' ') as Nom
FROM dbo.CSR_DOSSIER cd
join dbo.CSR_STATUT cs on cd.DOS_ID = cs.STA_ID
AND cd.DOS_NODE = @node
AND cs.STA_ID in (6,7)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply