473,554 Members | 2,793 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Concat variable to form Table name inside Select statement

9 New Member
Hi guys,

I have spend several days now trying to solve this problem inside a stored procedure. I want to call a procedure providing 3 variables. The variables area used to select the appropriate table from within the database. I have to concatenate the 3 variables to make up the whole table name and concatenate that with the field name.

The hard coded procedures looks as follows:

Expand|Select|Wrap|Line Numbers
  1. DROP PROCEDURE IF EXISTS `nebula_fit`.`Traffic_Profile` $$
  2. CREATE DEFINER=`root`@`localhost` PROCEDURE `Traffic_Profile`(IN sCompany CHAR(10), IN sMonth CHAR(3), IN sYear CHAR(4))
  3.  
  4. BEGIN
  5.  
  6. SELECT CONCAT('', sMonth, '_', sYear, '') as MonthValue,
  7. `usg_summaryinvoice`.`AccountNo`,
  8. `usg_calldetail_tel_npak_jun_2007`.`OriginatingNumber`,
  9. `usg_calltype_tel`.`CallType`,
  10. SUM(`usg_calldetail_tel_npak_jun_2007`.`CallCost`) as TotalCallCost,
  11. SUM(`usg_calldetail_tel_npak_jun_2007`.`CallDuration`) as TotalDuration,
  12. COUNT(`usg_calldetail_tel_npak_jun_2007`.`CallDetailID`) as TotalCallCount
  13. FROM `usg_calldetail_tel_npak_jun_2007`
  14. LEFT JOIN `usg_calltype_tel` ON `usg_calldetail_tel_npak_jun_2007`.`CallTypeID` = `usg_calltype_tel`.`CallTypeID`
  15. LEFT JOIN `usg_summaryinvoice` ON `usg_calldetail_tel_npak_jun_2007`.`InvoiceNo` = `usg_summaryinvoice`.`InvoiceNo`
  16. GROUP BY MonthValue,
  17. `usg_summaryinvoice`.`AccountNo`,
  18. `usg_calldetail_tel_npak_jun_2007`.`OriginatingNumber`,
  19. `usg_calltype_tel`.`CallTypeID`,
  20. `usg_calltype_tel`.`CallType`
  21. ORDER BY MonthValue,
  22. `usg_summaryinvoice`.`AccountNo`,
  23. `usg_calldetail_tel_npak_jun_2007`.`OriginatingNumber`,
  24. `usg_calltype_tel`.`CallTypeID`;
  25.  
  26. END
I want to pass three paramaters to replace npak, jun and 2007.

So basically what I'm trying to do is something like this

Expand|Select|Wrap|Line Numbers
  1. SELECT CONCAT('', sMonth, '_', sYear, '') as MonthValue,
  2. `usg_summaryinvoice`.`AccountNo`,
  3. concat("`usg_calldetail_tel_", sCompany, "_", sMonth, "_", sYear, ".OriginatingNumber"),
  4. `usg_calltype_tel`.`CallType`,
  5. SUM(`usg_calldetail_tel_npak_jun_2007`.`CallCost`) as TotalCallCost,
  6. SUM(`usg_calldetail_tel_npak_jun_2007`.`CallDuration`) as TotalDuration,
  7. COUNT(`usg_calldetail_tel_npak_jun_2007`.`CallDetailID`) as TotalCallCount
  8. FROM concat("`usg_calldetail_tel_", sCompany, "_", sMonth, "_", sYear);
If have also tried

Expand|Select|Wrap|Line Numbers
  1. SELECT CONCAT('', sMonth, '_', sYear, '') as MonthValue,
  2. `usg_summaryinvoice`.`AccountNo`,
  3. "usg_calldetail_tel_" ||  sCompany || "_" || sMonth || "_" || sYear || ".OriginatingNumber"),
  4. `usg_calltype_tel`.`CallType`,
  5. SUM(`usg_calldetail_tel_npak_jun_2007`.`CallCost`) as TotalCallCost,
  6. SUM(`usg_calldetail_tel_npak_jun_2007`.`CallDuration`) as TotalDuration,
  7. COUNT(`usg_calldetail_tel_npak_jun_2007`.`CallDetailID`) as TotalCallCount
  8. FROM "usg_calldetail_tel_" ||  sCompany || "_" || sMonth || "_" || sYear || ";"
  9.  
I am wits end, can anyone tell me how to concatenate the table name on the fly into a statement.

I have also tried to put it into one long string and exec that statement, but similiar problems.

Even a good article will do, because I have reached the end of the internet searching for a solution

Thx Ant
Jul 27 '07 #1
6 26823
ilearneditonline
130 Recognized Expert New Member
I have never tried to do this with MySQL, but in MS SQL you have to build the SQL Statement as a nvarchar and then use..

Expand|Select|Wrap|Line Numbers
  1.  exec(sqlstring)
This may assist you with what you are trying to do.

Execute String As Query
MySQL documentation
Jul 27 '07 #2
antmail
9 New Member
Thanks Joe but this is not it.

