Solved

Issue with MySQL VIEW column size

Posted on 2009-04-13
6
359 Views
Last Modified: 2013-12-12
Hi,

I have Product table with columns prod_id int(11),category_id int(11),prod_name varchar(255) and prod_quantity varchar(255).
I am creating VIEW in MySQL using above table  and following syntax:

CREATE OR REPLACE VIEW product_view AS
SELECT
 category_id,
 CONCAT("#",GROUP_CONCAT(CONCAT_WS('=',prod_name,prod_quantity) SEPARATOR '#'),"#") AS product_quantity_values

FROM
 product
GROUP BY category_id

Now when i describe VIEW then the column size of VIEW COLUMN "product_quantity_values" shows varchar(343). So this view only allow me to store 343 characters in COLUMN "product_quantity_values" . More than that are truncated.

Is there any way to avoid such behavior? I want the VIEW COLUMN "product_quantity_values" should store more characters.

0
Comment
Question by:teenashah
  • 3
  • 3
6 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24128586
please try this:
CREATE OR REPLACE VIEW product_view AS
SELECT
 category_id,
 cast(CONCAT("#",GROUP_CONCAT(CONCAT_WS('=',prod_name,prod_quantity) SEPARATOR '#'),"#") AS product_quantity_values as VARCHAR(65000)) 
FROM 
 product 
GROUP BY category_id

Open in new window

0
 

Author Comment

by:teenashah
ID: 24128719
Hi angelIII,

Its giving a error:

Error Code : 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 'AS fproduct_quantity_values as VARCHAR(65000))

BTW: I am using MySQL 5
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 24128828
sorry,...
CREATE OR REPLACE VIEW product_view AS

SELECT

 category_id,

 cast(CONCAT("#",GROUP_CONCAT(CONCAT_WS('=',prod_name,prod_quantity) SEPARATOR '#'),"#") as VARCHAR(65000))  AS product_quantity_values 

FROM  product 

GROUP BY category_id

Open in new window

0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:teenashah
ID: 24129011
Sorry angelIII,

Same error again.

Error Code : 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 'VARCHAR(65000)) AS
0
 

Author Comment

by:teenashah
ID: 24135661
Hi,

Above issue has been resolved.

MySQL 5 CAST function allows type as CHAR Not VARCHAR.

But thanks for giving way to tackle situation.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24136813
I see, glad I could help.
so, please, close the question.
a3
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to recover a database from a user managed backup

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now