Subscribe: MySQL Forums - Views
http://forums.mysql.com/rss.php?100
Added By: Feedage Forager Feedage Grade B rated
Language: English
Tags:
create view  create  customer customer  customer  database  join  mysql  null null  null  select  tables  values  view  views 
Rate this Feed
Rate this feedRate this feedRate this feedRate this feedRate this feed
Rate this feed 1 starRate this feed 2 starRate this feed 3 starRate this feed 4 starRate this feed 5 star

Comments (0)

Feed Details and Statistics Feed Statistics
Preview: MySQL Forums - Views

MySQL Forums - Views



Forum for MySQL Views



Last Build Date: Mon, 23 Apr 2018 14:07:58 +0000

 



decode (no replies)

Sat, 20 Jan 2018 03:37:05 +0000

Help with decoding mes



strange utf8mb4 issue with views (3 replies)

Thu, 19 Oct 2017 21:24:31 +0000

I have a strange problem whereby when creating views, MySQL 5.7 is "automagically" inserting convert() around calls to group_concat and lpad that are parameters to concat - for example:

CREATE VIEW `AccountDTOHelper05EmailByType` AS select `u`.`AccountID` AS `AccountID`,concat(`u`.`Email`,' (',group_concat(`u`.`Type` separator ','),')') AS `Email` from `AccountDTOHelper06EmailUnion` `u` group by `u`.`AccountID`,`u`.`Email`;

If I look at it in HeidiSQL after the statement is executed - I can see that it has been changed to:

CREATE VIEW `AccountDTOHelper05EmailByType` AS select `u`.`AccountID` AS `AccountID`,concat(`u`.`Email`,' (',convert(group_concat(`u`.`Type` separator ',') using utf8mb4),')') AS `Email` from `AccountDTOHelper06EmailUnion` `u` group by `u`.`AccountID`,`u`.`Email`;

Note the convert( using utf8mb4) that got added around the group_concat.

The only problem with this change is that it causes problems when doing certain types of queries against that column - namely the dreaded:

Illegal mix of collations (utf8mb4_bin,NONE) and (utf8_general_ci,COERCIBLE) for operation 'like'

My server and db are setup to handle utf8mb4 correctly I believe. Further, if I alter the view in HeidiSQL back to the original (i.e. remove the convert() part) - the change sticks, the view works, and the illegal mix of collations error goes away.

Anyone seen this before and know the fix. Given the fact that HeidiSQL can successfully alter the view to be like I originally had it - I'm thinking it must be setting something I'm not setting when I programmatically execute the create on all my DTO views.



What is the difference between PHP and CakePHP Development (1 reply)

Fri, 28 Jul 2017 15:49:59 +0000

Tell me What is the difference between PHP and CakePHP Development



combing tables which all have the same fields (3 replies)

Tue, 25 Jul 2017 08:32:50 +0000

I would like to add lots of tables which have the same fields and index together to create a new table with all the fields combined together with new names associated with the table from which they came. I understand that Full Outer Join is problematic in MYSQL and wondered if there was a quicker way as all the tables are the same length and structure. Is it possible?

Table 1
Index A B C
1 1 6 3
2 3 2 7
...

Table 2
Index A B C
1 3 5 5
2 6 4 5
...

Table 3
...

View/Table/Output
Index A1 B1 C1 A2 B2 C2 ...
1 1 6 3 3 5 5 ...
2 3 2 7 6 4 5 ...
...


What is the best of doing this?



Comments and sql formatting in views (1 reply)

Tue, 27 Jun 2017 13:23:57 +0000

Hi,

This is really odd.

I have a few servers running different version of MySQL (5.4, 5.5., 5.6, 5.7...).
When creating a view like the one below in all servers some of them change the SQL query completely and take the comments out...but some others keep the format and the comments. I have checked everything, version of MySQL, my.conf...but I cannot find what the option is to keep or not the format and comments in.

/*
testing
*/
SELECT * FROM table1

=> changed to

SELECT 'table1'.'field1','table1','field2' from 'table1'

Thanks



Creating Views problem in 5.6/5.7 (3 replies)

Fri, 23 Jun 2017 00:04:27 +0000

Hi,

