Sponsored Content
Full Discussion: Dynamic SQL for where clause
Top Forums Shell Programming and Scripting Dynamic SQL for where clause Post 302350180 by TeSP on Thursday 3rd of September 2009 04:06:01 AM
Old 09-03-2009
Dynamic SQL for where clause

Hi,

I have an app which user can query the database based on 4 criteria, that is Field1, Field2, Field3 and Field4

Mya I know how to write a dynamic SQL where I can choose to retrieve data based on their selected value.

eg. where Field1=AAA
eg. where Field1=AAA and Field2=BBB
eg. where Field2=BBB and Field4=CCC, etc.

I have been trying to code:

mySQL="Select Field1, Field2, Field3, Field4 from table1 "

if [ $field1 != "All" ] ; then
myWhere=" where Field1='$field1' " ;
else
myWhere=" where Field1 like '%' " ;
fi

if [ $field2 != "All" ] ; then
myWhere= ${myWhere} || " minus " || ${mySQL} || ${myWhere} || " and Field2 <> '$field2' " ;
fi

if [ $field3 != "All" ] ; then
myWhere= ${myWhere} || " minus " || ${mySQL} || ${myWhere} || " and Field3 <> '$field3' " ;
fi

if [ $field4 != "All" ] ; then
myWhere= ${myWhere} || " minus " || ${mySQL} || ${myWhere} || " and Field4 <> '$field4' " ;
fi

mySQL=${mySQL} || ${myWhere} ;

sqlplus -S "$ORACCS" <<-__EOF__
${myWhere} ;
__EOF__


I am hitting syntax error while trying to compare Field2. Please advise how should I create a dynamic SQL. Thanks in advance.

Regards
 

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Dynamic SQl in KSH

My requirement is to create a KSH to generate the SQL select statement in oracle with all the columns and optional where condition if given the table name as input to the program Have any of you worked with a similar requirement? Can you give me some inputs? Regards, Kousikan (2 Replies)
Discussion started by: kousikan
2 Replies

2. UNIX for Dummies Questions & Answers

if clause

hi, pls could you help me with one program in KSH ( i have sunOS). I need to create an If clause, that prints an error message and filenames, when in a directory are found some files of null size (find . -type f -size 0 ). thanks (3 Replies)
Discussion started by: palmer18
3 Replies

3. UNIX for Advanced & Expert Users

Sql dynamic table / dynamic inserts

I have a file that reads File (X.txt) Contents of record 1: rdrDESTINATION_ADDRESS (String) "91 971502573813" rdrDESTINATION_IMSI (String) "000000000000000" rdrORIGINATING_ADDRESS (String) "d0 movies" rdrORIGINATING_IMSI (String) "000000000000000" rdrTRAFFIC_EVENT_TIME... (0 Replies)
Discussion started by: magedfawzy
0 Replies

4. Shell Programming and Scripting

Dynamic SQL query based on shell script parameters

Hi, I need a script that will run a dynamic Oracle SQL. Dynamic meaning the SQL statement depends on the parameter. For instance, something like this: #!/bin/ksh -x # Set environment . /home/mine/set_vars sqlplus $LOGINID <<! >> /home/mine/log.txt select count(1) from $1 where... (2 Replies)
Discussion started by: laiko
2 Replies

5. Shell Programming and Scripting

Dynamic sql where contents

Hi all, I need to add the contents from a file into a sql stament in the where clause. file1: id 1 2 3 10 11 ... script should look like : select name from tab_user tus where tus.id in (1,2,3,10,11..) any ideas or suggetions will be appreciatte. (5 Replies)
Discussion started by: valigula
5 Replies

6. Shell Programming and Scripting

How to grep the where clause of a SQL?

Hi UNIX Gurus, I want to use extract the where clause of a SQL present in a file. Please suggest me how can I do it. Select * from emp where emp_id>10; cat <file_name> | grep -i "where" returns whole SQL. how can I extract only "where emp_id>10;" Thanks in advance (4 Replies)
Discussion started by: ustechie
4 Replies

7. Shell Programming and Scripting

How to extract WHERE clause from a SQL using UNIX???

