w3hello.com logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML videos Categories
Need help to review Postgre SQL query
The syntax is incorrect, you should use either VARCHAR(50) or CHARACTER VARYING(50), not VARCHAR VARYING(50). Eg. DECLARE _sql VARCHAR(50); _whereClause VARCHAR(50);

Categories : Postgresql

Oracle JDBC: How to know which row throws unique key constraint?
If you know the column that can cause the exception you can use (Oracle specific) SELECT col FROM bd_vehicles_temp INTERSECT SELECT col FROM bd_vehicles_temp_1; to identify all rows that are in both tables.

Categories : Java

oracle sql 2 records per day per unique fields, how to get the latest
You would use the analytic function row_number(): create view vw_afternoon as select id, field_a, field_b, date_field, value_field from (select t.*, row_number() over (partition by field_a, field_b, date_field order by id desc ) as seqnum from t ) t where seqnum = 1; The function row_number() assigns a sequential value to rows within a group (defined by the partition clause). The one with the highest id gets a value of 1, followed by the rest (as defined by the order by clause).

Categories : SQL

How to Generate Unique Serial Numbers for Oracle Database
The name less horse is right. For oracle you need to create a sequence and use that to generate the ID. The annotations will look like this @id @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="ENTITY_SEQUENCE") @SequenceGenerator(name="ENTITY_SEQUENCE", sequenceName="ENTITY_SEQUENCE") It looks like you are going to define the id in a super class intended to be extended. If that is the case strongly consider moving the IDs to a sub class level. In a large enterprise application with many objects you may want to use separate ID sequences for separate entities.

Categories : Java

How to give a unique constraint to a combination of columns in Oracle?
ALTER TABLE table_name DROP CONSTRAINT myUniqueConstraint; ALTER TABLE table_name ADD CONSTRAINT myUniqueConstraint UNIQUE(B, C, D); For more details

Categories : SQL

does Unique constraint on multiple columns has performance issues -Oracle
Unique constraints are enforced though an index. So there are additional reads involved in the enforcement process. However, the performance impact of the constraint is minimal compared to the performance impact incurred by resolving duplicate keys in the database. Not to mention the business impact of such data corruption. Besides, 50000 rows is a toy-sized table. Seriously, you won't be able to measure the difference of an insert with and without the constraints.

Categories : Performance

How to create a blob to a file content and save it to postgre sql using ruby
if you are looking for a gem that does this, carrier-wave should be helpflul: https://github.com/diogob/carrierwave-postgresql

Categories : Ruby On Rails

Oracle/J2EE Hibernate : ORA-00001: unique constraint violated due to HBM generated sequence
May be the current value of the sequence is conflicting with the primary key of your row. You can verify this by using the following query. SELECT trans_log_id.curr_val FROM dual; And comparing this number with the maximum value of the column LOG_ID. These two values should be either equal or the current value of the sequence should be greater. If the current value of the sequence is lesser, use the statement trans_log_id.next_val to increment the value of the sequence.

Categories : Java

Postgre multiple record delete query in the database with checkboxes using Ajax function returns nothing and doesn't work
As always, don't guess, check logs, debug, etc. If you need to log statements in PostgreSQL temporarily you can do something like: ALTER database foo SET log_statements=all' Then when you are done: ALTER DATABASE foo RESET log_statements; But the point is, see what is being passed, if it is being quoted before you send it, etc. And also note that your current approach is prone to SQL injection. You want to go a parameterized route and this may mean being a little more creative in how you generate your query string.

Categories : PHP

Neo4j 2.0 cypher enforce unique username (create unique node or fail)
Not thread safe yet, but MERGE is the proper way to do this. You could do something like this, that sort of checks for a property matching something before returning. This kind of depends on the fact that timestamp() returns the same value for an entire query, which is documented: http://docs.neo4j.org/chunked/milestone/query-functions-scalar.html#functions-timestamp. MERGE (u:User { username:"test" }) ON CREATE u SET u.created = timestamp() WITH u WHERE u.created = timestamp() RETURN u The second time you run this, you don't get any results.

Categories : Neo4j

assigning unique id and unique name to dynamically created element using global variable
Change: cell1.innerHTML="<td> <input type='text' name='nametext+count' id='nametext+count' required >"; To: cell1.innerHTML="<td> <input type='text' name='" + nametext+count + "' id='" + nametext+count + "' required >"; String literals are not interpreted in JavaScript.

