Here is the requirement: Find all teachers whose FirstName length is less than 5 and the first 3 characters of their FirstName and LastName are the same I tried this query (Scalar Function): To call function: But, when I execute first query, it shows error: Incorrect syntax near ‘<‘. Can anyone help me with this? Answer Just use a normal
Tag: sql-server
Find all ancestors without direct id-parentid in same table
I have a parent-child structure across two tables. The first table has BOM_ID for bills and ITEM_ID for associated children items. The other has BOM_ID and ITEM_ID for the parent item. I am able to find the first level of parents’ ITEM_ID with the following query So, in order to find all ancestors, I would have to repeat this step.
Get last month date range
the above query will return the last/previous month, however its missing 2 records from the last day of the last month (2022-04-30) due to the date including a time range: I’m currently manually pulling the dates but I want to automate this process, any help will be much obliged Answer The clearest and least error prone way to do this
Get all values for the XML file in SQL
I have tried the @Name and I get all the values such as DefCurrency, IsICP etc but I don’t get the values. See my code below. Thanks in advance Answer All supported versions of Microsoft SQL Server have the nodes() and value() methods available for querying the XML data type, e.g.: Which yields the output: LABEL val DefCurrency [None] AllowAdjs
Trouble dealing with max() function in SQL Server
The whole code is part of a stored procedure. I have two pieces of code: This one works fine. On the line this particular code is producing the output: and the other one: This is not working correctly. I get an error: Subquery returned more than 1 value on this line Independently it produces this output: Column and table names
SQL query – Return 0 for the column if no record found
I will like to see the specific fields returns with 0 value when there is no record found. Below are my SQL queries that I have tried so far. What it returns is only 1 row as below. Customer Address Number of Lines Date A 3 RF 30-5-2022 But what I expected to see is: Customer Address Number of Lines
Can’t execute a while clause in SQL Server with Dbeaver
I am trying to execute a loop while in Dbeaver with SQL Server, but the statement keeps loading and does not execute it. I just want to print the word ‘ok!’ 3 times. I’m not sure if it’s a loop problem, a Dbeaver problem, or another. Can anyone help please? My code: Screenshot from Dbeaver Answer @cnt never increments, so
SQL: Pull rows based on sequence of values
I need to pull rows of data based on the existence of certain values that exist in a specific sequence. Here’s an example of the data: Header EventId EventDate 67891882 382 2022-01-21 09:29:50.000 67891882 81 2022-01-21 09:03:23.000 67891882 273 2022-01-21 09:03:51.000 67891882 77 2022-01-21 09:05:58.000 67891882 2 2022-01-21 09:29:48.000 The results I need are to capture the Header and the
Return only the modified records in a table, using T-SQL
I’m attempting to return only the modified records in a table, using T-SQL. This is what I’ve done thus far: Results: Expected results: The following statement returns all the 0 to 1, and 1 to 0 records and I don’t understand why: Answer To track the changes at whole dataset, you were almost there, you just need to remove the
Conversion Failed in a CASE expression
I have a column that is of varchar type, it contains dates and ‘#’: I am trying to convert the dates to the standard date format (YYYY-MM-DD) and leave the ‘#’ as it is whenever it occurs. Here is my code: The outcome column is also of varchar(10) type (same as the original column). I expected to get # whenever