I’m afraid the answer to this is “NO, you can’t”, but since I can’t find this explained anywhere I’m gonna ask anyway. Given a user defined table type: And a function that returns that type: And another function that accepts that type: Since the return value of fnFind fits …
How to replace Over clause in sql version 4.9.5
I am trying to run a query to find the count and percentage of artisan based on gender. The query is running absolutely fine on the SQL server 8. But not on my live server which is 4.9.5. The below is my query. Answer In any database, you should be able to use:
How to check instring for alphanumeric values
I have the following dataset: I need to check whether or not one of the comma separated values is an alphanumeric value. How can I do this? Answer This could be one option; read comments within code.
Compare two SQL tables and return count of rows with changes
I have two partitions from an SQL table containing num_key records. I need to compare and count changes in the February records versus the January records. SAMPLE DATA AND DESIRED RESULTS: ptn_dt = ‘2019-01-31’ (January) num_key active_indicator 111 true 112 false 113 false 114 false 115 true 116 …
SQL – using IF statement after WITH statement
i am making a SQl report, which uses the With statment, and with a lot of inner joins and group etc. see Working Code below. the code below does work. the problem is, this table will have output data (as a table) sometime and sometime not based on order intake. when there is not data found, i want to display
SQL – using ‘HAVING’ with ‘EXISTS’ without using ‘GROUP BY’
Using ‘HAVING’ without ‘GROUP BY’ is not allowed: Column ‘products.UnitPrice’ is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause. But when placing the same code under ‘EXISTS’ – no problems: C…
Selecting multiple rows from the same table with common column?
Have the following hypothetical table User_Mail_Info in Oracle DB Primary key is User_Id,Mail_DL,DLId. There are other columns with the table as well. How to retrieve the UserId ,given the list of (Mail_DL and DL Id) ? Note: Mail_DL and DL_Id are not directly related. This is just hypothetical data For eg. In…
How use case statement alias in where clause
I’m trying to use a case statement and the results to in an alias, but I need to use the alias in my where clause and this doesn’t seem to be working. How do I use the alias (isPrimary below) in my where clause. See comment where I’m trying to use isPrimary in my where clause, which doesn…
Query a column with jsonb data meeting certain criteria
Problem I have a table like this: product tags (jsonb) P001 [{“name”: “LX”,”active”: true}, {“name”: “TX”,”active”: true}] P002 [{“name”: “LX”,”active”: true}] I am trying to query against this table to…
How to sanitize Arel SQL?
I have the following Arel SQL: I get SQL Injection warning when I run brakeman. I tried the following: However, I get the following error: How do I sanitize sql statement with Arel? Answer I am answering my own question. I am using Arel following the Github wiki for Ransack gem. I was doing something very sim…