I am performing insert operation like follow on Microsoft SQL Server Management Studio: This insert is supposed to make few hundreds of insert. But here I want to insert scode like C000512, C000513, C000514 …. and C000511 being the latest scode entry previously present in contacts. How do I make this SC…
Return number of rows (streak count) from entries where each entry is the previous days date
I have a table with two columns, userid and date. I want to query this table using a specific userid and date, and from that I want to return the row count of entries going backwards from the entered date. i.e. where userid = 1 AND date = ‘2020-07-09’. It should stop counting if there is a gap bet…
How to pass an array of JSON (or JSON array) to pg function to be INSERTed into a Table?
Function to insert rows of json array into a table: Call this function: or this form: or this form: Always received: Answer A JSON array (json) is different from a Postgres array of JSON values (json[]). vs: The first is an array nested inside a single JSON value, the second is an array of JSON values. Postgr…
How to get list of custom user types for listed stored procedures?
I was able to find way to find custom user types for one stored procedure: How should I use this function for comma separated list of stored procedures? Answer If you have it available (SQL Server 2016 and newer), you can use STRING_SPLIT to split your list and then use CROSS APPLY to call the function for ea…
Select from a table who is all available in other tables
I’m currently working on some sql tables, Here is the tables below in an image. Sailors sid sname rating age 22 Dustin 7 45.0 29 Brutus 1 33.0 31 Lubber 8 55.5 32 Andy 8 25.5 58 Rusty 10 35.0 64 Horatio 7 35.0 71 Zorba 10 16.0 74 Horatio 9 35.0 85 Art 3 25.5 95 Bob 3 63.5
Rollback SQL transaction without returning an error
Consider the trigger below The idea is to stop any changes from being done to a Tasks record if it is tied to a Jobs record that has a value in the QuotationID field. It works because when these conditions are met, the changes are not saved. However, though, an error is returned to the application that causes…
how to retrive data from VARRAY and filter it
i have to retrieve specific data from Varray for example i want to retrieve the name where VARRAY includes AC1 i tried select * from exprement where seat=’AC1′; Answer You can use: or As an aside, if you defined seat as a nested table (rather than a VARRAY) then you could use the MEMBER OF operato…
Increase or decrease customer credits in upsert
In my app customers can build up credit in different categories and then spend it on things the relevant table looks like this: There is one entry for every category in which a user has credit There are 2 constraints, one to make sure the amount is never negative and there is only one user category connection…
How to use Postgres CASE simple/short-hand syntax with multiple conditions?
I know with Postgres CASE expression, you can simplify it down to: SELECT a, CASE a WHEN 1 THEN ‘one’ WHEN 2 THEN ‘two’ ELSE ‘other’ END FROM test; …so …
How to enforce a max limit no. of rows per day per date in SQL?
Given a data that looks as follows where the date is in string format YYYYMMDD: item vietnamese cost unique_id sales_date fruits trai cay 10 abc123 20211001 fruits trai cay 8 foo99 20211001 fruits …