In mysql 5.5, I have created a view(slickval) that is created based on other two views(bslick,rslick) in the database . This slickval view has 13 columns . It is running fine in this version. I am copying the same view to a database which uses 5.6 version and the view is not working anymore . I am getting time out error. The same thing happens when I use 5.7 version.

In 5.6/5.7 mysql version, in the view if i reduce the number of columns from 13 to 10 the view runs fine. For some reason, it is not able to take 13 columns which is really odd .

Is there a limitation on the number of columns a view can have in 5.6/5.7 ?

Can someone pls assist ?

Thanks,
Kavitha.



Problem Migrate VIEW from MySQL 5.5 to 5.7 (2 replies)

Thu, 08 Jun 2017 14:20:07 +0000

In a server with MySQL 5.5 i have a specific situation:


Server version: 5.5.54 MySQL Community Server (GPL)


> SHOW FULL TABLES IN db WHERE TABLE_TYPE LIKE 'VIEW';
+---------------------+------------+
| Tables_in_proadess2 | Table_type |
+---------------------+------------+
................
| values_gr2 | VIEW |
................



> describe values_gr2;

.................................

| Z13300150000000000000 | float(11,1) | YES | | NULL | |
| Z13300150000000000001 | float(11,1) | YES | | NULL | |
| Z13300150000000000002 | float(11,1) | YES | | NULL | |
| Z13300150000000000003 | float(11,1) | YES | | NULL | |
| Z13300150000000000004 | float(11,1) | YES | | NULL | |
+-----------------------+-------------+------+-----+---------+-------+
4468 rows in set (0.14 sec)


PS: Unfortunately I do not have access to the application code that uses this database. I know that this number of columns is absurd and would probably solve with better data modeling. But this is not a choice for me. it's a legacy application.


I have problems with dump of this database in 5.5.

mysqldump db > db.sql

-- Warning: Creating a stand-in table for view values_gr2 may fail when replaying the dump file produced because of the number of columns exceeding 1000. Exercise caution when replaying the produced dump file.

But this view is used only in read only operations ( select )

If i need to restore i can run the code that generate this view.
Dump and restore tables and run the SQL to create this view.


I try to migrate this VIEW to mysql 5.7 but receive this error

mysql -u root -p db < create-views- values_gr2.sql
ERROR 1117 (HY000) at line 1: Too many columns


The code of the view is something like this.


create VIEW values_gr2 AS
select
.......
values_gr11.A02000030000000000000,
values_gr11.A02000030010800000000,
......
values_gr21.A02000030091100000000,
.
.
.
.
.
from
values_br11, values_br12, values_br21, values_br22
where
values_br11.cod = values_br12.cod and
values_br12.cod = values_br21.cod and
values_gr21.cod = values_gr22.cod
;



Anyone has a suggestion ? its possible to run this view in mysql 5.7 ? Any workaround ?

Regards



Mysql Error on importing View (3 replies)

Mon, 03 Apr 2017 18:33:31 +0000

I'm having problems importing a mysql database. It is generating an error at create view.



Heres the query from the dump file

/*!50001 DROP TABLE `v_categories`*/;
/*!50001 DROP VIEW IF EXISTS `v_categories`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`DBUSERNAME`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `v_categories` AS select `C`.`categoryID` AS `categoryID`,`C`.`mainCategoryID` AS `mainCategoryID`,`C`.`categoryName` AS `categoryName`,`M`.`name` AS

`name`,`C`.`visible` AS `visible`,`M`.`priority` AS `main_priority`,`C`.`Priority` AS `priority` from (`categories` `C` join `categories_main` `M`) where (`M`.`id` =

`C`.`mainCategoryID`) */;

--



How to create a View that hides the tables behind (1 reply)

Thu, 23 Mar 2017 17:35:38 +0000

Hi all

I have already posted this in the forum: Database Design & Modelling. Maybe it fits better here. My problem is described shortly below:

I am working on a logging system, that logs calls between radios. Things like CDRs: who called who at what time, and which organization do they belong to, are logged into the database.


The db contains data for different organizations. As of now each organization has their own database, which works fine also security wise.
Now the database has to meet a new requirement, only one database for several organizations.

To meet this requirement, I want to implement a Role Based Access Control system, and had hoped that 'views' could help me in doing this.
One of Roles could be to access data from only 1 specific organization. The corresponding view 'role0_view' should select only organization=1 records from the 'call' table behind.