Categories : Javascript

Laravel 4 - how to use a unique validation rule / unique columns with soft deletes?
Your Eloquent model should have the $softDeletes property set. If so, then when you perform a WHERE check, like User::where('username', 'jimbob'), Eloquent will automatically add in the query WHERE deleted_at IS NULL... which excludes soft deleted items.

Categories : Laravel

Unique Clustered Index - can I make non-unique without drop/create
Is the clustered index also the primary key? If the clustered index is the primary key, then a unique clustered is also created. This mean you have to drop and create. If the clustered index is not the primary key, you can use with drop existing to drop/create the index. e.g. create clustered index MyIndex on MyTable(MyColumn[s]) with(drop_existing=on);

Categories : Sql Server

Split multiple delimited string into unique rows - basically return unique words of sentences from table
E.g. this blog post applies to your question: Splitting a comma delimited string the RegExp way, Part Two. My answer is derived directly the blog: with data_(id_, str) as ( select 1, 'LEVI,GRADY Whitley' from dual union all select 2, 'Levi Grady;Whitley' from dual union all select 3, 'BEVIS,GRADY Leblanc' from dual union all select 4, 'aladdin grady;green' from dual union all select 5, 'ULLA,GRADY Holman' from dual union all select 6, '1aar,1bar;1car 1dar,1ear' from dual ) select distinct lower(regexp_substr(str, '[^,;[:space:]]+', 1, rownum_)) as splitted from data_ cross join (select rownum as rownum_ from (select max(regexp_count(str, '[,;[:space:]]')) + 1 as max_ from data_ ) connect by level <= ma

Categories : Regex

Adding unique two-column index with already not unique data
Run this query in your database console: SELECT *, COUNT(*) as n FROM table_name group by column_name HAVING n>1 Fix the duplicate rows Re-run your migration IMHO, you should edit your duplicate data manually so that you can be sure the data is correctly fixed. Update: OP didn't mention he/she is using Postgres and I gave a solution for MySQL. For Postgres: Based on this solution: Find duplicate rows with PostgreSQL Run this query: SELECT * FROM ( SELECT id, ROW_NUMBER() OVER(PARTITION BY merchant_Id, url ORDER BY id asc) AS Row FROM Photos ) dups WHERE dups.Row > 1 More explanation: In order for you to execute the migration and add unique constraint to your columns, you need to fix the current data first. Usually, there's no automatic step for this in order to mak

Categories : Ruby On Rails

Unique and non-unique lists of values in a data frame in R
You can use the %in% expression to find whether values of V2 exist in both dataframes. Use the not expression (!) to find those that do not exist in both dataframes, and then bind the results together from both of those. > rbind(Data1[!Data1$V2 %in% Data2$V2,], Data2[!Data2$V2 %in% Data1$V2,]) V1 V2 5 7 RE 2 2 ZZ > unique(rbind(Data1[Data1$V2 %in% Data2$V2,], Data2[Data2$V2 %in% Data1$V2,])) V1 V2 1 1 AB 2 3 XY 3 5 DH 4 8 ST On this last piece: if every V1,V2 combination will be the same, you can simply write Data1[Data1$V2 %in% Data2$V2,] and save yourself some lines of code.

Categories : R

What is the proper way to deal with non-unique rows that will become unique
Your concern is with the constraints. One method of fitting the multiple shifts into the current schema is to "invent" place-holder users. If you only needed to support two shifts at the same time, you can just set the user to NULL and the constraint is ok. For more than two shifts, create new user ids -- perhaps with negative ids so they are obvious -- that really mean "there is no user for this shift yet".

Categories : SQL

How to get a unique alphanumeric based on a unique integer
Simply, you can use Integer.toString(int i, int radix). Since you have base 36(26 letters+10 digits) you set the radix to 36 and i to your integer. For example, to use 16501, do: String identifier=Integer.toString(16501, 36); You can uppercase it with .toUpperCase() Now onto your other questions, yes, you should query the database first to ensure it doesn't exist. If depending on the database, it may need to be synchronized, or it may not be as it'll use its own locking system. In any case, you'd need to tell us which database. On the question of whether there's a builtin, we'd need to know the DB type as well.

Categories : Java

Unique SharedObjects for Unique Accounts - AS3
If you want to create multiple accounts, you could just store JSON against one of the data fields. Something that might end up looking like: so.data.users = '[ { "username": "Marty", "password": "4ec503be252d765ea37621a629afdaa6" }, { "username": "Bob", "password": "248e844336797ec98478f85e7626de4a" } ]';

Categories : Actionscript

postgresql unique constraint not unique enough
The primary key definitely defines a unique constraint. But the unique constraint is on (member,question). Your have two FOREIGN KEY constraints that references just (member) and (question) separately. I'm pretty sure what you want is: CREATE TABLE m_security_answer --defines members given answer ( member integer, question integer NOT NULL, answer character varying(255) NOT NULL, PRIMARY KEY (member,question), FOREIGN KEY (member, question) REFERENCES m_security_questions(member, question) );

Categories : Postgresql

Oracle: Fastest way to UPSERT and return the last affected Row ID in oracle for large data sets
If you're trying to return the maximum value of a sequence-generated PK on the table then I'd just run a "Select max(id) .." directly afterwards. If other sessions are also modifying the table then maybe reading the currval of the sequence would be better.

Categories : C#

Oracle 01830 error while converting string to date in oracle procedure
If you are passing in a string of the following format 01/08/2013 12:00:00 AM then in order to successfully convert that string into a date datatype you should use the following format mask 'dd/mm/yyyy hh:mi:ss AM' which includes meridian indicator: to_date(vDateFrom, 'dd/mm/yyyy hh:mi:ss AM') but how do i make it variable?it could be PM too Meridian indicators are interchangeable. For both strings 01/08/2013 2:00:00 AM and 01/08/2013 2:00:00 PM for instance, you can use date format model with one of the meridian indicators, whether it AM or PM. Here is an example: select to_date('01/08/2013 2:00:00 AM', 'dd/mm/yyyy hh:mi:ss AM') as res from dual Result: Res ----------- 01.08.2013 2:00:00 select to_date('01/08/2013 2:00:00 PM', 'dd/mm/yyyy hh:mi:ss AM') as res from du

Categories : Oracle

Oracle PL/SQL - How do i copy a large amount of data from one table to another using oracle collections
/* package header */ CREATE OR REPLACE PACKAGE perfrormance_test AS PROCEDURE nested_table; PROCEDURE associative_array; END perfrormance_test; /* package body */ CREATE OR REPLACE PACKAGE BODY perfrormance_test AS PROCEDURE nested_table AS /* select all records from source table */ CURSOR big_table_cur IS SELECT col1 , col2 , col3 FROM big_table; /* create nested table type and variable that will hold BIG_TABLE's records */ TYPE big_table_ntt IS TABLE OF big_table_cur%ROWTYPE; l_big_table big_table_ntt; BEGIN /* open pointer to SELECT statement */ OPEN big_table_cur; /* collect data in the collection */ FETCH big_table_cur BULK

Categories : SQL

Getting an error when try to connect to Oracle java.lang.ClassNotFoundException: oracle.jdbc.driver.OracleDriver
Add your Oracle JDBC Driver jar to the classpath Can download the driver for your Oracle Database Version from here

Categories : Oracle

OALL8 is in an inconsistent state in Oracle Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
This post (Oracle: OALL8 is in an inconsistent state) suggest that it's an OJDBC bug that was related to the CURSOR_SHARING mode SIMILAR. So: Check with Oracle if a fix exists. Check whether there are any differences in the settings between development and production and fix them. Set Cursor Sharing to EXACT if it's set to a different mode.

Categories : Database

getting date fields from oracle in correct format using Python and cx-oracle
You can use strftime. For example: >>> import datetime >>> print datetime.datetime.now().strftime("%Y-%m-%d %H:%M") 2013-08-13 13:10 In your case, this can make it: strftime("%d-%b-%Y") where: %b locale's abbreviated month name (e.g., Jan) %d day of month (e.g., 01) %Y year

Categories : Python

In SAS, how do I connect to a remote Oracle database by specifying the IP address of the Oracle's host?
To use SAS/Access to Oracle on your server "A", that server must have the Oracle networking client software installed and configured. Normally that is managed by an Oracle administrator on that server. It can be as simple as adding an entry into the tnsnames.ora file on your server that "points" to the Oracle instance running on your Windows machine "B". The details of what needs to be added should come from your Oracle DBA. Once you can successfully connect to that Oracle environment from your server "A" (using something like sqlplus or testing with the tnsping command), SAS will use the same connection. The point is that this does not have anything directly to do with SAS; you must set up the environment first. However, note that there are certain posit-installation steps that must

Categories : Oracle

What is location of built in SQL functions and Oracle Packages in Oracle Database
The scripts to create the build-in functions, packages and procedures are stored on the database server machine. You have to find the value of the environment variable $ORACLE_HOME, and then go to $ORACLE_HOME/rdbms/admin/. Just use grep to find the file you're looking for. If the database server is a Windows machine, look at ECHO %ORACLE_HOME% at the command prompt and proceed from there.

Categories : SQL

Connect Oracle Forms builder to Oracle XE database, both 10G
You need to configure your DevSuiteHome environment to look at your OracleXE tnsnames.ora, or copy the tns entry from the OracleXE environment to the DevSuiteHome one... In my environments, I just set the TNS_ADMIN registry key in DevSuiteHome to point to the OracleXE TNS_ADMIN directory...

Categories : Oracle

Difference in oracle 11.2.0.1.0 and oracle 11.2.0.2.0 while inserting a value into the table using sequence
The problem may have to due with the fact that NOORDER is the default with Oracle Sequences, especially if you're running a RAC environment. http://docs.oracle.com/cd/B12037_01/server.101/b10759/statements_6014.htm I've learned that with Sequences, if I want to guarantee that they be sequential I usually have to add the following keywords when creating the sequence: CREATE SEQUENCE m1_id_sq ORDER NOCACHE; Edit to refer to above comments: As noted by Alex Poole in the comments above: "This shouldn't really matter anyway - you'll get gaps in sequences for other reasons so you shouldn't rely on it starting with 1" The NOORDER being the default for sequences explains this issue. Alex Poole also noted a known issue: Oracle Note 1050193.1 (requires an Oracle Support account) rela

Categories : Oracle

SELECT unique values and the associated timestamp without having the timestamp making things unique
This is completely untested but grouping by a concat might work. SELECT mi.key, mi.value, meta.value FROM myInfo as mi JOIN metadata as meta ON mi.metaId = meta.id WHERE mi.key = 'maxWeight' AND meta.key = 'timestamp' GROUP BY CONCAT(mi.key, mi.value) Although you'll still have the problem of which timestamp is shown. For example, if you have 3 records for a given key/value pair, which record will be shown in your result set? key value timestamp maxWeight 10 tons 15:00:05 2011-01-01 maxWeight 10 tons 08:00:07 2011-10-12 maxWeight 10 tons 13:05:09 2013-08-01 The group by query will show just one of these results - but which? You'll need to think about ordering a group (which is a whole new ballgame)

Categories : Mysql

Get lines by a unique portion of the line, and display only the first occurrence of that unique portion
Yes, awk is your best bet. Here's a mysterious example: awk -F, '!seen[substr($6,4,8)]++' infile.txt Explanation: options: -F, set the field separator to , condition: substr($6,4,8) up to 8 characters starting at the fourth character of the sixth field seen[...]++ seen is an associative array (dictionary). Increment the value associated with ..., and return the old value !seen[...]++ if there was no old value, perform the action action: There is no action, only a condition, so the default action is performed if the test succeeds. The default action is to print the line. So the line will be printed if the relevant characters of the sixth field haven't yet been seen. Test: $ awk -F, '!seen[substr($6,4,8)

Categories : Bash

How do I export a 'database' from Oracle 11g XE and import it to Oracle 10.2?
EXP and IMP are ancient - do not use them unless you absolutely have to. They cannot handle some of the features of newer Oracle versions. The tools of choice are EXPDP and IMPDP (short for EXP datapump and IMP datapump). Unfortunately, using them is a little more complicated, because you can run them only on the database server (contrary to old-style EXP/IMP, which you could run from any client computer). So, to get your schema from the 11g source DB to the 10g target DB, you'll have to: open a terminal session on the 11g DB server run expdp with version set to 10 (so you can import it on the 10g server) copy the dump file fom the 11g server to the datapump directory of the 10g server (look for a directory called "dpump") open a terminal session on the 10g DB server run impdp with

Categories : Windows

Why would Oracle.ManagedDataAccess not work when Oracle.DataAccess does?
Try to add the path to tnsnames.ora to the config file: <?xml version="1.0" encoding="utf-8" ?> <configuration> <oracle.manageddataaccess.client> <version number="4.112.3.60"> <settings> <setting name="TNS_ADMIN" value="C:oracleproduct10.2.0client_1NETWORKADMIN" /> </settings> </version> </oracle.manageddataaccess.client> </configuration>

Categories : C#

when do we use unique index and unique key?
It's mainly about semantics. A unique key is an actual rule. A unique index is an index that helps Oracle to (quickly) enforce that rule. In older versions of Oracle ( < 8), creating a unique key also made Oracle create an index automatically. Later, this was changed. A great post, explaining the differences and history in more detail can be found here: http://www.jlcomp.demon.co.uk/faq/uk_idx_con.html

Categories : Oracle

Re-qwrite Oracle SQL using ANSI Oracle
Apart from the missing ON keyword for the LEFT OUTER JOIN, your query should be fine: SELECT emp_no, (SELECT emp_title FROM hr_v_employee WHERE organization_code LIKE SUBSTR(emp.depart_code, 0, 4) || '00' AND emp_position_code = (SELECT MIN(emp_position_code) FROM hr_v_employee WHERE organization_code LIKE SUBSTR(emp.depart_code, 0, 4) || '00')) || ' ' || NVL(employee_deptartment, '-') employee_deptartment FROM employees e LEFT OUTER JOIN employee_details o ON emp.emp_no = o.emp_no P

Categories : SQL

oracle -- Split multiple comma separated values in oracle table to multiple rows
Can use the below query to convert comma separated values in rows SELECT trim(x.column_value.extract('e/text()')) COLUMNS from t t, table (xmlsequence(xmltype('<e><e>' || replace(valuestring,':','</e><e>')|| '</e></e>').extract('e/e'))) x );

