Write count value from SQL query to tag id

Ignition
1

Hi,

I've been trying to write a script that queries the database fro Display_id and count how many units are in each of those. It then matches the first 1 value from database e.g "CCPI13TP21" to the same name memory tag Id i've got and writes the value from database count(*) column to matching tag id value.

I'm using this script below which doesn't do what I want. The script console is giving me this output : <<<

def getCount():
                                    # Set up database connection
                                    db = "PROD"
                                    
                                    # Define SQL query to count records
                                    query = "SELECT display_id, COUNT(*) FROM usv_AllCurrentLocations WHERE display_id LIKE 'C%' GROUP BY display_id"
                                    
                                    try:
                                        # Execute the SQL query
                                        result = system.db.runPrepQuery(query, db)
                                
                                        # Check if there are results
                                        if result.rowCount > 0:
                                            # Get column names
                                            headers = result.getColumnNames()
                                            print("Column Names:", headers)
                                
                                            # Define the tag paths
                                            tagPath1 = "[SCADA]Warrington/Ambient/Leg1LVL1Amb/SQLTags1/"
                                            tagPath2 = "[SCADA]Warrington/Ambient/Leg2LVL1Amb/SQLTags1/"
                                
                                            # Loop through results
                                            for row in result:
                                                display_id = row[0]
                                                count = row[1]
                                                # Build tag path based on display_id
                                                if display_id.startswith("CCPI13TP11"):
                                                    dynamicTagPath = "{}{}".format(tagPath1, display_id)
                                                elif display_id.startswith("CCPI13TP21"):
                                                    dynamicTagPath = "{}{}".format(tagPath2, display_id)
                                                else:
                                                    print("Display ID {} does not match expected prefixes.".format(display_id))
                                                    continue
                                                   
                                                # Write count to the appropriate tag path
                                                system.tag.write(dynamicTagPath, count)
                                
                                                print("Wrote count {} to tag {}".format(count, dynamicTagPath))
                                        else:
                                            print("No results found for the specified display_id.")
                                    except Exception as e:
                                        print("Error:", e)

type or paste code here
2

Should the query be,

    query = """SELECT 
                display_id AS Display_Id, 
                COUNT(display_id) AS Count 
                FROM usv_AllCurrentLocations 
                WHERE display_id LIKE 'C%' 
                GROUP BY display_id
            """

? i.e., Count a specific field rather than * wildcard?

3

I've changed that to specific field's but still the same problem

4

That's odd. Usually it's >>>. Typo?

Is the indentation right in your script? It came out with a lot of tabs / spaces in your post.

5

The result should be the same, and I believe most query optimizers are built to "count star" efficiently.

1 Like