Call table
----------
From, To, Organization
222, 211, 1
777, 711, 2


For each role in the system, and I want to create a database user, which is only granted SELECT rights on view, but hides the table behind.

role0_view view
---------------
CREATE VIEW role0_view
AS
SELECT * FROM .call WHERE Organization=1


For User0 I am granting privileges like this:
revoke all privileges, grant option from User0;
grant select on .role0_view to User0 identified by '';

But running the query below as User0, produces an error:
SELECT * FROM .role0_view
Error Code: 1356. View '.role0_view' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them 0.000 sec

Unfortunately I have to grant access to the 'call' table also, to get rid of the error. This is not acceptable because 'User0' then can access all organizations data again.

Are there any smart workarounds/solutions for this?



Maybe I need to emphasize that I want the MySql server to handle the security issues, so that 'organization 0' cannot access 'organization 1' data.

What I want is this:
SELECT * FROM .role0_view
returning 1 row (222, 211, 1), and at the same time
SELECT * FROM .call
to return something like:
SELECT command denied to user 'User0'@'localhost' for table 'call'

Not this:
SELECT * FROM .role0_view
returning 1 row (222, 211, 1),SELECT * FROM .call
SELECT * FROM .call
returns 2 rows at the moment (222, 211, 1), (777, 711, 2)

BTW MySQL server used is the community version 5.6.

Hope you can help solving this issue, or point me in the right direction.

Thanks in advance ;-)

BR Christian



tables in information_schema are views?or temporary tables? (1 reply)

Sun, 19 Feb 2017 04:07:13 +0000

1、show create table information_schema.views indicate this "views" object is a temporary table:
CREATE TEMPORARY TABLE `VIEWS` (````````)
2、the table_type column value from "views" object indicate this is a "system view"?
mysql> select table_type from information_schema.tables where table_name='views'
;
+-------------+
| table_type |
+-------------+
| SYSTEM VIEW |
+-------------+
1 row in set (0.00 sec)

So,what on earth are all the objects in information_schema,if they are views,what are their base tables?if they are temporary tables,how do they gain the metadata that we need?



Error Code: 1045. Access denied for user 'user_name'@'host_ip' wher calling view (1 reply)

Fri, 23 Sep 2016 18:46:10 +0000

Hello,
I am calling a view with the following select command:
SELECT * FROM vTarifasHoteles
WHERE
razon_comercial_rcht LIKE 'Melia Cohiba%'
AND fecha_inicio_fe00 BETWEEN '2016-10-30' AND '2016-11-02'
AND adultos = 2
AND menores_compartiendo = 1

and got the following error message:
Error Code: 1045. Access denied for user 'user_name'@'host_ip'

I am accessing a remote server on a Hosting service from MySQL Workbench, and as user, have all privileges on Database.
I created the view with this same user, but evidently, from a different host_ip, because my ip address is assigned dynamically.
Maybe I need to give user privileges

How can I do to avoid host check when executing views?



Read Only View (1 reply)

Wed, 13 Jan 2016 03:40:47 +0000

I am attempting to create read-only views. Currently, I am using algorithm=temptable to force my views to be read-only.

Is this the best practice for read-only views?



Flexviews (no replies)

Wed, 01 Jul 2015 12:33:08 +0000

Hi All,

Flexviews is the similiar concept used in the oracle(materialized view).Did any one of you using the flexviews in live production.Need to know how stable and effective it works?



SELECT from view crashes (4 replies)

Tue, 19 May 2015 17:27:51 +0000

Hi everyone,
today I ran a SELECT which leads MySQL cpu up to 75% on both Windows and Linux installation, causing the database to become totally locked and without the possibility to STOP the running process even after closing the calling thread ... I have to force the MySQL shutdown!!

Basically, what I have done is a SELECT which made use of both VIEWS and TABLES.
Something similar to the following:
SELECT V1.FIELD_1, T1.FIELD_2, T3.FIELD_3, T4.FIELD_4
FROM database1.VIEW1 AS V1
INNER JOIN database2.TABLE_2 AS T2 ON T2.ID = V1.ID_2
INNER JOIN database2.TABLE_3 AS T3 ON T3.ID = V1.ID_3
INNER JOIN database2.TABLE_4 AS T4 ON T4.ID = V1.ID_4
...