You can do the same with MSSQL using

Expand|Select|Wrap|Line Numbers
  1. Set @x = "Select your statement in here..."
  2. Prepare stmt as @x;
  3. Execute stmt;
However my problem is not executing but concating the string. I either end up with a literal string in stead of a table name or I get an SQL statement error, and we all know how helpful that error descriptions are.
Jul 27 '07 #3
mwasif
802 Recognized Expert Contributor
Are you looking for this?
Expand|Select|Wrap|Line Numbers
  1. SET @a = 'table';
  2. SET @x := CONCAT('SELECT * FROM ', CONCAT(@a, '_', 'name'));
  3. Prepare stmt FROM @x;
  4. Execute stmt;
This is equal to
Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM table_name;
Jul 27 '07 #4
antmail
9 New Member
Is the only way to do this using the exec method?

I am familiar with that, but found it very difficult to debug when you get one of those mystery SQL errors.

The statement I listed is one of the small ones, I have another even more complex and I found it very difficult to debug using set @x = "Select.... "
Jul 27 '07 #5
mwasif
802 Recognized Expert Contributor
I am only familiar with this.
Jul 27 '07 #6
antmail
9 New Member
Ok Guys,

I thought I reached the end of the internet, but it seems there was more.

The long and short of it, is that you can only do what I wanted do (dynamic SQL) in the upcoming version 5.1 of MySQL.

When concatenating values in pre-5.1 versions, it does not resolve the table or column name but either gives an SQL error or sees that value as a literal string.

So the only way to do this, is to create the whole literal string outside the stored proc in say a VB or Java app and then passing it as one long variable containing the complete statement. The stored proc will then prepare the statement and execute it.

Well there is never an easy way it seems.

Ant
Jul 30 '07 #7

Sign in to post your reply or Sign up for a free account.

Similar topics

4
4426
by: Nomen Nescio | last post by:
can anyone be so kind as to look at http://www.mysolution.ws/HYPOCRITE.php and let me know why it isn't passing the form data to http://www.mysolution.ws/insertHYPOCRITES.php for the most part, the scripts were created with http://phpcodegenie.sourceforge.net/
6
2734
by: BigDadyWeaver | last post by:
I am using the following code in asp to define a unique and unpredictable record ID in Access. <% 'GENERATE UNIQUE ID Function genguid() Dim Guid guid = server.createobject("scriptlet.typelib").guid guid=Left(guid,instr(guid,"}")) genguid=guid
3
1559
by: Tim | last post by:
Hi All, I have a small issue that I can't seem to figure out. I have a SQL statement that is dependant on the results of a drop down to chose which table to select from. Unfortunately it does not seem to work. Could anyone point me in the right direction? Unfortunately the datebase cannot be changed. I guess my question is this. Is it...
3
3342
by: r rk | last post by:
I am trying to write a utility/query to get a report from a table. Below is the some values in the table: table name: dba_daily_resource_usage_v1 conn|loginame|dbname|cum_cpu|cum_io|cum_mem|last_batch ------------------------------------------------------------ 80 |farmds_w|Farm_R|4311 |88 |5305 |11/15/2004 11:30 80 ...
25
10182
by: Lyn | last post by:
Hi, I am working on a genealogy form. The only table (so far) lists everybody in the family, one record per person. Each record has an autonum ID. The parent form (frmMainForm) displays the data in each record, which includes the ID of the father and the mother (who also have records in the table). One record per form. I have a Tab...
6
1898
by: GarryJones | last post by:
I think the following statement .... $ml_collect='SELECT *, DATE(CONCAT(field1, field2)) AS thedate FROM ml_lopp LEFT JOIN scfmforening ON (scfmforening.scfmnum = ml_lopp.scfmnum) LEFT JOIN ml_tidplats ON (ml_tidplats.loppnum = ml_lopp.loppnum) ORDER BY thedate'; .....would work if "field1" and "field2" were in the table "ml_lopp" ...
8
4953
by: tnspc | last post by:
I'm trying to access a Request.Form variable and plug it into an SQL statement, so that the statement will select just the particular data I need. Here's the offending snippet of code: sql = "SELECT DISTINCT name, extension FROM Employees WHERE name = " & Request.Form("name") I assumed that the problem was simply not having quotation...
5
2534
by: plumba | last post by:
Hi all I have a form (see below), which for some reason has decided to stop functioning all together. It just does not call up the function. It is called up in the opening <form> tag but fails.... Any ideas??? <html> <head><title>New Details</title> </head> <center> <H2><IMG SRC="$(path)smlogo.gif"><br>
3
2985
by: adiel_g | last post by:
Hello everyone, I am trying to move a field name to a variable in vb.net. For example, first I retrieve the record from the database and save its value: .... userGroup = ds.Tables("Default").Rows(x).Item("UserGroupAccess").ToString 'retrive access value from dataset ....
0
7573
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7496
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7857
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6114
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5412
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
3525
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1992
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1109
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
810
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.