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

#1 Grander   User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • 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   User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 2995
  • View blog
  • Posts: 11,531
  • 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

Was This Post Helpful? 2
  • +
  • -

#3 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15356
  • View blog
  • Posts: 61,553
  • Joined: 12-June 08

Re: fsa

Posted 16 January 2019 - 01:11 PM

This guy's asking the important questions. :^:
Was This Post Helpful? 0
  • +
  • -

#4 Grander   User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • 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

Was This Post Helpful? 0
  • +
  • -

#5 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6823
  • View blog
  • Posts: 28,271
  • 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?
Was This Post Helpful? 0
  • +
  • -

#6 Grander   User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • 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.
Was This Post Helpful? 0
  • +
  • -

#7 Sheepings   User is offline

  • D.I.C Lover
  • member icon

Reputation: 224
  • View blog
  • 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?
Was This Post Helpful? 0
  • +
  • -

#8 astonecipher   User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 2995
  • View blog
  • Posts: 11,531
  • 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.
Was This Post Helpful? 0
  • +
  • -

#9 benanamen   User is offline

  • D.I.C Head

Reputation: 36
  • View blog
  • Posts: 242
  • 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

Was This Post Helpful? 0
  • +
  • -

#10 ajwsurfer   User is offline

  • D.I.C Regular
  • member icon

Reputation: 21
  • View blog
  • 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.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1