I have an xml which I need to read using Snowflake SQL. I need to use the attribute name and lits value to fetch the data. For example -> id and “bk101” to fetch the contents. I am currently using below query- In this case I am passing the index 0-4 to fetch the data is not producing correct re…
Tag: sql
BigQuery: How to aggregate records in a STRUCT or JSON field?
I want to aggregate a pair of columns as a dictionary of key,value pairs to construct a STRUCT or a JSON STRING over multiple records. Currently my implementation leverages the STRING nature of JSON & STRING_AGG to build such a JSON value: Which results in the following: Is there a more readable approach?…
How do I pass a variable to a stored procedure to update a row in another database?
I need to get a value from one database for a customer and update another database with that value. The procedure below works but I need to have it go through table2 and update every customer in table1 with a matching CustomerID. I hate to use the word loop through but as I said, I am very new to this
How can I create new column in sql based on previous date?
Output Answer You can try with LAG() as suggested by @Akina EDIT i see you are in mysql just now, you can try with this answer as well
Filtering empty rows from Mysql SELECT query issue
I have a SELECT query as below: This query gives me an output like this: My question is, how to filter the 0 due records from due_balance and modify the query to get the rest? Expecting result should be: I tried it in this way, but it doesn’t work for me. Answer Use HAVING Clause or use subquery then ad…
How to create table/view with data from another SQL server?
I have a database managed by a third party vendor and have access to read-only for the tables and views. I would like to be able to create a table from this database in a remote server. Is this possible? If so, how do I do it? Thanks in advance. Hey this is what I got from SELECT @@VERSION Microsoft
Recursively Conditionally get parent Id
I have two tables: Budget Line and Expense. They are structured in such a way that an expense must have either a parent record in the budget line table, or a parent record in the expense table. I need to select all child-most expenses for each budget line. For example – BudgetLine: Id Description 1 TEST…
In the Treeview I only display the ID of a line and not the remaining (empty) fields
I receive an error updating the TreeView, after clicking on the button to filter the data of a database through two comboboxes. The filtering appears to be successful, but only the staff ID is displayed in the tree view and not all related data. Without using the filter button, everything is fine. This is the…
How does one get the total rows for a partition in postgresql
I’m using a windows function to help me pagination through a list of records in the database. For example I have a list of dogs and they all have a breed associated with them. I want to show 10 dogs from each breed to my users. So that would be That will give me ~ten dogs from each breed.. What
Trying to update column values in SQL Server based on time of insertion and getting “The column “ID” was specificed multiple times for “p””
Here is my query that’s throwing the “The column “ID” was specified multiple times for “p””: I’ve seen quite a few questions with the same error on SO but can’t seem to see what to apply in this scenario. Any help is greatly appreciated. Answer Unfortunate…