Before going on in deep, I'd like to know if "theorically" the mix of VIEWS/TABLES on a SELECT statement is admitted; if not, I'd like to know what are the limits.

Many thanks in advance.
Regards



MySql “view”, “prepared statement” and “Prepared statement needs to be re-prepared” (2 replies)

Wed, 08 Apr 2015 04:04:07 +0000

Hi..

I've a problem with MySql, specially with View and Prepared Statement, here is the details :
I created a new schema/database, then in PHPMyAdmin I executed (only) this queries :

create table mytable (
id varchar(50) not null,
name varchar(50) null default '',
primary key (id));

create view myview as
select id,name from mytable;

insert into mytable values ('1','aaa');
insert into mytable values ('2','bbb');
insert into mytable values ('3','ccc');

and then, if I run these queries :

select * from mytable;
select * from myview;
prepare cmd from 'select id,name from mytable where id=?';
set @param1 = '2';
execute cmd using @param1;
deallocate prepare cmd;

the queries give the correct result (3 rows,3 rows,1 row).

but, the problem exists if I run this query:

prepare cmd from 'select id,name from myview where id=?';
set @param1 = '2';
execute cmd using @param1;
deallocate prepare cmd;

ERROR: #1615 - Prepared statement needs to be re-prepared

I've done some research and found that the increment of configurations below "may" solve the problem :

increase table_open_cache_instances value
increase table_open_cache value
increase table_definition_cache value

As far as I know, the queries above are the common and standard MySql queries, so I think there is no problem with the syntax.

I'm on a "shared webhosting" and using MySql version is 5.6.22

But the things that make me confused is, it only contain 1 schema/database, with 1 table with 3 short records and 1 view, and I executed a common and standard MySql select query, does the increment of values above really needed? is there anyone with the same problem had increase the values and really solve the problem? or, perhaps do you have any other solution which you think may or will works to solve this problem?

It does not happen once or twice in a day (which assumed caused by some backup or related), but in all day (24 hours).

Below is some values in the environment status :

show status;
Com_stmt_prepare 0
Com_stmt_reprepare 0
Com_stmt_close 0
Com_stmt_reset 0
Com_stmt_execute 0
Com_stmt_fetch 0
Com_dealloc_sql 0

show global status;
Com_stmt_prepare 21538
Com_stmt_reprepare 222
Com_stmt_close 21194
Com_stmt_reset 11836
Com_stmt_fetch 0
Com_stmt_execute 21529
Com_dealloc_sql 236

To MySql DbAdmin experts, please give some opinions about the values above.

ps: If you find the exactly same post in an another forum, it is the same, and I 'will' post the final solution in both forum if I found the solution (so it can usefull to anyone who visit any of the forums).

Thank you.



which place does mysql view store a data? (1 reply)

Tue, 07 Apr 2015 14:56:03 +0000

Hi,

which place does mysql view store a data?
In memory or temporary tables on disk ?

If the result would be stored in memory. How can mysql control the big data over size in memory?



View with an outer join (3 replies)

Sat, 07 Mar 2015 21:32:33 +0000

Because I can't do subqueries in the from statement, I have created two views. Using an outer join, I am able to create a selection statement and get the results I want. After creating a view using that statement, I get an error when querying the final view.

Error: Error formatting SQL query: empty string given as argument for ! character

Using a CASE statement, I have set a value for when there is not a record in one table. Runs fine as a select statement, but not as a view.

Here is the code.
create view member_acct_balance as
select b.*,
case when p.total_payments is null then 0 else p.total_payments end as total_payments,
case when p.member_id is null then 0 else b.total_billings-p.total_payments end as acct_balance
from member_billing_history b left outer join member_payment_history p
on p.member_id = b.member_id
and p.payment_year = b.billing_year

Any help will be welcomed. I am experienced Sybase developer and this is my first project in MySQL.



SQL View to MySQL view (1 reply)

Mon, 02 Feb 2015 19:26:18 +0000

When I run the WB Migration all my views (and stored procedures) are commented out and not import. I've tried modifying and manually importing a view, but can't seem to get the syntax right. Can someone help me with the MySQL equivalent code of a view and I can probably take it from there. I suspect it's that "*" on line 5, what does mySQL use instead?

Thanks.

--------------
CREATE OR REPLACE VIEW dbo.vw_PIFS

AS

SELECT TOP (100) PERCENT p.LastName, p.FirstName, l.Description, c.StartDate, c.EndDate, c.BasePrice * c.NumMonths AS ProgramCost, c.BasePrice,

c.NumMonths

FROM dbo.tbl_ContractAgreements AS c INNER JOIN

dbo.tbl_Students AS s ON c.StudentID = s.StudentID INNER JOIN

dbo.tbl_Persons AS p ON s.PersonID = p.PersonID INNER JOIN

dbo.tbl_Location AS l ON c.LocationID = l.LocationID

WHERE (c.PaymentPlanID = 1) AND (c.AgreementStatusID IN (3, 6, 9, 10))

ORDER BY l.Description, p.LastName, p.FirstName



;

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'p.LastName, p.FirstName, l.Description, c.StartDate, c.EndDate, c.BasePrice * c.' at line 5



insert into view derived from table with discriminator (2 replies)

Wed, 04 Jun 2014 03:23:37 +0000

Hi everybody,

I am in a situation in which I have a base table with some fields and a discriminator field which could have one out of a specific set of values.

From that base table, I have derived many views, one for each possible discriminator value, but such views do not include the discriminator column themselves.

The problem is, when I try to INSERT INTO one of the view, the insert succeedes, but the discriminator field in the base table is NULL.

Isn't there a way for me to insert in the view while having the discriminator value correctly set? Or do I need necessarily to insert in the base table instead?



Workaround for subquery limitation in in view creation (3 replies)

Tue, 27 May 2014 21:52:13 +0000

Hi All,

I need to formulate the view based on these two tables. Firt table's unique key will be having more than one entry in second table. So we need to use the sub query to formulate the unique entry for each resource group entry for that view. But the sub query is not allowed in the from clause of the view.

Could you please share any work around to create the view for this scenario.
Given my scenario. Student name can have more than one entry in the second table.

StudentName Description
name1 test
name2 test

StudentName Subject Marks
name1 Tamil 80
name1 English 80
name2 Tamil 70
name2 English 89

StudentName Description Subjects Marks
name1 test Tamil,English 80,80
name2 test Tamil,English 70,89

Thanks in advance.



Cannot edit data in view (5 replies)

Wed, 23 Apr 2014 16:10:04 +0000

Hi,
I am having trouble editing data in a view. I am used to using Microsoft Access and am relatively new to MySQL. I have tried this same query in Access and it updates fine, but the view in MySQL is not editable.

The view references 2 tables which are created as follows.

