![]() I get the error 'Variable cannot be null' I am assuming it is the active_appropriation value. Values (user_number, transaction_type, hours, rate, active_appropriation) How do I concat a string and a variable to use it as a column name in an sql query?īased on what Lennart suggested I have this now SET STMT = 'SELECT APPROP' || appropriation || ' pr.earnings into ' || active_appropriation ||Īnd am trying to insert active_appropriation like this insert into pr.worksheet (W_EMPLOYEE_NUMBER, W_TRANSACTION_TYPE, W_HOURS, W_RATE, W_APPROPRIATION) ![]() I tried a using dynamic sql but kept getting errors because I don't know what I'm doing. I can't figure out how to concat a string to a variable to use in a query. The result I want is select APPROP1 FROM PR.EARNINGS What happens is I get a system created column with APPROP1 inside it. I tried select concat('APPROP',APPROPRIATION) In this same procedure I need to query pr.earnings for by taking the string 'APPROP' and concatenating the APPROPRIATION variable from the query. In that table I have a columns named APPROP1 through APPROP4. The appropriation variable contains one character and it is a number from 1-4. INTO TRANSACTION_TYPE, HOURS, RATE, FACTOR, APPROPRIATION Then, you have a table with the products and categories combinations individually written.I have a procedure where I query table a and create variables like this SELECT TRCODE, COLA, COLB, COLC, COLD Instead of running the 3 INSERT queries by hand, you could echo the 3 INSERT queries to a text file and execute it as a script. If your actual query had 1000s of rows, a single MySQL would not have been practical. I could have taken it to another level and concatenated all the queries into a single query, but the SQL would have been insanely long. To be honest, I think SQL Server can perform all of this in a single pivot query without a handmade temp table. Did the prodcat table populate properly? mysql> select * from prodcat Mysql> insert into prodcat select 12,cat from (select NULL cat union select 11 union select 18 union select 5) A where cat IS NOT NULL ![]() Mysql> insert into prodcat select 11,cat from (select NULL cat union select 8) A where cat IS NOT NULL Let me run each line by hand mysql> insert into prodcat select 10,cat from (select NULL cat union select 9 union select 12) A where cat IS NOT NULL | insert into prodcat select 12,cat from (select NULL cat union select 11 union select 18 union select 5) A where cat IS NOT NULL | | insert into prodcat select 11,cat from (select NULL cat union select 8) A where cat IS NOT NULL | | insert into prodcat select 10,cat from (select NULL cat union select 9 union select 12) A where cat IS NOT NULL | > replace(categories,',',' union select '),') A where cat IS NOT NULL ') ProdCatQueries from prod Here it is executed mysql> select concat('insert into prodcat select ',product_id,',cat from (select NULL cat union select ', Replace(categories,',',' union select '),') A where cat IS NOT NULL ') ProdCatQueries from prod Here it is: select concat('insert into prodcat select ',product_id,',cat from (select NULL cat union select ', OK, you need query to put together each product_id with each category. If you are willing to store the results in a temp table, I got just the thing.įirst, here is the code to use you sample data in a table called prod and a temp table called prodcat to hold the results you are looking for. What you are looking for is the inverse of a GROUP BY aggregate query using the GROUP_CONCAT.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |