# fsa

Page 1 of 1

## 9 Replies - 289 Views - Last Post: 22 February 2019 - 11:27 AM

### #1 Grander

Reputation: 1
• Posts: 26
• Joined: 15-November 18

# fsa

Posted 16 January 2019 - 11:59 AM

I need help with the following:
1-
Have a table B that needs to be updated based on the values of table A.
Table B’s column names are cell values in one of the columns of table A (Inits). The condition to be met to determine which cell to read from is that value of C_code column in table B and A are the same and that TableA.Inits value is as TableB filed name (column name). I can produce the column names of table a by this:

Declare @TempTable table(ColumnID nvarchar(30))

insert into @TempTable
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'JTP_Warehouse' --ORDER BY ORDINAL_POSITION

Select ColumnID from @TempTable



I am thinking of a dynamic sql, but then I am stuck 😊 I though initially that after getting the column names I would loop through and compare 300 rows and 90 column cell by cell. Is that the way to go ahead?

2-
I need to insert two different images to all the cells of a table (same table as above) based on values of a cell in another table content. So if the cell value is “yes” the image would be “green.jpg” and if it is “no” the image would be “red.jpg”. Those images are stored locally on C drive, for now.

Where shall I start? Any help is appriciated.

Is This A Good Question/Topic? 0

## Replies To: fsa

### #2 astonecipher

• Senior Systems Engineer

Reputation: 2881
• Posts: 11,266
• Joined: 03-December 12

## Re: fsa

Posted 16 January 2019 - 01:04 PM

Honestly, how did it get to that point?

Quote

I need to insert two different images to all the cells of a table (same table as above) based on values of a cell in another table content. So if the cell value is “yes” the image would be “green.jpg” and if it is “no” the image would be “red.jpg”. Those images are stored locally on C drive, for now.

1. There are columns, not cells in a database.
2. WHY!!!!

SELECT
CASE WHEN someColumnName = 1 THEN 'green.jpg'
ELSE'red.jpg' as someColumnName
FROM table


### #3 modi123_1

• Suitor #2

Reputation: 15115
• Posts: 60,493
• Joined: 12-June 08

## Re: fsa

Posted 16 January 2019 - 01:11 PM

This guy's asking the important questions.

### #4 Grander

Reputation: 1
• Posts: 26
• Joined: 15-November 18

## Re: fsa

Posted 17 January 2019 - 03:48 AM

@ astonecipher
It is for a visualization tool and to make the status more visible with red and green circles; or just the cell background color also would do to begin with. I say this as I just realized the for those columns to accept image their datatype must be "image" and not varchar as it is now. So one more problem.

Yes, yes, there are columns in database but columns have cells �� and I in each case I am interested in specific cell of a column.
This does that for a single column but then how will I run this on all columns for three tables in an efficient way. CompMatrix is where the data comes from and there are three tables to be updated: JTP_Moulding, JTP_PreAssembly and JTP_Warehouse and each of these table have different column count and name.

UPDATE
JTP_Moulding
SET
JTP_Moulding.AQAE = RAN.CourseStatus
FROM
JTP_Moulding SI
INNER JOIN
CompMatrix RAN
ON
SI.CourseCode = RAN.CourseCode And  RAN.EmpInits ='AQAE';



Any help is appriciated.

This post has been edited by Grander: 17 January 2019 - 03:58 AM

### #5 andrewsw

Reputation: 6798
• Posts: 28,102
• Joined: 12-December 12

## Re: fsa

Posted 17 January 2019 - 04:12 AM

What is "fsa" and what has it to do with this topic?

### #6 Grander

Reputation: 1
• Posts: 26
• Joined: 15-November 18

## Re: fsa

Posted 17 January 2019 - 05:14 AM

I was going to say it stands for Fast SQL Advance but I don't know how my topic is called that. Good question, and who changed my topic subject? If I did it then I apologize.

### #7 Sheepings

• D.I.C Lover

Reputation: 224
• Posts: 1,260
• Joined: 05-December 13

## Re: fsa

Posted 17 January 2019 - 08:08 AM

Dear or dear, what a way to go about it... Have you made the needed changed already?

### #8 astonecipher

• Senior Systems Engineer

Reputation: 2881
• Posts: 11,266
• Joined: 03-December 12

## Re: fsa

Posted 17 January 2019 - 08:22 AM

I'll reiterate. A datatable is not a spreadsheet. And what you are doing, is trying to bog a system down when it doesn't need to be. The query you use in your BI software, you specify what should be displayed if the value shows a specific value, like I demonstrated.

Typically, if you need to update a lot of information from a source, you would do an import stored procedure and run it as an agent.

I can't help that much, because I don't know what you are trying to do - other than update columns. But, I do this type of thing for 6 manufacturing facilities under a very large consumer goods company daily. And that entails multiple tables for multiple systems across even more databases.

### #9 benanamen

Reputation: 36
• Posts: 239
• Joined: 28-March 15

## Re: fsa

Posted 21 January 2019 - 12:05 AM



This post has been edited by benanamen: 21 January 2019 - 12:06 AM

### #10 ajwsurfer

• D.I.C Regular

Reputation: 21
• Posts: 385
• Joined: 24-October 06

## Re: fsa

Posted 22 February 2019 - 11:27 AM

Here is an example of the solution to question #1. (As long as the MySQL version being used is one of the latest ones.) The idea is to nest a statement that returns a single row (which can be interpreted as list of values).
DROP TABLE IF EXISTS tmp;
CREATE TABLE tmp (ColumnID varchar(128));

INSERT INTO tmp
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE NOT TABLE_NAME IN (SELECT COLUMN_NAME FROM  INFORMATION_SCHEMA.COLUMNS);
SELECT * FROM tmp;



For question #2
There are any number of ways of doing this: The values you are interested in are the path to the images directory and the file names. From there the SQL statement only needs to make a choice.
The first question you need to ask yourself is: Where should the strings for the path and image names be stored? If it is in a normalized SQL database, it would be in a separate table:
ImageTable
----------
path                      | image       | trigger
--------------------------------------------------
'C:\path\to\green\image\' | 'green.jpg' | 'g'
'C:\path\to\red\image\'   | 'red.jpg'   | 'r'



So a select would join this table as such:
SELECT CONCAT(I.path, I.image) AS path
FROM DecisionTable DC
INNER JOIN ImageTable I
ON DC.trigger = I.trigger

`

So there could be any number of ways to join these tables, add filter parameters, and add more columns to the select statement.
So in retrospect question #2 seems to be too broad of a question. This needs more analysis of the overall system, and what makes it easier to test and deploy, before knowing how much SQL needs to be involved. If there is a config file available, then the path and image strings might be better off there.