Still celebrating 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 was asked to create a query where using the same table to determine the names of the managers from the employee table and which employeeID belong to those managers.

I need the Manager Name and list of all associated employees.

What would the syntax be.

Select empId, employee name, manager name
from employeetbl
where empid = manager.
0
Learn how to optimize MySQL for your business need
LVL 3
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

I am using SQL  Server 2014,  I have two tables.   One is the staging source table that has data that was transformed from text files.  The other is a destination accounting table

If  three of the fields are the same in the staging I want to update the destination table with the amount. if they are not equal I want to insert it as a new record.  

My manager would like me to use a MERGE.

Here is the TSQL on an insert.   The  AccountNumber, TransactionDate  and  SalesRep are the columns I need to match.  If values in Staging.dbo.Reserves  is equal to Accounting.dbo.Reserves I UPDATE , if not I INSERT


 INSERT INTO Accounting.dbo.Reserves (
     AccountNumber,
     TransactionDate,
     SalesRep,
     Amount
  )
  SELECT
       AccountNumber,
       TransactionDate,
       SalesRep,         
       Amount
  FROM
     Staging.dbo.Reserves;
0
Hi Experts,

I have this bit of code:
Count(*) / Cast( SUM( Case when ActionableResult = 1 THEN 1 ELSE 0 END)  as float) *100 as PercentCompliant 

Open in new window


But that is wrong:

Example:
If 92 students takes a quiz and all fail: 92 then the PercentCompliant would be 0

92/92 = 100%
It should be:
92/92 = 0%


please help and thanks
0
Hi Experts,

I have this bit of code:
,(Select SUM( Case when ActionableResult = 1 THEN 1 ELSE 0 END ) as [Fail] FROM CTE_Compliance as di1 where di1.[Branch Region] = own.[Branch Region] ) as RegionFail
,(Select SUM( Case when ActionableResult = 1 THEN 1 ELSE 0 END ) as [Fail] FROM CTE_Compliance as di1 where di1.[Branch Region] = own.[Branch Region] and di1.[Branch Network] = own.[Branch Network] ) as NetworkFail
,(Select SUM( Case when ActionableResult = 1 THEN 1 ELSE 0 END ) as [Fail]  FROM CTE_Compliance as di1 where di1.[Branch Region] = own.[Branch Region] and di1.[Branch Network] = own.[Branch Network]  and di1.[Branch] = own.[Branch] ) as BranchFail
,(Select Cast( SUM( Case when ActionableResult = 1 THEN 1 ELSE 0 END)  as float) /Count(*) as PercentCompliant  FROM CTE_Compliance as di1 where di1.[Branch Region] = own.[Branch Region] ) as RegionPercent
,(Select Cast( SUM( Case when ActionableResult = 1 THEN 1 ELSE 0 END)  as float) /Count(*) as PercentCompliant FROM CTE_Compliance as di1 where di1.[Branch Region] = own.[Branch Region] and di1.[Branch Network] = own.[Branch Network] ) as NetworkPercent
,(Select Cast( SUM( Case when ActionableResult = 1 THEN 1 ELSE 0 END)  as float) /Count(*) as PercentCompliant FROM CTE_Compliance as di1 where di1.[Branch Region] = own.[Branch Region] and di1.[Branch Network] = own.[Branch Network]  and di1.[Branch] = own.[Branch] ) as BranchPercent
,(Select count(*) as [All] FROM CTE_Compliance as di1 where di1.[Branch 

Open in new window

0
Please help folks.

How to club this two logics together for a single field. Field name is description.

1.) case when right(rtrim(Description),1) = ',' then substring(rtrim(Description),1,len(rtrim(Description))-1)
2.) replace (replace (Description, char(10), ''), char(13), '') Description,

Please help.

Thanks in advance
0
I know I can do this in .Net but would like to deploy my solution in MS/SQL 2008 R2 as the processing needs to run every minute (or some such) 24/7/365

I have to read a SOURCE .xml file from disk and create/write to a DESTINATION .xml on disk

SOURCE.xml                 should always be a single <package_creation>.....</package_creation>
DESTINATION.xml       should always be a single <PackagingList>.....</PackagingList>