CREATE TABLE `tblSponsors` (
`SponsorID` int(11) unsigned NOT NULL AUTO_INCREMENT,
`SponsorName` varchar(50) DEFAULT NULL,
`SponsorPhoneNo` varchar(45) DEFAULT NULL,
`SponsorLink` varchar(45) DEFAULT NULL,
`SponsorImage` mediumblob,
`SponsorNote` varchar(255) DEFAULT NULL,
`CompetitionYear` smallint(6) DEFAULT NULL,
PRIMARY KEY (`SponsorID`)

CREATE TABLE `tblCompetitionSponsors` (
`CompetitionID` int(11) NOT NULL,
`SponsorIDCS` int(11) NOT NULL,
PRIMARY KEY (`CompetitionID`,`SponsorIDCS`)

VIEW `qryCompetitionSponsors` AS
select
`tblCompetitionSponsors`.`CompetitionID` AS `CompetitionID`,
`tblCompetitionSponsors`.`SponsorIDCS` AS `SponsorIDCS`,
`tblSponsors`.`SponsorID` AS `SponsorID`,
`tblSponsors`.`SponsorName` AS `SponsorName`,
`tblSponsors`.`SponsorPhoneNo` AS `SponsorPhoneNo`,
`tblSponsors`.`SponsorLink` AS `SponsorLink`,
`tblSponsors`.`SponsorImage` AS `SponsorImage`,
`tblSponsors`.`SponsorNote` AS `SponsorNote`,
`tblSponsors`.`CompetitionYear` AS `CompetitionYear`
from
(`tblSponsors`
left join `tblCompetitionSponsors` ON ((`tblSponsors`.`SponsorID` = `tblCompetitionSponsors`.`SponsorIDCS`)))

Can anyone offer a possible solution to make this view editable?

Cheers

Kenny



(reverse) join (6 replies)

Thu, 13 Mar 2014 16:35:59 +0000

here is a table, ServErog (service) wich is releaded to 4 tables ServA, ServB, ServC, ServD (they are different non uniformable services) with servtype (type of service) and with type_id (numeric id from one of the 4 service table)

Structure (simplyficaded):

ServErog

mysql> select * from ServErog
+----+-------+----------+------+
| idSE | servtype | type_id |
+----+-------+----------+------+
| 1 | 1 | 1 |
| 2 | 2 | 1 |
| 3 | 4 | 1 |
| 4 | 3 | 1 |
| 5 | 1 | 2 |
+----+-------+----------+-------+
ServA

mysql> select * from ServA
+----+-------+----------+------+
| idSA | service_code | type |
|+----+-------+----------+------+
| 1 | codice bla | 1 |
| 2 | codice ecc | 1 |
| 3 | bla bla | 1 |
+----+-------+----------+------+
ServB

mysql> select * from ServB
+----+-------+----------+------+
| idSB | service_code | type |
+----+-------+----------+------+
| 1 | codice bla | 2 |
| 2 | codice ecc | 2 |
| 3 | bla bla | 2 |
+----+-------+----------+------+
ServC

mysql> select * from ServC
+----+-------+----------+------+
| idSC | service_code | type |
+----+-------+----------+------+
| 1 | codice bla | 3 |
| 2 | codice ecc | 3 |
| 3 | bla bla | 3 |
+----+-------+----------+------+
ServD

mysql> select * from ServD
+----+-------+----------+------+
| idSA | service_code | type |
+----+-------+----------+------+
| 1 | codice bla | 4 |
| 2 | codice ecc | 4 |
| 3 | bla bla | 4 |
+----+-------+----------+------+
Left Join

Select
ServErog.idSE,
ServErog.servtype,
ServErog.typeid,
ServA.idSA,
ServA.type,
ServB.idSB,
ServB.type,
Serv.idSA,
Serv.type,
ServD.idSA,
ServD.type
From
ServErog
Left Join
ServA On ServErog.servtype = ServA.type And ServA.idSA = ServErog.typeid
Left Join
ServB On ServErog.servtype = ServB.type And ServB.idSB = ServErog.typeid
Left Join
ServC On ServErog.servtype = ServC.type And ServC.idSC = ServErog.typeid
Left Join
ServD On ServErog.servtype = ServD.type And ServD.idSD = ServErog.typeid
Order By
ServErog.idSE

+----+-------+----------+------+------+------+---------+
| idSE | servtype | type_id | idSA | idSB | idSC | idSD|
+----+-------+----------+------+------+------+---------+
| 1 | 1 | 1 | 1 | null | null | null |
| 2 | 2 | 1 | null | 1 | null | null |
| 3 | 4 | 1 | null | null | null | 1 |
| 4 | 3 | 1 | null | null | 1 | null |
| 5 | 1 | 2 | 2 | null | null | null |
+----+-------+----------+------+------+
This retur all records releaded with ServErog. Perfect!

Now I need to show all record from ServA, ServB, ServC, ServD NOT PRESENT in ServErog. Like an inverse the precedent Join.



Stored procedure (1 reply)

Wed, 12 Mar 2014 02:52:18 +0000

I this possible to create stored procedure to force MySQL users to change passwords every 30 days? If yes then need solution.



VIEW cuts off my text-string (1 reply)

Mon, 10 Feb 2014 20:35:47 +0000

Hello I,
I created a view that should list the coordinates of the element as used for sn svg-path-element.

The code is:
CREATE
ALGORITHM = UNDEFINED
DEFINER = `root`@`localhost`
SQL SECURITY DEFINER
VIEW `zeichenkoerper_fullview` AS
select
[...],
group_concat(concat(`k`.`x`, ',', `k`.`y`)
order by `k`.`reihenfolge_nr` ASC
separator ' ') AS `polygon`,
concat('M',
right(group_concat(concat('L', `k`.`x`, ',', `k`.`y`)
order by `k`.`reihenfolge_nr` ASC
separator ' '),
(length(group_concat(concat('L', `k`.`x`, ',', `k`.`y`)
order by `k`.`reihenfolge_nr` ASC
separator ' ')) - 1))) AS `path`
from
(`zeichenkoerper` `z`
join `koordinate` `k` ON ((`z`.`id` = `k`.`zeichenkoerper_id`)))
group by `z`.`id`

The view works for the most datasets. But in one dataset I have very much coordinates so it exceeds possibly some ranges. I dont know.

The problem is, that the 'path' cuts off the string after 342 characters.
If I look at the table structure of the view it says, that polygon is a text an path is a varchar (342). I dont know why mysql thinks this should be a varchar. I think if i would be text as well everything would be ok.

Does anyone know the bug? Is there a possiblity to say mysql the column is text?

Thanks and best regards,
Tobias

EDIT: Besides: It works right if I use the SELECT string out of a view as a single statement.



View's SELECT contains a variable or parameter (1 reply)

Mon, 10 Feb 2014 20:34:06 +0000

hi,
i have problem by creating a view.
at first, here is an example table:

----------------------------
|userId|timestamp|...|limit|
----------------------------
----------------------------
| 1 |111231111|...| 3 |
----------------------------
| 1 |114411112|...| 3 |
----------------------------
| 1 |112111113|...| 3 |
----------------------------
| 1 |111211114|...| 3 |
----------------------------
| 2 |111111115|...| 1 |
----------------------------
| 2 |111131117|...| 1 |
----------------------------

The userId and the timestamp is a clustered index.
I need a limit on results which has the same userId.
I found a solution by using a variable.

Here is an example:
"SELECT @row_num := IF(@prev_value=o.Customer,@row_num+1,1) AS RowNumber
,o.Customer
,o.OrderDate
,o.Amount
,@prev_value := o.Customer
FROM Orders o,
(SELECT @row_num := 1) x,
(SELECT @prev_value := '') y
ORDER BY o.Customer, o.OrderDate DESC"
Example results:
--------------------
|RowNumber|Customer|
--------------------
--------------------
| 1 |John |
--------------------
| 2 |John |
--------------------
| 3 |John |
--------------------
| 1 |Mark |
--------------------
| 2 |Mark |
--------------------


I can set a WHERE clause in my example and i have limit by userId.
"WHERE rowNumber <= limit"

But i got the following error: "View's SELECT contains a variable or parameter"

my question is:
is there another solution to set a limit on userId
or is there a way to use variables in views?



how to use sub query column in where condition in mysql? (1 reply)

Wed, 11 Dec 2013 18:53:57 +0000

Please check the below query. I am getting data from two databases and when i use the subquery result column in where condition, it is returning error in name column.

SELECT l.lead_id AS lead_id,l.customer_id AS customer_id,(SELECT a.phone FROM xenia.customers a WHERE a.customer_id=l.customer_id) AS phone,(SELECT b.name FROM xenia.customers b WHERE b.customer_id=l.customer_id) AS NAME, (SELECT c.email FROM xenia.customers c WHERE c.customer_id=l.customer_id) AS email, (SELECT d.name FROM melv1n.country d WHERE d.id IN (SELECT e.country FROM xenia.customers e WHERE e.customer_id=l.customer_id)) AS country, (SELECT f.city FROM melv1n.cities f WHERE f.city_id IN (SELECT g.city FROM xenia.customers g WHERE g.customer_id=l.customer_id)) AS city FROM xenia.lead l where name like '%Johny%' LIMIT 0,10

even I tried to create view like below

create or replace view lead_view as select l.lead_id as lead_id,l.customer_id as customer_id,(select phone from xenia.customers a where a.customer_id=l.customer_id) as phone,(select name from xenia.customers b where b.customer_id=l.customer_id) as name, (select email from xenia.customers c where c.customer_id=l.customer_id) as email, (select name from melv1n.country d where d.id in (select country from xenia.customers e where e.customer_id=l.customer_id)) as country, (select city from melv1n.cities f where f.city_id in (select city from xenia.customers g where g.customer_id=l.customer_id)) as city from xenia.lead l

but it is super slow.



View don't work (1 reply)

Tue, 03 Dec 2013 20:07:58 +0000

I'm create big request and it works , then i'm create view contained this request and it work but don't show results ( Query OK, 0 rows affected ) . BUT my request work and show (Query OK, 2 rows affected). What is wrong ?



Help with conceptualizing a query (3 replies)

Tue, 19 Nov 2013 03:50:42 +0000

Hi, I hope I posted in the right forum.

I have a small problem that I need help conceptualizing...hopefully from there I can make the actual SQL statement.

I have a project management database that keeps track of web pages needed to be done in each phase of the project.


--This is a list of all the phases. For our purposes assume there is only ever 1 project.
CREATE TABLE `list_phase` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`ProjectID` int(11) NOT NULL,
`Phase` varchar(25) NOT NULL,
`Number` tinyint(3) unsigned NOT NULL,
`DueDate` date DEFAULT NULL,
`StartDate` date DEFAULT NULL,
`EndDate` date DEFAULT NULL,
`Description` varchar(150) NOT NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `ProjectPhase` (`ProjectID`,`Phase`),
KEY `ProjectID` (`ProjectID`)
) ENGINE=MyISAM AUTO_INCREMENT=26 DEFAULT CHARSET=latin1

--This is a list of all possible pages in the entire project.
CREATE TABLE `list_page` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`ProjectID` int(11) NOT NULL,
`Page` varchar(30) NOT NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `Page` (`Page`),
KEY `ProjectID` (`ProjectID`)
) ENGINE=MyISAM AUTO_INCREMENT=86 DEFAULT CHARSET=latin1

