Problem with nested query

[idd]

Hi I am using Crystal reports on SQL Server 2000

Any help would appreciated, still a newbie.

I wrote the following code to present data about laptops booked and in which rooms they are booked.
The error I am getting is

Failed to open a rowset

The column prefix ‘P2’ does not match with a table name or alias name used in the query.


Activity table holds information about events. Product table holds info about many things. One of these are laptops another thing is that it holds info about rooms as well.

There is no clear distinction between what is categorised as a laptop other than

Product.Prod_type = ‘IT Equipment’

And product.Prod_Name like ‘%laptop%’

As the latops would be named with no naming convention but would have the word laptop amongst the name.

The table are:

Product --- Activity_Xref --- Activity --- Activity_Xref2 --- Product2

Activity_Xref and Product table are joined twice in the query.

The reason for aliasing and joining the activity_Xref table and the Products table is because the report needs to identify which laptops are booked and in which rooms they will be used or if used externally then the room column will be null most likely.

The room column is Product.prod_name which would also be the field used for the laptop name.


Does this make sense so far.

The code is given below.

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

Select P.prod_name
, P.Prod_type
, P2.Prod_name
, act.act_id
, act.act_id
, act.act_session_name
, act.act_action_date
, act.act_time
, act.act_end_date
, act.act_end_time

From (Activity as act inner join Activity_Xref
on act.act_Id = activity_Xref.ActXref_Act_ID)

Inner Join Product as p
on Activity_Xref.ActXref_Prod_Id = p.prod_id

Where P.prod_Type <> 'IT Equipment'
AND (P.PROD_NAME IS NULL OR (P.PROD_NAME='1 - Room A' OR P.PROD_NAME='2 - Room B' OR P.PROD_NAME='3 - Conference Room' OR P.PROD_NAME='4 - Training Room' OR P.PROD_NAME='5 - Lecture Theatre' OR P.PROD_NAME='6 - Library' OR P.PROD_NAME='7 - Commercial Affairs meeting room' OR P.PROD_NAME='8 - Training Room by Human Resources'))
and p2.prod_name in

(SELECT P2.Prod_Name From (Activity as activity2 Inner Join Activity_Xref as actX
on activity2.act_id = actX.ActXref_Act_Id)
Inner Join Product as P2 ON P2.Prod_Id = actX.ActXref_Prod_ID

Where P2.prod_Type = 'IT Equipment'
and p2.Prod_name like '%Laptop%')

ORDER BY act.act_id, act.act_action_date

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


Thanks in advance

Idd

 

[bborissov]

You can't use field from derived table in WHERE clause. You could only use fields from tables that are in FROM or JOIN sections.
Also PLEASE round your code portion with
[ code ] and [ /code ] tags (w/o spaces) that would make code easier to read.
BTW is this works:

Select P.prod_name
     , P.Prod_type
     , P.Prod_name
     , act.act_id
     , act.act_id
     , act.act_session_name
     , act.act_action_date
     , act.act_time
     , act.act_end_date
     , act.act_end_time
From Activity as act
     inner join Activity_Xref on act.act_Id = activity_Xref.ActXref_Act_ID
     Inner Join Product as p  on Activity_Xref.ActXref_Prod_Id = p.prod_id

Where (P.prod_Type <> 'IT Equipment'
     AND (P.PROD_NAME IS  NULL  OR 
         (LEFT(P.PROD_NAME,1) < '9'))
     OR 
     (P.prod_Type = 'IT Equipment' AND
      p.Prod_name like '%Laptop%')

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[idd]

BBorissov

Thanks for the advice and taking the time to help me.

I alway wondered how to make my code appear like that in Tek-tips.

At least thats one thing great I've learned already.

I see how you have used the Left(p.Prod_name,1) < '9')

however will this show the laptops names as well as the rooms that they have been booked in.

This was why I put in the subquery, I will try your advice now though and feedback.

Idd

 

[bborissov]

Idd,
can you post some example data from these 3 tables and desired result? I ask because I didn't understand how these tables are related and how you know which laptop in what room is it. maybe there is easier way to accomplish this

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[idd]

BBorissov

Below I give some sample data.

Activity Table data will be shown in green

Act_id Act_Action_Date Act_Action_Time Act_End_Date Act_End_Time Act_Session_Name

1 12/12/02 09:00 15/12/02 17:00 getting on brilliantly
2 13/12/02 09:00 16/12/02 18:00 how to win games
3 14/12/02 09:00 15/12/02 12:00 games consoles



Activity_Xref Table data shown in blue
two foreign keys in here are ActXref_act_id which links to the activity tables act_id key
second foreign key is Actxref_Prod_id whic links to the Product tables Prod_ID key field.

This table is just a linking table (Cross reference)

Actxref_id Act_xRef_Act_Id ActXref_Prod_id
23 1 20
23 1 1
32 2 22
32 2 3
34 3 20


Product Table holds info about all resources
will be red or purple
Prod_id Prod_name Prod_type Category
1 Conference Room Resources Cat1
2 Lecture Room Resources Cat1
3 3M Projector Resources Cat2
20 Laptop 1 IT Equipment Cat 1
22 HP Laptop IT Equipment Cat 1
23 Screen IT Equipment Cat 1


