Stored Procedure: Column '' has unsupported type "information_schema.sql_identifier"
I am trying to test my stored procedure in MySQL workbench/j. I get an error when I am trying to call the stored procedure.
I have created a table to store the result of my stored procedure
CREATE TABLE IF NOT EXISTS public.distTable ( table_schema VARCHAR(50) ENCODE lzo );
This is my stored procedure:
CREATE OR REPLACE PROCEDURE sp_GetDistSchema() AS ' BEGIN SELECT Distinct table_schema INTO public.distTable FROM information_schema.tables; END; ' LANGUAGE plpgsql;
This is how i call my stored procedure in SQL workbench/j:
An error occurred when executing the SQL command: call sp_getdistschema() [Amazon](500310) Invalid operation: Column "table_schema" has unsupported type "information_schema.sql_identifier".; [SQL State=0A000, DB Errorcode=500310] 1 statement failed.
May I know what is the problem?
The SELECT ... INTO structure is used to store a query result into variables. It looks as though you are really just trying to populate the distTable directly. Try this instead:
Update: When processing the information schema in Redshift/PostgreSQL, you apparently need to convert the column datatypes using CAST:
CREATE OR REPLACE PROCEDURE sp_GetDistSchema() BEGIN INSERT INTO distTable SELECT DISTINCT CAST(table_schema AS VARCHAR) FROM information_schema.tables; END;
See also questions close to this topic
Cummulative sum by year_month, location, state in Hive SQL
I want to make a cumulative count using Hive SQL in
recorrenciacolumn according to the other ones.
+------------+---------+-------+--------------+--+ | t.ano_mes | t.site | t.uf | recorrencia | +------------+---------+-------+--------------+--+ | 202001 | 174 | AM | 1 | | 202002 | 174 | AM | 1 | | 202003 | 174 | AM | 1 | | 202004 | 174 | AM | 1 | | 202005 | 174 | AM | 1 | | 202006 | 174 | AM | 1 | | 202007 | 174 | AM | 1 | | 202008 | 174 | AM | 1 | | 202005 | 1JN | SP | 1 | | 202006 | 1JN | SP | 1 | | 202005 | 1LJ | SP | 1 | | 202009 | 1LJ | SP | 1 | | 202001 | 1RG | SP | 1 | | 202002 | 1RG | SP | 1 | | 202003 | 1RG | SP | 1 | | 202004 | 1RG | SP | 1 | | 202005 | 1RG | SP | 1 | | 202006 | 1RG | SP | 1 | | 202007 | 1RG | SP | 1 |
+------------+---------+-------+--------------+--------+ | t.ano_mes | t.site | t.uf | recorrencia |cum_rec +------------+---------+-------+--------------+--------+ | 202001 | 174 | AM | 1 |1 | 202002 | 174 | AM | 1 |2 | 202003 | 174 | AM | 1 |3 | 202004 | 174 | AM | 1 |4 | 202005 | 174 | AM | 1 |5 | 202006 | 174 | AM | 1 |6 | 202007 | 174 | AM | 1 |7 | 202008 | 174 | AM | 1 |8 | 202005 | 1JN | SP | 1 |1 | 202006 | 1JN | SP | 1 |2 | 202005 | 1LJ | SP | 1 |1 | 202009 | 1LJ | SP | 1 |2 | 202001 | 1RG | SP | 1 |1 | 202002 | 1RG | SP | 1 |2 | 202003 | 1RG | SP | 1 |3 | 202004 | 1RG | SP | 1 |4 | 202005 | 1RG | SP | 1 |5 | 202006 | 1RG | SP | 1 |6 | 202007 | 1RG | SP | 1 |7
I've tried a lot of functions like
COUNT(*) OVER (t.ano_mes)and
COUNT(*) OVER (t.site)but it runs the sum until the end of table, and do not restarts as the
As soon as
t.sitechanges, the counter should restart.
Retrieve multiple status into one row based on multiple conditions SQL 2008
I need to retrieve multiple status into one row based on the previous day using status date, based on specific statuses and grouped by ID.
This is how the table looks like:
ID | _Status 101 | Processing > Tracking 102 | Approved > Reviewing > Approved 107 | Tracking > Received
Status category list: (Tracking, Approved, Reviewing, Received, Testing, Closed)
1- The StatusDate must be yesterday and _Status should be in the category list from above.
2-If there are statuses change from previous day but the status that change is not in the list then the ID should not come up.
3-If there is only one status that change from the previous day and that status are in the list but the previous status wasn't the previous day or the status is not in the list then the ID should come up regardless those conditions, but again if there is only one status from the previous day. Example (ID 101).
What I tried:
WITH PARTITIONED AS( SELECT ID, _Status, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY StatusDate) AS StatusNumber, COUNT(*) OVER (PARTITION BY ID) AS StatusCount FROM CTE), CONCATENATED AS( SELECT ID, CAST(_StatusAS VARCHAR(MAX) ) AS ALLSTATUS, _Status, STATUSNUMBER, StatusCount FROM PARTITIONED WHERE StatusNumber = 1 UNION ALL SELECT P.ID, CAST(C.ALLSTATUS+ '> ' +P._Status AS nvarchar), P.ID, P.StatusNumber, P.StatusCount FROM PARTITIONED P INNER JOIN CONCATENATED AS C ON P.ID = C.ID AND P.StatusNumber = C.StatusNumber + 1) SELECT ID, ALLSTATUS FROM CONCATENATED WHERE StatusNumber = StatusCount
Any help would be appreciated.
Populating table based on stored procedure results
I'm new to SQL and need to populate a table based on results from a stored procedure from another server. Local server has a table with a row number for each customer and some of the customer details ie address. Local server has another table with the customer details ie name etc which is blank.
At present the app loads the local address table but gets the customer name from a SP running on a remote server to get the rest of the customer details for each request. The wish is to bring the data from the remote database which we can only access by SP and populate the local customer table thereby de-linking the remote from the local database Any pointers would be gratefully received
Option Strict On and SQL stored procedure output parameter of nullable integer
There are several topics in this forum that come tantalisingly close to providing an answer to my question, but not quite what I need.
I am writing in VB.Net, retrieving data via TableAdapters and stored procedures. The stored procedures can return one or more Output parameters in the form of nullable integers.
This is some old code that I am revisiting and tidying up, including the addition of Option Strict On, and the Stored Procedure returns a zero where previously it returned a correct value. I can circumvent the problem but I would like to understand what "best practice" dictates for this circumstance.
This is the code before Option Strict was applied, and returns the correct value in the two Output parameters: RetVal (the return code, defined as an Enum) and UnspecifiedCategoryID (defined as an integer type).
Using oCategoriesTableAdapter As New YachtManagementDataSetTableAdapters.tvf_CategoriesTableAdapter oCategoriesTableAdapter.sp_UpdateUnspecifiedCategory( RetVal:=SQLReturn, UnspecifiedCategoryID:=oCP.GviUnspecifiedCategorySubCategory, UnspecifiedCategoryAttributeValueID:=oCP.GvtSystemAttributeNames(gcsSystemAttributeNameCategory).UnspecifiedEntityID, UnspecifiedSubCategoryAttributeValueID:=oCP.GvtSystemAttributeNames(gcsSystemAttributeNameSubCategory).UnspecifiedEntityID, VesselID:=oCP.GvoActiveVessel.ID ) End Using
With Option Strict On, if I simply cast both to an integer, using CInt or use CType in order to remove the compiler error ("Option Strict On disallows narrowing from type 'Integer?' to type 'Integer'"), then I will always have a zero returned:
Using oCategoriesTableAdapter As New YachtManagementDataSetTableAdapters.tvf_CategoriesTableAdapter oCategoriesTableAdapter.sp_UpdateUnspecifiedCategory( RetVal:=CType(SQLReturn, Integer), UnspecifiedCategoryID:=CType(oCP.GviUnspecifiedCategorySubCategory, Integer), UnspecifiedCategoryAttributeValueID:=oCP.GvtSystemAttributeNames(gcsSystemAttributeNameCategory).UnspecifiedEntityID, UnspecifiedSubCategoryAttributeValueID:=oCP.GvtSystemAttributeNames(gcsSystemAttributeNameSubCategory).UnspecifiedEntityID, VesselID:=oCP.GvoActiveVessel.ID ) End Using
I can circumvent the problem using this code:
Using oCategoriesTableAdapter As New YachtManagementDataSetTableAdapters.tvf_CategoriesTableAdapter oCategoriesTableAdapter.sp_UpdateUnspecifiedCategory( RetVal:=CType(SQLReturn, Integer), UnspecifiedCategoryID:=TestNullableInteger, UnspecifiedCategoryAttributeValueID:=oCP.GvtSystemAttributeNames(gcsSystemAttributeNameCategory).UnspecifiedEntityID, UnspecifiedSubCategoryAttributeValueID:=oCP.GvtSystemAttributeNames(gcsSystemAttributeNameSubCategory).UnspecifiedEntityID, VesselID:=oCP.GvoActiveVessel.ID ) End Using If TestNullableInteger.HasValue Then oCP.GviUnspecifiedCategorySubCategory = TestNullableInteger.Value Else oCP.GviUnspecifiedCategorySubCategory = 0 End If
Another alternative is to change the data type of GviUnspecifiedCategorySubCategory itself to that of a nullable integer and check there as to whether a value has been returned.
Friend Property GviUnspecifiedCategorySubCategory As Integer? Get Return _lviUnspecifiedCategorySubCategory End Get Set(Value As Integer?) If Value.HasValue Then _lviUnspecifiedCategorySubCategory = Value Else _lviUnspecifiedCategorySubCategory = 0 End If End Set End Property
However, if the stored procedure has three or four Output parameters then, using this approach, the recoding starts to become onerous. The GetValueOrDefault method shows promise but, using a TableAdapter, I cannot see how this would work.
There's always the chance that I've stopped seeing the wood for the trees.
Any suggestions would be much appreciated.
External Stored Procedures in Big Query
I know we can call a Java External Stored Procedure (JXSP) for Terdata and other databases. For Teradata (reference)
- We first write our External Stored Procedure logic in a Java file.
- We compile the file and create a jar of it.
- We install the jar on our database (provided we specific rights).
- we then create the procedure mapping it to the respective JXSP.
I want to know is there a similar way where we can call a Java External Stored Procedure from a BigQuery Stored Procedure?
Update query in same stored procedure in Oracle
I want to write the logic of
UPDATEfor the same table in same stored procedure..Below is my SP.
PROCEDURE INSERT_PROJECT_MST ( P_PROJECTNO IN NVARCHAR2, P_CRNO IN NVARCHAR2, P_APPNAME IN NVARCHAR2, P_APPFUNCDESC IN NVARCHAR2, P_AZUREFEATNO IN NVARCHAR2, P_AZUREFEATDESC IN NVARCHAR2, P_PROJMANAGER IN NVARCHAR2, P_PROJLEAD IN NVARCHAR2, P_REQBY IN NVARCHAR2, P_BUSINESSCAT IN NVARCHAR2, P_BUSINESSUSERCAT IN NVARCHAR2, P_FEATUREID IN NVARCHAR2, P_ISMGISRNO IN NVARCHAR2, P_REPLICARR IN NVARCHAR2, P_PRODRR IN NVARCHAR2, P_USERSTORY IN NVARCHAR2, P_ASSIGNEDBY IN NVARCHAR2, P_ASSIGNEDTO IN NVARCHAR2, P_CREATEDBY IN NVARCHAR2, P_LASTUPDBY IN NVARCHAR2, TBLDATA OUT NVARCHAR2 ) AS V_PROJ_ID NUMBER:=0; BEGIN -- SELECT COUNT(MST_ID) INTO V_PROJ_ID FROM TBL_PROJECT_MST_INFO WHERE PROJECT_NO = P_PROJECTNO; INSERT INTO TBL_PROJECT_MST_INFO ( PROJECT_NO, CR_NO, APPLICATION_NAME, APP_FUNC_DESC, AZURE_FEATURE_NO, AZURE_FEATURE_DESC, PROJECT_MANAGER_NAME, PROJECT_LEAD_NAME, REQUESTED_BY, BUSINESS_CATEGORY, BUSINESS_USER_CATEGORY, RATIONAL_FEATURE_ID, ISMG_ISR_NO, REPLICA_RR, PROD_RR, USER_STORY, ASSIGNED_BY, ASSIGNED_TO, CREATED_BY, LAST_UPDATED_BY, CREATED_DATE, LAST_UPDATED_DATE ) VALUES ( P_PROJECTNO, P_CRNO, P_APPNAME, P_APPFUNCDESC, P_AZUREFEATNO, P_AZUREFEATDESC, P_PROJMANAGER, P_PROJLEAD, P_REQBY, P_BUSINESSCAT, P_BUSINESSUSERCAT, P_FEATUREID, P_ISMGISRNO, P_REPLICARR, P_PRODRR, P_USERSTORY, UPPER(P_ASSIGNEDBY), UPPER(P_ASSIGNEDTO), P_CREATEDBY, P_LASTUPDBY, SYSDATE, SYSDATE ) RETURNING V_PROJ_ID INTO TBLDATA; TBLDATA:='Record Saved Succesfully'; END INSERT_PROJECT_MST;
Also below is the table description for the same.
Name Null Type ---------------------- -------- -------------- ID NUMBER MST_ID NOT NULL NUMBER PROJECT_NO NVARCHAR2(100) CR_NO NVARCHAR2(100) APPLICATION_NAME NVARCHAR2(255) APP_FUNC_DESC CLOB AZURE_FEATURE_NO NVARCHAR2(155) AZURE_FEATURE_DESC CLOB PROJECT_MANAGER_NAME NVARCHAR2(100) PROJECT_LEAD_NAME NVARCHAR2(100) REQUESTED_BY NVARCHAR2(100) BUSINESS_CATEGORY NVARCHAR2(100) BUSINESS_USER_CATEGORY NVARCHAR2(100) RATIONAL_FEATURE_ID NVARCHAR2(100) ISMG_ISR_NO NVARCHAR2(100) REPLICA_RR NVARCHAR2(100) PROD_RR NVARCHAR2(100) USER_STORY NVARCHAR2(500) CREATED_BY NVARCHAR2(100) CREATED_DATE DATE ASSIGNED_TO NVARCHAR2(100) ASSIGNED_BY NVARCHAR2(100) IS_ACTIVE CHAR(1) LAST_UPDATED_BY NVARCHAR2(100) LAST_UPDATED_DATE DATE COLUMN3 VARCHAR2(20) COLUMN4 VARCHAR2(20) COLUMN5 VARCHAR2(20)
MST_IDis my unique identity column based on which I will update the table.
How to create a Data model in AWS Redshift
I'm quite new using AWS Redshift and I would like some suggestions.
Coming from a Microsoft environment, when I had to report using Power BI, I would create a Data model using SSAS, and connect Power BI to my SSAS Model.
I now have to report using AWS Redshift source, and I would like a way to create a Data model on it before reporting.
What option is available for such a scenario?
Aggregating two values in same select statement. Second aggregation is decreasing in value for each row for some reason
I'm currently trying to aggregate two values simultaneously in one select statement; however, the second aggregated value is decreasing for some reason. I know what I'm doing is wrong, but I don't understand why it's wrong (assuming it's the very last code block). Mainly just trying to better understand what's going on, and why it's happening.
I already have a corrected query that works (at the bottom)
Note: Query and outputs are simplified, please ignore any syntax issues. Additionally, in real query, I need to keep
subscription_start_datefield in until the end.
Query with issue (very last block):
WITH max_product_user_count AS ( -- The total count is obtained when "days" = 0 SELECT subscription_start_date, datediff('days', subscription_start_date, subscription_date) AS days, product, num_users AS total_user_count FROM users WHERE days = 0 ), daily_product_user_count AS ( -- As "days" go up, the number of subscribers for each start date/product type decreases SELECT subscription_start_date, datediff('days', subscription_start_date, subscription_date) AS days, product, num_users AS daily_user_count FROM users WHERE days IN (0,5,14,21,30,33,60) ) -- Trying to aggregate by product and day, across all subscription start dates SELECT d.product, d.days, SUM(daily_user_count) AS daily_count, SUM(total_user_count) AS total_count FROM daily_product_user_count d INNER JOIN max_product_user_count m ON d.subscription_start_date = m.subscription_start_date AND d.product = m.product GROUP BY 1,2 ORDER BY 1,2
PRODUCT DAYS DAILY_COUNT TOTAL_COUNT product_1 0 10000 10000 product_1 5 99231 99781 product_1 14 96124 98123 product_1 21 85123 96441 product_1 30 23412 94142 product_1 33 12931 92111 product_1 60 10231 90123
PRODUCT DAYS DAILY_COUNT TOTAL_COUNT product_1 0 10000 10000 product_1 5 99231 10000 product_1 14 96124 10000 product_1 21 85123 10000 product_1 30 23412 10000 product_1 33 12931 10000 product_1 60 10231 10000
Updated correct query:
WITH max_product_user_count AS ( SELECT subscription_start_date, datediff('days', subscription_start_date, subscription_date) AS days, product, num_users AS total_user_count FROM users WHERE days = 0 ), max_user_count_aggregation AS ( SELECT product, SUM(total_user_count) AS total_count FROM max_product_user_count GROUP BY 1 ), daily_product_user_count AS ( SELECT subscription_start_date, datediff('days', subscription_start_date, subscription_date) AS days, product, num_users AS daily_user_count FROM users WHERE days IN (0,5,14,21,30,33,60) ) daily_user_count_aggregation AS ( SELECT product, days, SUM(daily_user_count) AS daily_count FROM daily_product_user_count GROUP BY 1 ) SELECT d.product, d.days, daily_count, total_count FROM daily_user_count_aggregation d INNER JOIN max_user_count_aggregation m ON d.product = m.product ORDER BY 1,2
Lambda function cant connect to redshift: FATAL: no pg_hba.conf entry for host
I understand there are a few questions related to these issues. I have seen them but could not find the correct solution.
I am using AWS Secret Manager to store the credentials and right now trying to connect with AWS Redshift using AWS Lambda Function.
Here is my lambda.py
import json import boto3 import base64 import psycopg2 from botocore.exceptions import ClientError def get_secret(secret_name): try: session = boto3.session.Session() client = session.client(service_name='secretsmanager', region_name='xx') get_secret_value_response = client.get_secret_value( SecretId=secret_name ) secret = get_secret_value_response['SecretString'] secret = json.loads(secret) return secret def lambda_handler(event, context): s = get_secret('secretname') conn = psycopg2.connect(database = s['xx'], user = s['username'], password = secret['password'], host = secret['host'], port = secret['port'] ) print('connected') cur = conn.cursor() cur.execute("""Select * from table1;""") rows = cur.fetchall() for row in rows: print(row)
ERROR: FATAL: no pg_hba.conf entry for host "::ffff:xx.xxx.xxx.xxx", user "xxxx", database "xxx", SSL off
I am using psycopg2.
I have tried inserting, " sslmode = 'require'", not working.
Any help will be appreciated
use concat function postgresql
I want to run this code but got this error
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
execute $$SELECT MAX(date) FROM $$||table_name INTO datum; IF datum is not NULL THEN execute $$DELETE FROM $$||table_name||$$ WHERE date >= $$||quote_literal(datum); ELSE datum := $$2001-01-01$$; end if; execute $$ INSERT INTO $$||table_name||$$ (number, date, departement, minutes) select number, gs.date, (case when trim(string) ~ '^ADM[0-9]$' or TRIM(string) ~ '^ADM[0-9]$' then 'ADMINISTRATION' end) as departement, sum(extract(epoch from (least(s.enddate, gs.date + interval '1 day' - greatest(s.beg, gs.date) ) ) / 60) as minutes from smp s cross join lateral generate_series(date_trunc('day', s.beg), date_trunc('day', least(s.enddate, current_date)), interval '1 day') gs(date) where (beg, enddate) overlaps (concat($$||quote_literal(datum)||$$, '00:00:00'), concat($$||quote_literal(datum)||$$, '23:59:59'))
Let's notice also that where (beg, enddate) overlaps (concat($$||quote_literal(datum)||$$, '00:00:00'), concat($$||quote_literal(datum)||$$, '23:59:59')) want to do it only when datum != $$2001-01-01$$; (not for the first iteration).
PostgreSQL: No function matches the given name and argument types. You might need to add explicit type casts
I have the following function:
create or replace function functions.send_occurrence(username_a varchar(80), time_stamp timestamp, location varchar(100)) returns table(username varchar(80), occurrences bigint) as $$ declare user_id int; begin select id into user_id from api.user where name = username_a; insert into api.event values(user_id, time_stamp, location); return query select name, count(api.event.id) from api.user left outer join api.event on api.user.id = api.event.id where name = username_a group by name; end; $$ language plpgsql;
The function is created successfully, but when I'm in the psql client and try to execute it
select functions.send_occurrence('teste1', NOW(), 'testlocation');
I get the following error message:
ERROR: function functions.send_occurrence(unknown, timestamp with time zone, unknown) does not exist HINT: No function matches the given name and argument types. You might need to add explicit type casts.
I know many people have asked this question already, but all of the answers I've seen had to do with numeric types and conversions.
PLPGSQL Function: Use Input Parameter in ORDER BY
I am trying to define a conditional ORDER BY in a plpgsql function. Something like this:
CREATE OR REPLACE FUNCTION test_function(p_sort_order TEXT, p_limit INTEGER) RETURNS TABLE ( id TEXT, test TEXT ) AS $$ BEGIN RETURN QUERY SELECT t.field1 AS id, MIN(t.field2) AS test FROM table t GROUP BY t.field1 ORDER BY CASE p_sort_order WHEN ‘asc’ THEN test END ASC, WHEN ‘desc’ THEN test END DESC END LIMIT p_limit; END; $$ LANGUAGE 'plpgsql';
It seems like the parameters only work in the WHERE, LIMIT, etc. clause but not with ORDER BY (even though I haven't found any official documentation on this).
What is a workaround to accomplish this?