Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x

Query Syntax

48K

Solutions

19K

Contributors

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.

Share tech news, updates, or what's on your mind.

Sign up to Post

I have sql server 2016 express and the report server.  How do I specify sql server authentication in the RSReportServer.config file rather than NTLM?
0
On Demand Webinar: Networking for the Cloud Era
LVL 10
On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

I have a query, when I try to run it commenting a logic defined below, it runs fine.

But I need to include this logic and run the query, the query is taking hell lot of time. 2 hours for 1 lakh record.

PLEASE HELP ME CHAMPIONS.


SELECT
--CASE WHEN (select distinct  1
--from TBL c            
--where EXISTS (SELECT 1 FROM TBL1 leave            
--               WHERE convert(date,c1.DT) = leave.DT
--                 ))
--IS NOT NULL THEN 1 ELSE 0 END AS LEAVE

FROM (select *,
case when test= 'A' then DATEADD(hour,8,Date)  
     when test=  'B' then DATEADD(hour,2,Date)  
     when  test= 'C' then DATEADD(hour,-4,Date)
     else Date end as DT

 from STAGING c
 ) c1
0
Hi,
I want to setup SharePoint 5 server farm on Azure with SharePoint 2016, Do I need SharePoint(2016) Server CAL and User CAL for Azure?
Same thing for SQL Server, Do I need SQL Server (Per Core) license for Azure?
0
Hello,

In my following code, I am getting the error 'Invalid number of arguments':

with Combined as(
      Select staff_name Staff, Status
      from rpt_scheduled_activities
      where trunc(service_date) between '01 aug 2017' and '31 aug 2017'
      and staff_id in (39882, 41116, 45723, 19395, 19365, 63898, 48071,
      55352, 36065, 60240, 62247, 33570, 60693, 61025)
      order by 1)

select
      Staff,
      "'Kept'" as NumKept,
      "'CBT'" as NumCBT,
      "'CBC'" as NumCBC,
      "'DNS'" as NumDNS,
      cast(round((("'Kept'" / NULLIF( ("'Kept'" + "'CBT'" + "'CBC'" + "'DNS'"))*100),2), 0) as varchar(10)) || ' %' as PercentKept
from
(Select * from Combined
PIVOT(count(*) for (Status)
      IN ('Kept', 'CBT', 'CBC', 'DNS')
      ))

I figure the error is somewhere in the 'cast(round...' statement.
0
Hi All,

In a table I have a date column, I want to calculate Month and Quarter start days and End days dynamically whenever I load the table or append new records. I have attached the excel on the same. In the sample I have given a series of sorted date but in my actual, it is not sorted.
Month-Quart-Start-End.xlsx
0
Hello

I have an Order HTML page where a User can enter data into fields.  On Submit PHP file is used to write the data to SQL Tables called Orders and Orders_details.

Currently, when l test this I'm getting the below error.
Capture.JPG
Below is my PHP file:
If you need table structures I can provide these too.

The line error is highlighted below.
Capture.JPG===================

<!DOCTYPE HTML>
<html>
<head>
<title>Product_Order</title>
<meta charset="utf-8">

</head>
<body>