SOURCE.xml

      <?xml version="1.0" encoding="UTF-8"?>
      <packaging_creation>
            <item_code>G1-000123-2</item_code>
            <packaging_code>0000014263</packaging_code>
            <quantity>2220</quantity>
            <location>Fuori</location>
      </packaging_creation>

DESTINATION.xml

      <?xml version="1.0" encoding="utf-8" ?>
      <PackagingList ImportVersion="1.0.1.1">
            <Packaging IdPackaging="0000014263" ComponentName="G1-000123-2" OriginalQuantity="2220"  />
      </PackagingList>

(indentation added for clarity)

Mapping of data (?nodes?) is

      SOURCE                        DESTINATION

      item_code            =>  ComponentName
      packaging_code  =>  IdPackaging
      quantity               =>  OriginalQuantity

A couple of days searching with "The Microsoft Search Engine" (ie: GOOGLE) has born little I can make sense of...  I have read the SOURCE into SQL using OPENROWSET but have been unable to expose the ?nodes? and their values.

I expected this to be fairly simple (I've been wrong before) but can't seem to get it wired up.

I would appreciate an example solution - at least for …
0
I am trying to print out a date with today's date.  The result i'm looking for is 9/18/2017 23:59:59.  Or it can be 2017-09-20 23:59:59.
DECLARE @sDate AS VARCHAR(25)
SET @sDate = CONVERT(VARCHAR, GETDATE(), 101) + ' 23:59:59.999'
SELECT @sDate

DECLARE @Date AS DATETIME
SET @Date = CONVERT(DATETIME, @sDate, 102)
--SET @Date = @sDate
SELECT @Date

Open in new window

0
Hi all,
Have two tables.  Receipts and locations as below.
Can't get the for XML path to work right.

Want to pull a query such that the locations are concatenated:
RouteriD                  Location
10764                      A5, A6, A16
10765                      A5, A6, A16
10776                     OrderReceipt
.....


Tables:
CREATE TABLE [dbo].[TReceipts](
	[ReceiptID] [int] IDENTITY(1,1) NOT NULL,
	[RouterID] [int] NULL,
	[LocID] [int] NULL,
	[Receiptdate] [date] NULL,
 CONSTRAINT [PK_TReceipts] PRIMARY KEY CLUSTERED 
(
	[ReceiptID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[TLocations](
	[LocID] [int] IDENTITY(1,1) NOT NULL,
	[DisplayBoardID] [int] NULL,
	[Location] [nvarchar](100) NULL,
 CONSTRAINT [PK_TLocations] PRIMARY KEY CLUSTERED 
(
	[LocID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Open in new window


Data:
Treceipts:
ReceiptID      RouterID      LocID      Receiptdate
3359      10776      50      2017-09-18
3360      10777      50      2017-09-18
3361      10764      6      2017-09-19
3362      10765      6      2017-09-19
3363      10778      6      2017-09-19
3364      10779      6      2017-09-19
3365      10780      6      2017-09-19
3366      10781      6      2017-09-19
3367      10782      6      2017-09-19
3368      10783      6      2017-09-19
3369      10784      6      2017-09-19
3370      10785      6      2017-09-19
3371      10786      6      2017-09-19
3372      10787      6      2017-09-19
3373      10788      …
0
I have 4 tables: Distributors, States, Counties, DistibutorsCountiesLookup

I am associating distributors with counties within a state. A state may be divided between many distributors. A distributor in linked to a state/county via the stateID and CountyID. So the lookup table has DistributorID, StateID, CountyID. When I switch states using a combobox, I want to return the counties in that state that are assigned to the selected distributor or have not yet been assigned. Here are the tables.

tbl_States
StateID (PK)
State
StateAbbreviation

tbl_Counties
CountyID (PK)
StateID (FK)
County

tbl_Distibutors
DistributorID (PK)
Distributor

tbl_DistributorCountyLookup
DistributorID (FK)
StateID (FK)
CountyID (FK)


Thank You,
Randy
0
Hi,

  I need to grant the 'SELECT' privileges to a user for a View that belongs to another database, but i do not want to grant 'SELECT' to any table of that database to the user, or even any of the tables that belongs to that view.    What happens is that I only want that user to be able to 'SELECT' to that view, for specific fields that belongs to that view.

  For example.., if the views is built using TABLE A  that has 8 fields, but in the view the display is only viewing  3 fields of that table, the user must be able to SELECT  the view for only to see the fields that belong to the view, but can not see the others fields of the table, or any other table of the database.
 
  Can i do it in MS SQL Server ?

Regards,
0
The Orion Papers
The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

I am running sql server 2008 R2. Everything seems to be running just fine.  The server is running on Scale hyperconverged servers.
When I try to run the report from my own desktop using studio management, or even when I RDP into the server, it does not work.
The report is called  "all blocking transactions".
Here is the error message.
sql error
When I log into the server directly via the console, the report works fine.   All other reports work just fine from my own desktop or even via RDP, it's just the one called "all blocking transactions" that does not work.

Any idea's if this is a sql issue or some kind of network problem, but I doubt it's a network issue, as all other reports are running just fine.
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

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
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
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
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
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE
LVL 4
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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
Hi

We have SFB 2015 installed in our network. Due to some issue we have to remove the servers from network after deleting all instances of SFB and SQL from network. Now when we have reinstalled new servers and tried to publish a topology its shows an error where it is still pointing to old Server.

OLD SERVER FQDN: dclyncsql.gsi.gov.in
NEW Server: dccluster.gsi.gov.in

Please help me in how to remove instance of the old server and how to find out why it is still pointing to that database when all servers and SQL instances has been removed.

Thanks

Avijit
GSI-Error.jpg
0
Below is the sample data:
Country;     Value
AAA          10
AAA           12
AAA           14
AAA           35
AAA           72
AAA           827
AAA           992
AAA           1000
AAA           1001
AAA           1002
BBB           12
BBB          15
BBB          26
BBB           27
BBB          30
BBB           33
BBB           50
BBB           400
BBB          800

My Final table should be as below
Country     FirstQ     ThirdQ     InterQ
AAA         XXX        YYY        ZZZ
BBB         XXX        YYY        ZZZ

I got the solution partially from this site  by CYBERKIWI

;with tmp as (
      select c=COUNT(*) over (), rn=ROW_NUMBER() over (order by n), n
      from quartile_test)
,qs as (
      select
            q1=1+(c-1.0)/4, q1a=FLOOR(1+(c-1.0)/4), q1b=CEILING(1+(c-1.0)/4),
            q2=1+(c-1.0)/2, q2a=FLOOR(1+(c-1.0)/2), q2b=CEILING(1+(c-1.0)/2),
            q3=1+(c-1.0)*3/4, q3a=FLOOR(1+(c-1.0)*3/4), q3b=CEILING(1+(c-1.0)*3/4)
      from (select top 1 c from tmp) x)
select (1-q1+q1a)*MIN(n)+(q1-q1a)*MAX(n)
from tmp,qs where rn in (q1a,q1b) group by qs.q1,qs.q1a,qs.q1b
union all
select (1-q2+q2a)*MIN(n)+(q2-q2a)*MAX(n)
from tmp,qs where rn in (q2a,q2b) group by qs.q2,qs.q2a,qs.q2b
union all
select (1-q3+q3a)*MIN(n)+(q3-q3a)*MAX(n)
from tmp,qs where rn in (q3a,q3b) group by qs.q3,qs.q3a,qs.q3b
union all
select n
from tmp,qs where rn=c


Can someone help me modify this as per country?
0
I have an Access 2010 table containing 196 records where a particular field appears blank (length = 0) but must contain something as setting the filter to Is Not Null shows these "blank" records and all others.  I tried searching for Ascii characters using Asc([Fieldname]) but that returned "#Func!" which, I'm guessing, means nothing was found or it doesn't recognize the function.  I also tried Asc(Left([Fieldname],1)) and Asc(Right([Fieldname],1)) but got the same result.

I need to replace these invisible characters with null values.  To do that I first need to identify what they are.  Any suggestions on how to do this would be greatly appreciated.
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.