Newbie - Parsing Error

[vharcourt]

I'm new to Cognos and have been using it for about 1 week. I have made some simple reports without issue. Now I have a more complex SQL to convert from an ACE report to a Cognos report and having a bit of trouble. I don't want anyone to do the whole thing for me so I'll just provide the first snippet of it and see if it can kick start me into the right direction.

This first snippet is generating a list of IDs for full time employees and adjuncts for the current semester.

Code Select Expand


SELECT unique id FROM emp_rec
WHERE bus_id = "12345"
AND (end_date IS NULL OR end_date = "  " OR end_date > today)
AND pos_level IN ("ADMS", "STAF", "FAC", "COAC")
INTO temp fac_staff with no log;

SELECT id FROM emp_rec
WHERE bus_id = "12345"
AND (end_date IS NULL OR end_date = "  " OR end_date > today) AND pos_level = "ADJ"
INTO temp adj with no log;

SELECT unique adj.id empid
FROM adj, sec_rec
WHERE adj.id = sec_rec.fac_id
AND sess = "FA" AND yr = 2016 AND reg_num > 0

UNION

SELECT unique fac_staff.id empid
FROM fac_staff
INTO temp all_emp with no log;


I have created a page with 2 lists for the first 2 SQLs without issue. As soon as I try to add filters using the 3rd SQL I start getting parsing errors. I have tried using joins, unions, and intersects with not luck. I have removed the 'end_date = "  " ' as it doesn't impact the output. I have made it to where the only thing in the query is the ID and proper filters. I'm also not sure how to get around the temp tables being used. Its my understanding that you have to create stored procedures in the FM.

Any help is appreciated.

[bdbits]

If you're using SQL as the basis for your reports instead of a package, you're doing it wrong. Seriously.

Are you also responsible for the Framework Manager models? If not, you should have a visit with them. If you are, you should crack open the FM user guide and dig in.

If you are the one and only Cognos person in your organization, you either need to get at least some basic training, or spend some time in the user guides to understand how Cognos works and is designed to be used.

[vharcourt]

I only setup the lists based off the first two SQLs to validate data. I am using a package that was already setup but do have access to FM. Basically there are multiple unions in the SQL that I'm converting and was look for a push in the right direction for combining the result set for this first union. I figured that would trigger the "oh yeah" and be able to take it from there. I thank you for any assistance you could provide.

[HalfBloodPrince]

Not much clear abt your requirement. But Try this
create two sql in cognos as below

SQL 1:
SELECT   unique id FROM   emp_rec
WHERE   bus_id = "12345"
   AND (end_date IS NULL OR end_date = "  " OR end_date > today)
   AND pos_level IN ("ADMS", "STAF", "FAC", "COAC")

SQL 2 :
SELECT   id FROM   emp_rec
WHERE   bus_id = "12345"
   AND (end_date IS NULL OR end_date = "  " OR end_date > today) AND pos_level = "ADJ"

Then drag drop Join/Union  operation in Report studio and create join /union as per ur requirement using above two queries as source.

[vharcourt]

I understand what you are trying to say. But the second SQL is filtered by the 3rd SQL before the union is made. Because it goes from using id_rec.id to sec_rec it makes the column not the same and therefore the union doesn't appear to work.