Categories : SQL

How to compare new value with the previous value in oracle form field when try to edit the paticular field in oracle
If I understand your description correctly, you want the form field to be editable only if the current value (retrieved from the row currently stored in the database, I presume) is NULL or in the future. To do that, I'd put some logic in the POST-QUERY trigger to disable the item, e.g. IF :MYBLOCK.MYDATEITEM <= SYSDATE THEN SET_ITEM_PROPERTY('MYBLOCK.MYDATEITEM', ENABLED, PROPERTY_FALSE); END IF; There are other ways of doing the same thing, e.g. by setting the following properties: IF :MYBLOCK.MYDATEITEM <= SYSDATE THEN SET_ITEM_PROPERTY('MYBLOCK.MYDATEITEM', NAVIGABLE, PROPERTY_FALSE); SET_ITEM_PROPERTY('MYBLOCK.MYDATEITEM', INSERT_ALLOWED, PROPERTY_FALSE); SET_ITEM_PROPERTY('MYBLOCK.MYDATEITEM', UPDATE_ALLOWED, PROPERTY_FALSE); END IF;

Categories : Oracle

create unique node or create unique index
I'm assuming this is for the stable version of neo4j(1.9) as 2.0 does change how indexing is presented. You're saying your User Node will have several properties, each unique, will these be used as a composite key, or will just the UserName be used as a Key? Neo4j has a special class for doing a type of "get or create" node, but it only works on one key, so if you were creating a composite key, you'd have to do something like concatenate yours keys. Look for the documentation here. Here is an example: public Node getOrCreateUserWithUniqueFactory(String username, GraphDatabaseService graphDb) { UniqueFactory<Node> factory = new UniqueFactory.UniqueNodeFactory(graphDb, "users") { @Override protected void initialize(Node createdNode, Map<String, Object> pr

Categories : Neo4j

Need help in converting the clob to varchar in oracle, I have to use the varchar in case function of oracle
Unfortunately SQL in Oracle supports varchars up to 4000. Your function won't work in SQL queries. You can upgrade to oracle 12c which increases this limit up to 32767 characters. However there is a simple workaround that works on 11g, here is an example of CLOBs pivot for 3 columns: SELECT (select val from xx where rowid = a_rid ) a, (select val from xx where rowid = b_rid ) b, (select val from xx where rowid = c_rid ) c from ( select max( case key when 'A' then rowid end ) a_rid, max( case key when 'B' then rowid end ) b_rid, max( case key when 'C' then rowid end ) c_rid from xx ); Here is SQLFiddle demo with 3 strings, each of them contains 7996 characters. A result row in this demo is very wide, it has over 150 "horizontal

Categories : Oracle



© Copyright 2017 w3hello.com Publishing Limited. All rights reserved.