Hi, I need to know how to extract entire WHERE clause from a SQL statement using UNIX command. For example: If following is the SQL then how to extract entire WHERE clause: UPDATE <TABLE_NAME_1> SET <FIELD_NAME> = VALUE WHERE CONDITION_1 AND CONDITION_2 = (SELECT FIELD_NAME FROM... (15 Replies)
Discussion started by: ustechie
15 Replies

8. Linux

Shell Script to generate Dynamic Param file Using SQL Plus Quey

Hi All, Can anyone give me Shell script sample script to generate Param file by Reading Values from SQL Plus query and it should assign those values to variables like.. $$SChema_Name='ORCL' Thanks in Advance... Srav... (4 Replies)
Discussion started by: Sravana Kumar
4 Replies

9. Shell Programming and Scripting

Request: How to Parse dynamic SQL query to pad extra columns to match the fixed number of columns

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)
Discussion started by: vikas_trl
7 Replies

10. Shell Programming and Scripting

Replace sql with dynamic values

Hi Guys, I am using a function to replace the values dynamically to frame sql query by reading a file. My file will have column names like file.txt col_1 col_2 expected output: select id,col_1,col_2 from ( select a.id, a.col_1, rank() over (ORDER BY cast(a.col_1 AS double)... (5 Replies)
Discussion started by: Master_Mind
5 Replies
MooseX::Emulate::Class::Accessor::Fast(3pm)		User Contributed Perl Documentation	       MooseX::Emulate::Class::Accessor::Fast(3pm)

NAME
MooseX::Emulate::Class::Accessor::Fast - Emulate Class::Accessor::Fast behavior using Moose attributes SYNOPSYS
package MyClass; use Moose; with 'MooseX::Emulate::Class::Accessor::Fast'; #fields with readers and writers __PACKAGE__->mk_accessors(qw/field1 field2/); #fields with readers only __PACKAGE__->mk_ro_accessors(qw/field3 field4/); #fields with writers only __PACKAGE__->mk_wo_accessors(qw/field5 field6/); DESCRIPTION
This module attempts to emulate the behavior of Class::Accessor::Fast as accurately as possible using the Moose attribute system. The public API of "Class::Accessor::Fast" is wholly supported, but the private methods are not. If you are only using the public methods (as you should) migration should be a matter of switching your "use base" line to a "with" line. While I have attempted to emulate the behavior of Class::Accessor::Fast as closely as possible bugs may still be lurking in edge-cases. BEHAVIOR
Simple documentation is provided here for your convenience, but for more thorough documentation please see Class::Accessor::Fast and Class::Accessor. A note about introspection Please note that, at this time, the "is" flag attribute is not being set. To determine the "reader" and "writer" methods using introspection in later versions of Class::MOP ( > 0.38) please use the "get_read_method" and "get_write_method" methods in Class::MOP::Attribute. Example # with Class::MOP <= 0.38 my $attr = $self->meta->find_attribute_by_name($field_name); my $reader_method = $attr->reader || $attr->accessor; my $writer_method = $attr->writer || $attr->accessor; # with Class::MOP > 0.38 my $attr = $self->meta->find_attribute_by_name($field_name); my $reader_method = $attr->get_read_method; my $writer_method = $attr->get_write_method; METHODS
BUILD $self %args Change the default Moose class building to emulate the behavior of C::A::F and store arguments in the instance hashref. mk_accessors @field_names Create read-write accessors. An attribute named $field_name will be created. The name of the c<reader> and "writer" methods will be determined by the return value of "accessor_name_for" and "mutator_name_for", which by default return the name passed unchanged. If the accessor and mutator names are equal the "accessor" attribute will be passes to Moose, otherwise the "reader" and "writer" attributes will be passed. Please see Class::MOP::Attribute for more information. mk_ro_accessors @field_names Create read-only accessors. mk_ro_accessors @field_names Create write-only accessors. follow_best_practices Preface readers with 'get_' and writers with 'set_'. See original Class::Accessor documentation for more information. mutator_name_for accessor_name_for See original Class::Accessor documentation for more information. set See original Class::Accessor documentation for more information. get See original Class::Accessor documentation for more information. meta See Moose::Meta::Class. SEE ALSO
Moose, Moose::Meta::Attribute, Class::Accessor, Class::Accessor::Fast, Class::MOP::Attribute, MooseX::Adopt::Class::Accessor::Fast AUTHORS
Guillermo Roditi (groditi) <groditi@cpan.org> With contributions from: Tomas Doran (t0m) <bobtfish@bobtfish.net> Florian Ragwitz (rafl) <rafl@debian.org> LICENSE
You may distribute this code under the same terms as Perl itself. perl v5.10.0 2009-09-15 MooseX::Emulate::Class::Accessor::Fast(3pm)
All times are GMT -4. The time now is 11:33 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy