SQL>
SQL> --
SQL> select * from t1;
INS NAME VALUE
---------- ---------- ----------
1 Test 12345
1 TestV1 12/10/2014
8 Test 85435
8 TestV1 11/11/2005
9 Test 42232
9 TestV1 19/10/2000
6 Test 54321
6 TestV1 11/05/2013
8 rows selected.
SQL>
SQL> --
SQL> select * from t2;
VALUE
----------
12345
54321
2 rows selected.
SQL>
SQL> -- Method 1
SQL> select x.value, z.value
2 from t1 x
3 join t2 y on (y.value = x.value)
4 join t1 z on (z.ins = x.ins and z.name = 'TestV1')
5 ;
VALUE VALUE
---------- ----------
12345 12/10/2014
54321 11/05/2013
2 rows selected.
SQL>
SQL> -- Method 2
SQL> select x.value,
2 y.testv1_value
3 from t2 x
4 join (
5 select ins,
6 max(case when name = 'Test' then value end) as test_value,
7 max(case when name = 'TestV1' then value end) as testv1_value
8 from t1
9 group by ins
10 ) y
11 on (x.value = y.test_value)
12 ;
VALUE TESTV1_VAL
---------- ----------
12345 12/10/2014
54321 11/05/2013
2 rows selected.
SQL>
SQL>
Hi ,
I used the below script to get the sql data into csv file using unix scripting.
I m getting the output into an output file but the output file is not displayed in a separe columns .
#!/bin/ksh
export FILE_PATH=/maav/home/xyz/abc/
rm $FILE_PATH/sample.csv
sqlplus -s... (2 Replies)
Hi,
I have a requirement as below which needs to be done viz UNIX shell script
(1) I have to connect to an Oracle database
(2) Exexute "SELECT field_status from table 1" query on one of the tables.
(3) Based on the result that I get from point (2), I have to update another table in the... (6 Replies)
Hi, this's Pom. I'm quite a new one for shell script but I have to do sql on shell script to query some information from database.
I found a concern to get a new line...When I run my script, it retrieves all data as wondering but it's shown in one line :(
What should I do? I'm not sure that... (2 Replies)
Hello
I have a file as below
chr1 start ref alt code1 code2
chr1 18884 C CAAAA 2 0
chr1 135419 TATACA T 2 0
chr1 332045 T TTG 0 2
chr1 453838 T TAC 2 0
chr1 567652 T TG 1 0
chr1 602541 ... (2 Replies)
Hi,
I have connected to oracle database with sqlplus -s / <<EOF
select ename, age from emp where empid=1234;
EOF
I want to save the values of ename and age in unix shell variables. Any pointers would be welcome..
Thanks in advance!!1
Cheers :):):):) (1 Reply)
Hi,
I need a shell script, which would search the result values from another files.
1)execute " select column1 from table_name" query on the table.
2)Based on the result, need to be grep from .wft files.
could please explain about this.Below is the way i am using.
#!/bin/sh... (4 Replies)
I have sql query in shell script.
select distinct account_no from adj order by account_no;
This query returns account number daily.Sometimes it may return 90 rows sometime it may return 1 row only and someday it may return 0 rows
I am storing the output of this query in sql_output.txt.
... (5 Replies)
Hello All,
I have a requirement in which i will be given a sql query as input in a file with dynamic number of columns. For example some times i will get 5 columns, some times 8 columns etc up to 20 columns.
So my requirement is to generate a output query which will have 20 columns all the... (7 Replies)
Given this file (I separated them in block to make my explanation clearer):
92157768877;Sof_deme_Fort_Email_am_%yyyy%%mm%%dd%;EMAIL;20/02/2015;1;0;0
92157768877;Sof_trav_Fort_Email_am_%yyyy%%mm%%dd%;EMAIL;20/02/2015;1;0;0
91231838895;Sof_deme_faible_Email_am;EMAIL;26/01/2015;1 0;0... (1 Reply)
Hi,
I'm trying to asign to ksh varible some values of a sql query. The output query would be:
xxxx 1
yyyy 2
I do:
values=`$PATH_UTI/query_sh "
select think1||'------'||think2 from some_table
where think3 = '$1';
... (2 Replies)
Discussion started by: mierdatuti
2 Replies
LEARN ABOUT PHP
sql-acceptable-by-4d
SQL-ACCEPTABLE-BY-4D(3) 1 SQL-ACCEPTABLE-BY-4D(3)SQL acceptable by 4D - PDO and SQL 4D
4D implements strictly the ANSI 89 standard, and have it enforced. It is highly recommended to read the 4D SQL documentation to learn
about the available commands. The URL of the manual is : http://doc.4d.com/. Below is a list of 4D SQL characteristics: it is not exhaus-
tive, but may serve as an introduction.
Characteristics of 4D SQL
+-------------------------------------+--------------------------------------+---+
| Characteristics | | |
| | | |
| | Alternative | |
| | | |
| | Note | |
| | | |
+-------------------------------------+--------------------------------------+---+
| INTEGER | | |
| | | |
| | Modify the SQL to use INT. | |
| | | |
| | INT is the supported integer type in | |
| | 4Dv12.0. | |
| | | |
| CHAR | | |
| | | |
| | Use VARCHAR instead. | |
| | | |
| | Unsupported in 4Dv12.0 | |
| | | |
| UNION | | |
| | | |
| | Unsupported. Make separate queries. | |
| | | |
| | Unsupported in 4Dv12.0 | |
| | | |
| SELECT 1 + 1; | | |
| | | |
| | SELECT 1 + 1 FROM _USER_SCHEMAS; | |
| | | |
| | FROM is required | |
| | | |
| FLOAT | | |
| | | |
| | Cast the FLOAT value into a FLOAT or | |
| | STRING, with an SQL 4D function | |
| | (CAST, ROUND, TRUNC or TRUNCATE) | |
| | | |
| | Unsupported in current versions of | |
| | the PDO_4D driver | |
| | | |
| Strong typing | | |
| | | |
| | Take care your SQL query, or your | |
| | PHP code provides data with the | |
| | expected type | |
| | | |
| | One must provide the right type | |
| | that 4D expect. One can't insert '1' | |
| | (as a string) in an INTEGER column. | |
| | | |
|PDO.execute(row)(3) : only works if | | |
|all the table's column are of type | | |
|TEXT or VARCHAR | | |
| | | |
| | Use the prepared statements, and use | |
| | the right types. | |
| | | |
| | The PDO extension cast all values | |
| | through execute() as string, and | |
| | expect the SQL database to parse the | |
| | values. | |
| | | |
| SELECT NULL FROM TABLE | | |
| | | |
| | Do not use NULL constants. Extract | |
| | them from the table | |
| | | |
| | It is not allowed to use the NULL | |
| | constant in the select list | |
| | | |
| SELECT * FROM TABLE WHERE 1 | | |
| | | |
| | Use WHERE 1 = 1 | |
| | | |
| | A constant can't be used in a WHERE | |
| | clause | |
| | | |
| SHOW TABLES | | |
| | | |
| | Use system tables | |
| | | |
| | The list of tables, schemas, index, | |
| | etc. are in these system tables : | |
| | _USER_TABLES, _USER_COLUMNS, | |
| | _USER_INDEXES, _USER_CONSTRAINTS, | |
| | _USER_IND_COLUMNS, _USER_CONS_COL- | |
| | UMNS, and _USER_SCHEMAS. | |
| | | |
| SQL structure delimiter | | |
| | | |
| | Use the following function to pro- | |
| | tect SQL elements: function | |
| | sqlEscapeElement(elem) { return '[' | |
| | . str_replace(']',']]', $elem) . | |
| | ']'; } | |
| | | |
| | To escape SQL elements names | |
| | (tables, fields, users, groups, | |
| | schema, primary key, etc.), the | |
| | whole identifier must be between | |
| | square brackets, and the closing | |
| | brackets ']' must be doubled. | |
| | | |
+-------------------------------------+--------------------------------------+---+
PHP Documentation Group SQL-ACCEPTABLE-BY-4D(3)