By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,483 Members | 3,229 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,483 IT Pros & Developers. It's quick & easy.

ADP: QueryDef equivalent?

P: n/a
Seems like ADP is predicated on the notion that you'd only want to use
stored procedures in the back end or in-line SQL in the front end.

Makes it really messy to develop something like
-------------------------------------
SELECT
v.SECURITY_ALIAS,
v.NEXT_REFIX_DATE AS ConversionDate,
v.RESET_DAY AS VariableRateMode,
CASE
WHEN v.RESET_DAY='MO' THEN 'Monday'
WHEN v.RESET_DAY='TU' THEN 'Tuesday'
WHEN v.RESET_DAY='WE' THEN 'Wednesday'
WHEN v.RESET_DAY='W' THEN 'Wednesday'
WHEN v.RESET_DAY='TH' THEN 'Thursday'
WHEN v.RESET_DAY='FR' THEN 'Friday'
WHEN v.RESET_DAY='F' THEN 'Friday'
WHEN v.RESET_DAY='1' THEN '1-Day'
WHEN v.RESET_DAY='7' THEN '7-Day'
WHEN v.RESET_DAY='28' THEN '28-Day'
WHEN v.RESET_DAY='35' THEN '35-Day'
WHEN v.RESET_DAY='90' THEN '90-Day'
WHEN v.RESET_DAY='D' THEN 'Daily'
WHEN v.RESET_DAY='FMO' THEN 'First of Month'
WHEN v.RESET_DAY='H' THEN 'Holiday Rules Appl'
WHEN v.RESET_DAY='MM' THEN 'Mid-Month'
WHEN v.RESET_DAY='NA' THEN Null
WHEN v.RESET_DAY='NUL' THEN Null
ELSE Null
END AS VariableRateMode_Mapped

FROM
SECURITYDBO.VARIABLE_RATE v,
(
SELECT
v0.SECURITY_ALIAS AS Security_Alias,
Max(v0.EFFECTIVE_DATE) AS Effective_Date

FROM
SECURITYDBO.VARIABLE_RATE v0

WHERE
v0.SRC_INTFC_INST = 83

GROUP BY
v0.SECURITY_ALIAS
) mx

WHERE
v.SECURITY_ALIAS = mx.Security_Alias AND
v.EFFECTIVE_DATE = mx.Effective_Date AND
v.SRC_INTFC_INST = 83
-------------------------------------

in, say, SQL Programmer and then adapt it to the ADP.

In regluar MS Access, you'd just create a passthrough query and paste
the SQL directly into it, and go.

With an ADP it seems like one has to choose between a humongous
in-line string and laborously putting in "_ " line continuation
strings - which makes it really time-consuming to flip-flop
back-and-forth between the SQL development tool and ADP...

Is there a better way for somebody who does not have CREATE authority
for SPs in the back end?
Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
PeteCresswell wrote:
Seems like ADP is predicated on the notion that you'd only want to use
stored procedures in the back end or in-line SQL in the front end.

Makes it really messy to develop something like
-------------------------------------
SELECT
v.SECURITY_ALIAS,
v.NEXT_REFIX_DATE AS ConversionDate,
v.RESET_DAY AS VariableRateMode,
CASE
WHEN v.RESET_DAY='MO' THEN 'Monday'
WHEN v.RESET_DAY='TU' THEN 'Tuesday'
WHEN v.RESET_DAY='WE' THEN 'Wednesday'
WHEN v.RESET_DAY='W' THEN 'Wednesday'
WHEN v.RESET_DAY='TH' THEN 'Thursday'
WHEN v.RESET_DAY='FR' THEN 'Friday'
WHEN v.RESET_DAY='F' THEN 'Friday'
WHEN v.RESET_DAY='1' THEN '1-Day'
WHEN v.RESET_DAY='7' THEN '7-Day'
WHEN v.RESET_DAY='28' THEN '28-Day'
WHEN v.RESET_DAY='35' THEN '35-Day'
WHEN v.RESET_DAY='90' THEN '90-Day'
WHEN v.RESET_DAY='D' THEN 'Daily'
WHEN v.RESET_DAY='FMO' THEN 'First of Month'
WHEN v.RESET_DAY='H' THEN 'Holiday Rules Appl'
WHEN v.RESET_DAY='MM' THEN 'Mid-Month'
WHEN v.RESET_DAY='NA' THEN Null
WHEN v.RESET_DAY='NUL' THEN Null
ELSE Null
END AS VariableRateMode_Mapped

FROM
SECURITYDBO.VARIABLE_RATE v,
(
SELECT
v0.SECURITY_ALIAS AS Security_Alias,
Max(v0.EFFECTIVE_DATE) AS Effective_Date

FROM
SECURITYDBO.VARIABLE_RATE v0

WHERE
v0.SRC_INTFC_INST = 83

GROUP BY
v0.SECURITY_ALIAS
) mx

WHERE
v.SECURITY_ALIAS = mx.Security_Alias AND
v.EFFECTIVE_DATE = mx.Effective_Date AND
v.SRC_INTFC_INST = 83
-------------------------------------

in, say, SQL Programmer and then adapt it to the ADP.

In regluar MS Access, you'd just create a passthrough query and paste
the SQL directly into it, and go.

With an ADP it seems like one has to choose between a humongous
in-line string and laborously putting in "_ " line continuation
strings - which makes it really time-consuming to flip-flop
back-and-forth between the SQL development tool and ADP...

Is there a better way for somebody who does not have CREATE authority
for SPs in the back end?


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Arvin Meyers (I believe) used to store his queries in a table and
retrieve them into a string before running. Do you have table creation
permission on the db you are working with?

Perhaps its time to talk to the DBA and get permission to do your work!
Yeah! You tell 'em!! :-)

If that fails, maybe you can create a local .mdb file that has a table
of queries, or the queries themselves if the .mdb file can be linked to
the SQL back-end. Some OpenDatabase() or Automation work to get at the
local tables/queries. Just some ideas . . . .

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQIQ9n4echKqOuFEgEQLfOwCgvT8JACkpKmJ5zfoMATm6bA Gk6bgAoOYt
JkA1hwzmFvsiwm2nV7POefKl
=jnHD
-----END PGP SIGNATURE-----

Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.