I need to be able to report on which laptops are booked and in which rooms they are booked.

Some laptop bookings will not have room bookings so the results might look like this

Act_ID Session_Name Prod_Name Prod_Name2
1 getting on Laptop 1 Conference room
2 How to win games HP Laptop 3m Projector
3 Games Consoles Laptop 1

Thanks once again for looking into this problem.

 

[bborissov]

Is that what you want?

---- Preparing test data. You don;t need this part
---- Just replace temporary table names with
---- actual names from your database
DECLARE @Activity      TABLE (Act_Id int, Act_Session_Name varchar(500))
DECLARE @Activity_Xref TABLE (AcActxref_id int, Act_xRef_Act_Id int, ActXref_Prod_id int)
DECLARE @Product       TABLE (Prod_id int, Prod_name varchar(200), Prod_type varchar(200), Category varchar(5))

INSERT INTO @Activity  VALUES(1,'getting on brilliantly')
INSERT INTO @Activity  VALUES(2,'how to win games')
INSERT INTO @Activity  VALUES(3,'games consoles')

INSERT INTO @Activity_Xref VALUES(23,1,20)
INSERT INTO @Activity_Xref VALUES(23,1,1)
INSERT INTO @Activity_Xref VALUES(32,2,22)
INSERT INTO @Activity_Xref VALUES(32,2,3)
INSERT INTO @Activity_Xref VALUES(34,3,20)

INSERT INTO @Product VALUES(1,'Conference Room','Resources','Cat1')
INSERT INTO @Product VALUES(2,'Lecture Room','Resources','Cat1')
INSERT INTO @Product VALUES(3,'3M Projector','Resources','Cat2')

INSERT INTO @Product VALUES(20,'Laptop 1','IT Equipment','Cat1')
INSERT INTO @Product VALUES(22,'HP Laptop','IT Equipment','Cat1')
INSERT INTO @Product VALUES(23,'Screen','IT Equipment','Cat1')

---- END



Select Activity.Act_Id
     , Activity.Act_Session_Name
     , MAX(ISNULL(Prod1.Prod_Name,'')) AS Prod_Name1
     , MAX(ISNULL(Prod2.Prod_Name,'')) AS Prod_Name2
From @Activity Activity
     inner join @Activity_Xref Activity_Xref on Activity.act_Id = Activity_Xref.Act_xRef_Act_Id
     LEFT JOIN  @Product as Prod1 on Activity_Xref.ActXref_Prod_Id = Prod1.prod_id AND Prod1.Prod_Type = 'IT Equipment' AND Prod1.Prod_Name LIKE '%laptop%'
     LEFT JOIN  @Product as Prod2 on Activity_Xref.ActXref_Prod_Id = Prod2.prod_id AND Prod2.Prod_Type <> 'IT Equipment'
GROUP BY Activity.Act_Id, Activity.Act_Session_Name

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[idd]

BBorisov,

Hi sorry for my response taking a while, I was away from my desk all morning.

Thanks for you time and assistance. The code you supplied seems to brings back all activities

and seems to bring back

1) activities with laptop bookings
2) activities with room bookings
3) activities with no laptop or room bookings

There is a fourth possibility which it doesn't seem to bring back that is

4)Activities with laptop and room bookings.

From those possibe outcomes i need the ones numbered
1) and 4)

I do not want 2) and 3)

The code you gave which I am using is

Select Activity.Act_Id
     , Activity.Act_Session_Name
  , MAX(ISNULL(Prod1.Prod_Name,'')) AS Prod_Name1
     , MAX(ISNULL(Prod2.Prod_Name,'')) AS Prod_Name2
From Activity Activity inner join Activity_Xref 

on Activity.act_Id = Activity_Xref.ActXRef_Act_Id
     
LEFT JOIN  Product as Prod1 on Activity_Xref.ActXref_Prod_Id = Prod1.prod_id 
AND Prod1.Prod_Type = 'IT Equipment' AND Prod1.Prod_Name LIKE '%laptop%'
     
LEFT JOIN  Product as Prod2 on Activity_Xref.ActXref_Prod_Id = Prod2.prod_id AND Prod2.Prod_Type = 'Resources' and Prod2.Prod_name <> 'Place Settings' and Prod2.Prod_category = 'Cat1'

GROUP BY Activity.Act_Id, Activity.Act_Session_Name, prod1.prod_name

Thanks you BBorisov and anyone else who tries to help me with this problem.

 

[idd]

Hi There,

My Mistake, the Activity_Xref table values would have unique Identifiers for each row, not the values that I have displayed above.

I am referring to the field ActXref_ID

the values would not be the same for two records

so it would be more accurate as

Act_Xref_ID
22
23
24
25
26
27

BBorisov

I tried your code with the code for the temp table and it seemed to give the right answers, but when i try it on the actual database it is not giving the expected results.

I mentioned the results I am getting in the previous post.

Idd