<?php

  /* Set oracle user login and password info */
  $dbuser = ;  
  $dbpass = ;  
  $db = ;
  $connect = oci_connect($dbuser, $dbpass, $db);

   /* Display connection error if fails */
  if (!$connect)  {
    echo "An error occurred connecting to the database";
    exit;
  }

  //Extract CGI variables
  $productitem = $_POST['productitem'] ;
  $price = $_POST['price'] ;
  $quantity = $_POST['quantity'] ;
  $firstname = $_POST['firstname'] ;
  $lastname = $_POST['lastname'] ;
  $address = $_POST['address'] ;
  $phone = $_POST['phone'] ;
  $email = $_POST['email'] ;
  $creditcard = $_POST['creditcard'] ;


  // count the record in orders table and use id number $count+1 for the new record
  $query_count = "SELECT max(ID) FROM Orders";

  echo "SQL: $query_count<br>";

  /* check the sql statement for errors and if errors report them */
  $stmt = oci_parse($connect, $query_count);

  if(!$stmt)  {
    echo "An error…
0
Hello,

I have the below search field in my HTML file.

Capture.JPG
This is connected to a PHP file on Submit.

If the User clicks on 'Search' only, without entering any data, I want all data to be displayed.
Currently, it will only display data it you enter at least one character.

Here is my SQL Select Query within my PHP file.

$query = "SELECT * FROM Products WHERE regexp_like(Title,'$book_name','i')";
0
Hi experts, How to set in sql command the table below where I wish to display by group the field name theId. Please see the table below!

Amount       Desc                                      TheId
10                 fair                                          x
12                 meals                                     y
15                 allowance                              y
15                 fuel                                         x
50                 Medicines                              x

I tried this command but it doesn't work;

Set rs = cn.Execute("Select Desc, theId, Amount from PList group by TheId")
0
i have a asp.net mvc application.

I'm using the Employees table of the SQL Server Northwind database

My table looks like this:

table
Right now my dropdownlist displays  the EmployeeID

My razor code on my view looks like this:

@Html.DropDownListFor(model => model.EmployeeID, "Please Select an Employee: ", new { @class = "employeeclass1", @id = "EmployeeDDL" })


How do i set the default value on page load for my DDL to be 0?
How do I add a "ALL" text to my DDL so if someone selects that then the selected value for that is -2.

So then the only options my DDL would have are:
"Please Select an Employee:"  and the default selected value for this would be 0
"All" and the selected value for this would be -2
The other choices they would have are EmployeeID 1 through 9 which I already have and come from my model.
0
Is the positioning of the begin try and begin transaction and end try and commit transaction correct? I ask because I sometimes get a message about transaction count being wrong with previous count being 0 and current count being 1.

CREATE PROCEDURE [dbo].[rbs_RevalidateQuote]
      @I_vQuote_Number INT,
      @I_vSequence_Number INT,
      @I_vQuote_Date DATETIME
AS
BEGIN
      
      SET NOCOUNT ON

DECLARE @TranCount int
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int

SET @TranCount = @@TRANCOUNT

IF @TranCount = 0
      BEGIN TRAN UpdateRevalidateNumber
ELSE
      SAVE TRAN UpdateRevalidateNumber

DECLARE @REVALIDATE_NUMBER INT

-- Get the current highest revalidate number
SELECT  @REVALIDATE_NUMBER = CASE WHEN MAX(REVALIDATE_NUMB_OEQH) + 1 < 9 THEN max(REVALIDATE_NUMB_OEQH) + 1 ELSE 9 END FROM CSTQUTHD WHERE QUOTE_NUMBER_OEQH=@I_vQuote_Number AND
      SEQUENCE_NUMBER_OEQH=@I_vSequence_Number

BEGIN TRY
      
              -- Update quote header
            UPDATE CSTQUTHD
            SET REVALIDATE_NUMB_OEQH = @REVALIDATE_NUMBER,DATE_OF_QUOTE_OEQH=@I_vQuote_Date,INACTIVE_OEQH=0,CST_QT_PRT_DATE_OEQH='19010101' WHERE QUOTE_NUMBER_OEQH=@I_vQUOTE_NUMBER AND
                  SEQUENCE_NUMBER_OEQH=@I_vSequence_Number

            --      Update quote lines
            UPDATE CSTQUTLN
            SET REVALIDATE_NUMB_OEQL = @REVALIDATE_NUMBER,CUST_QT_DATE_OEQL=@I_vQuote_Date WHERE QUOTE_NUMBER_OEQL=@I_vQUOTE_NUMBER AND SEQUENCE_NUMBER_OEQL=@I_vSequence_Number

            -- Update quote kit lines
            UPDATE CSTQUTKT
            SET REVALIDATE_NUMB_OEQK = …
0
Moving data to the cloud? Find out if you’re ready
LVL 3
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

I am trying to use a query in VBA Access. There is a criteria that involves a Form. As I understand it I have to use Eval for the DAO to understand the query. I have tried but I keep getting errors.

Below is the query, The Normal SQL VBA query, and what I have tried with Eval.  Thanks for the help.

Query:
SELECT SKUs.SKU, SKUs.SkuNm, Assemblies.Quantity
FROM SKUs INNER JOIN Assemblies ON SKUs.SkuID = Assemblies.ChildSkuID
WHERE (((Assemblies.SkuID)=[Forms]![frmPrintChildrenLabels]![txtParent].[value]));

Open in new window


VBA String:
sSQL = "SELECT SKUs.SKU, SKUs.SkuNm, Assemblies.Quantity" _
& " FROM SKUs INNER JOIN Assemblies ON SKUs.SkuID = Assemblies.ChildSkuID" _
& " WHERE (((Assemblies.SkuID)=[Forms]![frmPrintChildrenLabels]![txtParent].[value]));"

Open in new window


What I have tried but compile error expected end of statement:
VBA String:
sSQL = "SELECT SKUs.SKU, SKUs.SkuNm, Assemblies.Quantity" _
& " FROM SKUs INNER JOIN Assemblies ON SKUs.SkuID = Assemblies.ChildSkuID" _
& " WHERE ((([Assemblies].[SkuID])= Eval("[Forms]![frmPrintChildrenLabels]![txtParent].[value]")));"

Open in new window

0
We are having an issue on our SQL server where jobs that send out a lot of emails send some emails but not all of the them.  In going through the logs the emails are sending fine and report 'Mail successfully sent' and then all of a sudden the error comes up with 'The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2017-09-18T12:16:43). Exception Message: Cannot send mails to mail server. (The operation has timed out.).).  It seems to fail at different intervals.

On the failure emails, the last_mod_user in the sysmail_event_log is 'sa' and the account_id is NULL.  However, on the successfully sent emails, the last_mod_user is 'DOMAIN\Administrator' and the account_id is 1.  It almost appears as if the Administrator looses connection and then the mail starts failing thereafter.

The same activity seems to happen regardless of running the proc manually or having the agent job initiate it.

The mail settings are (we use AuthSMTP.com to send):

Server Name: mail.authsmtp.com
Port: 2525
SSL: required
Basic Authentication with our AuthSMTP credentials.

Database Mail Executable Minimum Lifetime (seconds) = 600

Does anyone know why this would occur?  

Thank you.
0
I am working on a .Net application that is using nHibernate and I am passing through a structured datatype into a stored procedure.

I am using an extended method like this

    public static class StructuredExtensions
    {
        private static readonly Sql2008Structured structured = new Sql2008Structured();

        public static IQuery SetStructured(this IQuery query, string name, DataTable dt)
        {
            return query.SetParameter(name, dt, structured);
        }
    }

Open in new window


The full code of this is in the attached file,

The SQL User Defined Table Type is

CREATE TYPE [dbo].[CEQBM_CEQBN_TYP] AS TABLE(
	[PRNCPL_ACC_CD] [char](10) NOT NULL,
	[BRANCH_ACC_CD] [char](10) NOT NULL,
	[COPY_MISC_DLR_FG] [bit] NOT NULL DEFAULT ((0)),
	[COPY_LABR_HRS_FG] [bit] NOT NULL DEFAULT ((0)),
	[COPY_CSUM_COST_FG] [bit] NOT NULL DEFAULT ((0)),
	[COPY_PART_PRICE_FG] [bit] NOT NULL DEFAULT ((0)),
	[COPY_TRVL_RATE_FG] [bit] NOT NULL DEFAULT ((0)),
	[COPY_DISC_FG] [bit] NOT NULL DEFAULT ((0)),
	[COPY_TAX_RATE_FG] [bit] NOT NULL DEFAULT ((0)),
	[COPY_REJ_RESN_FG] [bit] NOT NULL DEFAULT ((0)),
	[COPY_CNTR_OBLG_FG] [bit] NOT NULL DEFAULT ((0)),
	[COPY_LABR_OVRS_FG] [bit] NOT NULL DEFAULT ((0)),
	[EDIT_MISC_DLR_FG] [bit] NOT NULL DEFAULT ((0)),
	[EDIT_LABR_HRS_FG] [bit] NOT NULL DEFAULT ((0)),
	[EDIT_CSUM_COST_FG] [bit] NOT NULL DEFAULT ((0)),
	[EDIT_PART_PRICE_FG] [bit] NOT NULL DEFAULT ((0)),
	[EDIT_TRVL_RATE_FG] [bit] NOT NULL DEFAULT ((0)),
	

Open in new window

0
Hi Experts,

on my SQL AG I have some new DNS errors.
I use SQL2014 in an availabilty group.
On the Failover cluster I have  this errors, many time each day.
Can you help me out ?

Cluster network name resource 'BMD_BMD-DB' failed registration of one or more associated DNS name(s) for the following reason:
DNS operation refused.
0
hi i have upgrade the liabrary from 6i to 11g the foolowing code work in 6i but when i compile in 11g is giving error check attachment
erro0987.jpg
tpapi1Bdy.txt
tpapi1Spec.txt
0
I've tried theses code but these isn't working, please help me T_T

Dim st As String = "Update OrderItems set Item=@item,Price=@price where ID=@id"
        Dim cmd As New SqlCommand(st, con)

        Try

            If cmd.Connection.State = ConnectionState.Open Then cmd.Connection.Close()
            con.ConnectionString = connection
            cmd.Connection.Open()
            cmd.Parameters.AddWithValue("@item", txtitem.Text.Trim)
            cmd.Parameters.AddWithValue("@price", txtprice.Text.Trim)
            cmd.Parameters.AddWithValue("@id", txtid.Text.Trim)

            If cmd.ExecuteNonQuery > 0 Then
                MsgBox("Updated Successfully", MsgBoxStyle.Information)
                txtitem.Clear()
                txtprice.Clear()
                txtid.Clear()
                txtitem.Focus()

            End If

        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            cmd.Connection.Close()

        End Try
0
I am trying to add new link server to oracle db in SSMS. I've installed oracle client 12c 64 bit.
I can connect to the oracle db via SQL developer when I enter the tnsnames directory but not mgt studio, when adding a new linked server. This confirms all service names passwords and firewall rules, content of tnsname.ora are correct. I get "ORA-12154 tns could not resolve the connect identifier specified" immediately so its not finding the tnsnames.ora file.

So I have also checked the following:
I have amended the provider options to allow inprocess.

My tnanames.ora and sqlnet.ora files are in c:\oracle\product\12.2.0\client_1\network\admin\

I have names.directory_path= (Tnsnames) in my sqlnet.ora file.

If I do a tnsping tnsentry it uses the above sqlnet.ora as parameter file bit I get
tns-03505: failed to resolve name error

Ive check the path in environment variables. In regedit, the path of Inprocserver32 of the oracle clsid is c:\oracle\product\12.2.0\client_1\bin\oraOlEDB12.dll

Ive restarted SQL services.

How can I get SQL mgt studio to point.to my tnsnames file.?  Is there anywhere in regedit that tells SQL of the file location
0
I've just run a SQL command something like this and discovered  I have trailing white space in  file.path  how do I remove it?

select m.id ,m.title,m.tmdbid,m.imdbid,mlf.fileid,f.path from movie m,movielinkfile mlf,file f where m.id=mlf.movieid and mlf.fileid=f.id and  f.path like '%My Film Name%' order by m.title

Open in new window


Resulting in
--m.id ,m.title,m.tmdbid,m.imdbid,mlf.fileid,f.path
"2407","My Film Name","","","2569","Path1/to/My Film Name.mp4"
"2407","My Film Name","","","4850","Path2/to/My Film Name.mp4
" -- trailing white space
"3837","My Film Name","1234","tt123456","5318","Path2/to/My Film Name.mp4"

Open in new window


This means movie.id 3837 is a duplicate of movie.id2407 but wasn't picked up because of the trailing white space  not sure if '\n,CR or LF'

if possible before removing 3837 I'd like to grab the m.tmdbid,m.imdbid values and update 2407 if NULL  the only thing that links the 2 is "path2/to/My Film Name.mp4"

I think there is over 500 duplicates with  trailing white space every thing with a f.id >= 5216 maybe duplicate and have entries for  m.tmdbid,m.imdbid and <= 5216 may or may not have  m.tmdbid,m.imdbid values

Primarily after Removing the duplicate 3837 I then need to remove the trailing white space from f.path I can't do it before because it's indexed and won't allow duplication
0
Hi Guys

I built the below query and set it up as a Stored Procedure in SQL 2008, query:

USE [Cosmos]
GO

/****** Object:  StoredProcedure [dbo].[Tour]    Script Date: 9/16/2017 10:28:34 PM ******/

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:     
-- Create date: 
-- Description:
-- =============================================

ALTER PROCEDURE [dbo].[TourDesk] 
@TourStartDate AS DATETIME, @TourEndDate AS DATETIME

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
   SET NOCOUNT ON;
-- Query
SELECT 
	t.sale_no as [Sales Number]
	,t.trans_no as [Transaction Number]
	,t.department as [Department]
	,t.category as [Category]
	,t.item as [Product Name]
	,CAST(t.date_time as DATETIME) as [Date of Sale]
	,t.init_price as [Initial Price]
	,t.quantity as [Quantity]
	,t.disc_amt as [Discount Amount]
	,t.disc_flat as [Discount Flat]
	,t.tax_amount as [Tax Amount]
	,t.extension as [Extension]
	,t.pcsplit_1 as [Profit Center EX GST]
	,SUM(CASE WHEN t.item in ('PJB.EB.AD', 'PJB.CO.CH', 'PJB.EB.CH', 'PJB.EB.FAM', 'PJB.PM.AD', 'PJB.PM.CH', 'PJB.PM.FAM') THEN (t.quantity*(t.init_price*0.7)/1.1)
	WHEN t.item IN ('AQDUCK.FAM', 'AQDUCK.SEN', 'AQDUCK.AD', 'AQDUCK.CH') THEN (t.quantity*(t.init_price*0.7)/1.1)
	WHEN t.item in ('SC.GW.AD', 'SC.GW.CH', 'SC.GW.SEN', 'SC.HOP.AD', 'SC.HOP.CH', 'SC.HOP.SEN', 'SC.NB.AD', 'SC.NB.CH', 'SC.NB.SEN', 'SC.OT.AD', 'SC.OT.CH', 'SC.OT.SEN', 'SC.WW.AD', 'SC.WW.CH', 

Open in new window

0
Will your db performance match your db growth?
LVL 3
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Hi All

My Company is implementing SFB 2015 Ent Edition. We have 2 SQL Servers in cluster. The SQL Servers is easily getting connected through SSMS via cluster. But when I am trying to publish the topology it s giving error. Please find below the error:

****Creating DbSetupInstance for 'Microsoft.Rtc.Common.Data.XdsDatabase'****
Initializing DbSetupBase
Parsing parameters...
Found Parameter: SqlServer Value dccluster.gsi.gov.in.
Found Parameter: SqlFilePath Value C:\Program Files\Common Files\Skype for Business Server 2015\DbSetup.
Found Parameter: DatabaseType Value .
Found Parameter: FeatureName Value CentralMgmtStore.
Found Parameter: DatabaseNames Value System.Collections.Generic.List`1[System.String].
Found Parameter: Publisheracct Value GSI\RTCUniversalServerAdmins.
Found Parameter: Replicatoracct Value GSI\RTCUniversalConfigReplicator.
Found Parameter: Consumeracct Value GSI\RTCUniversalReadOnlyAdmins.
Found Parameter: Role Value master.
Trying to connect to Sql Server dccluster.gsi.gov.in. using windows authentication...
System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.InvalidOperationException: Cannot open Service Control Manager on computer 'dccluster.gsi.gov.in'. This operation might require other privileges. ---> System.ComponentModel.Win32Exception: The RPC server is unavailable

Please help to solve the error. All firewall service is disabled in all servers and we are using …
0
I am try to connect conquestdicomserver with sql Server 2008 R2 manually and also trying to editing "dicom.ini" file.. but this is not working..

Any One can help me..please.... Thanks in Advances

Here is DICOM.INI file code .

i am using window32

Sql Server Name :- GMDahri-PC
databse    Name :- conquest
login         Name :- conquest
password            :- conquest1415
sa                        :- 123




# This file contains configuration information for the DICOM server
# Do not edit unless you know what you are doing

[sscscp]
MicroPACS                = sscscp

# Network configuration: server name and TCP/IP port#
MyACRNema                = NICVDPACS
TCPPort                  = 5678

# Host(ignored), name, username and password for ODBC data source
SQLHost                  = localhost
SQLServer                = conquestpacs_s
Username                 = conquest
Password                 = conquest1415
DoubleBackSlashToDB      = 0
UseEscapeStringConstants = 0

# Configure server
ImportExportDragAndDrop  = 1
ZipTime                  = 05:
UIDPrefix                = 1.2.826.0.1.3680043.2.135.736588.40949073
EnableComputedFields     = 1

FileNameSyntax           = 4

# Configuration of compression for incoming images and archival
DroppedFileCompression   = un
IncomingCompression      = un
ArchiveCompression       = as

# For debug information
PACSName                 = NICVDPACS
OperatorConsole          = 127.0.0.1
DebugLevel…
0
Hello everyone!

I am running with TFS 2015.3, and SQL Server 2016 SP1, in separate servers.

Recently, I change our production TFS Database from 32 bit to 64 bit (for updating purposes), and I believe we loose some database configuration and permissions.

This week (after the change), we are having some issues with some Jobs, related to Warehouse and Coverage Analysis:

-Work Item Tracking Warehouse Sync
-Test Management Warehouse Sync
-Team Foundation Server Coverage Analysis

The error that is shown is:

Microsoft.TeamFoundation.Framework.Server.DatabaseOperationTimeoutException: TF246018: The database operation exceeded the timeout limit and has been cancelled. Verify that the parameters of the operation are correct. ---> System.Data.SqlClient.SqlException: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception: The wait operation timed out --- End of inner exception stack trace --- at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)…
0
I have a Wordpress Loop that Generates names from a query.  However, sometimes the name is repeated.  I only want to show the name once.  Is there anyway to limit this?

<?php
  $args = array(
                    'posts_per_page' => -1,
                    'post_type' => 'post',
                    'category_name' => $alias,
                    'meta_key'	=> 'Athlete Name',
                );
                $wp_query = new WP_Query($args);
 <ul>
                        <li class="show-all">
                            All
                        </li>
                        <?php if (have_posts()) : while (have_posts()) : the_post(); ?>
                                <?php
                                $key_name = get_post_custom_values($key = 'Athlete Name');
                                $keyname = $key_name[0];
                                ?>

       
                                <!--  START Custom Player Menu  --> 

                                <li class="<?php echo $keyname; ?>">
                                    <?php echo $keyname; ?>
                                </li>     
                                <!--  END Custom Player Menu  --> 
                                <?php
                            endwhile;
                        endif;
                        ?>
                    </ul>

Open in new window


So if that list generates a list of 10 names and John Doe appears twice, I only want that name to appear once. Is it Group BY and how do you add that into a Wordpress Query?
0
Good Afternoon

I am using an Oracle Form (10g) and I would like to return multiple records in a field rather than just returning one row.  I tried Bulk Collection but I received a client side error.
0
Hello - need assistance with writing a nested IIF statement in a MSACCESS query

Here is what I am trying to do:

If COMPONENT = MED, then I need to look at the difference between REFILLS ALLOWED and REFILLS REMAINING.  If they are equal numbers the result will be START. If they are not equal, then result should be "REFILL"

If COMPONENT IS NOT "MED", then need to look at the difference between REFILLS ALLOWED and REFILLS REMAINING.  If the difference between  REFILLS ALLOWED and REFILLS REMAINING is -1, Then  the result will be START. If the difference is more than 2, then result should be "REFILL".  

Note: REFILLS ALLOWED will always have a bigger number than Refills Remaining.

I tried.... but this does not work....
STATUS: IIf([COMPONENT]= "MED"& [REFILLS_ALLOWED] = [REFILLS_REMAINING], "START",REFILL",IIf([COMPONENT] NOT "MED" & [REFILLS_ALLOWED] = [REFILLS_REMAINING] -1, "START", IIf([COMPONENT] NOT "MED" & [REFILLS_ALLOWED] = [REFILLS_REMAINING] >-2, "REFILL",

EXAMPLE
Component    Refills Allowed         Refills Remaining             Status
   MED                    3                                    3                                START
    SOL                     3                                    2                                START
    MED                   3                                     1                                 REFILL
    MED                   2                                     0                                REFILL…
0

Query Syntax

48K

Solutions

19K

Contributors

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.