Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: Convert MS Access Queries to AIX SQL
Operating Systems AIX Convert MS Access Queries to AIX SQL Post 302925349 by techstudent01 on Sunday 16th of November 2014 03:22:18 PM
Old 11-16-2014
vbe,
I'm sorry to confuse you. Maybe if I give you a visual, it might explain a little more clearly what I need to do. Below is my MS access SQL query statement:
Code:
qryctnsReceived
SELECT Sum((dbo_e538a_rcv_dtl!rcv_qty_amt)/(dbo_item!case_qty)) AS Ctns
FROM dbo_e537a_rcv_hdr INNER JOIN (dbo_e538a_rcv_dtl INNER JOIN dbo_item ON dbo_e538a_rcv_dtl.t024_itm_nbr = dbo_item.itm_num) ON (dbo_e537a_rcv_hdr.e058_po_nbr = dbo_e538a_rcv_dtl.e058_po_nbr) AND (dbo_e537a_rcv_hdr.e537_rr_nbr = dbo_e538a_rcv_dtl.e537_rr_nbr) AND (dbo_e537a_rcv_hdr.t063_lct_nbr = dbo_e538a_rcv_dtl.t063_lct_nbr)
WHERE (((dbo_e538a_rcv_dtl.upd_dm) Between GetStartTime() And GetEndTime()) AND ((dbo_e538a_rcv_dtl.rcv_qty_amt)<>0) AND ((dbo_item.case_qty)<>0) AND ((dbo_e537a_rcv_hdr.ord_typ_cd)<>4) AND ((dbo_item.itm_class)<>758));

I've already started working on this project just to get a head start. I've massaged the code so it will work with in AIX and the following code is below. To clarify, I have DB2 on AiX and I need to convert MS access SQL queries to DB2 queries within Aix. I converted the above code below to this:
Code:
NAME[1]="Receiving Reg Ctns"
QUERY="SELECT Sum((e538a_rcv_dtl.rcv_qty_amt)/(item.case_qty)) AS Ctns"
QUERY=$QUERY" FROM e537a_rcv_hdr INNER JOIN (e538a_rcv_dtl INNER JOIN item ON e5
38a_rcv_dtl.t024_itm_nbr = item.itm_num) ON (e537a_rcv_hdr.e058_po_nbr = e538a_r
cv_dtl.e058_po_nbr) AND (e537a_rcv_hdr.e537_rr_nbr = e538a_rcv_dtl.e537_rr_nbr)
AND (e537a_rcv_hdr.t063_lct_nbr = e538a_rcv_dtl.t063_lct_nbr)"
QUERY=$QUERY" WHERE (((e538a_rcv_dtl.upd_dm) Between ('"$STARTTIME"') And ('"$EN
DTIME"')) AND ((e538a_rcv_dtl.rcv_qty_amt)<>0) AND ((item.case_qty)<>0) AND ((e5
37a_rcv_hdr.ord_typ_cd)<>4) AND ((item.itm_class)<>758))"
QUERY[1]=$QUERY

After much trial and error, I finally got it to work. I've been working on it all day, trying to figure out what I need to do. When looking at the above code, I believe this is a DB2 SQL statement. I just have 325 more to convert and I thought that maybe there was an easier way than each individual query manually. So to highlight my changes from the above code to this new code--> I removed the "dbo_" prefix on all my tables-->I replaced all my "!" exclamation marks with a period "."-->and date functions had to be converted to text. . .

I hope this helps. . .
Please let me know
 

10 More Discussions You Might Find Interesting

1. UNIX for Dummies Questions & Answers

SQL queries in background?

I have to query a DB2 database, and sometimes they take a long time to produce results. Can I run these queries in the background, and if so, where will the results appear? (1 Reply)
Discussion started by: jpprial
1 Replies

2. UNIX for Dummies Questions & Answers

shell script for sql queries

Hi All, I have written 4 sql queries . Now I want to write one SHELL SCRIPTING program for all these queries... i.e 1.select * from head; 2. select * from detail; 3. delete from head; 4. delete from detail; Please let me know how to write a shell script... Thank you (1 Reply)
Discussion started by: user71408
1 Replies

3. Shell Programming and Scripting

Select SQL Queries Option

count.sh#!/bin/ksh SQL1=`sqlplus -s usr/pwd @count.sql $1 $2 $3` SQL2=`sqlplus -s usr/pwd @selectall.sql $1 $2 $3` LIST="Count Select_All" select i in $LIST do if then echo $SQL1 elif then echo $SQL2 fi done (2 Replies)
Discussion started by: killboy
2 Replies

4. Shell Programming and Scripting

Nested SQL queries within Shell script

Hi, Would someone know if I can fire nested sql queries in a shell script? Basically what I am trying to do is as follows: my_sql=$(sqlplus -s /nolog<<EOF|sed -e "s/Connected. *//g" connect... (2 Replies)
Discussion started by: shrutihardas
2 Replies

5. Emergency UNIX and Linux Support

Insert data into sql queries from a file

Hello friends, I need to insert data from a file to another. I need this to form an sql query file which will consist of 50.000 INSERT INTO sentences. my sql query file will consist of 50.000 times the below line consecutively: insert into subscriber... (6 Replies)
Discussion started by: EAGL€
6 Replies

6. Shell Programming and Scripting

Script (with sql queries) not working using cron

Hi all, I have script, which performing sql queries and put output into file. When I run this script manually, its working fine, but when I want to schedule it with cron I am getting errors... I defined LD_LYBRARY_PATH and ,but no result. After I defined it, I am getting error: # more... (4 Replies)
Discussion started by: nypreH
4 Replies

7. Shell Programming and Scripting

Code needed to get sql queries

Hi i need code to get sql queries through a shell script for a text file input which contain the service ids iputfile I-H-2048-10GB-M I-H-4096-12GB-M I-H-2048-p1000-M the code should contain below queries among which service_id is replacable with value from input file. ... (4 Replies)
Discussion started by: surender reddy
4 Replies

8. Shell Programming and Scripting

Help with storing the output of multiple sql queries to a file

Hi All, I have a file queries.txt as follows : SELECT COLUMN1 FROM SCHEMA2.TABLE1 MINUS SELECT COLUMN1 FROM SCDEMA2.TABLE2; SELECT COLUMN2 FROM SCHEMA2.TABLE1 MINUS SELECT COLUMN2 FROM SCDEMA2.TABLE2; SELECT COLUMN3 FROM SCHEMA2.TABLE1 MINUS SELECT COLUMN3 FROM SCDEMA2.TABLE2; SELECT... (2 Replies)
Discussion started by: SriRamKrish
2 Replies

9. Programming

Join 2 SQL queries into one

Dear community, could someone help me to join 2 queries into one? Basically it's the same query with different clauses (please notice the FIELD3 filters and related counters into the subquery): SELECT A.FIELD1,A.FIELD2,A.FIELD3 FROM TABLE A INNER JOIN ( SELECT FIELD1,... (3 Replies)
Discussion started by: Lord Spectre
3 Replies

10. Shell Programming and Scripting

Storing multiple sql queries output into variable by running sql command only once

Hi All, I want to run multiple sql queries and store the data in variable but i want to use sql command only once. Is there a way without running sql command twice and storing.Please advise. Eg : Select 'Query 1 output' from dual; Select 'Query 2 output' from dual; I want to... (3 Replies)
Discussion started by: Rokkesh
3 Replies

LEARN ABOUT DEBIAN

sql::reservedwords

SQL::ReservedWords(3pm) 				User Contributed Perl Documentation				   SQL::ReservedWords(3pm)

NAME

       SQL::ReservedWords - Reserved SQL words by ANSI/ISO

SYNOPSIS

	  if ( SQL::ReservedWords->is_reserved( $word ) ) {
	      print "$word is a reserved SQL word!";
	  }

DESCRIPTION

       Determine if words are reserved by ANSI/ISO SQL standard.

METHODS

       is_reserved( $word )
	   Returns a boolean indicating if $word is reserved by either "SQL:1992", "SQL:1999" or "SQL:2003".

       is_reserved_by_sql1992( $word )
	   Returns a boolean indicating if $word is reserved by "SQL:1992".

       is_reserved_by_sql1999( $word )
	   Returns a boolean indicating if $word is reserved by "SQL:1999".

       is_reserved_by_sql2003( $word )
	   Returns a boolean indicating if $word is reserved by "SQL:2003".

       reserved_by( $word )
	   Returns a list with SQL standards that reserves $word.

       words
	   Returns a list with all reserved words.

EXPORTS

       Nothing by default. Following subroutines can be exported:

       is_reserved
       is_reserved_by_sql1992
       is_reserved_by_sql1999
       is_reserved_by_sql2003
       reserved_by
       words

SEE ALSO

       SQL::ReservedWords::DB2

       SQL::ReservedWords::MySQL

       SQL::ReservedWords::ODBC

       SQL::ReservedWords::Oracle

       SQL::ReservedWords::PostgreSQL

       SQL::ReservedWords::SQLite

       SQL::ReservedWords::SQLServer

       ISO/IEC 9075:1992 Database languages -- SQL

       ISO/IEC 9075-2:1999 Database languages -- SQL -- Part 2: Foundation (SQL/Foundation)

       ISO/IEC 9075-2:2003 Database languages -- SQL -- Part 2: Foundation (SQL/Foundation)

AUTHOR

       Christian Hansen "chansen@cpan.org"

COPYRIGHT

       This program is free software, you can redistribute it and/or modify it under the same terms as Perl itself.

perl v5.8.8							    2008-03-28						   SQL::ReservedWords(3pm)
All times are GMT -4. The time now is 08:20 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy