Convert MS Access Queries to AIX SQL Post: 302925349

Sponsored Content

Operating Systems AIX Convert MS Access Queries to AIX SQL Post 302925349 by techstudent01 on Sunday 16th of November 2014 03:22:18 PM

11-16-2014

Registered User

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

techstudent01

View Public Profile for techstudent01

Find all posts by techstudent01

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?

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

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

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...

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. 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...

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. ...

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...

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,...

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...

LEARN ABOUT OPENDARWIN

fc-query

FC-QUERY(1)															       FC-QUERY(1)

NAME

       fc-query - query font files

SYNOPSIS

       fc-query [ -Vh ]

	[   [  -b  ]   [  --ignore-blanks  ]  ]  [  [ -i index ]  [ --index index ]  ]	[  [ -f format ]  [ --format format ]  ]  [ --version ]  [
       --help ]  font-file...

DESCRIPTION

       fc-query queries font-file(s) using the normal fontconfig rules and prints out font pattern for each face found.  If --index is given, only
       one face of each file is queried, otherwise all faces are queried.

OPTIONS

       This  program follows the usual GNU command line syntax, with long options starting with two dashes (`-'). A summary of options is included
       below.

       -b     Ignore blanks to compute languages

       -i     Only query face indexed index of each file.

       -f     Format output according to the format specifier format.

       -V     Show version of the program and exit.

       -h     Show summary of options.

       font-file
	      Query font-file for font faces.

RETURN CODES

       fc-query returns error code 0 for successful parsing, or 1 if any errors occured or if at least one font face could not be opened.

SEE ALSO

       fc-scan(1) FcFreeTypeQuery(3) FcPatternFormat(3) fc-cat(1) fc-cache(1) fc-list(1) fc-match(1) fc-pattern(1)

       The fontconfig user's guide, in HTML format: /usr/share/doc/fontconfig/fontconfig-user.html.

AUTHOR

       This manual page was updated by Behdad Esfahbod <behdad@behdad.org>.

								   Aug 13, 2008 						       FC-QUERY(1)

10 More Discussions You Might Find Interesting

1. UNIX for Dummies Questions & Answers

SQL queries in background?

Discussion started by: jpprial

2. UNIX for Dummies Questions & Answers

shell script for sql queries

Discussion started by: user71408

3. Shell Programming and Scripting

Select SQL Queries Option

Discussion started by: killboy

4. Shell Programming and Scripting

Nested SQL queries within Shell script

Discussion started by: shrutihardas