I have a SQL Server table of properties related by an id and want to do a query to check if an id has a set of properties. Basically I want to do a transform as follows, grouping by the Id and adding a column for each unique property: Starting table: Id Prop Value 1 P1 V1 1 P2 V2
Oracle SQL hierarchical query from bottom to top
I have a table where I want to go from bottom to top using hierarchical queries. The problem is that I need the get the value of one column from root (top) using CONNECT_BY_ROOT, but since I reverse the way the hierarchical query works (reverse the prior in connect by and the start with), this function (CONNE…
How to extract date from a long string in SQL Server
I a long string like below in my column and I need to extract only the date (2021-07-05) from it. Could anyone please help? Answer We can use PATINDEX with SUBSTRING here: Demo The call to PATINDEX above finds the starting position of the date, while SUBSTRING takes 10 characters from that position.
Unable to convert string to Timestamp
I’m trying to convert a sample 12 hour date with AM/PM into a timestamp, however, Snowflake is throwing an error that it’s not parsable: SELECT TO_TIMESTAMP(‘7/16/2021 4:52:25 AM’, ‘MM/dd/yyyy HH12:mm:ss AM’) The error message returned: Can’t parse ‘7/16/2021 4:…
query to get customer list using JOIN with sum () of the amounts spent in orders
I have the following tables table anag (customer registry) table levels (table that connects each customer to his salesperson. For database registration reasons, the link between customer and seller is given by the customer’s telephone number) table orders (contains all purchases made by customers, of c…
Finding the first empty column in a data row
I saw similar questions and most turned into arguments about table design, normalization, etc. Not all of us have the luxury of making our clients change their database designs. Here is my dilemma. My client asks that I make it possible for their workers to be able to add call out time for the preceding day. …
ORACLE json_object_t – error when call get_string() method on object got by chain of methods .get_array() .get()
I am trying to get a value from an array contained in JSON-object by chain of methods of JSON_OBJECT_T type – .get_array(…).get(0).get_string(…) But getting an exception component ‘GET_STRING’ must be declared Here is my code, could someone explain why the chain does not work? An…
Triggers data retention in inserted table and combination of update and insert on one column
I have a table in SQL Server which houses snapshots of all data to track changes in status of the source table tbl_D_project. If this status changes or a new record is added in tbl_D_project, a trigger should start and the line will be copied to the snapshot table. I wrote two triggers based on another questi…
SQL – Replace characters in a SQL string
I would like to know how to replace all characters between 2 dashes (-) and replace the dashes as well. It should be in a single select statement, not a loop. The text between the dashes can be variable length and any character. The characters before and after the dashes can also be variable length. Answer Yo…
How to use LIMIT to sample rows dynamically
I have a table as follows: SampleReq Group ID 2 1 _001 2 1 _002 2 1 _003 1 2 _004 1 2 _005 1 2 _006 I want my query to IDs based on the column SampleReq, resulting in the following output: Group ID 1 _001 1 _003 2 _006 The query should pick any 2 IDs from group