must be doing something wrong, testing with two of the temp tables.
Getting this error msg: with USING
Server: Msg 155, Level 15, State 1, Procedure sp_ProjectStatusReport, Line
58
'Proj_id' is not a recognized OPTIMIZER LOCK HINTS option.
With this query, using MS SQL 2000
SELECT PROJ_NAME, OC_NAME, PROJ_ID
FROM #type_data INNER JOIN
#status_data USING(Proj_id)
WHERE #type_data LIKE '%Contr%'
and #status_data LIKE '%Engineering%'
Tried to do a UNION and got this error:
Server: Msg 8163, Level 16, State 4, Procedure sp_ProjectStatusReport, Line
56
The text, ntext, or image data type cannot be selected as DISTINCT.
with this query
SELECT PROJ_NAME, OC_NAME, PROJ_ID
FROM #type_data
UNION
SELECT PROJ_NAME, OC_NAME, PROJ_ID
FROM #status_data
These are the queries I'm using to creat the temp tables
SELECT P.PROJ_NAME, C.OC_NAME, P.PROJ_ID
into #type_data
FROM MSP_PROJECTS P INNER JOIN
MSP_CODE_FIELDS f ON P.PROJ_ID = f.PROJ_ID INNER JOIN
MSP_OUTLINE_CODES C ON f.CODE_UID = C.CODE_UID
WHERE (C.OC_FIELD_ID = 188744590) AND (C.PROJ_ID = 474) AND (C.OC_PARENT
= 16 OR
C.OC_PARENT = 14) AND (f.CODE_REF_UID = 0)
ORDER BY P.PROJ_ID
SELECT P.PROJ_NAME, C.OC_NAME, P.PROJ_ID
into #status_data
FROM MSP_PROJECTS P INNER JOIN
MSP_CODE_FIELDS f ON P.PROJ_ID = f.PROJ_ID INNER JOIN
MSP_OUTLINE_CODES C ON f.CODE_UID = C.CODE_UID
WHERE (C.OC_FIELD_ID = 188744590) AND (C.PROJ_ID = 474) AND (C.OC_PARENT
= 16 OR
C.OC_PARENT = 14) AND (f.CODE_REF_UID = 0)
ORDER BY P.PROJ_ID
The two temp tables are being created, I can pull from them individualy and
with other types of joins(that still have the same issues as before).
The other option was to try to create the tables using varchar or nvarchar.
Something like:
create table #Status_data (
Proj_ID int,
OC_NAME nvarchar(500),
PROJ_NAME nvarchar(500))
declare type_csr cursor for
SELECT P.PROJ_NAME as Proj_name, OC_NAME as OC_name, P.PROJ_ID
FROM MSP_PROJECTS P INNER JOIN
MSP_CODE_FIELDS f ON P.PROJ_ID = f.PROJ_ID INNER JOIN
MSP_OUTLINE_CODES C ON f.CODE_UID = C.CODE_UID
WHERE (C.OC_FIELD_ID = 188744590) AND (C.PROJ_ID = 474) AND (C.OC_PARENT
= 16 OR
C.OC_PARENT = 14) AND (f.CODE_REF_UID = 0)
ORDER BY P.PROJ_ID
open type_csr
fetch next from type_csr into @Proj_name, @OC_Name,@proj_id
while @@fetch_status = 0
begin
INSERT INTO #type_data values(@Proj_id, @OC_Name, @Proj_name)
fetch next from type_csr into @Proj_name, @OC_Name,@proj_id
end
close type_csr
deallocate type_csr
But SQL wont allow me to change from text to varchar/nvarchar.
Cant use any string functions on text data type either. Left(PROJ_NAME,
500).
Both OC_NAME and PROJ_NAME are text data type.
I am doing all of this in the Query Analyzer, trying to create a stored
procedure.
Any ideas
Thanks
Rodney
-----Original Message-----
From: Jochem van Dieten [mailto:[EMAIL PROTECTED]
Sent: Wednesday, March 10, 2004 9:03 AM
To: CF-Talk
Subject: RE: SQL Help
Bruce, Rodney S HQISEC/Veridian IT Services wrote:
>
> your suggested query below returns 0 records, becuase the field will
> never contain all three at the same time in the same record.
That is not the test being done.
> UID Name Description Parent Proj_ID
> 1 Status current status of project 0 0
> 2 Initializing Project is starting 1 100
> 3 Engineering Project in Eng phase 1 100
> 4 Type Who is doing Proj 0 0
> 5 Contr Contracted out 4 100
> 6 Govt Govt ran 4 100
> 7 Condition Proj open or close 0 0
> 8 Open Proj is open 7 100
> 9 closed Proj is closed 7 100
>
> So if you ask for name to be LIKE Init and Contr and Open, 0 records
> returned
>
> I can get the three records by going for project ID, but have not had
> a chance to run thru the suggections given by others.
> Just have tried your suggections already so know what happens.
WFM even with a subset of the data:
jochemd=> begin;
BEGIN
jochemd=> create table example (uid int, name text, description text,
parent int, proj_id int);
CREATE TABLE
jochemd=>
jochemd=> insert into example values (3, 'Engineering', 'Project in
Eng phase', 1, 100);
INSERT 6060153 1
jochemd=> insert into example values (6, 'Govt', 'Govt ran', 4, 100);
INSERT 6060154 1
jochemd=> insert into example values (8, 'Open', 'Proj is open', 7, 100);
INSERT 6060155 1
jochemd=>
jochemd=> SELECT
jochemd-> proj_ID
jochemd-> FROM
jochemd-> Example T1 INNER JOIN
jochemd-> Example T2 USING (proj_id) INNER JOIN
jochemd-> Example T3 USING (proj_id)
jochemd-> WHERE
jochemd-> T1.Name LIKE '%Open%'
jochemd-> AND
jochemd-> T2.Name LIKE '%Engineering%'
jochemd-> AND
jochemd-> T3.Name LIKE '%Govt%';
proj_id
---------
100
(1 row)
jochemd=> rollback;
ROLLBACK
jochemd=>
Are you sure your database understands USING correctly?
Jochem
--
I don't get it
immigrants don't work
and steal our jobs
- Loesje
_____
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
