How to convert the SQL into a Cognos report (very urgent)

Venkata Guntupalli said: Hi,I have to convert the given SQL into cognos report without changing any of the functionality included in SQL. The SQL looks like having involved with Subquery and filters on indivual columns. The report will need a date range prompt. In the attached SQL, I have hardcoded ...

Framework Manager - Subquery alternative due to bug...

I have a need to pre-filter a Query Subject in my Presentation layer using a Subquery...SQL example (simplified):Select from ALL_ASSIGNMENTS Where EFFECTIVE_END_DATE = ( Select Max(ALL_ASSIGNMENTS_1.EFFECTIVE_END_DATE) From ALL_ASSIGNMENTS ALL_ASSIGNMENTS_1 Where ALL_ASSIGNMENTS.PERSON_ID = ALL_ASSI ...

MySQL subquery to join

Is there any way to convert this subquery to a join? Does it even work in MySQL? or might be wrong to start of with :) SELECT p.pollID, p.thepoll, a.answerID, a.answer FROM polls AS p, pollAnswer AS a WHERE p.pollID = SELECT MAX(pollID) FROM polls; What I want to do is to get the last inserted row a ...

MySQL Rewriting a subquery

I have to move my site to another host that doesn't support subquery (v4.0). I'm having a problem with rewriting a subquery, and I'm wondering if there's any workaround for this problem. I'd really appreciate any helps. Say I have a category table with 2 fields (catID, and catName): A a B b C c D d ...

MySQL SELECT with subquery

Please point me where is the SQL syntax error here: SELECT date FROM `cart` WHERE id = (SELECT max(id) FROM `cart` WHERE session = "0e74401d62ad0c25dce17e17726b74cf"); Separately both querys work, if i substitute the subquery with it's result (id=34). Dmitri/dts ...

MySQL subquery memory management

How is memory managed for subqueries? Are the temporary tables created from a subquery loaded into memory in any way? For example: <code> SELECT * FROM ( SELECT * FROM super_big_table ) AS too_big_table LIMIT 1 </code> Will this code load all of super_big_table into memory, or is MySQL s ...

MySQL Using subquery IN

Hi, I am using MySQL 4.0.23_Debian-7 and I try to use subquery with "IN" E.g. SELECT s1 FROM t1 WHERE s1 = ANY (SELECT s1 FROM t2); SELECT s1 FROM t1 WHERE s1 IN (SELECT s1 FROM t2); from the manual Both of these types of queries return this error message: "You have an error in your S ...

MySQL Subquery pb....

I have a probleme with a very simple subquery: SELECT a, b, c, FROM ( SELECT role AS a, otherAttr AS b, COUNT( * ) AS c FROM `target` GROUP BY role, otherAttr ) AS test WHERE c >19 (Actually, what I want it to show the results of the count over 19) Can somebody told me why it's not working ? I ha ...

FETCH FIRST ROW ONLY not working in subquery

Any ideas why this works:SELECT NETINT.NETWORKIDFROM MSTRESRC.NETWORKINTERFACES AS NETINTLEFT OUTER JOIN MSTRESRC.NETWORKS AS NETWORK ON NETWORK.NETWORKID=NETINT.NETWORKIDWHERE NETINT.NODEID=8713ORDER BY NETWORK.NETMASK FETCH FIRST ROW ONLYbut this does not:SELECT HOSTNAMEFROM MSTRESRC.NETWORKINTERF ...

MySQL Select subquery

I am having difficulty in the following subquery. I keep getting the syntax error at 'SELECT count(*)'. SELECT ID, organization, (SELECT count(*) FROM Invoices WHERE paid = 'N') AS paid FROM clients What I am trying to do is to get a list of clients and also count any unpaid invoices. ...

db2 subquery