--This links Pages with Phases. It's possible that the Login page will appear in both Phase 1 and Phase 2 and then again in Phase 5.
CREATE TABLE `phase-page` (
`PhaseID` int(10) unsigned NOT NULL,
`PageID` int(10) unsigned NOT NULL,
`Description` varchar(150) NOT NULL,
`DateDue` date DEFAULT NULL,
`DateStart` date DEFAULT NULL,
`DateFinish` date DEFAULT NULL,
PRIMARY KEY (`PhaseID`,`PageID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1



This is what I currently have, which works well, but now I'd like to make a change to what data is returned and I can't figure out if it's even possible in SQL or if I'll have to use PHP to further manipulate the data. Currently it returns each page in each phase where the phase number is less than what I request. For example, the Login page would appear 3 times in this result.

SELECT pp.*,p.Page,lp.Number FROM `phase-page` pp JOIN list_page p ON pp.PageID=p.ID JOIN (SELECT ID,Number FROM list_phase WHERE ProjectID=18 AND Number<=5) lp ON PhaseID=lp.ID ORDER BY Number DESC,Page


What I want is to return each page only once but I want it to tell me if it exists in the current phase (phase 5 in this case). Essentially I need to get the first phase a pages appears in (as determined by a sorted phase number) and also have a column that indicates what the max phase number it appears in.

Clear?

Thanks for any thoughts...



MySQL XML stored in View ? (no replies)

Thu, 03 Oct 2013 12:40:18 +0000

Hi,
I currently work in oracle but I am building a new application using MySQL.
In oracle I create xml by using xmlelement,xmlattributes, xmlagg, xmlforest..etc..
Then I create a view from the xml and my apps will pull all the xml from the view.

Can I do anything similiar to this with MySQL ?
I know I can create the xml with PHP which is what I have done. But I want to be able to create all the xml in the database and store in a view. This way the DB Developers have full access to the sql and the web developers do not have to deal with sql.

I have scoured the internet but cannot really find anything built into MySQL that lets me generate xml and store in a view.
Thank you.

PS - I forgot to mention the MySQL I am using is on a Shared Hosting Platform.



Subquery with table like parameter (no replies)

Fri, 19 Jul 2013 19:05:08 +0000

Hi, this is my first ask.

I have some troubles with a query, I need return the values of multiple subquerys,

My database has multimple tables with different information, but I need return a global id, so , in another table (reports_by_type) I save the name of table and the ID of the register of each one, and return the ID of (reports_by_type)


This is my original query

select a.table as table, a.date as date, count(*) as reports from reports_by_type.a group by a.table, a.date

I only group and return the count of reports by date and type


I need to add two values more... Like this but doesn´t work

select a.table as table, a.date as date, count(*) as reports,

(SELECT count(*) FROM {table} WHERE column_x= some_a) as some_a,
(SELECT count(*) FROM {table} WHERE column_x= some_b) as some_b

from reports_by_type.a group by a.table, a.date


Any idea for solve this?
thank for all