i'm having problems executing a db2 query in my IBM DB2 command center v7.i'm trying to execute a select statement with a subquery. i constructed it like this:SELECT DISTINCTCOMPUTER_SYS_ID,(Select MANUFACTURER from PROCESSOR_DATA_VIEW where PROCESSOR_NUM=1 AND COMPUTER_SYS_ID = T1.COMPUTER_SYS_ID F ...

MySQL I do not obtain to make a SUBQUERY

It is the following one... I do not know if it is the version of mysql, but I do not obtain to make subquery's Help Please!!!! ...

MySQL Problem of selection when not using a subquery

System: MySQL 4.0.25 I have 2 tables: table1: name A B C table2: name1 name2 A Z B A A C I want to select all lines from table2 where name1 and name2 are in table1. Then, in my example, the last two lines of table2 would be selected. What is the corresponding SELECT instruction to make this without ...

MySQL max() function, Group By and Subquery

Hello, I am working with a website that I am creating using mySQL and PHP. My issue involves a subquery along with the max() function. So, if you have experience with those, please read on ... I would very much like to solve this query, I've been working long and hard on this. Here is the Database i ...

MySQL AVG() function and Subquery

Hello, I am studying mySQL and now I am practicing with the subquerys on examples of Data bases. My issue involves a subquery along with the AVG() function. I've been working on this, but I canĀ“t resolve it. Here is the information: Table Employees with the fields/columns: EmployeeID (The PK), Last ...

MySQL subquery and sum()

SELECT domainTable.name as domain, sum(NetworkTraffic.upload) as Upload, sum(NetworkTraffic.download) as Download, sum(NetworkTraffic.upload) as Upload2 sum(NetworkTraffic.download) as Download2 FROM `NetworkTraffic` LEFT JOIN domainTable ON domainTable.domainId = NetworkTraffic.domainId WHERE Netwo ...

Cognos 7.3 / DB2 cube disable

We have recently upgraded from Cognos 7.1 to 7.3 for use with DB2 and upon finalising installation, have discovered that the PPAdminTool.exe program which was originally included in 7.1, is not included in the 7.3 install.We require the PPAdminTool.exe to disable cognos cubes whilst refreshing them. ...

in mysql 5.0.15 concat_ws gives: ERROR 1242 (21000): Subquery returns more than 1 row

I am having a trouble with concat_ws. Consider the following example (table type is innoDB): CREATE TABLE person ( id INTEGER(3) AUTO_INCREMENT, name VARCHAR(10), CONSTRAINT pk_person PRIMARY KEY (id) ); CREATE TABLE telephone ( telephone_id CHAR(6), tel_no CHAR(7), CONSTRAINT pk_telephone PRIMARY K ...

Problem with subquery on MySQL 4.0.23

I have a server using MySQL 4.0.23 (and no I can't upgrade it). I am trying to write some code for a report that I need and I'm having a syntax problem (at least thats what the server tells me) with a simple subquery using the "IN" operator. Below is am example... select Id from tempSTR wh ...

MySQL How to rewrite a Subquery that contains a LIMIT statement?

can anyone help me to rewrite the query below. If I run it as it is I get the following error: ""1235 This version of mysql doesn't yet support 'Limit & IN/ALL/ANY/SOME subquery". The problem is the LIMIT statement in the subquery. However, I don't have a clue how to do the intend ...

MySQL table subquery

I have trouble making the following "table subquery" work. When run alone, the inner query, returns 25 results (each consisting of disciplinid and minimum score) as expected. When the entire query is run, however, only seven results are returned. This is quite weird, since the inner query ...

MySQL Subquery Error

I have 2 tables an item table and a stones table: CREATE TABLE items ( id int(11) NOT NULL auto_increment, sold int(1) NOT NULL default '0', stocknum varchar(10) default NULL, name text, type text, image text, description text, comments text, retailPrice float(5,2) NOT NULL default '0.00', tagPrice ...

MySQL Subquery problem

I trying to use a simple subquery to find out the highest primary key in a table and print all of the results other than that one, but I keep getting a syntax error related to the subquery and I have no Idea why... Does anybody know what I'm doing wrong? Here is the code: SELECT * FROM sm_articles W ...

MySQL Subquery result returning many rows as single string

Is it possible to return multiple rows from a select statement as a single string? Example: SELECT item, (some subquery) as transaction, price from myTable Desired result: item transaction price 'cd gem case' '00167,00174,00211' .38 The transaction field would represent multiple values returned by t ...

Cognos Impromptu-Catalog Creation

HiI want to use Cognos Impromptu.I have query about creating Catalogs in impromptu.Is it possible to create Database in impromptu using two databases from MS SQL database.For example I want to create database in Impromptu with master and msdb fro MSSQL so I can create report with data from master an ...

MySQL Using joins to substitute for a subquery

I was knocking my head on this subquery syntax for an hour trying to figure out why it's returning a syntax error: SELECT productid, product FROM products WHERE productid NOT IN (SELECT productid FROM order_details WHERE orderid=824790); Then, quite suddenly, I discovered that the system I'm trying ...

MySQL DELETE with subquery

I have situation similar to one I saw posted here. He didn't get answer, he had to write app code to do what he wants I think my answer is the same... :( Here is the situation I inherit this dataset and it is not scaling well as the data tables grows. We have people and we have a large selection of ...

MySQL DELETE and subquery

Here is my DELETE statement that should work but doesn't due to "Currently, you cannot delete from a table and select from the same table in a subquery. " DELETE FROM history WHERE eventTime = (SELECT MIN(eventTime) from history) I found information about selecting the max with a subquery ...

MySQL Need help to convert a sub-query to a non-sub-subquery

I have a table CREATE TABLE `Pricing` ( `Size` smallint(6) NOT NULL default '0', `Qty` int(11) NOT NULL default '0', `Single` double NOT NULL default '0', `Additional` double NOT NULL default '0', KEY `Size` (`Size`,`Qty`) ) TYPE=MyISAM; The following sub-query does not work in MySQL 4.0.25. Can som ...

MySQL delete with subquery

I always get an error with the following delete-constraint (constraint includes three tables LEKTION, SACHVERHALT and MEDIEN_SACHVERHALT): DELETE FROM MEDIEN_SACHVERHALT WHERE sachverhaltKey = (Select t3.sachverhaltKey FROM LEKTION t1, SACHVERHALT t2, MEDIEN_SACHVERHALT t3 WHERE t1.lektionKey = 276 ...

MySQL convert subquery to join

I am having a problem converting this subquery to a join. I want all the rows from t1 that match the substring in t3. SELECT count(*) FROM t1 WHERE t1.a IN (SELECT t2.b FROM t2 WHERE t2.b IN (SELECT t3.b from t3 WHERE MATCH (t3.matchname)AGAINST('a string ' IN BOOLEAN MODE ))); If I write the join t ...

MySQL Subquery too slow - another way with same table search?

I have a table that is called "threads". It contains within it "posts" and some of those posts are categorized as threads by the thrdtype column. This is to notify that that post is the first post in a specific thread and then other posts are attached to it using the unique thrdi ...

MySQL SubQuery not working

Hello, I have a major problem whitch I have to fix ASAP.. Here's the deal: select count(1) from Dossiers d where d.no in( select b.dossier_no from Dossiers dd,dossiers_owner_bridge b, users u where u.id=b.user_id and b.dossier_no=dd.parent and u.username like 'SI%' group by b.dossier_no ) the follow ...

MySQL SELECT subquery CONCAT

SELECT * FROM (SELECT CONCAT(comments.category,'s') FROM comments WHERE comments.author_user_id = '1') The subquery alone yields "post" (`comments.category` is an ENUM() field); and I want to select all the rows from the "posts" database table. Ideally, the query would be process ...

MySQL SELECT CONCAT() Subquery

SELECT * FROM (SELECT CONCAT(comments.category,'s') FROM comments WHERE comments.author_user_id = '1') The subquery alone yields "post" (`comments.category` is an ENUM() field); and I want to select all the rows from the "posts" database table. Ideally, the query would be process ...

Subquery in Update statement

I would like to do an update on a table as below. But i get the error : ERROR: ILLEGAL SYMBOL ",". SOME SYMBOLS THAT...I think i could fix this by putting the subquery into the 'WHERE' clause...but that would mean 4 subqueries: one for each criteria.Is there a easier way to do this ?Thanks ...

MySQL subquery-to-join conversion

Is it possible to transform subqueries in WHERE clasue to joins? I can do it for some class of subqueries but have difficulties, e.g., when there is an aggregation in a subquery. Are there any general procedures to make such a conversion? Thanks in advance for any answers. Piotr Synak Infobright Inc ...

MySQL Subquery (results) into an Array?

Is it possible to place a Subquery (results) into an Array? I still haven't figured out the correct Subquery syntax, but I'm trying to select 10 items from "store" and for each item, generate list of possible product_name matches in "master"; SELECT * FROM store LIMIT 0, 10 SELEC ...

MySQL Need subquery help

Although I am not a newbie, I did not see a forum for help postings. I have a complicated subquery that will not work. The actual subquery works fine on its own, but the entire query will not work. I am using MySQL 4.0.25 on a host server and cannot upgrade my database to a newer version. Here are p ...

SQL/400 Case When Subquery

I have a query that I've been able to do in T-Sql using an 'in' predicate in a Case/When. I modified this to run in SQL/400 by the error is get is:Quote: Simple comparison operators other than equal and not equal cannot be used with a list of items. ANY, ALL, and SOME comparison operators must be fo ...

MySQL Subquery oddities

I think that there may be a problem with subqueries. On an AIX platform running 5.0.19 using the innodb storage engine, explain states that every subquery is dependent, and won't use any indexes, it seems, other than the primary key. Simple cases (made up for test, where I can convert between joins ...

Possible to have a subquery within a JOIN statement?

so i was wondering if there's anyone who can help me sort something out. i have an SQL query that should show the details of a person (a search feature) that has a subquery inside an Inner Join, and the subquery is a Select statement.Currently there's no problem with the sql query, but when i test t ...

MySQL Subquery for duplicates removal

Greetings, I have the following query. It produces some details for a specific dat ($scandat) and for a specific location ($_GET["dep"]) Select scanarch.*, substr(scanid,6)+0 as sort, c.senddep, c.deliverdep, c.docnum from scanarch s,consignments c where (s.scantype = 'O') and s.scandate = ...

MySQL subquery not working

subquery not working for mySQL control center0.9.4 beta. The query is syntactically correct and a simple one. Is it a bug in mySQLcc? ...

optimize correlated subquery for updates

Is it possible to optimize a correlated subquery in case of updation.Is there any type of SQL which can replace these type of SQL.My sql is given belowupdate table_A set (A.col1l, A.col12, A.col3) = ((select B.col1l, B.col12, B.col3 from table_B where B.site_id = A.site_id)) ; [Note there is a index ...

MySQL Using MIN with subquery with joins possible?

I have a query that works: SELECT obiskovalci.uporabnik as uporabnik, obiskovalci.email as emailobiskovalci, recipes.Name, recipes.datum, recipes.kategorija, recipes.ID, rs.sirina, rs.visina, rs.ime, rs.ID as IDslike, recipes.podkategorija, recepti.clan, MIN(rs.sortiranje) as slika FROM recipes FORC ...

MySQL Need distinct in join (SubQuery?)

am stuck again in need to do a distinct for two fields on the first inner join something like distinct a.urlsite , a.occured how can i do this , do i need to do a subquery SELECT u.name, DATE_FORMAT(a.occured, '%Y-%M-%D'), c.category, count(a.user) AS hits FROM boxlogs.users AS u INNER JOIN boxlogs. ...

MySQL Join/subquery Question

I get the results I wish from each of these queries. I would like to make the 2 queries one. I have tried subquery and several joins. I can't get seem to fine the correct syntax. SELECT * FROM CourseSchedule, Days WHERE CS_Campus = "Lowell" AND CS_Year = "2007" AND CS_Season = ...

SQL Update statement with subquery join

Greetings,I am attempting what I believe to be a very basic operation. I am trying to do a table update using a subquery that contains a join operation.For your information I am running:IBM Informix IDS 11.10 Developer EditionRed Hat Enterprise Linux 4Before writing the SQL statement I consulted the ...

MySQL Limit in subquery?

Hi...not sure where to post this question. Is there any version of MySQL that will support Limits within a subquery? I am using 5.0. I tried to run this query: SELECT Count(b.SessionID) as RecordCount FROM reports.Views c JOIN reports.Sessions b ON c.SessionID = b.SessionID RIGHT JOIN reports.ViewTy ...